Consider the following three relations: TRAVEL_AGENT (name, age, salary) CUSTOME
ID: 3675740 • 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.
Explanation / Answer
TRAVEL_AGENT (name, age, salary)
CUSTOMER (name, departure_city, destination, journey_class)
TRANSACTION (number, cust_name, travel_agent_name, amount_paid)
As we all know every relation is identified by Primary key. So we first decide what will be the primary key for all the relations
For TRAVEL_AGENT : As given name, age, salary can not identified each tuple in relation nor combination of all. So I am assuming Name is unique attribute in relation. So name is primary key
For Customer: For same reason as above name is primary key
For Transaction: Transaction Number would be primary key
a. Compute the number of different customers who have a transaction.
In Transaction relation one customer can have different transaction numbers also because of multiple transactions So transaction numbers would be different and customer name can be same.
For this query We need to count distinct customer names.
Select count(distinct(cust_name)) from TRANSACTION
b. Display the name of the oldest travel agent.
Here oldest travel agent means agent who have maximum age.
Select name from TRAVEL_AGENT where age= (select max(age) from TRAVEL_AGENT)
Inner query will give you maximum age present in relation .
c. List the total number of transactions for each travel agent. Consider only those transactions where the amount paid exceeds 1 000.
In transaction relation one travel agent can have multiple transactions. So travel agent name can duplicate.
Select count(number) from TRANSACTION group by (travel_agent_name) having amount_paid >1000
In this query travel agent name can be multiple So we will group by according to travel agent name then one agent name has multiple transaction numbers. After that we will filter out those whose amount paid is less than 1000. Remains only those whose amount paid is greater than 1000.
After that we will count all those transaction numbers and output that count.