Exercise #27
Define the average size of the PC hard drive for each maker that also produces printers.
Result set: maker, average capacity of HD.
Result set: maker, average capacity of HD.
Track the steps of the solution to the exercise and discover mistakes.
- Determining all manufacturers who produce printers.
SELECT Product.maker
FROM Product INNER JOIN
Printer ON Product.model = Printer.model
GROUP BY Product.maker;mssql
🚫
[[ error ]]
| [[ column ]] |
|---|
| [[ value ]] |
- Deducing the size of a hard disk and its manufacturer for each personal computer.
SELECT PC.hd, Product.maker
FROM PC INNER JOIN
Product ON PC.model = Product.model;mssql
🚫
[[ error ]]
| [[ column ]] |
|---|
| [[ value ]] |
- Choosing only the rows from item 2 which include the manufacturer found in item 1.
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
);mssql
🚫
[[ error ]]
| [[ column ]] |
|---|
| [[ value ]] |
- In the final solution we receive average values on the basis of query from item 3.
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;mssql
🚫
[[ error ]]
| [[ column ]] |
|---|
| [[ value ]] |