Scribd
Upload
10 views

Nested Queries in SQL

Nested Queries in SQL

Uploaded by

muhammadalibajwa013
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
10 views

Nested Queries in SQL

Nested Queries in SQL

Uploaded by

muhammadalibajwa013
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
You are on page 1/ 15

Database Systems

Nested Query
 A Subquery or 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.
Cont.

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.
– An ORDER BY cannot be used in a subquery, although
the main query can use an ORDER BY.
– 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:
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ] [WHERE])
Example
Cont.

SELECT * FROM CUSTOMERS


WHERE ID IN (SELECT ID FROM
CUSTOMERS WHERE SALARY > 4500) ;
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 basic syntax is as follows:
INSERT INTO table_name [ (column1 [,
column2 ]) ] SELECT [ *|column1 [,
column2 ] FROM table1 [, table2 ] [ 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:

INSERT INTO CUSTOMERS_BKP SELECT *


FROM CUSTOMERS WHERE ID IN
(SELECT ID FROM CUSTOMERS) ;
Subqueries with the UPDATE
Statement:
Following example updates SALARY by 0.25
times in CUSTOMERS table for all the
customers whose AGE is greater than or equal
to 27:
Example
 UPDATE CUSTOMERS SET SALARY = SALARY * 0.25
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
WHERE AGE >= 27 );
Subqueries with the DELETE
Statement:
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 or equal to 27:
Example

DELETE FROM CUSTOMERS WHERE AGE IN


(SELECT AGE FROM CUSTOMERS_BKP WHERE
AGE >= 27 );
GROUP BY

The SQL GROUP BY clause is used in


collaboration with the SELECT statement to
arrange identical data into groups.
The GROUP BY clause follows the WHERE
clause in a SELECT statement.
Syntax
The GROUP BY clause must follow the
conditions in the WHERE clause and can
precede the ORDER BY clause if one is used.

SELECT column1, column2


FROM table_name WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2
Example
SELECT NAME, SUM(SALARY)
FROM CUSTOMERS GROUP BY NAME;

You might also like