Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

sql

VIEWS in Oracle SQL – PLSQL

by

A VIEW in Oracle SQL / PLSQL is like a virtual table, there is no physical existence of a view. A VIEW can be created using a SELECT statement by forming a valid SQL query.

Syntax for creation of a VIEW in Oracle SQL / PLSQL is :

CREATE VIEW view_name
AS
SELECT column(s)
FROM table_name(s)
WHERE condition(s);

Let’s take an example for understanding
Suppose have a table named ‘employee’ in the database as shown below.

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Scenario 1:

Creating a VIEW

Suppose we want to create a view so that we can see the data only for those employees who are having ‘salary’ > 25000, we can achieve the same as:

CREATE VIEW employee_view
AS
SELECT *
FROM employee
WHERE salary > 25000;

The above query will create a view named as ‘employee_view’. If we query the ‘employee_view’ as:

SELECT *
FROM employee_view;

We will get the following result:

Employee_Id Employee_Name Salary Department Commission
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Here we can understand that we have created a view named ‘employee_view’ based on ‘employee’ table which shows us the records of employees having ‘salary’ > 25000 using CREATE VIEW and SELECT statement.

Scenario 2:

Updating an already existing view:

Syntax for updating a view is:

CREATE OR REPLACE VIEW view_name
AS
SELECT column(s)
FROM table(s)
WHERE condition(s);

Suppose we want to update the view named ‘employee_view’ created in the previous scenario so that the ‘employee_view’ can display the records of employees having ‘salary’ < 30000.

The same can be achieved as:

CREATE OR REPLACE VIEW employee_view
AS
SELECT *
FROM employee
WHERE salary < 30000;

The above statement will alter or change the definition of ‘employee_view’ so that it will show record of employees from ‘employee’ table having ‘salary’ < 30000.

If we query the ‘employee_view’ as:

SELECT *
FROM employee_view;

We will get the following result:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20

Here we can see that the view definition for ‘employee_view’ has been updated and the view is now displaying the records of employees having salary < 30000.

Scenario 3:

Deleting or dropping a view

The syntax for deleting or dropping view is

DROP VIEW view_name;

E.g.

DROP VIEW employee_view;

The above statement will delete or drop the definition of ‘employee_view’ from the database.

Important Points about VIEWS in SQL / PLSQL:

  1. The data in the VIEWS can be updated if the user has the privileges for doing the same.
  2. If the data in the VIEW is updated, it updates the data in the tables on which the VIEW  is based upon.
  3. If the table (on which the view is based) is dropped or deleted from the database the definition if the view still remains i.e. the VIEW continues to exist, but if we query the view we will get a message that VIEW has errors.
  4. If we create the base table again the VIEW become alright as it was before the deletion if the base table.

MINUS in Oracle SQL – PLSQL

by

A MINUS in Oracle SQL / PLSQL query is used to join or combine results from 2 or more SELECT statements, a MINUS returns only those rows which are found in the first SELECT statement but are not found in the second SELECT statement.

Also, each SELECT statement must have the same number of column(s)/field(s) with similar data-types for the MINUS to work.

Syntax for MINUS in Oracle SQL / PLSQL is:

SELECT column_name1
,column_name2
.
.
,column_nameN
FROM table_name(s)
MINUS
SELECT column_name1
,column_name2
.
.
,column_nameN
FROM table_name(s);

Suppose have a table named ‘employee’ in the database as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Also we have one more table ‘comm’ as shown below:

Emp_Id Commission_Percent
102 20
103 20
104
105 10
106 15
107 25

Let’s take an example to understand MINUS, suppose we write our SQL query using MINUS as:

SELECT employee_id
FROM employee
MINUS
SELECT emp_id
FROM comm;

The result of the above query will be:

Employee_Id
101

Here we can see that the records for ‘employee_id’ = ‘102’,’103,’104’ and ‘105’ do not appear in the result set because they are present in both (‘employee’ and ‘comm’) tables.

The record for ‘employee_id’ = ‘101’ is present in employee table but is not present in ‘comm’ table and hence is placed in the result.

Suppose we change our SQL query for MINUS as:

SELECT emp_id
FROM comm
MINUS
SELECT employee_id
FROM employee;

The result of the above query will be:

Emp_Id
106
107

Here we can see that the records for ‘employee_id’ = ‘102’,’103,’104’ and ‘105’ do not appear in the result set because they are present in both (‘employee’ and ‘comm’) tables.
The record for ‘employee_id’ = ‘101’ is present in employee table(second table) but is not present in ‘comm’ table (first table)and hence is not placed in the result.

INTERSECT in Oracle SQL – PLSQL

by

An INTERSECT in Oracle SQL / PLSQL query is used to join or combine results from 2 or more SELECT statements, an INTERSECT fetches only the common rows returned by SQL SELECT statements.
Also, each SELECT statement must have the same number of column(s)/field(s) with similar data-types for the INTERSECT to work.

Syntax for INTERSECT in Oracle SQL / PLSQL is:
SELECT column_name1
,column_name2
.
.
,column_nameN
FROM table_name(s)
INTERSECT
SELECT column_name1
,column_name2
.
.
,column_nameN
FROM table_name(s);

Suppose have a table named ‘employee’ in the database as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Also we have one more table ‘comm’ as shown below:

Emp_Id Commission_Percent
102 20
103 20
104
105 10
106 15
107 25

Let’s take an example to understand INTERSECT, suppose we write our SQL query using INTERSECT as:

SELECT employee_id
FROM employee
INTERSECT
SELECT emp_id
FROM comm;

The result of the above query will be:

Employee_Id
102
103
104
105

Here we can see that the records for employee_id = ‘102’,’103,’104’ and ‘105’ only appear in the result set because they are present in both (‘employee’ and ‘comm’) tables.

UNION ALL in Oracle SQL – PLSQL

by

As the name suggests UNION ALL in Oracle SQL / PLSQL query is used to join or combine results from 2 or more SELECT statements, a UNION ALL does not excludes the duplicate rows returned by SQL SELECT statements.
Also, each SELECT statement must have the same number of column(s)/field(s) with similar data-types for the UNION ALL to work.

Syntax for UNION ALL in Oracle SQL / PLSQL is:

SELECT column_name1
,column_name2
.
.
,column_nameN
FROM table_name(s)
UNION ALL
SELECT column_name1
,column_name2
.
.
,column_nameN
FROM table_name(s);

Suppose have a table named ‘employee’ in the database as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Also we have one more table ‘comm’ as shown below:

Emp_Id Commission_Percent
102 20
103 20
104
105 10
106 15
107 25

Let’s take an example to understand UNION ALL, suppose we write our SQL query using UNION ALL as:

SELECT employee_id
FROM employee
UNION ALL
SELECT emp_id
FROM comm;

The result of the above query will be:

Emp_Id
101
102
102
103
103
104
104
105
105
106
107

Here we can see that the records for employee_id = ‘102’,’103,’104’ and ‘105’ appear twice in the result set because they are present in both (‘employee’ and ‘comm’) tables. Also note that all the ‘employee_id’ from both ‘employee’ and ‘comm’ tables are present in the result set.

UNION in Oracle SQL – PLSQL

by

As the name suggests UNION in Oracle SQL / PLSQL query is used to join or combine results from 2 or more SELECT statements, a UNION excludes the duplicate rows returned by SQL SELECT statements.

Also, each SELECT statement must have the same number of column(s)/field(s) with similar data-types for UNION to work.

Syntax for UNION in Oracle SQL / PLSQL is:

SELECT column_name1
,column_name2
.
.
,column_nameN
FROM table_name(s)
UNION
SELECT column_name1
,column_name2
.
.
,column_nameN
FROM table_name(s);

Suppose have a table named ‘employee’ in the database as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Also we have one more table ‘comm’ as shown below:

Emp_Id Commission_Percent
102 20
103 20
104
105 10
106 15
107 25

Let’s take an example to understand UNION , suppose we write our SQL query using UNION as:

SELECT employee_id
FROM employee
UNION
SELECT emp_id
FROM comm;

The result of the above query will be:

Employee_Id
101
102
103
104
105
106
107

Here we can see that the records for employee_id = ‘102’,’103,’104’ and ‘105’ appear only once in the result set thought they are present in ‘employee’ and ‘comm’ tables. Also note that all the ‘employee_id’ from both ‘employee’ and ‘comm’ tables are present in the result set.