Sub Queries in DBMS.
Sub Query: Query inside the query is called Sub Query. It is also known as Nested Query.
Following are the four major types of Sub Query.
- Sub Query with Complex test.
- Sub Query with Quantified test.
- Sub Query with Membership test.
- Sub Query with Existence test.
Sub Query with Complex test:
This type of sub query is written with the relational operators and its syntax is as follows:
SELECT COLUMN_NAME
FROM TABLE_NAME
WHERE COLUMN_NAME
relational operator
(QUERY2)
In the above syntax the outer and inner query can be of any complexity and this sub query is also can written in having clause of main query.
In such a query, inner query will be if output as a single row which will be consider for running outer query.
Examples:
Q1. Write a sub query to find out all the order with amounts more than average order amount.
syntax:
SELECT ORDER_ID, ORDER_DATE, AMOUNT
FROM ORDERS
WHERE AMOUNT > (SELECT AVG(AMOUNT FROM ORDERS));
(This is bottom up approach)
Q2. List employee with their sales, quota , date of join, date of birth with sales below 50% of avg amount.
syntax:
SELECT SALES, QUOTA, DOB, DOJ
FROM SALESREP
WHERE SALES > 0.5*
(SELECT AVG(SALES) FROM SALESREP);
# Related Sub Query: Such a query also known as sub query with outer referenced where the inner query will have join condition that will refers the table of outer join.
Sub Query with Quantified test:
Such a sub query is written with relational operators along with keywords "any" or "all". In this type of sub query inner query can give multiple rows as output.
Syntax:
SELECT COLUMN_NAME
FROM TABLE_NAME
WHERE COLUMN_NAME
relational operator
ANY/ALL (QUERY);
Examples:
Q1. Write a sub-query to list employee if they have sales which is less than any orders amount in the orders table.
syntax:
SELECT EMP_ID, NAME, SALES
FROM SALESREP
WHERE SALES < ANY(SELECT AMOUNT FROM ORDERS);
Q2. List employees with quota having sales more than orders amount in the orders table.
syntax:
SELECT EMP_ID, NAME, SALES
FROM SALESREP
WHERE SALES >
ALL (SELECT AMOUNT FROM ORDERS);
Sub Query with Membership test:
Such a query can be written with set membership operators "in" and "not in". For such a sub query, inner query can give multiple rows as output.
It syntax is given as follows:
SELECT COLUMN_NAME
FROM TABLE_NAME
WHERE COLUMN_NAME
in/ not in
(QUERY2);
Example:
Q1. Write a sub-query to find out employees who have accepted an order of amount more than 20000.
syntax:
SELECT EMP_ID, NAME
FROM SALESREP
WHERE EMP_ID
IN (SELECT REP FROM ORDERS WHERE AMOUNT > 20000);
# in case NOT IN
SELECT EMP_ID NAME
FROM SALESREP
WHERE EMP_ID
NOT IN (SELECT REP FROM ORDER WHERE AMOUNT < 20000);
Sub Query with Exist test:
Such type of sub-query is written with exist and not exist. Sub-query with existent test in special type of sub query where if inner query return any row, it output will be true, otherwise false.
If inner query output is true than outer query will executed otherwise output query will directly display result as no rows selected.
This condition is applicable in exist keyword and exactly reverse will happen in case of not exist.
It syntax is given as follows:
SELECT COLUMN_NAME
FROM TABLE_NAME
WHERE EXIST / NOT EXIST
(QUERY2);
A sub-query with exist test is always have an outer reference. Since inner query return the output as true or false, inner query can directly written as select (*) along with outer reference.
Example:
Q1. List employee if they are servicing a customer with credit limit above 20000.
syntax:
SELECT EMP_ID, NAME
FROM SALESREP
WHER EXIST
(SELECT * FROM CUSTOMER WHERE REP=EMP_ID AND
CREDIT_LIMIT > 20000);
Nested Sub-Query:
A query with in a sub-query is known as Nested Sub-Query. As we have different types of sub-query according we have different types of Nested-Sub-Query.
Examples:
Q1. List employee if they have accepted an order for which the order amount is more than inventory values of all the products.
syntax:
SELECT EMP_ID, NAME
FROM SALESREP
WHERE EMP_ID
IN (SELECT REP FROM ORDERS WHERE AMOUNT > ALL
(SELECTED PRICE * QTY FROM PRODUCTS));
Comments
Post a Comment