Question 1 (8 points). Consider the following relational schema. An employee can

Question

Question 1 (8 points). 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(eidinteger, did:integer, pct time:integer) Dept(did:integer, budget:real, managerid:integer) Write the following queries in SQL: 1. For each department with more than 20 full-time-equivalent employees (i.e., where the part-time and MI-time employees add up to at least that many full-time employees), print the did together with the number of employees that work in that department. 2. Find the enames of managers who manage the departments with the largest budgets. 3. If a manager manages more than one department, he or she controls the sum of all the budgets for those departments. Find the managerids of managers who control more than $5 million. 4. Find the managerids of managers who control the largest amounts.

Explanation / Answer

1
SELECT a.did
FROM   (SELECT did, count(eid) num_emp
   FROM   works
   WHERE pct_time = 40
   GROUP BY did
   HAVING count(eid) > 20) a;

2
SELECT e.ename
FROM   emp e, works w, (SELECT did, max(budget)
                  FROM   dept
                  GROUP BY did) d
WHERE d.did = w.did
AND    e.eid = w.eid;

3
SELECT a.managerid
FROM   (
      SELECT managerid, sum(budget) budget
      FROM   dept
      GROUP BY managerid
      HAVING sum(budget) > 5000000
      ) a;

4
SELECT managerid
FROM   dept
WHERE budget = (SELECT max(budget) FROM dept);

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

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