loading..
Русский    English
06:28
go

Exercise #15

Find the hard drive sizes that are equal among two or more PCs. Result set: hd.

A superficial knowledge of data structure is the reason of incorrect solution:

Solution 1.11.1

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

This query extracts those PCs for which an another model of PC with same size of hard drive exists. The mistake is in the intuitive notion of the model uniqueness in the PC table. However, as was mentioned early, a model numbers are unique only in Product table. But here a  numbers can be not unique. So, the query is wrong because the same models with same hard drive size are not taken into account.

T&S

To solve the 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 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 EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100