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
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
|
\echo '--- Question 4 ---'
\set ON_ERROR_STOP on
DROP TABLE IF EXISTS ProjAssigned;
DROP TABLE IF EXISTS Employee;
CREATE TABLE Employee(
"emp-no" bigint primary key,
name varchar(255),
department varchar(255),
salary decimal
);
CREATE TABLE ProjAssigned(
"proj-no" bigint primary key,
"emp-no" bigint references Employee("emp-no"),
"worked-hours" integer
);
INSERT INTO Employee VALUES (1, 'Tsuyoshi Garrett', 'X', 200000);
INSERT INTO Employee VALUES (2, 'Peter Parker', 'X', 300000);
INSERT INTO Employee VALUES (3, 'Diana Prince', 'X', 400000);
INSERT INTO Employee VALUES (4, 'Tony Stark', 'X', 500000);
INSERT INTO ProjAssigned VALUES (1, 3, 120);
INSERT INTO ProjAssigned VALUES (2, 2, 40);
INSERT INTO ProjAssigned VALUES (3, 1, 80);
CREATE INDEX employees_idx ON ProjAssigned("emp-no");
CREATE INDEX employee_salary_idx ON Employee(salary);
CREATE INDEX employee_names_idx ON Employee(name ASC);
-- CREATE INDEX employee_composite_idx ON Employee(salary, name ASC);
-- \d Employee;
-- \d ProjAssigned;
EXPLAIN ANALYZE SELECT e."emp-no", e.name, pa."proj-no", pa."worked-hours"
FROM Employee e
INNER JOIN ProjAssigned pa ON pa."emp-no" = e."emp-no"
WHERE e.salary > 66000.0
ORDER BY e.name ASC;
DROP TABLE IF EXISTS TravelAgent;
DROP TABLE IF EXISTS Customer;
DROP TABLE IF EXISTS Transaction;
CREATE TABLE TravelAgent(
name varchar(255),
age integer,
salary decimal
);
CREATE TABLE Customer(
name varchar(255),
departure_city varchar(255),
destination varchar(255),
journey_class varchar(255)
);
CREATE TABLE Transaction(
"number" integer,
cust_name varchar(255),
travel_agent_name varchar(255),
amount_paid decimal
);
INSERT INTO TravelAgent VALUES('A', 31, 100000);
INSERT INTO TravelAgent VALUES('B', 32, 100000);
INSERT INTO TravelAgent VALUES('C', 33, 100000);
INSERT INTO TravelAgent VALUES('D', 34, 100000);
INSERT INTO TravelAgent VALUES('E', 34, 100000);
INSERT INTO Customer VALUES ('I', 'Calgary', 'Seattle', 'Coach');
INSERT INTO Customer VALUES ('II', 'Calgary', 'Vancouver', 'Coach');
INSERT INTO Customer VALUES ('III', 'Calgary', 'Toronto', 'Coach');
INSERT INTO Customer VALUES ('IV', 'Calgary', 'Montreal', 'Coach');
INSERT INTO Customer VALUES ('V', 'Calgary', 'Ottawa', 'Coach');
INSERT INTO Transaction VALUES(1, 'I', 'A', 10000.0);
INSERT INTO Transaction VALUES(2, 'II', 'B', 10000.0);
INSERT INTO Transaction VALUES(3, 'III', 'A', 10000.0);
INSERT INTO Transaction VALUES(4, 'I', 'A', 1000.0);
\echo '--- Question 5 ---'
-- Compute the number of different customers who have a transaction.
SELECT COUNT(DISTINCT c.name)
FROM Customer c
INNER JOIN Transaction t ON t.cust_name = c.name;
-- Display the name of the oldest travel agent.
SELECT t.name
FROM TravelAgent t
WHERE t.age IN (
SELECT MAX(age) from TravelAgent
);
-- List the total number of transactions for each travel agent.
-- Consider only those transactions where the amount paid exceeds 1 000.
SELECT travel_agent_name, COUNT(travel_agent_name)
FROM Transaction
WHERE amount_paid > 1000.0
GROUP BY travel_agent_name
ORDER BY travel_agent_name;
-- Display the names and ages of the travel agents who have arranged journeys for customer "John Smith", in descending order of age (use a subquery).
SELECT name, age
FROM TravelAgent
WHERE name IN (
SELECT DISTINCT(travel_agent_name)
FROM Transaction
WHERE cust_name = 'John Smith'
)
ORDER BY age DESC;
-- Display the names and ages of travel agents who have arranged journeys for customer “John Smith”, in descending order of age (do not use a subquery).
SELECT DISTINCT(ta.name), ta.age
FROM TravelAgent ta
INNER JOIN Transaction t ON ta.name = t.travel_agent_name
WHERE t.cust_name = 'John Smith'
ORDER BY ta.age DESC;
-- Display the age of travel agents who have arranged journeys for customer “John Smith” to “Ottawa” (use a subquery).
SELECT age
FROM TravelAgent
WHERE name IN (
SELECT DISTINCT(travel_agent_name)
FROM Transaction
WHERE cust_name IN (
SELECT DISTINCT(name)
FROM Customer
WHERE name = 'John Smith'
AND destination = 'Ottawa'
)
);
-- Display the age of travel agents who have arranged journeys for customer “John Smith” to “Ottawa” (do not use a subquery).
SELECT DISTINCT(ta.age)
FROM TravelAgent ta
INNER JOIN Transaction t ON t.travel_agent_name = ta.name
INNER JOIN Customer c ON c.name = t.cust_name
WHERE c.destination = 'Ottawa'
AND c.name = 'John Smith';
-- Display the names and salaries of all travel agents who did not arrange journeys for customer “John Smith”, in ascending order of salary.
SELECT Distinct(ta.name), ta.salary
FROM TravelAgent ta
WHERE ta.name NOT IN (
SELECT travel_agent_name
FROM Transaction
WHERE cust_name = 'John Smith'
)
ORDER BY ta.salary ASC;
-- Display the names of travel agents who have five or more transactions.
SELECT DISTINCT(t.travel_agent_name)
FROM Transaction t
GROUP BY t.travel_agent_name
HAVING COUNT(t.travel_agent_name) > 5;
-- Display the names of all travel agents who have arranged at least ten journeys to “Ottawa”.
SELECT DISTINCT(t.travel_agent_name)
FROM Transaction t
INNER JOIN Customer c on t.cust_name = c.name
WHERE c.destination = 'Ottawa'
GROUP BY t.travel_agent_name
HAVING COUNT(t.travel_agent_name) > 10;
|
