Exercise #23

Query below includes typical mistake peculiar to beginners which solve this exercise.

Solution 1.15.1

SELECT DISTINCT maker
FROM product
WHERE model IN (SELECT model
FROM PC
WHERE speed >= 750
) OR
model IN (SELECT model
FROM Laptop
WHERE speed >= 750
);

The mistake lies in the fact that the result set will include a producer which manufactures something one: either PC or laptops, because the predicate in WHERE clause will be true if at least one of two conditions linked with OR operator will be satisfied. This doesn’t match the task and is fairly discarded by a system.

T&S

To solve the problem on SQL-EX.RU

There is one more attempt to “change” the situation in the better way:

Solution 1.15.2

SELECT DISTINCT maker
FROM Product a, PC b, Laptop c
WHERE b.speed >= 750 AND
c.speed >= 750 AND
(a.model = b.model OR
a.model = c.model
);

Using an equality of predicates,

x AND (y OR z) = (x AND y) OR (x AND z),

we’ll perform syntax conversions of the considered query: 

SELECT DISTINCT maker
FROM Product a, PC b, Laptop c
WHERE ((b.speed >= 750 AND
c.speed >= 750
) AND
a.model = b.model
) OR
((b.speed >= 750 AND
c.speed >= 750
) AND
a.model = c.model
);

The rows that satisfy even one of predicates, connected with an OR operator, will appear in the result set. Let’s consider, for example, a query with the first predicate:

SELECT DISTINCT maker
FROM Product a, PC b, Laptop c
WHERE ((b.speed >= 750 AND
c.speed >= 750
) AND
a.model = b.model
);

Let’s rewrite it in a more convenient way from the syntax point of view:

SELECT DISTINCT maker
FROM Product a JOIN
PC b ON a.model = b.model,
Laptop c
WHERE (b.speed >= 750 AND
c.speed >= 750
);

SELECT DISTINCT maker
FROM (SELECT maker
FROM Product a JOIN
PC b ON a.model = b.model
WHERE b.speed >= 750
) x,
(SELECT *
FROM Laptop c
WHERE c.speed >= 750
) y;

Now we may analyze it. First subquery, which we marked as “x”, joins table PC with table Product on a foreign key, selecting the producers of PC with the speed >=750. Second subquery (“y”)  filters models of laptops with the speed >=750. 

The way of joining “x” and “y” is called Cartesian product. That is the producer of required PCs in the result set will match with EVERY model of laptop, even if it was produced by ANOTHER maker.

As a result, we will get again the producers that can make only something one. Some difference in comparison with the first solution is in that if NOONE produces laptops with a required speed, then we’ll get the empty result set. The first example 1.15.1 doesn’t give this partially correct result.

The coincidence of results on the main database is completely accidental. It’s turned out that those makers, who produce PC, required by task conditions, also produce required laptops. So, in spite of coincidence of results on “visible” database the query is wrong in any scheme-compatible state of DB.

To not be proofless, I’ll show the results of original query (solution 1.15.2) with an extension of output columns set:

SELECT maker, a.model a_m, b.model b_m, c.model c_m
FROM Product a, PC b, Laptop c
WHERE ((b.speed >= 750 AND
        c.speed >= 750
        ) AND
        a.model = b.model
       ) OR
       ((b.speed >= 750 AND
         c.speed >= 750
        ) AND
         a.model = c.model
       );

Let’s consider a couple of rows from the result set:

As you can see, model 1121 (PC) belongs to the maker B, but model 1752 (laptop) – to the maker A. So, we don’t have a reason to consider that both these makers satisfy the task conditions.

Uniting of demanded models of the PCs and laptops in one set gives only illusion that we receive both types:

Solution 1.15.3

SELECT maker
FROM (SELECT maker
      FROM Product INNER JOIN
           PC ON Product.model = PC.model
      WHERE type='PC' AND
            speed >= 750
      UNION ALL
      SELECT maker
      FROM Product INNER JOIN
            Laptop ON Product.model = Laptop.model
      WHERE type='laptop' AND
            speed >= 750
      ) S
GROUP BY maker;

As a result, the list of manufacturers for which there is at least one row in a set from FROM clause will be received. Below shorter variant of the same mistake.

Solution 1.15.4

SELECT maker
FROM Product
WHERE model IN (SELECT model
                FROM PC
                WHERE speed >= 750
                UNION ALL
                SELECT model
                FROM Laptop
                WHERE speed >= 750
                )
GROUP BY maker;

The following solution uses join.

Solution 1.15.5

SELECT maker
FROM Product INNER JOIN
     PC ON Product.model = PC.model INNER JOIN
     Laptop ON Laptop.model = Product.model
WHERE PC.speed >= 750 AND
     Laptop.speed >= 750
GROUP BY maker;

The idea lies in the following: to have in derived table a column with a manufacturer’s name, a column with speed of PC produced by this manufacturer, and also a column with speed of a laptop of the same manufacturer. Therefore, if the necessary restrictions on speeds will be imposed, the rows satisfying to these restrictions should give us what is necessary (a grouping on the manufacturer eliminates duplicates). The idea correct, however, what is joining is not that and not on those predicates which are expected.

Really, the first INNER JOIN will give us the list of manufacturers and nos of PC models. But only of PCs, as model is a primary key in Product table . Therefore the second INNER JOIN with Laptop table (on model column!) will give us empty result set, as none of PCs is present (and cannot be!) in Laptop table.

To achieve this idea, it is necessary to join the manufacturers of the necessary PCs with corresponding manufacturers of laptops not on model, but on the name of the manufacturer, or to use outer join instead of inner join.

T&S

To solve the problem on SQL-EX.RU