Exercise 124

Among the passengers, who flew by the planes of more then one company, find those who made the same number of trips by the planes of each of these companies. Result set: passenger name.

This task generates a big number of wrong solutions, which I further subdivide into two groups. The first type of error is caused by misinterpreting of the task. For example, some visitors try to identify two passengers, who would
have made equal number of flights with two or more airlines.

I want to make it clear, that it is necessary to consider an individual passenger and to count the number of flights, he has made with each of the airlines, the services of which he has used.

What comes next? Lets consider an example from the second group of wrong solutions:

  2. FROM (SELECT id_psg, id_comp, COUNT(pt.trip_no) AS CNT
  3. FROM pass_in_trip pt JOIN trip t ON pt.trip_no=t.trip_no
  4. GROUP BY id_comp,id_psg)a,
  5. (SELECT id_psg, id_comp, COUNT(pt.trip_no) AS CNT
  6. FROM pass_in_trip pt JOIN trip t ON pt.trip_no=t.trip_no
  7. GROUP BY id_comp,id_psg)b,
  8. passenger p
  9. WHERE a.id_psg=b.id_psg AND a.id_comp<>b.id_comp AND a.cnt=b.cnt
  10. AND p.id_psg=b.id_psg;

I want to point it out, that the erroneous DISTINCT name eliminates the possibility of taking namesakes into account from the start. Although thats not the most important thing about it. Two identical queries are joined in FROM clause

  1. SELECT id_psg, id_comp, COUNT(pt.trip_no) AS CNT
  2. FROM pass_in_trip pt JOIN trip t ON pt.trip_no=t.trip_no
  3. GROUP BY id_comp,id_psg;

As it is mentioned above, they are aimed to count for each passenger the number of flights, he has made with the planes of each company.

  • the passenger is the same;
  • the companies are different;
  • the number of flights coincides.

Thus, if the passenger has made 3 flights, say, with Aeroflot Company and 3 flights with Don_avia Company, such a passenger satisfies the terms of such a joining and his name will be returned within the results of such a query. If the passenger has used only two companies, it is the correct result. But what if he has used three companies?

If the result of the aforementioned subquery is

Bruce Willis       Don_avia         2
Bruce Willis       Aeroflot          2
Bruce Willis        Dale_avia        1
then the passenger Bruce Willis does not correspond to the terms of the task, although the query under consideration will print it out, because two first lines are joined in the query.

Thus, the number of flights of a passenger with each of the companies, the services of which he has used, must be in proportion 1:1:...:1.

To solve the problem on SQL-EX.RU

Bookmark and Share
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 CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100