loading..
Ðóññêèé    English
08:13
go

Exercise #13

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

Solution 1.10.1. A common  mistake of beginners is, attempting to use freshly learned language constructs regardless of whether they are suitable for the task or not. Below is a 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.                 );

Here, the subquery in the WHERE clause gets the processor speed values for PCs produced by maker A. Then, the average speed is calculated for all PCs whose processor speeds match any values in the list returned by said subquery. As a result, processor speeds of PCs by, say, maker B will be included in the calculation if they happen to be equal to any speed values for maker A. The computation will be done correctly only if the set of PC processor speeds for maker A doesn’t intersect with corresponding sets for other manufacturers.

Solve this task at 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.