22:32

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Common mistakes in solving SELECT statement exercises

Tips and solutions

Exercise #46 (tips and solutions)

Example of automatic join of two solutions promised in 3.1 accounting one of two moments leads to mistakes, can look like this (read comments).

Console

Execute

-- Ships which had taken part in the battle of Guadalcanal and are present in the Ships table.
-- Take note of using the correlative subquery in the WHERE clause
-- which solve problem of removing duplicates in Cartesian product. 
SELECT a.ship, displacement, numGuns
FROM (SELECT ship 
 FROM Outcomes 
 WHERE battle = 'Guadalcanal'
 ) AS a, Classes 
WHERE class IN (SELECT class 
 FROM Ships 
 WHERE name = a.ship
 )
UNION
-- The query is analogous to one which selects lead ships from
-- Outcomes table which had actioned in battle of Guadalcanal.
SELECT a.ship, displacement, numGuns
FROM (SELECT ship 
 FROM Outcomes 
 WHERE battle = 'Guadalcanal'
 ) AS a, Classes 
WHERE class IN (SELECT ship 
 FROM Outcomes 
 WHERE ship = a.ship
 )
UNION
-- In essence this is the solution in 3.1.1
SELECT a.ship, displacement, numGuns
FROM (SELECT ship 
 FROM Outcomes 
 WHERE battle = 'Guadalcanal'
 ) AS a LEFT JOIN 
 Classes ON a.ship = class;

The excessive rows like below will be return as a result:

ship	displacement	numGuns
California	32000	12
California	NULL	NULL

It`s possible to complicate this query more (and make it less efficient), by adding code for exception the wrong row. The presence of NULL-value in displacement column may be a criteria for exception, if there is one else row with the same ship`s name. However, we advice to manage without this solution and solve task by another way. It is possible, and you can make certain of it by visiting cite`s forum.

In conclusion let`s note almost right solution.

Console

Execute

SELECT name, displacement, numGuns
FROM Classes, Ships
WHERE Classes.class = Ships.class AND 
 name IN (SELECT Ship 
 FROM Outcomes
 WHERE battle = 'Guadalcanal'
 )
UNION 
SELECT class, displacement, numGuns
FROM Classes
WHERE class IN(SELECT ship 
 FROM Outcomes
 WHERE battle = 'Guadalcanal'
 );

The first query from union finds information about ships which is present in the Ships table and had taken part in the battle of Guadalcanal. The second one finds required lead ships in the Outcomes table. The potential duplicates (lead ship is also present in the Ships table) excepts by using UNION clause.

So what`s wrong with this solutuion? If it`s no clear for you, go back to item 3.1 task`s discussion.

To return to discussion of exercise #46

To solve a problem on SQL-EX.RU

tips and solutions Outcomes ships Guadalcanal exercise 46 Bismarck

Previous [Exercise #30 (tips and solutions)]

[Exercise #37 (tips and solutions)] 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
month ago

SQL Problems and solutions

S. I. Moiseenko

Common mistakes in solving SELECT statement exercises

Tips and solutions

Exercise #46 (tips and solutions)