loading..
Ðóññêèé    English
23:49
go

Exercise #13

Find out the average speed of the PCs produced by maker A.

Solution 1.10.1. Usual mistake of beginners is applying of unsuitable learned language statements. Here is typical example:

Console
Execute
  1. SELECT AVG(speed) AS avg_speed 
  2. FROM PC
  3. WHERE speed IN (SELECT speed 
  4.                 FROM PC, Product 
  5.                 WHERE product.model = PC.model AND 
  6.                       maker='A'
  7.                 );

In the subquery of WHERE clause the speed values of PC's processors, produced by A producer, are extracting. Then average speed are calculating for all PC with processor speed equal to any value from list extracted by subquery. As a result a speed of the B's PC  producer will be taken into account if speed coincides with speed of any A's PC producer. The result will be only correct if PC producers have models with unique speed of PC processor.

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 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
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.