SQLQuery.sql

use DWHAIRLINE

-- what flights the company’s frequent flyers take?
select FLIGHT_NUMBER, COUNT(FLIGHT_NUMBER) as frequent_flight
from FLIGHTS
group by FLIGHT_NUMBER
having FLIGHT_NUMBER=Max(FLIGHT_NUMBER)

-- what fare basis they pay?
select distinct PASSENGER_FK,T.TICKETS_CLASS,T.TICKETS_FARE
from MARKETING M, TICKETS T
Where M.TICKET_FK=T.TICKET_PK

--	how often they upgrade?
SELECT FLIGHT_FK,SUM(UPGRADED_TICKETS) AS No_upgrade
FROM MARKETING
GROUP BY FLIGHT_FK

-- how long their overnight stays are?
SELECT P.PASSENGER_PK ,SUM(FLIGHT_DURATION) AS FLIGHT_DURATION
FROM FLIGHTS F, PASSENGERS P, RESERVATIONS R
WHERE P.PASSENGER_PK=R.PASSENGER_FK
AND R.FLIGHT_FK=F.FLIGHT_PK
GROUP BY P.PASSENGER_PK

-- what proportion of these frequent flyers have gold, platinum, aluminum, or titanium status?
SELECT FLIGHT_FK,
(sum(GOLD_PASSENGERS)/sum(PASSENGER_FK)*100) as GOLD_PASSENGERS_PRSE,
(sum(TITANIUM_PASSENGERS)/sum(PASSENGER_FK)*100)as TITANIUM_PASSENGERS_PRSE,
(sum(PLATINUM_PASSENGERS)/sum(PASSENGER_FK)*100)as PLATINUM_PASSENGERS_PRSE,
(sum(ALUMINUM_PASSENGERS)/sum(PASSENGER_FK)*100)as ALUMINUM_PASSENGERS_PRSE
FROM MARKETING
group by FLIGHT_FK

-- The number of times each problem occurs
select PASSENGER_COMPLAINT,COUNT(PASSENGER_COMPLAINT) AS No_PROBLEMS
FROM PROBLEMS
GROUP BY PASSENGER_COMPLAINT

--The number of bookings in each channel
select RESERVATION_CHANNEL,COUNT(RESERVATION_CHANNEL) AS No_RESERVATION
FROM RESERVATIONS_CHANNEL
GROUP BY RESERVATION_CHANNEL

--Which city has been visited more than once?
select  distinct FLIGHT_ARRIVAL_CITY,COUNT(FLIGHT_ARRIVAL_CITY) AS No_OF_visits
FROM FLIGHTS
GROUP BY FLIGHT_ARRIVAL_CITY
HAVING COUNT (FLIGHT_ARRIVAL_CITY)>1