loading..
Ðóññêèé    English
18:55
go

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:

Console
Execute
  1. SELECT Trip.time_out, Trip.time_in
  2.        FROM Trip
  3.        WHERE Trip.id_comp=2;

time_out                time_in
1900-01-01 09:35:00.000 1900-01-01 11:23:00.000
1900-01-01 17:55:00.000 1900-01-01 20:01:00.000
It 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:

Console
Execute
  1. SELECT pt.trip_no, date, time_out, time_in
  2.        FROM pass_in_trip pt 
  3.        JOIN
  4.        (SELECT trip_no,time_out,time_in FROM trip WHERE id_comp=2) t
  5.                ON t.trip_no=pt.trip_no 
  6.        GROUP BY pt.trip_no, date, time_out, time_in;

Here is the result of the query above:

trip_no  date                    time_out                time_in
1145     2003-04-05 00:00:00.000 1900-01-01 09:35:00.000 1900-01-01 11:23:00.000
1145     2003-04-25 00:00:00.000 1900-01-01 09:35:00.000 1900-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
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.