Post date: Jan 01, 2017 6:38:17 AM
/!\ Final Practical exam on Week 16 Monday 09 January 2017 13:00
Interesting resources:
Consider the following relational schema. 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 the following queries in SQL:
Print the names and ages of each employee who works in both the Hardware department and the Software department.
SELECT ename, age
FROM Emp e, Works w1, Dept d1, Works w2, Dept d2
WHERE e.eid = w1.eid AND w1.did = d1.did AND e.eid = w2.eid AND w2.did = d2.did AND d1.dname = "Hardware" AND d2.dname = "Software";
---
SELECT ename, age
FROM Emp e INNER JOIN Works w1 ON e.eid = w1.eid INNER JOIN Dept d1 ON w1.did = d1.did INNER JOIN Works w2 ON e.eid = w2.eid INNER JOIN Dept d2 ON w2.did = d2.did
WHERE d1.dname = "Hardware" AND d2.dname = "Software";
Print the name of each employee whose salary exceeds the budget of all of the departments that he or she works in.
select ename
from Emp e
where salary > all ( select budget
from Works w, Dept d
where e.eid = w.eid and w.did = d.did );
Print the names and age of all employees with the total of the budgets of all departments they work in along with how many departments they work in. Display only the employees who work in more than one department sorted in descending order.
select ename, age, sum(budget), count(*)
from Emp e, Works w, Dept d
where e.eid = w.eid and w.did = d.did
group by ename, age
having count(*) > 1
order by ename desc;
Display the names of all employees working in a department that has more than 2 employees
select distinct ename
from Emp e, Works w
where e.eid = w.eid and w.did in ( select did
from Works
group by did
having count(*)>2);