Create view with department summary

2 files changed, 13 insertions, 2 deletions

diff --git a/labs/2/README.md b/labs/2/README.md
index 2ef04a2..ea87c0f 100644
--- a/labs/2/README.md
+++ b/labs/2/README.md
@@ -70,9 +70,9 @@ Indicates the name (Consumer Products, Industrial Products, and Research) and nu
   Your query should make use of your trigger (from c) to ensure database integrity.
 
 * [x] Write an SQL statement to retrieve the number of people working in each of the departments responsible for mobile technology projects.
-* [X] Retrieve the department name and the number of female employees working for each department whose average salary is more than 27K.
+* [x] Retrieve the department name and the number of female employees working for each department whose average salary is more than 27K.
 
-* [ ] For some strategic decisions, the president of the company needs summary data about the departments.
+* [x] For some strategic decisions, the president of the company needs summary data about the departments.
   For each department,
   she needs to know the number of employees working on mobile technology projects,
   as well as their total and average salaries.
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;