loading..
Русский    English
02:52

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

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
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100