Create view with department summary

1 files changed, 11 insertions, 0 deletions

diff --git a/labs/2/run.sql b/labs/2/run.sql
index ca943bf..5e0ac67 100644
--- a/labs/2/run.sql
+++ b/labs/2/run.sql
@@ -111,3 +111,14 @@ INNER JOIN employees e on e.dno = d.dnumber
 WHERE e.gender = 'F'
 AND e.salary > 27000
 GROUP BY d.dnumber;
+
+CREATE OR REPLACE VIEW dept_summary (department_id, employees_count, total_salary, average_salary) AS
+  SELECT e.dno, count(e.sin), sum(e.salary), round(avg(e.salary), 2)
+  FROM employees e
+  INNER JOIN departments d on d.dnumber = e.dno
+  INNER JOIN projects p on p.dnum = d.dnumber
+  WHERE e.dno = d.dnumber
+  AND p.pname LIKE 'Mobile%'
+  GROUP BY e.dno;
+
+SELECT * FROM dept_summary;