Complete question 5

1 files changed, 101 insertions, 0 deletions

diff --git a/assignments/2/README.md b/assignments/2/README.md
index fd6e92b..7b9fe29 100644
--- a/assignments/2/README.md
+++ b/assignments/2/README.md
@@ -204,12 +204,113 @@ TRANSACTION (number, cust_name, travel_agent_name, amount_paid)
 Write SQL statements to answer the following questions.
 
 * Compute the number of different customers who have a transaction.
+
+```sql
+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.
+
+```sql
+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.
+
+```sql
+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).
+
+```sql
+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).
+
+```sql
+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).
+
+```sql
+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).
+
+```sql
+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.
+
+```sql
+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.
+
+``sql
+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”.
+
+```sql
+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;
+```