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.

1. Display the names and salaries of all travel agents who did not arrange journeys for customer “John Smith”, in ascending order of salary.

2. Display the names of travel agents who have five or more transactions.

3. Display the names of all travel agents who have arranged at least ten journeys to “Ottawa”.

Answer 1

1.

SQL query that retrieves the name and salary of Travel agents who did not arrage journey for John Smith

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;

2.

SQL query that retrieves the names of travel agents who have five or more transactions.

select t.travel_agent_name from TRANSACTION t group by t.travel_agent_name HAVING COUNT(t.travel_agent_name) >= 5;

3.

SQL query that the names of all travel agents who have arranged at least ten journeys to “Ottawa”.

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;