13:35

# 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
`SELECT DISTINCT makerFROM ProductWHERE type = 'PC' AND       maker NOT IN (SELECT maker                     FROM Product                     WHERE type = 'Laptop'                    );  `

Solution 4.4.2. Predicate EXISTS (usually for this predicate, the subquery is correlated)

Console
Execute
`SELECT DISTINCT makerFROM Product AS pc_productWHERE type = 'pc' AND       NOT EXISTS (SELECT maker                   FROM Product                  WHERE type = 'laptop' AND                         maker = pc_product.maker                  );`

Now I provide a few original solutions.

Solution 4.4.3. Using correlated subqueries with grouping

Console
Execute
`SELECT DISTINCT maker FROM Product AS p WHERE (SELECT COUNT(1)        FROM Product pt        WHERE pt.type = 'PC' AND              pt.maker = p.maker       ) > 0 AND       (SELECT COUNT(1)        FROM Product pt        WHERE pt.type = 'Laptop' AND              pt.maker = p.maker       ) = 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 (*).

 Pages 1 2