Add instructions for lab 2

1 files changed, 73 insertions, 0 deletions

diff --git a/labs/2/README.md b/labs/2/README.md
new file mode 100644
index 0000000..9f90d9e
--- /dev/null
+++ b/labs/2/README.md
@@ -0,0 +1,73 @@
+Lab 2: Tables, Views, SQL Queries, and Triggers
+
+Use the Teradata University Network to implement the following tables.
+
+Table 1: Employees
+Table 1: 'Employees' includes the first name, middle initial, and last name of every worker in the company, as well as their SIN, date of birth, address, sex, salary, and the number of the department with which they are associated.
+
+| FNAME | MINIT | LNAME | SIN | BDATE | ADDRESS | GENDER | SALARY | DNO |
+| ----- | ----- | ----- | --- | ----- | ------- | ------ | ------ | --- |
+| Harris | T | Chomsky | 123 | 1965-12-10 | Edmonton | M | 50000 | 	12 |
+| Kristian | C | Bohr | 456 | 1975-10-05 | Ottawa | M | 28000 | 11 |
+| Charlotte | F | Bouchard | 789 | 1985-08-06 | Montreal | F | 40000 | 11 |
+| Said | J | Ahmad | 111 | 1970-09-07 | Toronto | M | 30000 | 12 |
+| Andrew | U | Brahe | 222 | 1970-04-02 | Winnipeg | M | 20000 | 10 |
+| Nadia | O | Mamary | 333 | 1960-01-08 | Saskatoon | F | 35000 | 10 |
+| Yuan | P | Nielsen | 987 | 1983-02-27 | Moncton | F | 32000 | 11 |
+| Neil | A | Dion | 654 | 1953-02-27 | Moncton | M | 32000 | 11 |
+| Karen | C | Ming | 321 | 1963-11-16 | Victoria | F | 26000 | 12 |
+
+Table 2: Departments
+Table 2: 'Departments' indicates the name (Consumer Products, Industrial Products, and Research) and number of each department in the company, and the SIN and start date of the manager of each department.
+
+| DNAME | DNUMBER | MGRSIN | MGRSTARTDATE |
+| ----- | ------- | ------ | ------------ |
+| ConsProd | 10 | 333 | 2004-10-01 |
+| InduProd | 11 | 654 | 2005-05-01 |
+| Research | 12 | 111 | 2000-06-15 |
+
+Table 3: Projects
+Table 3: 'Projects' includes the project name and number, the number of the department in charge of the project, and the location of the office working on the project.
+
+| PNAME | PNUMBER | PLOCATION | DNUM |
+| ----- | ------- | --------- | ---- |
+| Mobile | University | 1 | Ottawa | 10 |
+| Interactive | TV | 2 | Ottawa | 12 |
+| Intelligent | Agent | 3 | Athabasca | 11 |
+| E-Commerce | 4 | Edmonton | 10 |
+| Mobile | Office | 5 | Athabasca | 11 |
+
+Table 4: Locations
+Table 4: 'Locations' lists the locations of all the offices of each department.
+
+| DNBR | DLOCATION |
+| ---- | --------- |
+| 10 | Edmonton |
+| 10 | Ottawa |
+| 11 | Athabasca |
+| 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.
+
+```sql
+CREATE VIEW DEPT_SUMMARY (D, C, TOTAL_S, AVERAGE_S) AS
+	SELECT DNO, COUNT (*), SUM (SALARY), AVG (SALARY)
+	FROM EMPLOYEE, PROJECT
+	WHERE DNO = DNUM and PNAME like 'Mobile%'
+	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.
+
+Q1. `SELECT * FROM DEPT_SUMMARY;`
+Q2. `SELECT D, C FROM DEPT_SUMMARY WHERE TOTAL_S > 100000;`
+Q3. `SELECT D, AVERAGE_S FROM DEPT_SUMMARY WHERE C > (SELECT C FROM DEPT_SUMMARY WHERE D=4);`
+Q4. `UPDATE DEPT_SUMMARY SET D=3 WHERE D=4;`
+Q5. `DELETE FROM DEPT_SUMMARY WHERE C > 4;`