View, Sequence & Synonym in RDBMS

VIEW, SEQUENCE & SYNONYMS in RDBMS

View

A view is a logical entity also called virtual table which is created from the base table or main table.

The main objective of creating a view is to provide secure access over the main table. In some cases when admin want to show some column from the specific table, admin or database programmer can do this by creating separate table i.e. virtual table or VIEW with the same data for each user as per the requirement but Data Redundancy is the limitation of this approach.

Read-only view:

A view by which user can only view records but unable to insert, update or delete records is called read only view.

Updatable view:

A view by which user can perform DML statements like select, insert, update or delete operation is called updatable view.

A view can be read only or updatable, that can be determined by its definition.

If a view definition having complete primary key field as well as Not Null key fields then view is updatable otherwise read only.

Example-

Let us take example of each view one by one by following given steps-

1. Create a table student by given command for which you want to create a VIEW.

SQL> CREATE TABLE Student

            (

                Enroll_No int PRIMARY KEY,

                Student_Name varchar(20),

                Course varchar(10),

                Fees numeric(10,2)

            );

2. Create a Read Only VIEW named  VWR_Student by given command-

SQL>CREATE OR REPLACE VIEW  VWR_Student AS Select Student_Name, Course, Fees FROM Student;

3. Create Updatable VIEW named VWU_Student by given command-

SQL>CREATE OR REPLACE VIEW VWU_Student AS Select * FROM Student;

4. Now we can check the difference between these two views one by one by given commands-

    A. We can not perform insert operation by Read Only View because it does not have primary key. If we perform below insert command without primary key data than there will be an error.

        SQL>INSERT INTO VWR_Student VALUES('ABC', 'BCA', 12000);

        error: can not insert NULL into System.Student.Enroll_No.

    B. We can perform below insert command by Updatable View because it has primary key.

        SQL>INSERT INTO VWU_Student VALUES(1, 'ABC', 'BCA', 12000); 

 OUTPUT WINDOW of VIEW

Practical Demonstration of VIEW in Oracle

SEQUENCE

Sequence is an order set of values like 1, 3, 5, … that are generated by SQL Program in Oracle and supported by some database systems to produce unique values on requirement.

A sequence is nothing but a user-defined schema or object that automatic generates an ordered value of numeric type.

Sequences are regularly used in database programming skills now a days because of the requirement of a unique value in each row inside a table in many applications and sequences provides an easy way to generate it.

The order of numeric values are automatically generated in an ascending or descending order according to definition of Sequence with defined intervals and can be restarted when exceeds maximum value.

Example-

Let us understand Sequence in Oracle with following Steps.

1. Execute below command to create a table student for which you want to create a SEQUENCE.

SQL> CREATE TABLE Student

            (

                Enroll_No int PRIMARY KEY,

                Student_Name varchar(20),

                Course varchar(10),

                Fees numeric(10,2)

            );

2. Create a SEQUENCE named  SEQ_Student by given command-

SQL>CREATE SEQUENCE SEQ_Student

          start with 1

          increment by 1

          minvalue 0

          maxvalue 100

         cycle;

3. Insert the sequential value by nextval predefined function given below-

SQL>INSERT INTO Student VALUES(SEQ_Student.nextval, 'ABC', 'BCA', 12000); 

SQL>INSERT INTO Student VALUES(SEQ_Student.nextval, 'ABCD', 'BCOM', 14000);

OUTPUT WINDOW of Sequence

Practical Demonstration of Sequence in Oracle

SYNONYMS

The CREATE SYNONYM is a statement which allows you to create an alternative name called synonym for a database object such as a table, sequence, view, procedure and  stored function.

Example-

Let us take an Example to understand Synonyms by following steps-

1. Execute below command to create a student table.

SQL> CREATE TABLE Student

            (

                Enroll_No int PRIMARY KEY,

                Student_Name varchar(20),

                Course varchar(10),

                Fees numeric(10,2)

            );

2. Execute below command to insert a record in student table.

SQL>INSERT INTO Student VALUES(1,'abc', 'BCA', 10000);

  

3. Execute below command to create an alternate name or synonym of student table (ex. Stud).

SQL> CREATE OR REPLACE SYNONYM Stud FOR Student;

4. Execute any one of the below commands to select record from student table.

SQL>SELECT * FROM STUDENT;

                        

                        OR

SQL>SELECT * FROM STUD;

OUTPUT WINDOW of SYNONYM

Practical Demonstration of Synonym in Oracle

BCA RDBMS PRACTICAL NUMBER_7

PRATICAL NUMBER-7 

➤ Perform Different types of joins on any two tables.

Types of joins-

A. Equi Join

B. Theta Join or Non-Equi Join

C. Self Join 

D. Natural Join

E. Inner join

F. Outer Join

    1. Left Outer Join

    2. Right Outer Join

    3. Full Outer Join

G. Cross Join 

We can perform these joins by given below steps-

1. Create Student and Course table to perform join operations by given below commands-

    SQL>CREATE TABLE Student

            (

                Enroll_No int,

                Student_Name varchar(20),

                Course_ID int

            );

            Table created.

    SQL>CREATE TABLE Course

            (

                Course_ID int,

                Course_Name varchar(20),

                Course_Duration varchar(20)

            );

            Table created.

2. Insert records in above table by given below commands-

    SQL> INSERT INTO Student VALUES(101, 'Shreya', 102);

               1 row created.

    SQL> INSERT INTO Student VALUES(102, 'Tanu', 103);

               1 row created.

    SQL> INSERT INTO Student VALUES(103, 'Roshni', 100);

               1 row created.

    SQL> INSERT INTO Student VALUES(104, 'Manisha', 101);

               1 row created.

    SQL> INSERT INTO Course VALUES(101, 'BCA', 'Three Years');

               1 row created.

    SQL> INSERT INTO Course VALUES(103, 'BEd', 'Two Years');

               1 row created.

    SQL> INSERT INTO Course VALUES(108, 'BALLB', 'Five Years');

               1 row created.

3. Now check the record by below select Command-

    SQL>SELECT * FROM Student;

Enroll_No	Student_Name	Course_ID
101	Shreya	102
102	Tanu	103
103	Roshni	100
104	Manisha	101

    SQL> SELECT * FROM Course;

Course_ID	Course_Name	Course_Duration
101	BCA	Three Years
103	BEd	Two Years
108	BALLB	Five Years

4. Now we can start to perform the join operations by below commands-

    A. Equi Join-It is used to join two tables by equal to operator(=).

    SQL> SELECT Enroll_No, Student_Name, Course_Name, Course_Duration FROM Student S, Course C WHERE S.Course_ID = C.Course_ID; 

Enroll_No	Student_Name	Course_Name	Course_Duration
102	Tanu	BEd	Two Years
104	Manisha	BCA	Three Years

    B. Non-Equi Join-It is used to join two tables by Relational Operator except equal to operator.

    SQL> SELECT Enroll_No, Student_Name, Course_Name, Course_Duration FROM Student S, Course C WHERE S.Course_ID > C.Course_ID;

Enroll_No	Student_Name	Course_Name	Course_Duration
101	Shreya	BCA	Two Years
102	Tanu	BCA	Two Years

    C. Self Join- It is used to join table itself by its aliases.

    SQL> SELECT S1. Enroll_No, S2. Course_ID, S1.Student_Name FROM Student S1, Student S2 WHERE S1.Enroll_No=S2.Course_ID;

Enroll_No	Course_ID	Student_Name
102	102	Tanu
103	103	Roshni
101	101	Shreya

    D. Natural Join- It is used to join tables by common Attribute.

    SQL> SELECT * FROM Student NATURAL JOIN Course;

Course_ID	Enroll_No	Student_Name	Course_Name	Course_Duration
103	102	Tanu	BEd	Two Years
101	104	Manisha	BCA	Three Years

    E. Inner Join- It is used to join tables by common values of conditional result.

    SQL> SELECT * FROM Student INNER JOIN Course ON Student.Course_ID=Course.Course_ID;

Enroll_No	Student_Name	Course_ID	Course_ID	Course_Name	Course_Duration
102	Tanu	103	103	BEd	Two Years
104	Manisha	101	101	BCA	Three Years

    F. Outer Join- It is used to join tables by common Attribute as well as table remaining attributes based on different outer joins. Let us Discuss all three Outer Joins by an example of each. 

        1. LEFT OUTER JOIN- It is used to fetch the common result of both tables as well as complete record of Left Table and null values from Right table.

SQL> SELECT * FROM Student LEFT OUTER JOIN Course ON Student.Course_ID = Course.Course_ID;

Enroll_No	Student_Name	Course_ID	Course_ID	Course_Name	Course_Duration
104	Manisha	101	101	BCA	Three Years
102	Tanu	103	103	BEd	Two Years
103	Roshni	100
101	Shreya	102

           2. RIGHT OUTER JOIN- It is used to fetch the common result of both tables as well as complete record of Right Table and null values from left table.

SQL> SELECT * FROM Student RIGHT OUTER JOIN Course ON Student.Course_ID = Course.Course_ID;

Enroll_No	Student_Name	Course_ID	Course_ID	Course_Name	Course_Duration
102	Tanu	103	103	BEd	Two Years
104	Manisha	101	101	BCA	Three Years
			108	BALLB	Five Years

        3. FULL OUTER JOIN- It is used to fetch the common result of both tables as well as complete record of Left Table and right table with null values from both tables.

SQL> SELECT * FROM Student FULL OUTER JOIN Course ON Student.Course_ID = Course.Course_ID;

Enroll_No	Student_Name	Course_ID	Course_ID	Course_Name	Course_Duration
104	Manisha	101	101	BCA	Three Years
102	Tanu	103	103	BEd	Two Years
103	Roshni	100
101	Shreya	102
			108	BALLB	Five Years

    G. Cross Join- It is used to join tables by mapping of each row of first table to each row of second table.

    SQL> SELECT * FROM Student CROSS JOIN Course;

Enroll_No	Student_Name	Course_ID	Course_ID	Course_Name	Course_Duration
101	Shreya	102	101	BCA	Three Years
102	Tanu	103	101	BCA	Three Years
103	Roshni	100	101	BCA	Three Years
104	Manisha	101	101	BCA	Three Years
101	Shreya	102	103	BEd	Two Years
102	Tanu	103	103	BEd	Two Years
103	Roshni	100	103	BEd	Two Years
104	Manisha	101	103	BEd	Two Years
101	Shreya	102	108	BALLB	Five Years
102	Tanu	103	108	BALLB	Five Years
103	Roshni	100	108	BALLB	Five Years
104	Manisha	101	108	BALLB	Five Years

 OUTPUT WINDOWS

TABLE CREATION FOR JOIN OPERATIONS

Equi, Non-Equi, Self & Natural Join Operations

Inner Join, Outer Join (Left, Right & Full Outer Join) Operations

Cross Join Operation

BCA RDBMS PRACTICAL NUMBER_3

 ➤ On a Table Perform WHERE clause, HAVING, GROUP BY, ORDER BY, IN, NOT IN, BETWEEN.

Steps to perform the given commands one by one mentioned below-

1. Create a table Employee by given commands-

    SQL>CREATE TABLE Employee

            (

                Emp_ID int,

                Emp_Name varchar(20),

                Department varchar(10),

                Salary numeric(10,2)

            );

            Table Created.

2. Insert 5 Records in Employee Table by given commands-

    SQL> INSERT INTO Employee VALUES(1, 'Shreya', 'HR', 12000);

            1 row created.

    SQL> INSERT INTO Employee VALUES(2, 'Tanu', 'Manager', 15000);

            1 row created.

    SQL> INSERT INTO Employee VALUES(3, 'Roshni', 'Caller', 10000);

            1 row created.

    SQL> INSERT INTO Employee VALUES(4, 'Sejal', 'Caller', 10000);

            1 row created.

    SQL> INSERT INTO Employee VALUES(5, 'Manisha', 'Caller', 10000);

            1 row created.

3. Now we can see our table record by given command-

    SQL> SELECT * FROM Employee;

Emp_ID	Emp_Name	Department	Salary
1	Shreya	HR	12000
2	Tanu	Manager	15000
3	Roshni	Caller	10000
4	Sejal	Caller	10000
5	Manisha	Caller	10000

4. Now we can perform the practical on given keywords or clauses-

    A. WHERE Clause- it is used for filtering the record by conditions. 

        For an Example we can filter record of Employee whose Emp_ID is 3 by given query-

        SQL> SELECT * FROM Employee WHERE Emp_ID=3;

Emp_ID	Emp_Name	Department	Salary
3	Roshni	Caller	10000

    B. Having Clause- It is used with GROUP BY Clause for filtering the record.

        Example- we can filter record to count Employees whose department='Caller' by given query.

        SQL> SELECT Department, COUNT(*) as Total_Emp FROM Employee GROUP BY Department HAVING department='Caller';

Department	Total_Emp
Caller	3

    C. Group By Clause- It is used for grouping the data according to given attribute with Aggregate function.

        Example- We can calculate sum of salary grouping by Department by given query.

        SQL>SELECT Department, SUM(Salary) as Salary FROM Employee GROUP BY Department; 

Department	Salary
Manager	12000
Caller	30000
HR	12000

    D. Order By- it is used for fetching record according to ascending or descending column values.

        Example- we can show data in descending order of Emp_ID by given query.

        SQL> SELECT * FROM Employee ORDER BY Emp_ID DESC;

Emp_ID	Emp_Name	Department	Salary
5	Manisha	Caller	10000
4	Sejal	Caller	10000
3	Roshni	Caller	10000
2	Tanu	Manager	15000
1	Shreya	HR	12000

    E. IN - This Logical Operator is used to compare matching record from set of values which is separated by comma.

    Example- We can show data whose Emp_ID in 2,4,5 by given query.

    SQL> SELECT * FROM Employee WHERE Emp_ID IN (2,4,5);

Emp_ID	Emp_Name	Department	Salary
2	Tanu	Manager	15000
4	Sejal	Caller	10000
5	Manisha	Caller	10000

    F. NOT IN- This Logical Operator is used to compare not matching record from set of values which is separated by comma.

    Example- We can show data whose Emp_ID except 2,4,5 by given query.

    SQL> SELECT * FROM Employee WHERE Emp_ID NOT IN (2,4,5);

Emp_ID	Emp_Name	Department	Salary
1	Shreya	HR	12000
3	Roshni	Caller	10000

    G. BETWEEN- This Range Operator is used to select record from given range.

    Example- We can show data whose salary lies between 10000 to 12000 by given query.

    SQL> SELECT * FROM Employee WHERE Salary BETWEEN 10000 AND 12000;

Emp_ID	Emp_Name	Department	Salary
1	Shreya	HR	12000
3	Roshni	Caller	10000
4	Sejal	Caller	10000
5	Manisha	Caller	10000

➤ Practical Screen Shot of Oracle 10g

BCA RDBMS Practical Number 3.1 Screen Shot

BCA RDBMS Practical Number 3.2 Screen Shot

BCA RDBMS Practical Number 3.3 Screen Shot

BCA RDBMS PRACTICAL NUMBER_2

 ➤ On the Employee table use the many faces of Select Command.

Select Command can be categorized in many ways given below-

1. Select all columns data from table without condition.

2. Select specific Columns data from table without Condition.

3. Select all columns data from table with Condition.

4. Select specific Columns data from table with Condition.

Let us fire the queries of each by given below steps -

1. Create table Employee

SQL> CREATE TABLE Employee

(

    Emp_ID int,

    EName varchar(20),

    Age int,

    Department char(10)

);

        Table Created.

2. Insert record into the Employee Table

SQL> INSERT INTO Employee VALUES(1,'Tanu', 20, 'HR');

        1 Row Created.

SQL> INSERT INTO Employee VALUES(2,'Shreya', 19, 'Caller');

        1 Row Created.

SQL> INSERT INTO Employee VALUES(3,'Roshni', 26, 'Manager');

        1 Row Created.

3. Now we can apply different faces of Select Command.

    A. Select all columns data from table without condition by below query.

        SQL> SELECT * FROM Employee;

Emp_ID	EName	Age	Department
1	Tanu	20	HR
2	Shreya	19	Caller
3	Roshni	26	Manager

    B. Select specific Columns data from table without Condition by below query.

        SQL> SELECT EName, Age FROM Employee;

EName	Age
Tanu	20
Shreya	19
Roshni	26

    C. Select all columns data from table with Condition by below query.

        SQL> SELECT * FROM Employee WHERE Emp_ID=2;

Emp_ID	EName	Age	Department
2	Shreya	19	Caller

    D. Select specific Columns data from table with Condition by below query.

        SQL> SELECT EName, Age FROM Employee WHERE Emp_ID=2; 

EName	Age
Shreya	19

➤ Practical Screen Shot of Oracle 10g-

BCA RDBMS Practical Number 2.1 Screen Shot

BCA RDBMS Practical Number 2.2 Screen Shot