Question

--Know how to nest subqueries: in, not in, some, all, and exists. Know how to nest queries in the from clause. Be able to use the with clause. Be able to use scalar subqueries.
--Know how to use correlation variables in subqueries.
--Know how to use case in queries.
--Know how to insert, delete, and update using subqueries.

Study guide please give examples

Answer 1

Nested subqueries: A subquery can be nested inside other subqueries. SQL has an ability to nest queries within one another. A subquery is a SELECT statement that is nested within another SELECT statement and which return intermediate results. SQL executes innermost subquery first, then next level.

Subqueries IN and NOT IN :

ContactTitle IN ('CEO', 'Owner', 'President')

SELECT CompanyName, ContactName, ContactTitle
FROM Customers
WHERE ContactTitle IN ('CEO', 'Owner', 'President');

Exist and Not Exists:

WHERE EXISTS (sub query)

SELECT SalesOrderID,
       RevisionNumber,
       OrderDate
FROM   Sales.SalesOrderHeader
WHERE  EXISTS (SELECT 1
               FROM   sales.SalesPerson
               WHERE  SalesYTD > 3000000
                      AND SalesOrderHeader.SalesPersonID 
                        = Sales.SalesPerson.BusinessEntityID)

When this SQL executes the following comparisons are made:

1. The WHERE clause returns all records where the EXISTS clause is TRUE.
2. The EXIST clause uses a correlated subquery. The outer query is correlated to the inner query by SalesPersonID.
3. Only SalesPersons with SalesYTD greater than three million are included in the results.
4. The EXISTS clause returns TRUE if one or more rows are returned by the subquery.

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 ]
   FROM table1 [, table2 ]
   [ WHERE VALUE OPERATOR ]

Example

Consider a table CUSTOMERS_BKP with similar structure as CUSTOMERS table. Now to copy the complete CUSTOMERS table into the CUSTOMERS_BKP table, you can use the following 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.

The basic syntax is as follows.

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. The following example updates SALARY by 0.25 times in the 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 );

Subqueries with the DELETE Statement

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

The basic syntax is as follows.

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

Example

Assuming, we have a CUSTOMERS_BKP table available which is a backup of the CUSTOMERS table. The following example deletes the records from the CUSTOMERS table for all the customers whose AGE is greater than or equal to 27.

SQL> DELETE FROM CUSTOMERS
   WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
      WHERE AGE >= 27 );