Page 210 - Informatics_Practices_Fliipbook_Class12
P. 210

10004     Hiten     Oberoi      M      15, Dimna Road,  Jamshedpur 831018 1985-06-24 100000        4
                                                Mango

         10005    Anshul     Verma       M      House 10, Sector    Noida    201304 1990-01-01 100000       1
                                                16, Gautum Budh
                                                Nagar
         10006     Rajit      Gadh        F     12, Beldih       Jamshedpur 831001 1960-05-07     60000     4
                                                Triangle, Bistupur
         10007     Taran     Adarsh      M      B-76, CST Road,   Mumbai     400098 1965-01-13    70000     5
                                                Kalina, Santacruz
                                                East
         10008     Naval    Dhingra      M      E-14 Vivek Vihar    Delhi    110095 1975-08-04    70000     2
         10009    Naveen     Basra        F     28, Aambagan     Jamshedpur 831001 1980-09-24     60000     4
                                                Road, Sakechi
         10010     Savita   Ambedkar      F     C-49, G-Block,    Mumbai     400051 1987-07-11    50000     5
                                                Bandra Kurla,
                                                Bandra East


        DEPARTMENT


                             Dept_No      Dept_Name          Location           Mgr_Id
                             1          Accounts         Noida             10002

                             2          Administration   Delhi             10005
                             3          Home Goods       Mumbai            10003

                             4          Automobile       Jamshedpur        10004
                             5          Textile          Mumbai            10007


        Query:  Retrieve  the  first  name,  last  name,  and  the  city  of  all  employees  who  work  in  the  Administration
        department.
        Solution:
        SELECT FName, LName, City

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

                                        FName        LName               City

                                      Raj        Reddy            Andhra Pradesh

                                      Muskan     Taneja           Delhi
                                      Naval      Dhingra          Delhi

                 Table 5.13: First name, last name, and the city of all employees who work in the Administration department.

        Query: Find sum of the salaries of all employees of the Accounts department, as well as the maximum salary, the
        minimum salary, and the average salary in this department.


          196  Touchpad Informatics Practices-XII
   205   206   207   208   209   210   211   212   213   214   215