loading..
   English
03:21

Exercise #8 (tips and solutions) page 1

First couple of "natural" solutions, which differ only in the predicate, which checks the lack of vendor models of portable computer.

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 (usually for this predicate, the subquery is correlated)

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 I provide a few 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;

In the subqueries, are verified that the number of PC models from the supplier from main query is greater than zero, while the number of models of portable computers of the same provider is zero.

Attention is drawn to the argument of COUNT (1). The standard defines two types of arguments of this function: "*" and an expression. The use of "*" leads to the calculation of the number of rows of query. Using the expression gives the number of rows for which the expression has a value that is not NULL. As an expression is usually a column name, so the involvement of constant surprise to those who are not yet sufficiently familiar with the language. Since the constant (in the query 1) can not be NULL, then this expression is completely equivalent to COUNT (*).

Bookmark and Share
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 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