loading..
Русский    English
15:18
go

Exercise #26

Define the average price of the PCs and laptops produced by maker A.Result set: single total price.

Solution 1.18.1

Console
Execute
  1. SELECT AVG(av.p) AS avg_price 
  2. FROM (SELECT AVG(price) p 
  3.  FROM Product m, PC 
  4.  WHERE m.model = PC.model AND 
  5.  maker = 'A' 
  6.  UNION 
  7.  SELECT AVG(price) p 
  8.  FROM Product m, Laptop l 
  9.  WHERE m.model = l.model AND 
  10.  maker = 'A' 
  11.  ) AS av;

In the subquery of FROM clause, the average prices for the PCs and laptops for the manufacturer A are united, then the average of these average values is calculated in the main query. A mistake cleanly arithmetic, which consists that the general average value (which and it is necessary to get) is not equal generally to an average from average values.

Solution 1.18.2

Console
Execute
  1. SELECT ((SELECT SUM(price)
  2.  FROM Product INNER JOIN 
  3.  PC ON Product.model = PC.model 
  4.  WHERE maker='A'
  5.  ) +
  6.  (SELECT SUM(price)
  7.  FROM Product INNER JOIN 
  8.  Laptop ON Product.model = Laptop.model 
  9.  WHERE maker='A')
  10.  ) / ((SELECT COUNT(price)
  11.  FROM Product INNER JOIN 
  12.  PC ON Product.model = PC.model 
  13.  WHERE maker='A')
  14.  +
  15.  (SELECT COUNT(price)
  16.  FROM Product INNER JOIN 
  17.  Laptop ON Product.model = Laptop.model 
  18.  WHERE maker='A')
  19.  ) AS AVG_price;

The query 1.18.2 takes the sum price of both the PCs and laptops produced by maker A. Then that sum price is divided by the overall quantity of these PCs and laptops. It is all right from the mahtematical point of view, but is not from the point of view of SQL. If the reference database does not include any PC (or laptop) by the maker A, the COUNT function returns 0 (which is the expected result), but the SUM function returns NULL value. As a result, the sum price will be NULL, but will not be the sum price of other products, which is required to be.

T&S

To solve the problem on SQL-EX.RU
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 CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.