08:20

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

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

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, there are a few more 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;`

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