Page 212 - Informatics_Practices_Fliipbook_Class12
P. 212

Output (5.17):


                                                  FName      LName
                                                  Muskan     Taneja
                                                  Hiten      Oberoi
                                                  Anshul     Verma
                                                  Rajit      Gadh
                                                  Taran      Adarsh

                                                  Naval      Dhingra
                                                  Naveen     Basra
                                                  Savita     Ambedkar
                 Table 5.17: Names of all employees who work in the department located in the same city in which they live.

        Query 6: Retrieve the first and last names of the employees working in the same department as the employee with
        Employee ID = 10001.
        Answer:

        SELECT E1.FName AS EmpFName, E1.LName AS EmpLName, E2.FName AS ColleagueFName,
        E2.LName AS CollagueLName
        FROM EMPLOYEE AS E1, EMPLOYEE AS E2
        WHERE (E1.Dept_No = E2.Dept_No) AND (E1.ID = 10001) AND (E2.ID != 10001);
        Output (Table 5.18):

                           EmpFName      Empl_Name        ColleagueFName     CollagueLName
                            Raj          Reddy            Muskan             Taneja
                            Raj          Reddy            Naval              Dhingra

               Table 5.18: First name, last name of the employees along with the employees working in the same department of
                                                    Employee ID = 10001.

        Query 7: Retrieve the first name, last name, department name, and date of birth of the employees born after the
        employee named "Hiten Oberoi".
        Answer:

        SELECT FName, LName, Dept_Name, DOB
        FROM EMPLOYEE AS E, DEPARTMENT AS D
        WHERE (E.Dept_No = D.Dept_No) AND DOB >
        (SELECT DOB FROM EMPLOYEE

        WHERE FName = "Hiten" AND LName = "Oberoi");
        Output (Table 5.19):


                                   FName      LName       Dept_Name       DOB
                                   Muskan     Taneja      Administration  1990-01-25
                                   Anshul     Verma       Account         1990-01-01
                                   Savita     Ambedkar    Textile         1987-07-11

           Table 5.19: First name, last name, department name, and date of birth of the employees who are born after the employee
                                                   named "Hiten Oberoi"


          198  Touchpad Informatics Practices-XII
   207   208   209   210   211   212   213   214   215   216   217