# Lab 2: Tables, Views, SQL Queries, and Triggers

Use the Teradata University Network to implement the following tables.

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

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

'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

'Locations' lists the locations of all the offices of each department.

| DNBR | DLOCATION |
| ---- | --------- |
| 10 | Edmonton |
| 10 | Ottawa |
| 11 | Athabasca |
| 12 | Ottawa |
| 12 | Montreal |

* [x] Create these MIS COMPANY tables in your home database.
* [x] Use SQL queries to populate the tables with the specified data.


* [x] 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.

* [x] 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.

* [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] 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
  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.

1. `SELECT * FROM DEPT_SUMMARY;`

```sql
# SELECT * FROM DEPT_SUMMARY;
 department_id | employees_count | total_salary | average_salary
---------------+-----------------+--------------+----------------
            10 |               2 |        55000 |       27500.00
            11 |               4 |       132000 |       33000.00
(2 rows)
```

2. `SELECT D, C FROM DEPT_SUMMARY WHERE TOTAL_S > 100000;`

```sql
# select department_id, employees_count from dept_summary where total_salary > 100000;
 department_id | employees_count
---------------+-----------------
            11 |               4
(1 row)
```

3. `SELECT D, AVERAGE_S FROM DEPT_SUMMARY WHERE C > (SELECT C FROM DEPT_SUMMARY WHERE D=4);`

```sql
# select department_id, average_salary from dept_summary where employees_count > (select employees_count from dept_summary where department_id = 4);
 department_id | average_salary
---------------+----------------
(0 rows)
```
4. `UPDATE DEPT_SUMMARY SET D=3 WHERE D=4;`

```sql
# update dept_summary set department_id = 3 where department_id = 4;
ERROR:  55000: cannot update view "dept_summary"
DETAIL:  Views containing GROUP BY are not automatically updatable.
HINT:  To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.
LOCATION:  rewriteTargetView, rewriteHandler.c:2880
Time: 0.553 ms
```

5. `DELETE FROM DEPT_SUMMARY WHERE C > 4;`

```sql
# delete from dept_summary where employees_count > 4;
ERROR:  55000: cannot delete from view "dept_summary"
DETAIL:  Views containing GROUP BY are not automatically updatable.
HINT:  To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule.
LOCATION:  rewriteTargetView, rewriteHandler.c:2888
Time: 0.427 ms
```