Consider the following relational schema for the airport transfer database: airp

Question

Consider the following relational schema for the airport transfer database:

airport ( a-code, name, capacity) <- a-code is primary key
customer ( c-id, name, birth-year, address) <- c-id is primary key
vehicle ( plate, capacity, driver-name) <- plate is primary key
transfer( a-code, c-id, plate, year, ti-no) <- a-code, c-id, plate, year are primary key
ticket ( ti-no, price) <- ti-no is primary key

Give the corresponding SQL queries for each of the following.
(a) Find the vehicle plates, which are used in the transfers to "Esenboga" Airport, and also have a driver whose name has at least five characters and starts with 'Z'.

(b) List the airport codes with the total number of transfer tickets sold, and also total price.

(c) Find the oldest customer's birth year and address, who have not bought any ticket yet.

(d) Find the code and name of the airport with the 5th highest capacity. You can assume, for simplicity, that the capacity values are distinct.

(e) Delete vehicles that have capacity lower than the average number of customers that are transferred by a vehicle in 2017.

Explanation / Answer

Note: Below queries follow MySQL database syntax

Answer (a):

select v.plate from vehicle v
inner join transfer t on v.plate=t.plate
inner join airport a on a.a-code=t.a-code
where a.name='Esenboa' and v.driver-name like 'Z_____%';

-----------------------------------------------------------------------------------------------

Answer (b):

select t.a-code,count(*) as total_transfer_ticket_sold, sum(tkt.price) as total_price from transfer t
inner join ticket tkt on t.ti-no=tkt.ti-no
group by t.a-code;

----------------------------------------------------------------------------------------

Answer (c):

select c.name,c.birth-year,c.address from customer c
left join transfer t on c.c-id=t.c-id
where t.c-id is null
order by c.birth-year asc
limit 1;

------------------------------------------------------------------------------

Answer d:

select a.* from
(select * from airport order by capacity desc limit 5) a
order by a.capacity asc
limit 1;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.