From a8f03a5dc9b92950a06fb79da6af5eb3ef7844ae Mon Sep 17 00:00:00 2001 From: mo khan Date: Wed, 1 Jan 2020 11:15:15 -0700 Subject: Identify tasks to complete --- labs/2/README.md | 35 +++++++++++++++++++++++++++-------- labs/2/run.sql | 6 +++--- 2 files changed, 30 insertions(+), 11 deletions(-) (limited to 'labs') diff --git a/labs/2/README.md b/labs/2/README.md index 9c63dfe..c481f4c 100644 --- a/labs/2/README.md +++ b/labs/2/README.md @@ -52,13 +52,32 @@ Indicates the name (Consumer Products, Industrial Products, and Research) and nu | 12 | Ottawa | | 12 | Montreal | -* Create these MIS COMPANY tables in your home database. -* Use SQL queries to populate the tables with the specified data. -* It is preferable to use triggers to enforce an “on updates cascade” policy for foreign keys. In other words, if X is an attribute in Table 1, and is a foreign key in Table 2 and Table 3, then any change to an X value in Table 1 will result in all X values equal to the old value being updated accordingly in Table 2 and Table 3. Write a trigger to handle this “on updates cascade” for your tables. Your solution should be simple and correct. -* The company administration has decided to change the research department number from “12” to “14”. 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. -* 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. For each department, he needs to know the number of employees working on mobile technology projects, as well as their total and average salaries. Does the following view answer the president’s request? If not, write the correct view that will satisfy the president’s request. +* [x] Create these MIS COMPANY tables in your home database. +* [x] Use SQL queries to populate the tables with the specified data. + + +* [ ] It is preferable to use triggers to enforce an "on updates cascade" policy for foreign keys. + In other words, if X is an attribute in Table 1, + and is a foreign key in Table 2 and Table 3, + then any change to an X value in Table 1 will result in all X + values equal to the old value being updated accordingly in Table 2 and Table 3. + Write a trigger to handle this "on updates cascade" for your tables. + + Your solution should be simple and correct. + +* [ ] The company administration has decided to change the research department number from "12" to "14". + 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. +* [ ] 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. + For each department, + she needs to know the number of employees working on mobile technology projects, + as well as their total and average salaries. + Does the following view answer the president’s request? + If not, **write the correct view that will satisfy the president’s request.** ```sql CREATE VIEW DEPT_SUMMARY (D, C, TOTAL_S, AVERAGE_S) AS @@ -68,7 +87,7 @@ CREATE VIEW DEPT_SUMMARY (D, C, TOTAL_S, AVERAGE_S) AS GROUP BY DNO; ``` -* Which of the following queries and updates would be allowed by this view (from g)? If a query or update is allowed, what would the corresponding query or update on the base relations look like? Give its result when applied to the database. +* [ ] Which of the following queries and updates would be allowed by this view (from g)? If a query or update is allowed, what would the corresponding query or update on the base relations look like? Give its result when applied to the database. 1. `SELECT * FROM DEPT_SUMMARY;` 2. `SELECT D, C FROM DEPT_SUMMARY WHERE TOTAL_S > 100000;` diff --git a/labs/2/run.sql b/labs/2/run.sql index b0e0fc7..01355ae 100644 --- a/labs/2/run.sql +++ b/labs/2/run.sql @@ -14,9 +14,9 @@ CREATE TABLE employees ( INSERT INTO employees VALUES ('Harris', 'T', 'Chomsky', 123, '1965-12-10', 'Edmonton', 'M', 50000, 12); INSERT INTO employees VALUES ('Kristian', 'C', 'Bohr' , 456, '1975-10-05', 'Ottawa', 'M', 28000, 11); INSERT INTO employees VALUES ('Charlotte', 'F', 'Bouchard' , 789, '1985-08-06', 'Montreal', 'F', 40000, 11); -INSERT INTO employees VALUES ('Said', 'J', 'Ahmad', 111, '1970-09-07', 'Toronto', M, 30000, 12); -INSERT INTO employees VALUES ('Andrew', 'U', 'Brahe', 222, '1970-04-02', 'Winnipeg', M, 20000, 10); -INSERT INTO employees VALUES ('Nadia', 'O', 'Mamary', 333, '1960-01-08', 'Saskatoon', F, 35000, 10); +INSERT INTO employees VALUES ('Said', 'J', 'Ahmad', 111, '1970-09-07', 'Toronto', 'M', 30000, 12); +INSERT INTO employees VALUES ('Andrew', 'U', 'Brahe', 222, '1970-04-02', 'Winnipeg', 'M', 20000, 10); +INSERT INTO employees VALUES ('Nadia', 'O', 'Mamary', 333, '1960-01-08', 'Saskatoon', 'F', 35000, 10); INSERT INTO employees VALUES ('Yuan', 'P', 'Nielsen', 987, '1983-02-27', 'Moncton', 'F', 32000, 11); INSERT INTO employees VALUES ('Neil', 'A', 'Dion', 654, '1953-02-27', 'Moncton', 'M', 32000, 11); INSERT INTO employees VALUES ('Karen', 'C', 'Ming', 321, '1963-11-16', 'Victoria', 'F', 26000, 12); -- cgit v1.2.3