Consider the following three relations: TRAVEL_AGENT (name, age, salary) CUSTOME
ID: 3606136 • 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:
a. Compute the number of different customers who have a transaction.
b. Display the name of the oldest travel agent.
c. List the total number of transactions for each travel agent. Consider only those transactions where the amount paid exceeds 1 000.
d. 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).
e. 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).
f. Display the age of travel agents who have arranged journeys for customer “John Smith” to “Ottawa” (use a subquery).
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
a. Here count will find the number of distinct name from customer and transation table where the name of customer in customer table equals the cust_name in transaction table.
b. Here the subquery "(select max(age) from TRAVEL_AGENT) " will find the max age of the travel agent. And the front query will match the max age from subquery to the TRAVEL_AGENT table match and if match found will display it which will bethe oldest travel agent.
SELECT name FROM TRAVEL_AGENT WHERE AGE=(select max(age) from TRAVEL_AGENT));
c. Here we are doing the join of the table transaction and travel_agent when the name in transaction table travel_agent_name equals the name in TRAVEL_AGENT table and also the amount_paid in transaction is greater than 1000
SELECT travel_agent_name, number FROM TRANSACTION t1 JOIN TRAVEL_AGENT t2 where t1.travel_agent_name=t2.name and t1.amount_paid>1000 ;
d. Here the subquery "(SELECT travel_agent_name from TRANSACTION where cust_name=John Smith)" will give travel_agent_name where the cust_name in the table equals John Smith. Then we match it with the name in the TRAVEL_AGENT table which gives the name,age of the travel agents who have arranged journeys for customer “John Smith” and then we arrange it by age in descending order by usind order by.
SELECT name, age FROM TRAVEL_AGENT t1 where name=(SELECT travel_agent_name from TRANSACTION where cust_name=John Smith) order by age DESC;