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
- Solving an old SQL puzzle (cartesianproduct.wordpress.com)
- Idera Covers Storage Area Networks, Networked Attached Storage in Part Two of ‘Secrets of SQL Server’ Webcast Series (prweb.com)
- The versatile MERGE T-SQL statement in SQL Server 2008 (msbistack.wordpress.com)
- Difference Between Oracle Sql *plus and MS Sql Commands (devdex.wordpress.com)
- Querying SQL Tables Named With SQL Keywords (gunnalag.wordpress.com)
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.
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 🙂