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
Solution 4.4.2. Predicate EXISTS (usually for this predicate, the subquery is correlated)
Now I provide a few original solutions.
Solution 4.4.3. Using correlated subqueries with grouping
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 (*).