CODEWITHSUNDEEP
sql-servert-sql
Julia
Julia

Reputation: 47

For each maker who has models at least in one of the tables PC, Laptop, or Printer, determine the maximum price for his products. Output: maker;

TASK: for each maker who has models at least in one of the tables PC, Laptop, or Printer, determine the maximum price for his products. Output: maker; if there are NULL values among the prices for the products of a given maker, display NULL for this maker, otherwise, the maximum price.

This is task 41 from this website: http://www.sql-ex.ru/

Database description:

The database scheme consists of four tables:

The Product table contains data on the maker, model number, and type of product ('PC', 'Laptop', or 'Printer'). It is assumed that model numbers in the Product table are unique for all makers and product types. Each personal computer in the PC table is unambiguously identified by a unique code, and is additionally characterized by its model (foreign key referring to the Product table), processor speed (in MHz) – speed field, RAM capacity (in Mb) - ram, hard disk drive capacity (in Gb) – hd, CD-ROM speed (e.g, '4x') - cd, and its price.

The Laptop table is similar to the PC table, except that instead of the CD-ROM speed, it contains the screen size (in inches) – screen. For each printer model in the Printer table, its output type (‘y’ for color and ‘n’ for monochrome) – color field, printing technology ('Laser', 'Jet', or 'Matrix') – type, and price are specified.

I've tried several queries, most of them worked on the first database but not on the second one

Here's my last tried query:

SELECT DISTINCT 
    maker, 
    CASE 
       WHEN price IS NULL 
          THEN NULL 
          ELSE MAX(price) OVER (PARTITION by maker) 
    END max_p 
FROM 
    (SELECT DISTINCT maker, price 
     FROM product 
     LEFT JOIN pc ON pc.model = product.model
     WHERE product.model IN (SELECT model FROM pc)

     UNION 

     SELECT DISTINCT maker, price 
     FROM product 
     LEFT JOIN laptop ON laptop.model = product.model
     WHERE product.model IN (SELECT model FROM laptop)

     UNION

     SELECT DISTINCT maker, price FROM product 
     LEFT JOIN printer ON printer.model = product.model
     WHERE product.model IN (SELECT model FROM printer)) as x

Your query returned the correct dataset on the first (available) database, but it returned incorrect dataset on the second checking database.

Upvotes: 1

Views: 2654

Answers (2)

Andrew Kabanov
Andrew Kabanov

Reputation: 1

SELECT maker, CASE WHEN SUM(CASE WHEN price IS NULL THEN 1 ELSE 0 END) > 0
                   THEN null
                   ELSE MAX(price)
              END AS price
FROM (SELECT maker, price
      FROM product LEFT JOIN laptop ON product.model = laptop.model
      WHERE product.model IN (SELECT model FROM laptop)
                                       
      UNION

      SELECT maker, price
      FROM product LEFT JOIN printer ON product.model = printer.model
      WHERE product.model IN (SELECT model FROM printer)
                                        
      UNION

      SELECT maker, price
      FROM product LEFT JOIN pc ON product.model = pc.model
      WHERE product.model IN (SELECT model FROM pc)                                 
    ) X
GROUP BY maker

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74740

Most expensive product per maker, or null if any of the maker's prices are null:

SELECT
  pro.maker,
  NULLIF(MAX(COALESCE(pri.price, 922337203685477)), 922337203685477) as price
FROM
  product pro
  INNER JOIN
  (
    SELECT model, price FROM printer
    UNION ALL
    SELECT model, price FROM pc
    UNION ALL
    SELECT model, price FROM laptop
  ) pri 
  ON pri.model = pro.model
GROUP BY pro.maker

How it works:

Strip the laptop, pc and printer tables out to just model and price (including any null prices) and union then together to provide a consistent data set. Join this to product to get the maker. Group by the maker looking for the max price. Because we have to show null as the max price of there is any null price we COALESCE to make all the null prices a massively high price so they will surely be the max price, then we turn that massively high price back into null with NULLIF

NULLIF is proprietary. I could do the same thing with case when max(...) = 922... then null else max(...) end But it's more wordy

Trivia:

922337203685477 Is almost the max value for a MONEY; I omitted the fractional component

Also consider:

Two of the three hardware tables have a type column. It is not stated whether this relates to the type column of the product table. If it is you'll have to add it into the join. For the table that lacks a type, null it and make the join condition include "or pri.type is null"

Upvotes: 3

Related Questions