Write SQL queries for sub queries, nested queries
A Subquery or Inner query or a 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 command cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY command 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
|
Syntax : SELECT column_name FROM table_name
WHERE column_name expression operator
( SELECT column_name from table_name WHERE ... );
|
Table STUDENT-
Example
| select *from student where class in(select class from student where class="XII"); |
Output
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.
|
Syntax : INSERT INTO table_name (column1, column2, column3....)
SELECT * FROM table_name WHERE VALUE OPERATOR
|
Example
| insert into student select*from student where id in (select id from student); |
Output
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.
|
Syntax : UPDATE table SET column_name = new_value WHERE
VALUE OPERATOR (SELECT COLUMN_NAME FROM TABLE_NAME
WHERE condition);
|
Consider the student and college table... i have joined both tables
Example
|
UPDATE student SET marks=marks*0.25 WHERE id IN (SELECT id FROM college WHERE id >= 104);
|
Output
Subqueries with the DELETE Statement
The subquery can be used in conjunction with the DELETE statement like with any other statements mentioned above.
|
Syntax : DELETE FROM TABLE_NAME WHERE VALUE OPERATOR
(SELECT COLUMN_NAME
FROM TABLE_NAME WHERE condition);
|
Example
|
delete from college where clg_id in(select clg_id from student where clg_id >= 105);
|
Output
For more Uttrakhand Technical University(UTU) CSE-IV Sem Database Management System Lab Experiments Click here
| 
| 
