00:30

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

Tags
The book was updated
several days ago
•  Êóïèòü òðîòóàðíóþ ïëèòêó Òðîòóàðíàÿ Ïëèòêà! Õîòèòå Êóïèòü Òðîòóàðíóþ Ïëèòêó Ïî Íèçêîé Öåíå? Çâîíèòå goldmandarin.kiev.ua
•  Ãîðÿùèå òóðû âî âñå ñòðàíû Àêòóàëüíàÿ èíôîðìàöèÿ î ñòðàíàõ, êóðîðòàõ, îòåëÿõ ìèðà. Îòäûõ â Ðîññèè. meridian-tur.ru
•  Èçãîòîâëåíèå è ìîíòàæ îêîí ïâõ Îêíà ÏÂÕ - ðåàëüíî Íèçêèå öåíû! Çàéäèòå è ïîñìîòðèòå! Êà÷åñòâî Ñóïåð oknavse.ru