Page 50 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 3: Structured Query Language

Rules to be Followed in Query Evaluation Notes

Consider a simple query, Query
Query (b): Find the names of employees who are working in ‘accounting’ department.
Solution:
SELECT E.ename

FROM Employee E, Department D,
WHERE E.eid = D.Dept_managerid AND
D.Dname = ‘Accounting’.
In order to solve this, follow the steps as shown

1. Calculate the cross product of the tables whose names are mentioned in the from-list
Eid Ename D.No. Esal Age Phone Dept. d.no. D.Name D.Location P.No.
Manage
rid
101 John 2 35000 50 24578912 108 1 Administration Hyderabad 44
101 John 2 35000 50 24578912 101 2 Accounts Secunderabad 11
101 John 2 35000 50 24578912 97 5 Accounts Hyderabad 22
97 Harry 5 30000 41 23535135 108 1 Administration Hyderabad 44
97 Harry 5 30000 41 23535135 101 2 Accounts Secunderabad 11
97 Harry 5 30000 41 23535135 97 5 Accounts Hyderabad 22
10 Sam 1 25000 32 24532121 108 1 Administration Hyderabad 44
108 Sam 1 25000 32 24532121 101 2 Accounts Secunderabad 11
108 Sam 1 25000 32 24532121 97 5 Accounts Hyderabad 22
102 Henry 2 22000 35 24578290 108 1 Administration Hyderabad 44
102 Henry 2 22000 35 24578290 101 2 Accounts Secunderabad 11
102 Henry 2 22000 35 24578290 97 5 Accounts Hyderabad 22
2. Among the obtained rows from the cross product, reject the rows that do not satisfy the
qualification condition.
3. The names of the columns that are not present in the select-list are deleted.
4. Remove the rows that have appeared twice when the distinct keyword is not used.
Now let us implement these steps an instance of employee table E

Eid Ename DNo Esal Age Phone
101 John 2 35000 50 2457891
97 Harry 5 30000 41 23555135
108 Sam 1 25000 32 2453212
102 Henry 2 22000 35 24578290

An instance of department table D
Dept_Managerid DNo Dname Dlocation PNo
108 1 Administration Hyderabad 44
101 2 Accounts Secunderabad 11
97 5 Accounts Hyderabad 22

LOVELY PROFESSIONAL UNIVERSITY 43