loading..
Ðóññêèé    English
03:21
go

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
  1. SELECT Product.maker 
  2. FROM Product INNER JOIN 
  3.      Printer ON Product.model = Printer.model 
  4. GROUP BY Product.maker;

2. Deducing the size of a hard disk and its manufacturer for each personal computer.

Console
Execute
  1. SELECT PC.hd, Product.maker 
  2. FROM PC INNER JOIN 
  3.      Product ON PC.model = Product.model;

3. Choosing only the rows from item 2 which include the manufacturer found in item 1.

Console
Execute
  1. SELECT PC.hd, Product.maker 
  2. FROM PC INNER JOIN 
  3.      Product ON PC.model = Product.model
  4. WHERE Product.maker IN (SELECT Product1.maker 
  5.                         FROM Product Product1  INNER JOIN 
  6.                              Printer ON Product1.model = Printer.model 
  7.                         GROUP BY Product1.maker
  8.                         );

4. In the final solution we receive average values on the basis of query from item 3.

Console
Execute
  1. SELECT Result.maker, AVG(result.hd) 
  2. FROM (SELECT PC.hd, Product.maker 
  3.       FROM PC INNER JOIN 
  4.            Product ON PC.model = Product.model
  5.       WHERE Product.maker IN (SELECT Product1.maker 
  6.                               FROM Product Product1  INNER JOIN 
  7.                                    Printer ON Product1.model = Printer.model 
  8.                               GROUP BY Product1.maker
  9.                               )
  10.       ) AS result
  11. GROUP BY result.maker;

T&S

To solve the problem on SQL-EX.RU
Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100