Consider the following three relations: TRAVEL_AGENT (name, age, salary) CUSTOME
ID: 3891471 • 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.
1. 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).
2. Display the age of travel agents who have arranged journeys for customer “John Smith” to “Ottawa” (use a subquery).
3. Display the age of travel agents who have arranged journeys for customer “John Smith” to “Ottawa” (do not use a subquery).
Explanation / Answer
Helllo there,
I am providing the queries for the above three questions.
I have tested them on test database, and they are working perfectly fine..
[1] -> select distinct(TRAVEL_AGENT.name), TRAVEL_AGENT.age from TRAVEL_AGENT,CUSTOMER,TRANSACTION where (TRAVEL_AGENT.name=TRANSACTION.travel_agent_name and TRANSACTION.cust_name="John Smith") ORDER BY TRAVEL_AGENT.age DESC;
[2] -> select age from TRAVEL_AGENT where name in (select travel_agent_name from TRANSACTION,CUSTOMER where cust_name="John Smith" and destination="Ottawa");
[3] -> select age from TRAVEL_AGENT,TRANSACTION,CUSTOMER where TRAVEL_AGENT.name=travel_agent_name and cust_name="John Smith" and destination="Ottawa";
Feel free to ask queris and modifications in above solutions.
I am here to help!!!
Thank you so much!! :)