Finish remaining queries

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;