loading..
Ðóññêèé    English
18:23

Exercise #15

Get hard drive capacities that are identical for two or more PCs.

This incorrect solution is the result of a superficial knowledge of the database schema:

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 gets such PCs for which another PC model having the same hard drive capacity exists. The mistake lies in the intuitive assumption of the model uniqueness in the PC table. However, as mentioned earlier, models are unique in the Product table only; in other tables, duplicates can occur. This fact renders the query under review wrong, since it excludes identical models with the same hard drive capacity from consideration.

T&S

Solve this task at 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 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
https://exchangesumo.com/obmen/to/KSPBKZT/ . Òàéíîå ïîñëàíèå, èëè Âåñ¸ëûé êîíêóðñ
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.