14:49

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Common mistakes in solving SELECT statement exercises

Tips and solutions

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 maker
FROM Product
WHERE 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 maker
FROM Product AS pc_product
WHERE 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

tips and solutions EXISTS correlated subqueries exercise 8 NOT IN COUNT(1)

Previous [Exercise #7 (tips and solutions)]

[Exercise #10 (tips and solutions)] Next

Last added:

Tags

aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags

The book was updated
month ago

SQL Problems and solutions

S. I. Moiseenko

Common mistakes in solving SELECT statement exercises

Tips and solutions

Exercise #8 (tips and solutions) page 1