09:53

# 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.

1. Determining all manufacturers who produce printers.

Console
Execute
`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
Execute
`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
Execute
`SELECT PC.hd, Product.maker FROM PC INNER JOIN      Product ON PC.model = Product.modelWHERE 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
Execute
`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 resultGROUP BY result.maker;`

T&S

To solve the problem on SQL-EX.RU