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

