Books and articles about SQL Rambler's Top100 Сменить язык на: Русский 29 March 2024 05:17:54


www.sql-ex.ru
Skip Navigation Links  

 

Print  Print version

Main page

Typical errors at solution of exercises. Exercise 40

S. Moiseenko

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

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:

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:

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.

Main page

Print  Print version


Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.