From eee866de5730b8cd5859444fda79ceb46b24ca18 Mon Sep 17 00:00:00 2001 From: mo khan Date: Wed, 1 Jan 2020 13:11:34 -0700 Subject: Create view with department summary --- labs/2/README.md | 4 ++-- labs/2/run.sql | 11 +++++++++++ 2 files changed, 13 insertions(+), 2 deletions(-) (limited to 'labs') 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; -- cgit v1.2.3