Page 205 - Informatics_Practices_Fliipbook_Class12
P. 205

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
                                                    Table 5.4: DEPARTMENT table

            SELECT Statement Involving Multiple Tables

            When a SELECT statement is used to retrieve data from multiple tables, names of all the tables are specified in the
            FROM clause in the form of a table_list. Syntax of a SELECT statement involving multiple tables is given below:
            SELECT attribute [, attribute] ...
            FROM table_list
            WHERE criterion_for_selecting_specific_tuple
            To execute the above query, the FROM clause constructs the cross product of the specified tables, then the attributes
            mentioned in the SELECT clause are selected from those tuples of the cross product which satisfy the criterion
            specified  in  WHERE  clause.  To  obtain  a  cross-product  of  tables  in  SQL,  we  only  need  to  list  all  the  tables  whose
            cross-product is required in the FROM clause. For example, the following SELECT statement yields a cross-product of
            tables EMPLOYEE and DEPARTMENT.
            SELECT *
            FROM EMPLOYEE, DEPARTMENT;
            Execution of the above SQL statement yields Table 5.5.

                  ID     FName        …        Salary   Dept_No Dept_No        Dept_Name        …       Mgr_Id
                10001      Raj        …       100000        2         1         Accounts                10002
                10001      Raj        …       100000        2         2       Administration            10005
                10001      Raj        …       100000        2         3       Home Goods                10003
                10001      Raj        …       100000        2         4        Automobile               10007

                10001      Raj        …       100000        2         5          Textile                10004
                10002     Dhiraj      …        85000        1         1         Accounts                10002
                10002     Dhiraj      …        85000        1         2       Administration            10005
                10002     Dhiraj      …        85000        1         3       Home Goods                10003
                   .                  …                     .                                    .
                   .                                        .                                    .
                   .                                        .                                    .
                10009    Naveen       …        60000        4         4        Automobile               10007
                10009    Naveen       …        60000        4         5          Textile                10004
                10010     Savita      …        50000        5         1         Accounts                10002
                10010     Savita      …        50000        5         2       Administration            10005
                10010     Savita      …        50000        5         3       Home Goods                10003

                10010     Savita      …        50000        5         4        Automobile               10007
                10010     Savita      …        50000        5         5          Textile                10004

                                      Table 5.5: CROSS PRODUCT of EMPLOYEE and DEPARTMENT
                                                                                     SQL: Working with Two Tables  191
   200   201   202   203   204   205   206   207   208   209   210