21:31

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Common mistakes in solving SELECT statement exercises

Database «Ships»

Exercise #70 page 2

Solution 3.7.2

Console

Execute

SELECT bat 
FROM (SELECT DISTINCT d.battle AS bat, a.country, COUNT(d.ship) AS s
      FROM Outcomes d, Ships b, Classes a
      WHERE d.ship = b.name AND 
            b.class=a.class
      GROUP BY d.battle, a.country 
      UNION 
      SELECT DISTINCT d.battle AS bat, a.country, COUNT(d.ship) AS s
      FROM Outcomes d, Classes a
      WHERE d.ship = a.class AND 
            d.ship NOT IN (SELECT name 
                           FROM Ships
                           )
      GROUP BY d.battle, a.country
      ) AS t1
WHERE s > 2;

The typical mistake of many beginners is in the order of operations under the resultant set. At first, they implement grouping and then they unioning. In this case the mistake of solution 3.7.1 is absent, but this solution do not returns country, which have three ships taking part in one battle, two of its` are in the table Ships and one (leading ship) is in the Outcomes table.

There was time the checking system accepted the wrong solution:

Pages

mistakes GROUP BY COUNT exercise 70

Previous [Database «Ships»]

[Exercise #37] Next

Last added:

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 Problems and solutions

S. I. Moiseenko

Common mistakes in solving SELECT statement exercises

Database «Ships»

Exercise #70 page 2