2

I need to find the airport(departure or arrival) that had the most passengers in the last year.

The tables are:

PASSENGER(id,name,surname)
TAKES(id_passenger,id_flight)
FLIGHT(id,departure_id,arrival_id,departure_date,arrival_date,id_plane)
AIRPORT(id,name,city,country)
PLANE(id,type,capacity)

What I think I need to do is:
Count the number of passengers each airport has and return that but I do not know how to pick the one with the most passengers among those two. Do I need to use nested select queries? Would it be useful to use something like

WITH maxdep as 
  SELECT ( max passengers from departure airport)
maxar as SELECT  ( max passengers from arrival airport)
select max (val1, val2),  from ... ?

How do I return just one?

EDIT: I was thinking of a case select from the two previous with statements. Can that work?

WITH departures as (SELECT departure_id, count(id_passenger)  
    from passenger, takes, flight
    WHERE passenger.id=takes.id_passenger
    and takes.id_flight=flight.id
    and flight.departure_date between '01-JAN-2013' AND '01-JAN-2014'
    group by 1
    order by 2 desc
    where ROWNUM <2)

arrivals as (SELECT arrival_id, count(id_passenger) 
    from passenger, takes, flight
    WHERE passenger.id=takes.id_passenger
    and takes.id_flight=flight.id
    and flight.arrival_date between '01-JAN-2013' AND '01-JAN-2014'
    group by 1
    order by 2 desc
    where ROWNUM <2)
SELECT CASE 
        WHEN departures.count > arrivals.count THEN departures.departure_id  
        WHEN departures.count < arrivals.count THEN arrivals.arrival_id  
        ELSE departures.departure_id || 'has the same number of visitors as'|| arrivals.arrival_id   
        END;  

I am sorry if this is messed up

Will this work?

  • Your query does not match your table definitions (and is syntactically invalid). I assume this is homework so I will only give you a hint on where to start. Find out the number of passengers per flight. Add that to your question and any additional questions you might have. – Lennart Sep 4 '14 at 13:35
  • The query is just a sketch. I am thinking about making two "with" selections where i count the passengers for departure airport / arrival airport, group by airport sort by count and from these two tables make a select with case and if count from departure > arrival return departure and the other if arrival> departure. Will it work? – Thom E. Sep 4 '14 at 16:19
  • Your over complicating things, just start with counting passengers for each flight, from there it is easy to get to the final answer. – Lennart Sep 4 '14 at 16:37
  • Well that is what i tried to do, it's just that because i must check for both the arrival and departure having more people i ended up using "WITH". – Thom E. Sep 4 '14 at 16:43
  • Ok, I see I missed one requirement, the "most passengers in the last year". I will actually be easier to go with your approach with two cte. I'll give a sketch in an answer (I think you have shown that you have tried to solve the question) – Lennart Sep 4 '14 at 16:49
0

Here's one possibility:

with departures(departure_id, cnt) as (
    select f.departure_id, count(t.id_passenger)
    from flight f
    join takes t
        on f.id = t.id_flight
    where year(departure_date) = ?
    group by f.departure_id
),
arrivals as (arrival_id, cnt) as (
    select f.arrival_id, count(t.id_passenger)
    from flight f
    join takes t
        on f.id = t.id_flight
    where year(arrival_date) = ?
    group by f.arrival_id
)
select position, max(cnt)
from (
    select arrival_id as position, cnt
    from arrivals
    union
    select departure_id as position, cnt
    from departures
) as t;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.