Welcome All,
This is the 6th part of the series & here we are going to explore sub-queries.
1) Retrieve products whose list price is higher than the average unit price
Retrieve the product ID, name, and list price for each product where the list price is higher than the
average unit price for all products that have been sold.
SELECT ProductID, Name, ListPrice from SalesLT.Product
WHERE ListPrice >
(SELECT AVG(UnitPrice) FROM SalesLT.SalesOrderDetail)
ORDER BY ProductID;
2) Retrieve Products with a list price of $100 or more that have been sold for less than $100
Retrieve the product ID, name, and list price for each product where the list price is $100 or more
and the product has been sold for less than $100.
SELECT ProductID, Name, ListPrice FROM SalesLT.Product
WHERE ProductID IN
(SELECT ProductID from SalesLT.SalesOrderDetail
WHERE UnitPrice < 100.00)
AND ListPrice >= 100.00
ORDER BY ProductID;
We can achieve the same using joins as well below is the query re-written
select distinct sp.ProductID,name,ListPrice
from SalesLT.Product sp
inner join saleslt.SalesOrderDetail sod
on sp.ProductID=sod.ProductID
where UnitPrice<100 and ListPrice>100
order by ProductID
3) Retrieve the cost, list price, and average selling price for each product
Retrieve the product ID, name, cost, and list price for each product along with the average unit price for which that product has been sold.
SELECT ProductID, Name, StandardCost, ListPrice,
(SELECT AVG(UnitPrice)
FROM SalesLT.SalesOrderDetail AS SOD
WHERE P.ProductID = SOD.ProductID) AS AvgSellingPrice
FROM SalesLT.Product AS P
ORDER BY P.ProductID;
4) Retrieve products that have an average selling price that is lower than the cost
Filter your previous query to include only products where the cost price is higher than the average selling price.
SELECT ProductID, Name, StandardCost, ListPrice,
(SELECT AVG(UnitPrice)
FROM SalesLT.SalesOrderDetail AS SOD
WHERE P.ProductID = SOD.ProductID) AS AvgSellingPrice
FROM SalesLT.Product AS P
WHERE StandardCost >
(SELECT AVG(UnitPrice)
FROM SalesLT.SalesOrderDetail AS SOD
WHERE P.ProductID = SOD.ProductID)
ORDER BY P.ProductID;
5) Retrieve customer information for all sales orders
Retrieve the sales order ID, customer ID, first name, last name, and total due for all sales orders from the SalesLT.SalesOrderHeader table and the dbo.ufnGetCustomerInformation function.
SELECT SOH.SalesOrderID, SOH.CustomerID, CI.FirstName, CI.LastName, SOH.TotalDue
FROM SalesLT.SalesOrderHeader AS SOH
CROSS APPLY dbo.ufnGetCustomerInformation(SOH.CustomerID) AS CI
ORDER BY SOH.SalesOrderID;
6) Retrieve customer address information
Retrieve the customer ID, first name, last name, address line 1 and city for all customers from the SalesLT.Address and SalesLT.CustomerAddress tables, and the dbo.ufnGetCustomerInformation function.
SELECT CA.CustomerID, CI.FirstName, CI.LastName, A.AddressLine1, A.City
FROM SalesLT.Address AS A
JOIN SalesLT.CustomerAddress AS CA
ON A.AddressID = CA.AddressID
CROSS APPLY dbo.ufnGetCustomerInformation(CA.CustomerID) AS CI
ORDER BY CA.CustomerID;
This is the 6th part of the series & here we are going to explore sub-queries.
1) Retrieve products whose list price is higher than the average unit price
Retrieve the product ID, name, and list price for each product where the list price is higher than the
average unit price for all products that have been sold.
SELECT ProductID, Name, ListPrice from SalesLT.Product
WHERE ListPrice >
(SELECT AVG(UnitPrice) FROM SalesLT.SalesOrderDetail)
ORDER BY ProductID;
2) Retrieve Products with a list price of $100 or more that have been sold for less than $100
Retrieve the product ID, name, and list price for each product where the list price is $100 or more
and the product has been sold for less than $100.
SELECT ProductID, Name, ListPrice FROM SalesLT.Product
WHERE ProductID IN
(SELECT ProductID from SalesLT.SalesOrderDetail
WHERE UnitPrice < 100.00)
AND ListPrice >= 100.00
ORDER BY ProductID;
We can achieve the same using joins as well below is the query re-written
select distinct sp.ProductID,name,ListPrice
from SalesLT.Product sp
inner join saleslt.SalesOrderDetail sod
on sp.ProductID=sod.ProductID
where UnitPrice<100 and ListPrice>100
order by ProductID
3) Retrieve the cost, list price, and average selling price for each product
Retrieve the product ID, name, cost, and list price for each product along with the average unit price for which that product has been sold.
SELECT ProductID, Name, StandardCost, ListPrice,
(SELECT AVG(UnitPrice)
FROM SalesLT.SalesOrderDetail AS SOD
WHERE P.ProductID = SOD.ProductID) AS AvgSellingPrice
FROM SalesLT.Product AS P
ORDER BY P.ProductID;
4) Retrieve products that have an average selling price that is lower than the cost
Filter your previous query to include only products where the cost price is higher than the average selling price.
SELECT ProductID, Name, StandardCost, ListPrice,
(SELECT AVG(UnitPrice)
FROM SalesLT.SalesOrderDetail AS SOD
WHERE P.ProductID = SOD.ProductID) AS AvgSellingPrice
FROM SalesLT.Product AS P
WHERE StandardCost >
(SELECT AVG(UnitPrice)
FROM SalesLT.SalesOrderDetail AS SOD
WHERE P.ProductID = SOD.ProductID)
ORDER BY P.ProductID;
5) Retrieve customer information for all sales orders
Retrieve the sales order ID, customer ID, first name, last name, and total due for all sales orders from the SalesLT.SalesOrderHeader table and the dbo.ufnGetCustomerInformation function.
SELECT SOH.SalesOrderID, SOH.CustomerID, CI.FirstName, CI.LastName, SOH.TotalDue
FROM SalesLT.SalesOrderHeader AS SOH
CROSS APPLY dbo.ufnGetCustomerInformation(SOH.CustomerID) AS CI
ORDER BY SOH.SalesOrderID;
6) Retrieve customer address information
Retrieve the customer ID, first name, last name, address line 1 and city for all customers from the SalesLT.Address and SalesLT.CustomerAddress tables, and the dbo.ufnGetCustomerInformation function.
SELECT CA.CustomerID, CI.FirstName, CI.LastName, A.AddressLine1, A.City
FROM SalesLT.Address AS A
JOIN SalesLT.CustomerAddress AS CA
ON A.AddressID = CA.AddressID
CROSS APPLY dbo.ufnGetCustomerInformation(CA.CustomerID) AS CI
ORDER BY CA.CustomerID;
Comments