Nested Queries (Sub Queries)

1. Nested Queries (Sub Queries) • A nested query is a form of a SELECT command that appears inside another SQL statement. • It is also termed as subquery. • The SELECT command containing a subquery are referred as parent statement. The row returned by the subquery are used by the parent statement. Use of Nested Query: • Develop complex commands (query) • Define the sets of rows to be inserted into the target table of INSERT or CREATE table command or CREATE VIEW Ex :for INSERT Insertion with subquery SQL> INSERT INTO S_EMP SELECT * FROM EMP WHERE dept = ‘Sales’ Ex : for CREATE table (To remove particular column , delete sex field from emp table)

2. SQL> CREATE table t_emp as ( SELECT eno,ename,dept,post,basic from emp); • SQL> DROP table emp; • SQL> RENAME t_emp to emp; • To define one or more values to be assigned to existing rows in an UPDATE command. • To provide values for conditions in WHERE, HAVING clauses of SELECT, UPDATE and DELETE command.

4. Using Sub query in SELECT command Ex:1 To find all employees who have the same post as Raj. ordinary way step 1: SQL> select post from emp where ename = ‘Raj’; output - Manager step 2: SQL> select * from emp where post = ‘Manager; output -111 kumar Sales Manager 16000 -222 raj Marketing Manager 15000 Sub query way SQL> select * from emp where post = ( select post from emp where ename = ‘Raj’);

5. Subquery returning more than one column: Ex: To find all employees who have same post and basic as ‘Raj’ SQL> select * from emp where (post,basic) = (select post,basic from emp where ename = ‘Raj’; output: 444 Raj Marketing Manager 15000 555 Muniya Purchase Manager 15000 Subquery returning more than one row: (Ex : ANY operator) Ex: To find all employees who earn more than any employee in Purchase department SQL> select * from emp where basic > ANY (select basic from emp where dept = ‘Purchase’; output:

6. (Ex : ALL operator) Ex: To find all employees who earn more than all employee in Purchase department SQL>select * from emp where basic > ALL (select basic from emp where dept = ‘Purchase’; output: Multiple Subqueries: The WHERE clause of a query may contain any combination of ordinary conditions and subqueries with the help of AND or OR operator. Ex : To list all employees with either the same department as ‘Kumar’ or a basic grater than or equal to ‘Raj’ select * from emp where dept = (select dept from emp where ename = ‘Kumar’) or basic >= (select basic from emp where ename = ‘RAj’); Outout:

7. Correlated Subquery: In the ordinary subquery ( previous examples) , each subquery was executed once and the resulting value was used by the WHERE clause of the main query. We can also frame a subquery that can be executed repeatedly once for each candidate in each through the main query, Which is called correlated subquery. Ex: To find employees who earn more then the average salary in their own department. SQL> select * from emp X where basic > (select avg(sal) from emp where X.dept = dept); Output: 111 Kumar Sales Manager 16000 555 Muniya Purchase Manager 15000 666 Omna Sales Asst.Manager 12000

8. Joining Data from multiple tables • Rows in one table may be joined to rows in another by common value in corresponding columns. • The ability of the relational ‘join’ operator is an important feature of relational systems. • A join makes it possible to select data from more than one table by means of a single statement. • The joining of table is done in SQL by specifying the table to be joined in the FROM clause of the SELECT command using WHERE conditions. Basic Syntax: SELECT columns FROM table1, table2, …… WHERE <conditions>;

9. Ex: Consider two tables such as EMP and SALARY

10. SALARY

11. Ex: To list all employee’s personal and salary details from the table EMP and SALARY. SQL> SELECT x.eno,x.ename,x.dept,x.post,y.basic,y.da,y.hra,y.ma,y.pf FROM emp x, salary y WHERE x.eno = y.eno; OR SQL> SELECT emp.eno,emp.ename,emp.dept,emp.post,salary.basic, salary.da,salary.hra,salary.ma,salary.pf FROM emp , salary WHERE emp.eno = salary.eno; ENO ENAME DEPT POST BASIC DA HRA MA PF -------- ---------- --------- -------- --------- ----- -------- ----- --- 111 Kumar Sales Manager 16000 8000 700 100 1200 222 Umar Purchase Account 7000 3500 500 50 800 333 Vino Sales Clerk 5000 2500 400 40 700 444 Raj Marketing Manager 15000 7500 700 100 1200 555 Muniya Purchase Manager 15000 7500 700 100 1200 • Omna Sales As.manager 12000 6000 600 40 700

12. Types of Join Three types, such as • Equijoin • Non-equijoin • Outer join Equijoin When we join two or more tables using the relational operator = (equal) in the join condition, then it is Equijoin. In joining tables, a join condition that specifies a relationship = ( equal) is called Equijoin. Ex: Above example, The type of join in the above example is termed as equijoin because the comparison operator in the join condition is = (equal) SQL> SELECT x.eno,x.ename,x.dept,x.post,y.basic,y.da,y.hra,y.ma,y.pf FROM emp x, salary y WHERE x.eno = y.eno;

13. Non – Equijoin When we join two or more tables using the relational operator except = (equal) in the join condition, then it is Non -Equijoin. In joining tables, a join condition that specifies a relationship other than = ( equal) is called Non - Equijoin. Ex: To find the salaries and jobs of employees who earn more than employee no 666 (Omna) SQL> SELECT x.eno,x.basic,x.da,x.hra,x.ma,x.pf FROM salary x, salary y WHERE x.basic > y.basic AND y.eno = 666; ENO BASIC DA HRA MA PF --- --------- --------- --------- --------- --------- 111 16000 8000 700 100 1200 444 15000 7500 700 100 1200 555 15000 7500 700 100 1200

14. Self – Join Note that in the above example , the same table has been joined to achieve the required result such type of join statement involving the joining of the same table is termed as self-join. Outer – Join Suppose one or more rows in one of the table does not satisfy the join condition, that rows ordinarily will not appear in the query’s result. If we still wish to display those rows (not satisfied rows) , then use Outer-Join operator. ie (+)  a plus enclosed in parenthesis

15. Ex:To list all employee’s personal and salary details from the table EMP and SALARY SQL> SELECT x.eno,x.ename,x.dept,x.post,y.basic,y.da,y.hra,y.ma,y.pf FROM emp x, salary y WHERE x.eno = y.eno(+); ENO ENAME DEPT POST BASIC DA HRA MA PF -------- ---------- --------- -------- --------- ----- -------- ----- --- 111 Kumar Sales Manager 16000 8000 700 100 1200 222 Umar Purchase Account 7000 3500 500 50 800 333 Vino Sales Clerk 5000 2500 400 40 700 444 Raj Marketing Manager 15000 7500 700 100 1200 555 Muniya Purchase Manager 15000 7500 700 100 1200 • Omna Sales As.manager 12000 6000 600 40 700 888 Jeeva Marketing Clerk

16. SQL> SELECT x.eno,x.ename,x.dept,x.post,y.basic,y.da,y.hra,y.ma,y.pf FROM emp x, salary y WHERE x.eno(+) = y.eno; ENO ENAME DEPT POST BASIC DA HRA MA PF -------- ---------- --------- -------- --------- ----- -------- ----- --- 111 Kumar Sales Manager 16000 8000 700 100 1200 222 Umar Purchase Account 7000 3500 500 50 800 333 Vino Sales Clerk 5000 2500 400 40 700 444 Raj Marketing Manager 15000 7500 700 100 1200 555 Muniya Purchase Manager 15000 7500 700 100 1200 • Omna Sales As.manager 12000 6000 600 40 700 10000 5000 600 40 700