loading..
Ðóññêèé    English
18:37
go

Exercise #15 (tips and solutions)

Incorrect solution 1.11.1 for this exercise is easily corrected if is not distinguish PC by model number, but, as expected, by the primary key - column code.

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.               );

Because it is only two PCs needed with identical hard drives, you can use a self-join the PC table in similar conditions:

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 will be the solution with grouping and conditions of the selection in a HAVING clause:

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

For completeness, we present another solution that uses a subquery with grouping and which performance is also inferior to the 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 the correlated subquery, the subquery has to be executed for each row of the main query. Join query has the lowest productivity. This is understandable, since the join operation is very costly, despite the fairly efficient algorithms for their implementation  A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server 2005" class="e" href="/en/book_bibliography.html">[5].

To return to discussion of exercise #15

To solve a problem on SQL-EX.RU
Bookmark and Share
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 date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates equi-join EXCEPT exercise (-2) exercise 19 More tags
The book was updated
several days ago
scott plank
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100