Solve a bunch of queries

1 files changed, 44 insertions, 8 deletions

diff --git a/assignments/2/run.sql b/assignments/2/run.sql
index d7bffcb..293b1e4 100644
--- a/assignments/2/run.sql
+++ b/assignments/2/run.sql
@@ -57,11 +57,11 @@ CREATE TABLE Transaction(
 	amount_paid decimal
 );
 
-INSERT INTO TravelAgent VALUES('A', 30, 100000);
-INSERT INTO TravelAgent VALUES('B', 30, 100000);
-INSERT INTO TravelAgent VALUES('C', 30, 100000);
-INSERT INTO TravelAgent VALUES('D', 30, 100000);
-INSERT INTO TravelAgent VALUES('E', 30, 100000);
+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');
@@ -69,9 +69,9 @@ 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 Transaction VALUES(1, 'I', 'A', 1000.0);
-INSERT INTO Transaction VALUES(2, 'II', 'B', 1000.0);
-INSERT INTO Transaction VALUES(3, 'III', 'A', 1000.0);
+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 ---'
@@ -80,3 +80,39 @@ INSERT INTO Transaction VALUES(4, 'I', 'A', 1000.0);
 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;