Exercise #25 page 2

What is not yet considered? We shall recall the phrase of the above in the beginning of the chapter: PCs with the lowest RAM and the highest-speed processor among PCs with the  lowest RAM. Actually, we have here two conditions:

PCs with the lowest RAM


PCs with the highest-speed processor among PCs with the  lowest RAM

In solution being inspected, there is used only the second one of these conditions, namely, the solution finds models with speed coincided with the maximum speed among models with lowest RAM.

I explain in terms of an example. If the least RAM capacity for PC in the database is 64 Mb and there are such PC models:

Speed ram
600 64
600 128
450 64

The code used for definition of the needed processor speed

  1. SELECT MAX(speed)
  2. FROM (SELECT speed
  3. FROM PC
  4. WHERE ram = (SELECT MIN(ram)
  5. FROM PC
  6. )
  7. ) AS z4
give us 600. Really, it is the maximal processor speed for models with the least RAM capacity (64). Then we select the models with this speed, among these is the model {600, 128}, although it does not conform with the task conditions. If the maker of this model is also producing printers (and he is producing!), and he is not the maker of the model {600, 64}, we'll get the Incorrect when checking the query on the site. 

Of course the right selection is only the model {600, 64}. I hope it isn't difficult to solve this task now :-).

To solve the problem on SQL-EX.RU

Bookmark and Share
Pages 1 2
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 date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates equi-join EXCEPT exercise (-2) exercise 19 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100