diff options
| -rw-r--r-- | assignments/2/run.sql | 53 |
1 files changed, 52 insertions, 1 deletions
diff --git a/assignments/2/run.sql b/assignments/2/run.sql index 293b1e4..13d3692 100644 --- a/assignments/2/run.sql +++ b/assignments/2/run.sql @@ -67,7 +67,7 @@ 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', 'Edmonton', '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); @@ -116,3 +116,54 @@ 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; |