loading..
Ðóññêèé    English
05:46
go

Exercise #8 (tips and solutions) page 1

Let’s start with a couple of "natural" solutions that differ only in the predicate checking the maker doesn’t manufacture any laptops.

Solution 4.4.1. Predicate NOT IN 

Console
Execute
  1. SELECT DISTINCT maker
  2. FROM Product
  3. WHERE type = 'PC' AND 
  4.       maker NOT IN (SELECT maker 
  5.                     FROM Product 
  6.                     WHERE type = 'Laptop'
  7.                     );  

Solution 4.4.2. Predicate EXISTS (as usual for this predicate, it uses a correlated subquery)

Console
Execute
  1. SELECT DISTINCT maker
  2. FROM Product AS pc_product
  3. WHERE type = 'pc' AND 
  4.       NOT EXISTS (SELECT maker 
  5.                   FROM Product
  6.                   WHERE type = 'laptop' AND 
  7.                         maker = pc_product.maker
  8.                   );

Now, there are a few more original solutions.

Solution 4.4.3. Using correlated subqueries with grouping

Console
Execute
  1. SELECT DISTINCT maker 
  2. FROM Product AS p 
  3. WHERE (SELECT COUNT(1) 
  4.        FROM Product pt 
  5.        WHERE pt.type = 'PC' AND 
  6.              pt.maker = p.maker
  7.        ) > 0 AND 
  8.       (SELECT COUNT(1) 
  9.        FROM Product pt 
  10.        WHERE pt.type = 'Laptop' AND 
  11.              pt.maker = p.maker
  12.        ) = 0;

The subqueries check that the number of PC models by the manufacturer in the outer query is greater than zero, while the number of models of portable computers of the same maker is zero.

Please note the usage of COUNT (1). The standard defines two types of arguments for this function: "*" and an expression. The usage of "*" results in calculating the number of rows returned by the query. Using an expression counts the number of rows for which said expression has a value that is not NULL. Since in most cases, a column name acts as an expression, using a constant can be surprising for those not yet sufficiently familiar with the language. Obviously, a constant (1 in our case) can’t be NULL; thus, this expression is completely equivalent to COUNT (*).
Pages 1 2
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.