SQL Question 2

Problem

  • USE AdventureworksLT
  • Display the part id, description, and price for the highest and lowest priced parts in our inventory.
  • Projection: SalesLT.Product.productid, SalesLT.Product.name, SalesLT.Product.listprice

Code - Option #1 - Compound selection criteria

SELECT productid, name, listprice
FROM SalesLT.Product
WHERE listprice IN (SELECT MAX(listprice)
FROM SalesLT.Product)
OR listprice IN (SELECT MIN(listprice)
FROM SalesLT.Product)
ORDER BY listprice DESC;

Code - Option #2 - TOP WITH TIES

SELECT productid, name, listprice
FROM SalesLT.Product
WHERE listprice IN (SELECT TOP 1 WITH TIES
listprice
FROM SalesLT.Product
ORDER BY listprice DESC)
OR listprice IN (SELECT TOP 1 WITH TIES
listprice
FROM SalesLT.Product
ORDER BY listprice ASC)
ORDER BY listprice DESC;

Code - Option #3 - UNION

SELECT productid, name, listprice
FROM SalesLT.Product
WHERE listprice IN (SELECT MAX(listprice)
FROM SalesLT.Product)
UNION
SELECT productid, name, listprice
FROM SalesLT.Product
WHERE listprice IN (SELECT MIN(listprice)
FROM SalesLT.Product)
ORDER BY listprice DESC;

Result

Question 2 Result

Question 2 Result

No Comments Yet.

Leave a comment