AIM : To implement sub query

Subquery or Inner query or Nested query is a query in a query. A subquery is usually added in the WHERE Clause of the sql statement. Most of the time, a subquery is used when you know how to search for a value using a SELECT statement, but do not know the exact value.

Subqueries are an alternate way of returning data from multiple tables.

Subqueries can be used with the following sql statements along with the comparison operators like =, <, >, >=, <= etc.

  • SELECT
  • INSERT
  • UPDATE
  • DELETE

SQL SELECT Statement :-

The most commonly used SQL command is SELECT statement. The SQL SELECT statement is used to query or retrieve data from a table in the database. A query may retrieve information from specified columns or from all of the columns in the table. To create a simple SQL SELECT Statement, you must specify the column(s) name and the table name.

SELECT statement using Sub queries

Example:

Consider CUSTOMERS table

SELECT *

FROM CUSTOMERS

WHERE ID IN (SELECT ID

FROM CUSTOMERS

WHERE SALARY > 4500) ;

SQL INSERT Statement :-

The INSERT Statement is used to add new rows of data to a table.

INSERT statement using Sub queries

Example:

Consider a table CUSTOMERS_BKP with similar structure as CUSTOMERS table. Now to copy complete CUSTOMERS table into CUSTOMERS_BKP,

INSERT INTO CUSTOMERS_BKP

SELECT * FROM CUSTOMERS

WHERE ID IN (SELECT ID

FROM CUSTOMERS) ;

SQL UPDATE Statement :-

The UPDATE Statement is used to modify the existing rows in a table.

UPDATE statement using Sub queries

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 );

SQL Delete Statement :-

The DELETE Statement is used to delete rows from a table.

DELETE statement using Sub queries

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 or equal to 27:

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

Single row sub queries:

Subqueries that can return only one or zero rows to the outer statement are calledsingle-row subqueries. Single-row subqueries are subqueries used with a comparison operator in a WHERE, or HAVING clause.

Example:

The following statement retrieve the details of the employee holding the highest salary.

SQL> SELECT * FROM employee

WHERE sal = (SELECT MIN(sal) FROM employee);

Multiple row sub queries:

Subqueries that can return more than one row (but only one column) to the outer statement are calledmultiple-row subqueries. Multiple-row subqueries are subqueries used with an IN, ANY, or ALL clause.

Example:

The following statement find the employees whose salary is more than the minimum salary of the employees in any department.

SQL> SELECT name, sal FROM employee

WHERE sal > ANY (

SELECT MIN(sal)

FROM employee

GROUP BY dept_no);

Correlated sub queries:

A correlated sub query is evaluated once for each row as opposed to a normal sub query which is evaluated only once for each table.

Example:

Select all employees whose salary is less than the average of all the employee’s salaries in the same department.

Select ename, sal, deptno from emp a where a.sal< (select avg(sal) from emp b where a.deptno = b.deptno) order by deptno;

ENAME SAL DEPTNO

Sam 2450 10

Smith 1300 20

Adam 1642 30

Queries using

  1. EXIST

Exist clause is used for testing whether a sub query has any tuples in the result set or is it empty. It returns True value if the sub query is non empty else it returns False value.

Example: What kind of store is present in one or more cities?

SELECT DISTINCT store_type FROM Stores
WHERE EXISTS (SELECT * FROM Cities_Stores
WHERE Cities_Stores.store_type = Stores.store_type);

  1. NON EXIST

This clause is used for testing whether a sub query does not have any tuples in their result set.

Example: What kind of store is present in no cities?

SELECT DISTINCT store_type FROM Stores
WHERE NOT EXISTS (SELECT * FROM Cities_Stores
WHERE Cities_Stores.store_type = Stores.store_type);

  1. ALL

Syntax:

<operand> <comparison operator> ALL (<subquery>)

The word ALL, which must follow a comparison operator, means return TRUE if the comparison is TRUE for ALL of the rows that the subquery returns.

For example,

SELECT * from customer where AGE > ALL ( Select AGE from customers where salary < 7000);

  1. ANY OR SOME

Compares a value to each value in a list or returned by a query. If any of the entry matches with the value in list it will return true or it will be evaluated to false.

SELECT * from customers where AGE > ANY ( Select AGE from customers where salary > 7000);

Here initially second query will be executed and it will list all ages of customers whose salary > 7000 and then it will pass that list to ANY operator.

CONCLUSION

Thus we have successfully implement sub queries supported by DBMS.