Another SQL puzzle

Standard
3 Comments
Time And Date

Image by mobology via Flickr

Once more from http://www.sql-ex.ru which seems to have a phase-of-the-moon related availability.

Database is defined as:

Database schema consists of 4 tables:
Company(ID_comp, name)
Trip(trip_no, ID_comp, plane, town_from, town_to, time_out, time_in)
Passenger(ID_psg, name)
Pass_in_trip(trip_no, date, ID_psg, place)
Company table has ID and name of the company, which transports passengers. Trip table has information about trips: trip number, company ID, plane type, departure city, arrival city, departure time, and arrival time. The passenger table has passenger’s ID and passenger’s name. Pass_in_trip table has information about the flights: trip number, departure date (day), passenger’s ID and his place during the flight. We should note that,
– Any trip is being accomplished every day; duration of a flight is less than a calendar-day (24 hours);
– Time and date are considered comparatively one time zone;
– The departure time and the arrival time are given to within a minute;
– There can be the passengers bearing the same names (for example, Joe Bloggs);
– The place during the flight is a number followed by a letter; the number defines the row number, the letter (a – d) – the place in the row (from the left to the right) in the alphabetical order;
– Relationships and restrictions are shown in the data schema.

And the question is:

Among the clients which only use a single company, find the different passengers who have flown more often than others. Result set: passenger name, number of trips, and company name.

So, I have this SQL:

SELECT P.name, COUNT(T.trip_no) AS journeys, C.name
FROM
Passenger P, Trip T, Company C, Pass_in_trip PIT
WHERE
P.ID_psg = PIT.ID_psg AND PIT.trip_no = T.trip_no
AND T.ID_comp = C.ID_comp
AND
NOT EXISTS (SELECT * FROM
Passenger, Trip, Company, Pass_in_trip
WHERE Pass_in_trip.ID_psg = P.ID_psg AND Pass_in_trip.trip_no = Trip.trip_no
AND Trip.ID_comp <> C.ID_comp)
GROUP BY P.name, C.name

Which correctly, as far as I can see, outputs the names of clients who use only one company, the number of trips they have made and the company name.

But how do I limit that to the MAX?

I have tried adding “HAVING MAX(COUNT(T.trip))” but the RDMS objects – telling me I am trying to evaluate something that does not give a Boolean answer as a Boolean.

Related articles

3 thoughts on “Another SQL puzzle”

  1. I don’t think you need Passenger and Company in the FROM clause of the nested query.

    HAVING(MAX(…)) probably gorks because MAX() returns a value. Assuming you are looking for the most frequent flier dedicated to each company (the wording of the task was slightly fuzzy), I think you need to nest your two level query inside yet another query, which selects name and journeys from those in the current query having journeys = MAX(journeys), grouped by company.

  2. You ought to charge🙂

    You are right about the FROM clause, they are left over from my initial scribbles – I had a longer where clause which, as I was typing I realised could be shorter but plainly didn’t clean it all up.

    I guess you are right about the third level of query nesting. I could see that was a possibility before but thought I was probably missing a simpler way to do this.

    My exam is tomorrow, so hopefully I won’t be looking for your help on this stuff for much longer🙂

  3. Pingback: The long way round? | cartesian product

Comments are closed.