1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
|
# 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
```
|
