Define the average size of the PC hard drive for each maker that also produces printers.Result set: maker, average capacity of HD.
Track the steps of the solution to the exercise and discover mistakes.
1. Determining all manufacturers who produce printers.
Console
SELECT Product.maker
FROM Product INNER JOIN
Printer ON Product.model = Printer.model
GROUP BY Product.maker;
2. Deducing the size of a hard disk and its manufacturer for each personal computer.
Console
SELECT PC.hd, Product.maker
FROM PC INNER JOIN
Product ON PC.model = Product.model;
3. Choosing only the rows from item 2 which include the manufacturer found in item 1.
Console
SELECT PC.hd, Product.maker
FROM PC INNER JOIN
Product ON PC.model = Product.model
WHERE Product.maker IN (SELECT Product1.maker
FROM Product Product1 INNER JOIN
Printer ON Product1.model = Printer.model
GROUP BY Product1.maker
);
4. In the final solution we receive average values on the basis of query from item 3.
Console
SELECT Result.maker, AVG(result.hd)
FROM (SELECT PC.hd, Product.maker
FROM PC INNER JOIN
Product ON PC.model = Product.model
WHERE Product.maker IN (SELECT Product1.maker
FROM Product Product1 INNER JOIN
Printer ON Product1.model = Printer.model
GROUP BY Product1.maker
)
) AS result
GROUP BY result.maker;
T&S
go
Console
