Exercise 93

For each company, find time the company’s planes have spent during accomplished flights. Result set: company name, time in minutes.

  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;
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]
time_outtime_in
1900-01-01 09:35:00.0001900-01-01 11:23:00.000
1900-01-01 17:55:00.0001900-01-01 20:01:00.000

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;
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]

Here is the result of the query above:

trip_nodatetime_outtime_in
11452003-04-05 00:00:00.0001900-01-01 09:35:00.0001900-01-01 11:23:00.000
11452003-04-25 00:00:00.0001900-01-01 09:35:00.0001900-01-01 11:23:00.000

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