Sql-ex Solutions

Exercise: 41

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.

Solution

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

References

1. NULLIF (Transact-SQL)

2. COALESCE (Transact-SQL)