diff options
| author | mo khan <mo.khan@gmail.com> | 2020-01-01 12:58:28 -0700 |
|---|---|---|
| committer | mo khan <mo.khan@gmail.com> | 2020-01-01 12:58:40 -0700 |
| commit | bcbbacae9b14c0e233c3c3027ad9943db1fd338d (patch) | |
| tree | 7723e9a77bd5df38520cfdd8571f1932b93dc55b | |
| parent | ae55501aa2a5b84417ab816cf507f86d79251e0f (diff) | |
Retrieve count of users in each department working on mobile projects
| -rw-r--r-- | labs/2/README.md | 2 | ||||
| -rw-r--r-- | labs/2/run.sql | 7 |
2 files changed, 8 insertions, 1 deletions
diff --git a/labs/2/README.md b/labs/2/README.md index e513903..ae5e05c 100644 --- a/labs/2/README.md +++ b/labs/2/README.md @@ -69,7 +69,7 @@ Indicates the name (Consumer Products, Industrial Products, and Research) and nu Write an SQL query that performs this update. Your query should make use of your trigger (from c) to ensure database integrity. -* [ ] Write an SQL statement to retrieve the number of people working in each of the departments responsible for mobile technology projects. +* [x] Write an SQL statement to retrieve the number of people working in each of the departments responsible for mobile technology projects. * [ ] 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. diff --git a/labs/2/run.sql b/labs/2/run.sql index 38aa0ef..0b070e2 100644 --- a/labs/2/run.sql +++ b/labs/2/run.sql @@ -97,3 +97,10 @@ CREATE TRIGGER update_sin AFTER UPDATE EXECUTE PROCEDURE update_referenced_sin_numbers(); UPDATE departments SET dnumber = 14 where dnumber = 12; + +SELECT departments.dname, count(distinct(employees.sin)) +FROM departments +INNER JOIN employees on employees.dno = departments.dnumber +INNER JOIN projects on projects.dnum = departments.dnumber +WHERE projects.pname LIKE 'Mobile %' +GROUP BY departments.dnumber; |