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.

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;

🚫

[[ error ]]

[[ column ]]
NULL [[ 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;

🚫

[[ error ]]

[[ column ]]
NULL [[ 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
                       );

🚫

[[ error ]]

[[ column ]]
NULL [[ 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;

🚫

[[ error ]]

[[ column ]]
NULL [[ value ]]

T&S

To solve the problem on SQL-EX.RU

#Mistakes #PC #GROUP BY #AVG #Grouping #Average Value #Maker #Exercise 27

Exercise #26 Exercise #28