From eee866de5730b8cd5859444fda79ceb46b24ca18 Mon Sep 17 00:00:00 2001
From: mo khan <mo.khan@gmail.com>
Date: Wed, 1 Jan 2020 13:11:34 -0700
Subject: Create view with department summary

---
 labs/2/run.sql | 11 +++++++++++
 1 file changed, 11 insertions(+)

(limited to 'labs/2/run.sql')

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;
-- 
cgit v1.2.3

