Database Systems 6th Edition by Ramez Elmasri, Shamkant B. Navathe

تمرين 3

Q1: SELECT Fname, Lname, Address
FROM EMPLOYEE, DEPARTMENT
WHERE Dname='Research' AND Dnumber=Dno;

Q8: SELECT E.Fname, E.Lname, S.Fname, S.Lname
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.Super_ssn=S.Ssn;

Q1B: SELECT E.Fname, E.LName, E.Address
FROM EMPLOYEE E, DEPARTMENT D
WHERE D.DName='Research' AND D.Dnumber=E.Dno;


Q27: SELECT Pnumber, Pname, COUNT (*)
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE Pnumber=Pno AND Ssn=Essn AND Dno=5
GROUP BY Pnumber, Pname;
-Consider SQL queries Q1, Q8, Q1B, Q4, Q27 in Chapter 5.
(a) Draw at least two query trees that can represented each of these queries. Under what circumstances would you use each of your query trees?
(b) Draw the initial query tree for each of these queries; then show how the query tree is optimized by the algorithm outlined in section 18.7.
(c) For each query, compare your on query trees of part (a) and the initial and final query trees of part (b).