Typical mistakes at the solution of SELECT statement exercises

Tips and solutions

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

SELECT DISTINCT maker FROM Product WHERE type = 'PC' AND       maker NOT IN (SELECT maker                     FROM Product                     WHERE type = 'Laptop'                     );  

    Scheme

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

Console

Execute

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

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

    Scheme

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;

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;

    Scheme

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 (*).