From 4be3f841402da6b7666481da6ed499415058a21d Mon Sep 17 00:00:00 2001
From: mo khan <mo.khan@gmail.com>
Date: Thu, 30 Apr 2020 14:35:04 -0600
Subject: create index

---
 assignments/exam/README.md | 29 +++++++++++++++++++++++++++++
 1 file changed, 29 insertions(+)

diff --git a/assignments/exam/README.md b/assignments/exam/README.md
index 1f23031..fdda0dc 100644
--- a/assignments/exam/README.md
+++ b/assignments/exam/README.md
@@ -248,3 +248,32 @@ WHERE m.name IN (
   HAVING COUNT(p.manager_name) > 20
 ):
 ```
+
+## 4
+
+Consider the following database.
+
+```text
+TRAVEL-AGENT (name, age, salary)
+CUSTOMER (name, departure_city, destination, journey_class)
+TRANSACTION (number, cust_name, travel_agent_name, amount_paid)
+```
+
+a. Write a SQL query to display the names of all travel agents who arranged trips for customer "John Smith".
+
+```sql
+SELECT ta.name
+FROM "Travel-Agent" ta
+WHERE ta.name IN (
+  SELECT DISTINCT(travel_agent_name)
+  FROM TRANSACTION t
+  WHERE t.cust_name = 'John Smith'
+);
+```
+
+b. Define indexes on selected attributes to speed up your query. Justify your selections.
+
+```sql
+CREATE INDEX ON "Travel-Agent" (name); -- Joining on travel_agent.name
+CREATE INDEX ON Transaction (cust_name); -- filtering by cust_name
+```
-- 
cgit v1.2.3