Exercise 93

The issue with this exercise may be illustrated by a message of one of our participants:

SELECT Trip.time_out, Trip.time_in
FROM Trip
WHERE Trip.id_comp=2;

t turns out that the company Aeroflot has made two flights, the first one has flight time of 1 hour 48 minutes, the second one - 2 hour 6 minutes, The total duration of flights turns out (108 + 126) = 234 minutes, but not is 216 minutes (as it is specified in “correct result”).

Misunderstanding is caused by insufficient studying of the description and the scheme of database. Trip table represents the schedule of flights, which are carried out daily.

The information on flights of passengers contains in Pass_in_trip table. Let’s look what flights of the company with id_comp=2 have been carried out:

SELECT pt.trip_no, date, time_out, time_in
FROM pass_in_trip pt
    JOIN (SELECT trip_no,time_out,time_in 
          FROM trip WHERE id_comp=2
         ) t ON t.trip_no=pt.trip_no
GROUP BY pt.trip_no, date, time_out, time_in;

Here is the result of the query above:

So, the first flight has been carried out twice, but the second one did not be carried out at all, i.e. 108*2 = 216.

To solve the problem on SQL-EX.RU