Page 216 - Informatics_Practices_Fliipbook_Class12
P. 216

Dept_                                                 Pin_
          No  ID   FName LName    Gender Address     City      Code  DOB       Salary  Dept_Name  Location  Mgr_Id
           2  10001 Raj   Reddy   M     West Godavari  Andhra   534197 1980-06-13 100000 Administration Delhi  10005
                                                     Pradesh
           1  10002 Dhiraj  Bora  M     Dispur, Kamrup,  Guwahati  781005 1975-09-30 85000  Account  Noida  10002
                                        Assam
           2  10003 Muskan Taneja  F    8/33, Geeta   Delhi    110031 1990-01-25 100000 Administration Delhi  10005
                                        Colony
           4  10004 Hiten   Oberoi  M   15, Dimna Road,  Jamshedpur 831018 1985-06-24 100000 Automobile  Jamshedpur 10007
                                        Mango
           1  10005 Anshul  Verma  M    House 10, Sector  Noida  201304 1990-01-01 100000 Accounts  Noida  10002
                                        16, Gautum Budh
                                        Nagar
           4  10006 Rajit  Gadh   M     12, Beldih   Jamshedpur 831001 1960-05-07 60000  Automobile  Jamshedpur 10007
                                        Triangle, Bistupur
           5  10007 Taran  Adarsh  M    B-76, CST Road,  Mumbai  400098 1965-01-13 70000  Textile  Mumbai  10004
                                        Kalina, Santac
           2  10008 Naval   Dhingra  M  E-14 Vivek    Delhi    110095 1975-08-04 70000  Administration  Delhi  10005
                                        Vihar
           4  10009 Naveen  Basra  F    28, Aambagan   Jamshedpur 831001 1980-09-24 60000  Automobile  Jamshedpur 10007
                                        Road, Sakechi
           5  10010 Savita  Ambedkar F  C-49, G-Block,   Mumbai  400051 1987-07-11 50000  Textile  Mumbai  10004
                                        Bandra Kurla, B
                                 Table 5.24: NATURAL JOIN of EMPLOYEE and DEPARTMENT
        Of course, we can achieve the same effect by dropping the duplicate column while specifying the list of columns to be
        displayed in the SELECT clause as shown below:
        SELECT EMPLOYEE.Dept_No, ID, FName, LName, Gender, Address, City, Pin_Code, DOB,
        Salary, Dept_Name, Location, Mgr_Id

        FROM EMPLOYEE, DEPARTMENT
        WHERE EMPLOYEE.Dept_No = DEPARTMENT.Dept_No;
        Table 5.25 shows the result of executing the above SELECT statement.

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

             Table 5.25: EQUIJOIN of EMPLOYEE and DEPARTMENT with department number appearing once.

          202  Touchpad Informatics Practices-XII
   211   212   213   214   215   216   217   218   219   220   221