Consider the following database Schema Employee (Fname,Minit,Lname,Ssn,Bdate,Add

Question

Consider the following database Schema

Employee (Fname,Minit,Lname,Ssn,Bdate,Address,Sex,Salary,Super_ssn,Dno)

Department(Dname,Dnumber,Mg_ssn,Mgr_Start_date)

Dept_Locations(Dnumber,Dlocation)

Apply following query by two different methods given below and show the intermediate relations. Also describe which one of the methods is good in query optimization context and why?
Query
SELECT E.Lname, E.Bdate, D.Mgr_ssn
FROM EMPLOYEE AS E, DEPARTMENT AS D
WHERE E.Salary >= 30000 AND D.Dname = ‘Research’
AND E.Dno = D.Dnumber;
Method1: Apply Join first and then Selection according to given conditions
Methdo2: Apply Selection first and then Join according to given conditions

Explanation / Answer

SELECT Lname, Bdate
FROM EMPLOYEE E
LEFT JOIN (
   SELECT Mgr_ssn
   FROM DEPARTMENT
   WHERE Dname = ‘Research’
   GROUP BY Dnumber
) D
WHERE E.Salary >= 30000 AND
D.Dnumber = E.Dno;

SELECT Lname, Bdate
FROM EMPLOYEE E
WHERE E.Salary >= 30000 AND (
    SELECT Mgr_ssn
   FROM DEPARTMENT
   WHERE Dnumber = E.Dno
   AND Dname = ‘Research’
);

optimization may vary with your different RDBMS.

According to my concern. The method1 is faster than method2 because it uses join with subquery. Generally joins are fatster then subqueriy.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.