SQL Based Questions – Part 4

Question 1 : 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:
1. Print the names and ages of each employee who works in both the
   Hardware department and the Software department.
Solution : SELECT E.ename, E.age
           FROM Emp E, Works W1, Works W2, Dept D1, Dept D2
           WHERE E.eid = W1.eid 
           AND W1.did = D1.did AND D1.dname = ‘Hardware’
           AND E.eid = W2.eid AND W2.did = D2.did
           AND D2.dname = ‘Software’
2. For each department with more than 20 full-time-equivalent
   employees (i.e., where the part-time and full-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.
Solution : 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. Print the name of each employee whose salary exceeds the budget of
   all of the departments that he or she works in.
Solution : SELECT E.ename
           FROM Emp E
           WHERE E.salary > ALL (SELECT D.budget
           FROM Dept D, Works W
           WHERE E.eid = W.eid AND D.did = W.did)
4. Find the managerids of managers who manage only departments with 
   budgets greater than $1 million.
Solution : SELECT DISTINCT D.managerid
           FROM Dept D
           WHERE 1000000 < ALL (SELECT D2.budget
           FROM Dept D2
           WHERE D2.managerid = D.managerid )
5. Find the enames of managers who manage the departments with the
   largest budgets.
Solution : SELECT E.ename
           FROM Emp E
           WHERE E.eid IN (SELECT D.managerid
           FROM Dept D
           WHERE D.budget = (SELECT MAX (D2.budget)
           FROM Dept D2))
6. 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.
Solution : SELECT D.managerid
           FROM Dept D
           WHERE 5000000 < (SELECT SUM (D2.budget)
           FROM Dept D2
           WHERE D2.managerid = D.managerid )
7. Find the managerids of managers who control the largest amounts.
Solution : SELECT DISTINCT tempD.managerid
           FROM (SELECT DISTINCT D.managerid,
                 SUM (D.budget) AS tempBudget
           FROM Dept D
           GROUP BY D.managerid ) AS tempD
           WHERE tempD.tempBudget = (SELECT MAX (tempD.tempBudget)
           FROM tempD)
8. Find the enames of managers who manage only departments with 
   budgets larger than $1 million, but at least one department
   with budget less than $5 million.
Solution : SELECT E.ename
           FROM Emp E, Dept D
           WHERE E.eid = D.managerid GROUP BY E.Eid, E.ename
           HAVING EVERY (D.budget > 1000000) 
           AND ANY (D.budget < 5000000)

Previous Home Next
Qustions On SQL – Part 3 Questions On SQL – Part 5

     

Incoming search terms:

  • consider the following relational schema an employee can work more than one department

Leave a Reply