loading..
Ðóññêèé    English
03:36
go

Exercise #15 (tips and solutions)

The incorrect solution 1.11.1 for this exercise can be corrected easily by using, instead of the model number, the proper column – the primary key code - for distinguishing PCs:

Console
Execute
  1. SELECT DISTINCT t.hd 
  2. FROM PC t 
  3. WHERE EXISTS (SELECT * 
  4.               FROM PC 
  5.               WHERE pc.hd = t.hd AND 
  6.                     pc.code <> t.code
  7.               );

Since it is sufficient to check matching hard drive capacities for as little as two PCs, a self-join of the PC table with the same conditions can be used:

Console
Execute
  1. SELECT DISTINCT pc1.hd 
  2. FROM PC pc1, PC pc2 
  3. WHERE pc1.hd = pc2.hd AND 
  4.       pc1.code <> pc2.code;

However, the optimal solution would be using grouping with a filtering condition in a HAVING clause

Console
Execute
  1. SELECT
  2. PC.hd FROM PC
  3. GROUP BY hd
  4. HAVING COUNT(hd) > 1;

For the sake of completeness, here is another solution using a subquery with grouping, whose performance is inferior to that of the one above:

Console
Execute
  1. SELECT DISTINCT hd
  2. FROM PC
  3. WHERE (SELECT COUNT(hd) 
  4.        FROM PC pc2 
  5.        WHERE pc2.hd = pc.hd
  6.        ) > 1;

The reason for the low efficiency of the solutions with subqueries is that they all use a correlated subquery, i. e. a subquery to be re-executed for each row returned by the main query. The query using join is the worst-performing one. This is quite understandable, since join operations are very costly despite the fairly efficient algorithms of their implementation [ 5 ].

Return to discussion of exercise #15

Solve this task at SQL-EX.RU
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-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.