Page 211 - Informatics_Practices_Fliipbook_Class12
P. 211

Solution:

            SELECT SUM(Salary), MAX(Salary), MIN(Salary), AVG(Salary)
            FROM EMPLOYEE AS E, DEPARTMENT AS D
            WHERE E.Dept_No = D.Dept_No AND Dept_Name = 'Accounts';
            Output (Table 5.14):


                                      SUM(Salary)    MAX(Salary)   MIN(Salary)   AVG(Salary)
                                        185000         100000         85000      92500.0000
               Table 5.14: Sum of the salaries, maximum salary, minimum salary, and the average salary of all employees of the Accounts
                                                           department.
            Query 3: Find the number of employees in the Administration department.

            Answer:
            SELECT COUNT(*) AS NumOfEmployees

            FROM EMPLOYEE AS E, DEPARTMENT AS D
            WHERE E.Dept_No = D.Dept_No AND Dept_Name = 'Administration';
            Output (Table 5.15):


                                                        NumOfEmployees
                                                               3

                                    Table 5.15: Number of employees in the Administration department.
            Query 4: Retrieve the names of all the departments and their managers' names.

            Answer:
            SELECT Dept_Name, Fname, LName
            FROM EMPLOYEE AS E, DEPARTMENT AS D

            WHERE E.ID = D.Mgr_Id;
            Output (Table 5.16):


                                                Dept_Name       FName     LName
                                               Account         Dhiraj     Bora

                                               Administration  Anshul     Verma
                                               Home Goods      Muskan     Taneja

                                               Automobile      Hiten      Oberoi
                                               Textile         Taran      Adarsh
                                    Table 5.16: Names of all the departments and their manager's name.

            Query 5: Retrieve the names of all employees who work in the department located in the same city where they live.
            Answer:

            SELECT FName, LName
            FROM EMPLOYEE AS E, DEPARTMENT AS D
            WHERE (E.Dept_No = D.Dept_No) AND (E.City = D.Location);




                                                                                     SQL: Working with Two Tables  197
   206   207   208   209   210   211   212   213   214   215   216