Consider the following relations. An employee can work in more than one departme

Question

Consider the following relations. An employee can work in more than one department; the pct
time field of the Works relation shows the percentage of time that a given employee works in a
given department.

Emp(eid: integer, ename: string, age: integer, salary: real)
Works(eid: integer, did: integer, pct_time: integer)
Dept(did: integer, dname: string, budget: real, managerid: integer)

Write and execute the following queries in SQL:

1. Print the names and ages of each employee who works in the Hardware department.

2. For each department with more than 20 employees, print the did together with the number of
employees that work in that department.

3. Print the name of each employee whose salary exceeds the average salary of all employees.

4. Find the managerids of managers who manage only departments with budgets greater than
$1 million.

5. Find the employees who work in the ‘Operations’ department and print the information (eid,
ename, age, and salary) for each employee. The output should be ordered by ename.

Explanation / Answer

1. SELECT E.ename, E.age FROM Emp E, Works W, Dept D WHERE E.eid = W.eid AND W.did = D.did AND D.dname = ‘Hardware’;

2. SELECT W.did, COUNT (W.eid) FROM Works W GROUP BY W.did HAVING 2000 < ( SELECT SUM (W1.pct time) FROM Works W1 WHERE W1.did = W.did );

3.SELECT ename FROM Emp WHERE salary > ( SELECT AVG(salary) FROM Emp );

4. SELECT DISTINCT D.managerid FROM Dept D WHERE 1000000 < ALL (SELECT D2.budget FROM Dept D2 WHERE D2.managerid = D.managerid );

5. SELECT E.ename, E.age FROM Emp E, Works W, Dept D WHERE E.eid = W.eid AND W.did = D.did AND D.dname = ‘Operations’ ORDER BY E.ename;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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