Exercise #23 (tips and solutions)
To solve this exercise is generally used two approaches: first, the join and selecting the required row and second, check for hit maker in the two lists. The next incorrect solution implements the first approach:
Solution 4.11.1
SELECT DISTINCT a.maker
FROM Product a LEFT JOIN
PC b ON a.model = b.model AND
b.speed> =750 LEFT JOIN
Laptop c ON a.model = c.model AND c.speed> =750
WHERE NOT (b.model IS NULL AND
c.model IS NULL
);| [[ column ]] |
|---|
| NULL [[ value ]] |
This is another variant on the theme of “something one”. Indeed, the model is unique, ie it is either a PC or laptop. Next rows are obtained as result of external joins:
maker model (PC) NULLor
maker NULL model (laptop)However, there can be no rows such as:
maker model (PC) model (laptop)because the join is performed by model number.
As a result, the list contains only makers who make only one product type with the desired characteristics. However, the correct solution is simply adding the grouping by maker and model counting. We suggest you do it yourself.
Solution 4.11.2
To demonstrate the second approach, consider the following variant:
SELECT DISTINCT t.maker
FROM Product t
WHERE (t.model IN (SELECT model
FROM PC
WHERE speed >= 750
) OR
t.model IN (SELECT model
FROM Laptop
WHERE speed >= 750
)
) AND
EXISTS (SELECT *
FROM Product
WHERE Product.maker = t.maker AND
Product.type='PC'
) AND
EXISTS (SELECT *
FROM Product
WHERE Product.maker = t.maker AND
Product.type='Laptop'
);| [[ column ]] |
|---|
| NULL [[ value ]] |