Request from association of associations

Question

I am doing sql exercises from sql-ex.ru and stumbled upon one which I cannot do correctly.

Here he is:

Find the product model number (PC, laptop or printer) using the maximum price.

Set of results: model.

The database schema is explained here . I will just put the diagram here:

I came up with this solution:

with model_price(model,price) as ( select model,price from PC union select model,price from Laptop union select model,price from Printer ) select model from model_price where price >= all(select price from model_price) 

The system displays the following result:

 Your query produced correct result set on main database, but it failed test on second, checking database. * Wrong number of records (less by 2) 

The system provides an answer for the main database . But I don’t know what the control database is, and I can’t determine what’s wrong by doing separate subqueries and looking where I’m mistaken.

So I ask here, hoping you will notice what I am doing wrong. This task has level 2 (quite simple), so it should be something trivial that I am missing.

_{About the sql engine: Now we use Microsoft SQL Server 2012 on the rating stages, and MySQL 5.5.11, PostgreSQL 9.0, and Oracle Database 11g on the learn stage in addition. So I don’t know which engine he uses to illuminate this exercise.}

Answer 1

Please try the following. This is ANSI-Standard SQL, so you don’t have to worry about which RDBMS is used.

 SELECT model FROM ( SELECT model, price FROM pc UNION ALL SELECT model, price FROM printer UNION ALL SELECT model, price FROM laptop ) sq WHERE price = (SELECT MAX(price) FROM ( SELECT model, price FROM pc UNION ALL SELECT model, price FROM printer UNION ALL SELECT model, price FROM laptop ) sq2 ) 

Answer 2

The solution was to use a subquery with the max function instead of comparing with all :

 with model_price(model,price) as ( select model,price from PC union select model,price from Laptop union select model,price from Printer ) select model from model_price where price = (select max(price) from model_price) 

There is no need for a distinct key, because union returns unique strings.

Thanks to tombom who helped me find this.

The solution with all does not work, since the price column can be NULL , and in comparison with NULL creates false , which makes the whole result for all false , when using max simply searches for the maximum price, ignoring NULL s. I found out by asking this question .

Answer 3

 SELECT model, price FROM ( (SELECT model, price FROM pc ORDER BY price DESC LIMIT 1) /* your database may use a TOP 1 syntax for the LIMIT queries */ UNION ALL (SELECT model, price FROM laptop ORDER BY price DESC LIMIT 1) UNION ALL (SELECT model, price FROM printer ORDER BY price DESC LIMIT 1) ) AS subquery_name_required ORDER BY price DESC LIMIT 1; 

If the price field is indexed (which should be for such a request), ORDER / LIMIT (or the equivalent TOP) will work very quickly, since DB resets the highest price from the table.

Note. In a database that supports table inheritance (or can be locked to support something similar using discriminator columns), refactoring shared columns into a single table can say a lot about this! Imagine if you start selling scanners, monitors, SSDs, etc. What a nightmare!

[EDIT to show links]

 SELECT model, price FROM ( SELECT model, price FROM (SELECT model, price, rank() OVER (ORDER BY price DESC) AS r FROM pc) AS s1 WHERE r=1 /* your database may not yet support windowing queries; in that case you probably have to compare to MAX, which may mean an extra pass through every table. */ UNION ALL SELECT model, price FROM (SELECT model, price, rank() OVER (ORDER BY price DESC) AS r FROM laptop) AS s2 WHERE r=1 UNION ALL SELECT model, price FROM (SELECT model, price, rank() OVER (ORDER BY price DESC) AS r FROM printer) AS s3 WHERE r=1 ) AS subquery_name_required ORDER BY price DESC LIMIT 1; 

I do not have enough experience in this type of windows to find out whether the index on price will be checked as much as necessary. I hope so. It is clear that this should not be more complicated than LIMIT 1 , you just have to admit that this is a more complex, but similar request.

Answer 4

I think I ran into a problem: you use union with models and price: if you have two different products (code) with the same price (max) and the model you end up giving only one line ( union instead of union all eliminates duplicates) . The result set is correct, yes, but in the check they say that you gave only one line instead of two

Answer 5

 WITH tab1 as ( /* laptop */ ( SELECT 1 model, 2000 price FROM DUAL UNION ALL SELECT 2 model, 2250 price FROM DUAL UNION ALL SELECT 3 model, 2500 price FROM DUAL UNION ALL SELECT 4 model, 2550 price FROM DUAL -- <---- the highest value ) UNION ALL /* printer */ ( SELECT 5 model, 500 price FROM DUAL UNION ALL SELECT 6 model, 750 price FROM DUAL UNION ALL SELECT 7 model, 1000 price FROM DUAL UNION ALL SELECT 8 model, 1250 price FROM DUAL ) UNION ALL /* pc */ ( SELECT 9 model, 1000 price FROM DUAL UNION ALL SELECT 10 model, 1250 price FROM DUAL UNION ALL SELECT 11 model, 1500 price FROM DUAL UNION ALL SELECT 12 model, 1750 price FROM DUAL ) ) SELECT model FROM tab1 WHERE price = (SELECT MAX(price) FROM tab1); 

Answer 6

 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) ) as A where price = ( select max(price) 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) ) as B )