Page 206 - Informatics_Practices_Fliipbook_Class12
P. 206

Next, suppose we wish to retrieve complete details about employees along with the details of their departments. To
        do this, we need to consider only those tuples from Table 5.5 which have the same value of the Dept_No attribute for
        the tables EMPLOYEE and DEPARTMENT. The following SQL query achieves this:

        SELECT *
        FROM EMPLOYEE, DEPARTMENT
        WHERE EMPLOYEE.Dept_No = DEPARTMENT.Dept_No;
        Note that the attribute Dept_No is common in EMPLOYEE and DEPARTMENT tables. SQL provides a simple mechanism
        for distinguishing between the attributes of different tables having the same name. It allows us to prefix an attribute
        name with the table's name followed by a period to distinguish between common attribute names in different tables.
        Thus  EMPLOYEE.Dept_No  denotes  attribute  Dept_No  of  table  EMPLOYEE  and  DEPARTMENT.Dept_No
        denotes attribute Dept_No of table DEPARTMENT. You will recall that this is the usual method of specifying an
        attribute of an object.
        Suppose  we  wish  to  find  the  ID,  first  name,  last  name,  department  numbers,  department  name,  and  salary  for
        employees with a salary greater than 80000. The following SELECT statement achieves this:

        SELECT ID, FName, LName, EMPLOYEE.Dept_No, Dept_Name, Salary
        FROM EMPLOYEE, DEPARTMENT

        WHERE EMPLOYEE.Dept_No = DEPARTMENT.Dept_No AND Salary > 80000;
        Execution of the above statement will yield Table 5.6.


                      ID          FName         LName       Dept_No       Dept_Name          Salary
                    10001          Raj          Reddy           2        Administration      100000
                    10002         Dhiraj         Bora           1           Accounts         85000
                    10003        Muskan         Taneja          2        Administration      100000
                    10004         Hiten         Oberoi          4          Automobile        100000

                    10005         Anshul        Verma           1           Account          100000
                Table 5.6: Details from EMPLOYEE and DEPARTMENT tables for the employees with Salary > 80000
        Sometimes, it is convenient to define an alias (another name) for a table using the keyword AS. Thus above query
        may be written as follows:

        SELECT ID, FName, LName, E.Dept_No, Dept_Name, Salary
        FROM EMPLOYEE AS E, DEPARTMENT AS D

        WHERE E.Dept_No = D.Dept_No AND E.Salary > 80000;
        In the above query, E and D are aliases for the tables EMPLOYEE and DEPARTMENT respectively. The result of the
        above query (Table 5.7) is the same as in Table 5.6.


                      ID          FName         LName       Dept_No       Dept_Name          Salary
                    10001          Raj          Reddy           2        Administration      100000
                    10002         Dhiraj         Bora           1           Accounts         85000
                    10003        Muskan         Taneja          2        Administration      100000
                    10004         Hiten         Oberoi          4          Automobile        100000

                    10005         Anshul        Verma           1           Account          100000
                   Table 5.7: Details from EMPLOYEE and DEPARTMENT tables for the employees with salary > 80000
        Now, let us answer some more queries using SQL statements.


          192  Touchpad Informatics Practices-XII
   201   202   203   204   205   206   207   208   209   210   211