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/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