loading..
Ðóññêèé    English
23:56

Exercise #24 page 1

Find the model number of the product (PC, laptop, or printer) with the highest price.Result set: model

Below is erroneous solution that has been admitted by site's checking system some time ago.

Solution 1.16.1

Console
Execute
  1. SELECT  model
  2. FROM (SELECT  model, price
  3.       FROM PC
  4.       WHERE price = (SELECT MAX(price)
  5.                      FROM PC
  6.                      )
  7.       UNION
  8.       SELECT  model,  price
  9.       FROM Laptop
  10.       WHERE price = (SELECT MAX(price)
  11.                      FROM Laptop
  12.                      )
  13.       UNION
  14.       SELECT  model,  price
  15.       FROM Printer
  16.       WHERE price = (SELECT MAX(price)
  17.                      FROM Printer
  18.                      )
  19.       ) T
  20. WHERE price = (SELECT MAX(price)
  21.                FROM Laptop
  22.                );

I suggest to clear up what data must be in the test database to block such solutions.

So, what does this query do? In each of three similar subqueries 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 rows {model, price}. At last, the models having the same price as the maximum price for PC-laptops are left. 

So if the maximum price over all products coincides with the maximum price on printers this solution will be rejected by the system. But then a solution having

  1. WHERE price = (SELECT MAX(price)
  2.                FROM Printer
  3.                )
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 solution will be passed through checking:

Solution 1.16.2

Console
Execute
  1. SELECT   DISTINCT  model FROM Printer
  2. WHERE price = (SELECT MAX(price)
  3.                FROM Printer
  4.                );

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.

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.

Bookmark and Share
Pages 1 2
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.