Books and articles about SQL Сменить язык на: Русский 24 February 2024 14:55:18

Main page

## Typical errors at solution of exercises. Exercise 24

S. Moiseenko

Find the number of product's model (PC, PC-laptop or printer) that has the highest price. Output: model.

Here is one of solutions published on the forum of the site:

(1)
SELECT model FROM
(SELECT model, price FROM Pc
WHERE price = (SELECT MAX(price) FROM Pc)
UNION
SELECT model, price FROM Laptop
WHERE price = (SELECT MAX(price) FROM Laptop)
UNION
SELECT model, price FROM Printer
WHERE price = (SELECT MAX(price) FROM Printer)) T
WHERE price = (SELECT MAX(price) FROM Laptop)

As the author (nika_k) fairly states, such a solution must not be accepted by the system. I suggest to clear up what data must be in the test base to block such solutions.

So, what does this request do? In each of three similar sub queries models from three types of products, - PC, PC-laptop or printer - are selected by maximum price. Then UNION is used to combine the found models and besides to remove duplicated strings {model, price}. At last, the models having the same price as the maximum price for PC- laptops, are left.

So if there is a maximum price on printers this solution will be rejected by the system. But then a decision having

WHERE price = (SELECT MAX( price) FROM Printer)

as a last string will be taken. What's more, if the maximum price will be only for one type of products (printers, for instance), even more incorrect decision will be made:

(2)
SELECT DISTINCT model FROM Printer
WHERE price = (SELECT MAX(price) FROM Printer)

Conclusion. Whatever the data may be, with the help of the first query the solution can be adjusted in three attempts, in the worst case. The second solution won't pass at all, if the maximum is reached for at least two types of products. But then for adjusting the first solution we'll need only two attempts. If in every type of product there is a model with the same maximum price, one try will be enough.

By the way, the data is picked up optimally for the examined cases, but, anyway, it doesn't prevent from the wrong queries' passage.

Putting “who's guilty?” question aside, let's concentrate on “what to do?” matter. Way out of this situation, and not only this, can be found in increasing the amount of test databases, where different data variants will be simulated. The only thing that prevents me from doing this is slowing down the system's work, which will cause the user to spend more time waiting. I also comfort myself by a thought that our visitors' motivation is SQL study and qualification improvement but not a tendency to fool the system.

Anyway, I ask you to notify me of cases of undoubtedly wrong solutions passage, to improve the quality of the system in limits that don't increase the time for response.

I also suggest to consider the question of modifying the formulation (and solution) of task 24. So write back.

» Given examples here can be done directly on the website by selecting the check box “Without checking” on the page with SELECT exercises.

Main page


 Usage of any materials of this site is possibleonly under condition of mandatory allocation of the direct link to a sitehttp://www.sqlbooks.ruon each page where used materials are placed. Main Articles Books
 Copyright c 2002-2006. All rights reserved. contact