The database schema consists of four tables:

Product(maker, model, type) PC(code, model, speed, ram, hd, cd, price) Laptop(code, model, speed, ram, hd, price, screen) Printer(code, model, color, type, price)

Task: Find manufacturers of the cheapest color printers. Print: maker, price

Wrong:

 SELECT DISTINCT maker, price FROM printer, product WHERE printer.model = product.model AND (color = 'y') AND price <= ALL (SELECT price FROM printer WHERE color = 'y')

Right:

 SELECT DISTINCT maker, price FROM printer, product WHERE printer.model = product.model AND color = 'y' AND price = (SELECT MIN(price) FROM printer WHERE color = 'y')
  • Is the result different only in the number of entries? - Grundy
  • And what sql-ex.ru not respond? - msi

1 answer 1

I think this is due to the fact that in the price column NULL can occur, and then the ALL operator will not return the result, since for this line, the price <= NULL condition price <= NULL , naturally, will not be fulfilled.

For example:

 with my_data (maker, price) as (select 'A', 10 from dual union select 'B', null from dual union select 'C', 30 from dual) select * from my_data md1 where price <= all (select price from my_data);

One has only to replace select 'B', null from dual with select 'B', 2 from dual as everything “starts working”. The min() function selects the minimum value, and the presence of NULL among them does not prevent the return of the result.