Tricky SQL Exercise

Here is the database description:

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, Bruce Willis);
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.

Here is the Question:

Find the names of the different passengers, which flew more than once in the same seat.

I’ve tried this query

select name  from (
select id_psg, count(name) as total from (
select a.id_psg, name, date,place from passenger a join
pass_in_trip b on a.id_psg=b.id_psg order by a.id_psg, place
) as t1
group by t1.id_psg
) as a join passenger b on a.id_psg = b.id_psg 
join pass_in_trip c on a.id_psg=c.id_psg
where total > 1
group by name,place
having count(place) >=2 
order by name,place;

But it says:

Wrong
Your query produced correct result set on main database, but it failed test on second, checking database
* Wrong number of records (more by 8)

This is an exercise from SQL-RU btw.

Answer

SELECT
    p.name
FROM
    passenger AS p
  JOIN
    pass_in_trip AS pt
      ON p.id_psg = pt.id_psg
GROUP BY 
    p.id_psg, p.pame
HAVING
    COUNT(DISTINCT pt.place) < COUNT(*) ;

Advertisement

Answer