Exercise 20.9 Consider two ways to compute the names of employees who earn more than $100,...

Exercise 20.9 Consider two ways to compute the names of employees who earn more than $100,000 and whose age is equal to their manager’s age. First, a nested query:

   SELECT E1.ename   FROM Emp E1   WHERE E1.sal > 100 AND E1.age = ( SELECT E2.age                                     FROM Emp E2, Dept D2                                     WHERE E1.dname = D2.dname                                          AND D2.mgr = E2.ename )

Second, a query that uses a view definition:

   SELECT E1.ename   FROM Emp E1, MgrAge A   WHERE E1.dname = A.dname AND E1.sal > 100 AND E1.age  = A.age

   CREATE VIEW MgrAge (dname, age)      AS SELECT D.dname, E.age         FROM Emp E, Dept D         WHERE D.mgr = E.ename

1. Describe a situation in which the first query is likely to outperform the second query.

2. Describe a situation in which the second query is likely to outperform the first query.

3. Can you construct an equivalent query that is likely to beat both these queries when every employee who earns more than $100,000 is either 35 or 40 years old? Explain briefly.