DBMS Lab 7

Assignment-7
Study & Implementation of

 Sub queries
 Views
SQL-UsingViews
A view is nothing more than a SQL statement that is stored in the database with an associated name. A view is
actually a composition of a table in the form of a predefined SQL query.
A view can contain all rows of a table or select rows from a table. A view can be created from one or many tables
which depends on the written SQL query to create a view.
Views, which are kind of virtual tables, allow users to do the following:
 Structure data in a way that users or classes of users find natural or intuitive.
 Restrict access to the data such that a user can see and (sometimes) modify exactly what they need and no
more.
 Summarize data from various tables which can be used to generate reports.
Creating Views:
Database views are created using the CREATE VIEW statement. Views can be created from a single table,
multiple tables, or another view.
To create a view, a user must have the appropriate system privilege according to the specific implementation.
The basic CREATE VIEW syntax is as follows:
CREATE VIEW view_name AS

SELECT column1, column2.....
FROM table_name
WHERE [condition];
You can include multiple tables in your SELECT statement in very similar way as you use them in normal SQL
SELECT query.
Example:
Consider the CUSTOMERS table having the following records:
+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
Q. 1 Execute above query to
check updated AGE of Ramesh.
+ + + + + +
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
SQL Sub
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+ + + + + +
Q .1 create a view from CUSTOMERS_VIEW from

Queries
CUSTOMER table , VIEW should contain all records with
column
Name , AGE
Q.2 Display all records from CUSTOMERS_VIEW
Updating a View:
A view can be updated under certain conditions:
 The SELECT clause may not contain the keyword

DISTINCT.
 The SELECT clause may not contain summary functions.
 The SELECT clause may not contain set functions.
 The SELECT clause may not contain set operators.
 The SELECT clause may not contain an ORDER BY

clause.
 The FROM clause may not contain multiple tables.
 The WHERE clause may not contain subqueries.
 The query may not contain GROUP BY or HAVING.
 Calculated columns may not be updated.
 All NOT NULL columns from the base table must

be included in the view in order for the INSERT
query tofunction.
So if a view satisfies all the abovementioned rules then

you can update a view. Following is an example to
SQL > UPDATE CUSTOMERS_VIEW

SET AGE = 35
WHERE name='Ramesh';
updatethe age of Ramesh:
SQL Subquery
A Subqueryor Inner query or Nested query is a query within another SQL query and embedded within the
WHERE clause.
A subquery is used to return data that will be used in the main query as a condition to further restrict the data to
be retrieved.
Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators
like =, <, >, >=, <=, IN, BETWEEN etc.
There are a few rules that subqueries must follow:
 Subqueries must be enclosed within parentheses.
 A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query
for the subquery to compare its selected columns.
 An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP
BY can be used to perform the same function as the ORDER BY in a subquery.
 Subqueries that return more than one row can only be used with multiple value operators, such as the IN
operator.
 The SELECT list cannot include any references to values that evaluate to a BLOB, ARRAY, CLOB, or
NCLOB.
 A subquery cannot be immediately enclosed in a set function.
 The BETWEEN operator cannot be used with a subquery; however, the BETWEEN operator can be used
within the subquery.
Subqueries with the SELECT Statement:

Subqueries are most frequently used with the SELECT statement. The basic syntax is as follows:
SELECT column_name [, column_name ]

FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
[WHERE])
Example:
Consider the CUSTOMERS table having the following records:
+ + + + + +
+ + + + + +
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+ + + + + +
Now, let us check the following subquery with SELECT statement:
SQL> SELECT *
FROM CUSTOMERS
WHERE ID IN (SELECT ID
FROM CUSTOMERS
WHERE SALARY > 4500) ;
This would produce the following result:
+ + + + + +
+ + + + + +
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+ + + + + +
Subqueries with the INSERT Statement:

Subqueries also can be used with INSERT statements. The INSERT statement uses the data returned from the
subquery to insert into another table. The selected data in the subquery can be modified with any of the character,
date or number functions.
The basic syntax is as follows:
INSERT INTO table_name [ (column1 [, column2 ]) ]

SELECT [ *|column1 [, column2 ]
[ WHERE VALUE OPERATOR ]
Example:
Consider a table CUSTOMERS_BKP with similar structure as CUSTOMERS table. Now to copy complete
CUSTOMERS table into CUSTOMERS_BKP, following is the syntax:
SQL> INSERT INTO CUSTOMERS_BKP

SELECT * FROM CUSTOMERS
WHERE ID IN (SELECT ID
FROM CUSTOMERS) ;
Subqueries with the UPDATE Statement:

The subquery can be used in conjunction with the UPDATE statement. Either single or multiple columns in a table
can be updated when using a subquery with the UPDATE statement.
UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
Example:
Assuming, we have CUSTOMERS_BKP table available which is backup of CUSTOMERS table.
Following example updates SALARY by 0.25 times in CUSTOMERS table for all the customers whose AGE is
greater than or equal to 27:
SQL> UPDATE CUSTOMERS

SET SALARY = SALARY * 0.25
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
WHERE AGE >= 27 );
This would impact two rows and finally CUSTOMERS table would have the following records:
+ + + + + +
+ + + + + +
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 2125.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+ + + + + +
Subqueries with the DELETE Statement:

The subquery can be used in conjunction with the DELETE statement like with any other statements mentioned
above.
DELETE FROM TABLE_NAME

[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
Example:
Assuming, we have CUSTOMERS_BKP table available which is backup of CUSTOMERS
table.
Following example deletes records from CUSTOMERS table for all the customers
whose AGE is greater than orequal to 27:
SQL> DELETE FROM CUSTOMERS

WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
WHERE AGE > 27 );
This would impact two rows and finally CUSTOMERS table would have the following
records:
+ + + + + +
+ + + + + +
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+ + + + + +
Q. Execute all above query to understand Subqueries concept In SQL .

DBMS Lab 7

Uploaded by

Copyright:

Available Formats

DBMS Lab 7

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

DBMS Lab 7

Uploaded by

Copyright:

Available Formats

Assignment-7

Study & Implementation of

The basic CREATE VIEW syntax is as follows:

CREATE VIEW view_name AS

Q .1 create a view from CUSTOMERS_VIEW from

 The SELECT clause may not contain the keyword

 The SELECT clause may not contain summary functions.

 The SELECT clause may not contain set functions.

 The SELECT clause may not contain set operators.

 The SELECT clause may not contain an ORDER BY

 The FROM clause may not contain multiple tables.

 The WHERE clause may not contain subqueries.

 The query may not contain GROUP BY or HAVING.

 Calculated columns may not be updated.

 All NOT NULL columns from the base table must

So if a view satisfies all the abovementioned rules then

SQL > UPDATE CUSTOMERS_VIEW

There are a few rules that subqueries must follow:

 Subqueries must be enclosed within parentheses.

 A subquery cannot be immediately enclosed in a set function.

Subqueries with the SELECT Statement:

SELECT column_name [, column_name ]

Now, let us check the following subquery with SELECT statement:

This would produce the following result:

Subqueries with the INSERT Statement:

The basic syntax is as follows:

INSERT INTO table_name [ (column1 [, column2 ]) ]

SQL> INSERT INTO CUSTOMERS_BKP

Subqueries with the UPDATE Statement:

The basic syntax is as follows:

SQL> UPDATE CUSTOMERS

Subqueries with the DELETE Statement:

The basic syntax is as follows:

DELETE FROM TABLE_NAME

SQL> DELETE FROM CUSTOMERS

Q. Execute all above query to understand Subqueries concept In SQL .

You might also like