Consider the following three relations: TRAVEL_AGENT (name, age, salary) CUSTOME
ID: 3675743 • Letter: C
Question
Consider the following three relations:
TRAVEL_AGENT (name, age, salary)
CUSTOMER (name, departure_city, destination, journey_class)
TRANSACTION (number, cust_name, travel_agent_name, amount_paid)
Write SQL statements to answer the following questions.
g. Display the age of travel agents who have arranged journeys for customer “John Smith” to “Ottawa” (do not use a subquery).
h. Display the names and salaries of all travel agents who did not arrange journeys for customer “John Smith”, in ascending order of salary.
i. Display the names of travel agents who have five or more transactions.
j. Display the names of all travel agents who have arranged at least ten journeys to “Ottawa”.
Explanation / Answer
g) select tg.age from TRAVEL_AGENT tg, CUSTOMER c, TRANSACTION t where tg.name = t.travel_agent_name and t.cust_name = c.name and c.name = "John Smith" and c.destination = "Ottawa";
h) select tg.name,tg.salary from TRAVEL_AGENT tg, CUSTOMER c, TRANSACTION t where tg.name = t.travel_agent_name and t.cust_name = c.name and c.name not in ("John Smith") order by tg.salary ASC;
i) select t.travel_agent_name from TRANSACTION t group by t.travel_agent_name HAVING COUNT(t.travel_agent_name) >= 5;
j)select t.travel_agent_name from TRANSACTION t,CUSTOMER c where (t.cust_name=c.name and c.destination = "Ottawa") group by t.travel_agent_name HAVING COUNT(t.travel_agent_name) >= 10;