create index

1 files changed, 29 insertions, 0 deletions

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
+```