18:56

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Common mistakes in solving SELECT statement exercises

Computer Firm Database

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

and

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

Console

Execute

SELECT MAX(speed)
FROM (SELECT speed 
      FROM PC 
      WHERE ram = (SELECT MIN(ram) 
                   FROM PC
                   )
      ) 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

Pages

mistakes printer PC MIN MAX exercise 25

Previous [Exercise #24]

[Exercise #26] Next

Last added:

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 Problems and solutions

S. I. Moiseenko

Common mistakes in solving SELECT statement exercises

Computer Firm Database

Exercise #25 page 2