NESTED SUBQUERY.pptx

Database System-Nested Subquery

1. NESTED SUBQUERY

2. PREPARED BY Ms.R.Ragavi -III- B.Sc - Information Technology UNDER THE GUIDANCE OF Mrs.P.Anusha M.Sc (IT)., M.Phil.,D.P.T.T .,( Ph.D )., Assistant professor, Department of Information Technology, Bon secours college for women, Thanjavur .

3. DEFINE NESTED SUBQUERY A Subquery or inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause. A Common use of subqueries is to perform test for set membership, make set comparisons, and determine set cardinality by nesting of subqueries in the WHERE clause.

4. USES 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. Subquery can be used with the SELECT , INSERT, UPDATE , and DELETE statements along with the operators like =,<,>,>=,<=, IN BETWEEN etc. Sub queries must be enclosed within parentheses.

5. A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the sub query to compare its selected columns. An ORDER BY COMMAND cannot to used in a subquery. SQL provides a mechanism for nesting subqueries.

6. CUSTOMER ID NAME AGE ADDRESS SALARY 001 ANITHA 30 CHIDAMBARAM 20,000 002 SIVAKAMI 25 KUMBAKONAM 24,000 003 ALAMELU 26 NEYVELI 26,000 004 GEETHA 45 THANJAVUR 38,000 005 RANI 35 TRICHY 20,000 006 MALATHI 36 BANGALORE 007 GOPI 29 KERALA

7. SYNTAX Select column name from table name where column name operator(select column name from table name where);

8. EXAMPLE Select * from customers where customer_id IN (select customers_id from customers where salary >=25000; Customer _id Name Age Address Salary 003 Alamelu 26 Neyveli 26000 004 Geetha 45 Thanjavur 38000