Page 209 - Informatics_Practices_Fliipbook_Class12
P. 209

UNION, INTERSECTION AND DIFFERENCE IN SQL

                   You may combine the results returned by two different SELECT clauses using union, intersection and
                   set-difference operators. The tables involved in these operations must be union compatible. Let us
                   consider the problem discussed before. A company wishes to give bonuses to all employees working in
                   the Administration department or working for more than 20 hours on any project. We can answer this
                   query by writing two queries and taking the union of the results obtained on executing them.  The first
                   query finds the ID of employees working in the Administration department, and the second query finds
                   the ID of employees working for more than 20 hours on any project. Thus, we may use the  following
                   SQL statement to answer the original query:

                   (SELECT E.ID
                   FROM EMPLOYEE AS E, DEPARTMENT AS D

                   WHERE E.Dept_No = D.Dept_No AND D.Dept_Name = 'Administration')
                   UNION
                   (SELECT DISTINCT Emp_Id
                   FROM WORKS_ON
                   WHERE Hours > 20);
                   Result after executing the above statement is shown in Table 5.12.

                                                               ID
                                                              10001
                                                              10003
                                                              10008
                                                              10010
                                                              10006

                    Table 5.12: UNION for displaying the employees who are either working in the Administration department or
                                              work for more than 20 hours on any project.
                   Similarly,  you  may  use  operators  INTERSECT and  EXCEPT  for  intersection  and  set  difference,
                   respectively, depending upon the problem statement.




            a. Examples
            Let us now practice some SQL queries that retrieve data from multiple tables.

            EMPLOYEE TABLE


                                                                                  Pin_                       Dept_
               ID     FName     LName      Gender      Address          City                DOB       Salary
                                                                                  Code                         No
             10001      Raj      Reddy       M     West Godavari       Andhra    534197 1980-06-13 100000       2
                                                                      Pradesh
             10002    Dhiraj     Bora        M     Dispur, Kamrup,    Guwahati   781005 1975-09-30    85000     1
                                                   Assam
             10003   Muskan      Taneja      F     8/33, Geeta          Delhi    110031 1990-01-25 100000       2
                                                   Colony


                                                                                     SQL: Working with Two Tables  195
   204   205   206   207   208   209   210   211   212   213   214