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
Solution 4.4.2. Predicate EXISTS (as usual for this predicate, it uses a correlated subquery)
Now, there are a few more original solutions.
Solution 4.4.3. Using correlated subqueries with grouping
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 (*).