write some quick sql

1 files changed, 98 insertions, 0 deletions

diff --git a/assignments/exam/README.md b/assignments/exam/README.md
index 26356c6..1f23031 100644
--- a/assignments/exam/README.md
+++ b/assignments/exam/README.md
@@ -150,3 +150,101 @@ maintenance(_id_, technician_id, plane_id, type)
 |                |
 ------------------
 ```
+
+## 3
+
+Consider the following three relations:
+
+```text
+Manager(name, address, specialization, salary)
+Employee(name, address, rank, salary)
+Project (proj_num, emp_name, manager_name, duration)
+```
+
+Write the SQL queries corresponding to the following questions.
+
+a. Find the number of different employees who are assigned to projects.
+
+```sql
+SELECT count(distinct(e.name))
+FROM Employee e
+WHERE e.name IN (SELECT DISTINCT(emp_name) FROM PROJECT);
+```
+or
+
+```sql
+SELECT count(distinct(e.name))
+FROM Employee e
+INNER JOIN Project p on p.emp_name = e.name;
+```
+
+b. Find the managers' average salary.
+
+```sql
+SELECT AVG(m.salary)
+FROM Manager m;
+```
+
+c. List the name and number of projects supervised by each manager.
+
+```sql
+SELECT p.manager_name, count(p.proj_numb)
+FROM Project p
+GROUP BY p.manager_name;
+```
+
+d. Show the names and salaries of managers who supervise employees of rank "beginner" in ascending
+order of salary (use a subquery).
+
+```sql
+SELECT m.name, m.salary
+FROM Manager m
+WHERE m.name in (
+  SELECT p.manager_name
+  FROM Project p
+  INNER JOIN Employee e on e.name = p.emp_name
+  WHERE e.rank = 'beginner'
+)
+ORDER BY m.salary ASC;
+```
+
+or
+
+```sql
+SELECT m.name, m.salary
+FROM Manager m
+WHERE m.name in (
+  SELECT p.manager_name
+  FROM Project p
+  WHERE p.emp_name IN (
+    SELECT e.name
+    FROM Employee e where e.rank = 'beginner'
+  )
+)
+ORDER BY m.salary ASC;
+```
+
+e. Show the names and salaries of managers who supervise employees of rank "beginner" in
+descending order of salary (do not use a subquery).
+
+```sql
+SELECT m.name, m.salary
+FROM Manager m
+INNER JOIN Project p on p.manager_name = m.name
+INNER JOIN Employee e on e.name = p.emp_name
+WHERE e.rank = 'beginner'
+ORDER BY m.salary ASC;
+```
+
+f. Show the names and salaries of supervisors who supervise more than 20 employees.
+
+```sql
+SELECT m.name, m.salary
+FROM Manager m
+WHERE m.name IN (
+  SELECT p.manager_name
+  FROM Project p
+  GROUP BY p.manager_name
+  HAVING COUNT(p.manager_name) > 20
+):
+```