Page 215 - Informatics_Practices_Fliipbook_Class12
P. 215

Output (Table 5.22):


                                            FName       LName         Dept_Name
                                            Raj         Reddy         Administration

                                            Dhiraj      Bora          Account

                                            Muskan      Taneja        Administration
                                            Hiten       Oberoi        Automobile

                                            Anshul      Verma         Account
                                            Rajit       Gadh          Automobile

                                            Taran       Adarsh        Textile
                                            Naval       Dhingra       Administration

                                            Naveen      Basra         Automobile

                                            Savita      Ambedkar      Textile
                                  Table 5.22: First name, last name, and department name of all employees.

            Query: To display the name of the departments located in Mumbai and first name of the employees working in it.
            Solution:

            SELECT FName, Dept_Name
            FROM EMPLOYEE AS E, DEPARTMENT AS D
            WHERE E.Dept_No = D.Dept_No AND Location = "Mumbai";
            Output (Table 5.23):

                                                  FName         Dept_Name
                                                  Taran         Textile
                                                  Savita        Textile

                               Table 5.23: First name and department name of departments located in Mumbai.

            NATURAL JOIN

            SQL supports a NATURAL JOIN operator that removes the duplicate column common to both the tables and positions
            the common attribute as the first column in the result. A NATURAL JOIN query to get the details of all the managers
            of all the departments may be formulated as follows:

            SELECT *
            FROM EMPLOYEE
            NATURAL JOIN DEPARTMENT;
            The execution of the above query will yield Table 5.24. Note that Dept_No is the first column in Table 5.24.


                  NATURAL  JOIN  operator  that  removes  the  duplicate  column  common  to  both  the  tables  and  positions  the
                  common attribute as the first column in the result.








                                                                                     SQL: Working with Two Tables  201
   210   211   212   213   214   215   216   217   218   219   220