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. 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.
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. 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.
3. Is it Solvable with XML type structure
Step by step
Solved in 2 steps
