15:39

	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 #37 page 3

Solution 3.2.3

Look now, how we can more easily write a query that contains a similar mistake:

Console

Execute

SELECT class
FROM (SELECT class 
 FROM Ships 
 UNION ALL 
 SELECT ship 
 FROM Outcomes o 
 WHERE o.ship IN(SELECT class 
 FROM Classes
 )
 ) AS cl
GROUP BY class
HAVING COUNT(class) = 1;

The idea: Ship classes from the Ships table are selected in the subquery and merged with the leading ships of the Outcomes table with preservation of duplicates (UNION ALL). This uses the fact that the name of the leading ship coincides with the class name (SELECT Ship) (!!!). The fact that the duplicates are stored is absolutely correct, because we get one row for a class for any number of ships in this class otherwise. Then grouping by class, and picking the classes that contain a single ship are being done. The solution looks much shorter and clearer than the solution 3.2.2. Correction it will be easier, but will have to be corrected, because the solution will give an incorrect result, if the leadIng ship is present both in the Ships table and in the Outcomes table, with the result that we count it twice.

Attention:

It is worth paying attention to the fruitful idea of this decision - first to combine all the ships, and only then carry out the grouping by classes.

Pages

mistakes grouping UNION ALL exercise 37

Previous [Exercise #70]

[Exercise #38] 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

https://exchangesumo.com/obmen/XLM-PUMBUAH/

SQL Problems and solutions

S. I. Moiseenko

Common mistakes in solving SELECT statement exercises

Database «Ships»

Exercise #37 page 3