Page 213 - Informatics_Practices_Fliipbook_Class12
P. 213

Query 8: Retrieve the department name, location, number of employees, and the average salary for all employees in
            that department.
            Answer:

            SELECT Dept_Name, Location, COUNT(ID) AS "NoofEmployees", AVG(Salary) AS "AverageSalary"
            FROM EMPLOYEE AS E, DEPARTMENT AS D
            WHERE (E.Dept_No = D.Dept_No)
            GROUP BY Dept_Name;
            Output (Table 5.20):

                                  Dept_Name      Location     NoofEmployees    Average Salary
                                  Account        Noida        2                92500.0000
                                  Adminstration  Delhi        3                90000.0000
                                  Automobile     Jamshedpur   3                73333.3333
                                  Textile        Mumbai       2                60000.0000
              Table 5.20: Department name, location, number of employees, and the average salary for all employees in that department.

            b. EQUI JOIN vs NATURAL JOIN

            Suppose we wish to retrieve the details of all the employees along with their department details. The following SQL
            query joins the tables EMPLOYEE and DEPARTMENT to achieve this:

            SELECT *
            FROM DEPARTMENT, EMPLOYEE
            WHERE EMPLOYEE.Dept_No = DEPARTMENT.Dept_No;
            Note that the result of executing the above query yields the column Dept_No twice, once for each table (Table 5.21).
            As the above query joins only those tuples which have identical values of Dept_No, such a join is called equijoin.



                   Equijoin is a type of join operation in relational database systems that combines tables based on matching values
                   in specified columns. In an equijoin, the join condition is an equality comparison between corresponding columns
                   in the joined tables.


                                                             Pin_                   Dept_
            ID   FName LName    Gender Address     City      Code   DOB      Salary   No  Dept_Name   Location  Mgr_Id
            10001 Raj  Reddy      M   West Godavari  Andhra   534197 1980-06-13 100000  2  Administration  Delhi  10005
                                                   Pradesh
            10002 Dhiraj  Bora    M   Dispur, Kamrup,  Guwahati  781005 1975-09-30 85000  1  Account   Noida   10002
                                      Assam
            10003 Muskan Taneja   F   8/33, Geeta   Delhi    110031 1990-01-25 100000  2  Administration  Delhi  10005
                                      Colony
            10004 Hiten   Oberoi  M   15, Dimna Road,  Jamshedpur 831018 1985-06-24 100000  4  Automobile  Jamshedpur 10007
                                      Mango
            10005 Anshul  Verma   M   House 10, Sector  Noida  201304 1990-01-01 100000  1  Accounts   Noida   10002
                                      16, Gautum Budh
                                      Nagar
            10006 Rajit  Gadh     M   12, Beldih   Jamshedpur 831001 1960-05-07 60000  4  Automobile  Jamshedpur 10007
                                      Triangle, Bistupur
            10007 Taran  Adarsh   M   B-76, CST Road,  Mumbai  400098 1965-01-13 70000  5  Textile     Mumbai  10004
                                      Kalina, Santacruz
                                      East
            10008 Naval   Dhingra  M  E-14 Vivek    Delhi    110095 1975-08-04 70000  2   Administration   Delhi  10005
                                      Vihar
            10009 Naveen  Basra   F   28, Aambagan   Jamshedpur 831001 1980-09-24 60000  4  Automobile  Jamshedpur 10007
                                      Road, Sakechi
            10010 Savita  Ambedkar  F  C-49, G-Block,   Mumbai  400051 1987-07-11 50000  5  Textile    Mumbai  10004
                                      Bandra Kurla,
                                      Bandra East
                                       Table 5.21: EQUIJOIN of EMPLOYEE and DEPARTMENT
                                                                                     SQL: Working with Two Tables  199
   208   209   210   211   212   213   214   215   216   217   218