# SQL Based Questions – Part 4

## 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)```