Hi Folks,
We are back & continuing with the second part of the series.
1) Retrieve a list of cities
Initially, you need to produce a list of all of you customers' locations. Write a Transact-SQL query that queries the Address table and retrieves all values for City and StateProvince, removing duplicates.
SELECT DISTINCT City, StateProvince FROM SalesLT.Address
2)Retrieve the heaviest products
Transportation costs are increasing and you need to identify the heaviest products. Retrieve the names of the top ten percent of products by weight.
select top 10 percent name from SalesLT.Product order by Weight
3)Retrieve the heaviest 100 products not including the heaviest ten
The heaviest ten products are transported by a specialist carrier, therefore you need to modify the previous query to list the heaviest 100 products not including the heaviest ten.
SELECT Name FROM SalesLT.Product ORDER BY Weight DESC OFFSET 10 ROWS FETCH NEXT 100 ROWS ONLY;
The above solution works only if you are running from SQL 2012 if you are on lower versions this needs to be written as below
SELECT Name
FROM
(
SELECT Name,
ROW_NUMBER() OVER (ORDER BY weight desc) AS Seq
FROM SalesLT.Product
)t
WHERE Seq BETWEEN 11 AND 110
4) Retrieve product details for product model 1
Initially, you need to find the names, colors, and sizes of the products with a product model ID 1.
select Name,Color,size from SalesLT.Product where ProductModelID=1
5)Filter products by color and size
Retrieve the product number and name of the products that have a color of 'black', 'red', or 'white' and a size of 'S' or 'M'.
SELECT ProductNumber, Name FROM SalesLT.Product
WHERE Color IN ('Black','Red','White') and Size IN ('S','M')
6)Filter products by product number
Retrieve the product number, name, and list price of products whose product number begins 'BK-'.
SELECT ProductNumber, Name, ListPrice FROM SalesLT.Product
WHERE ProductNumber LIKE 'BK-%';
7) Retrieve specific products by product number
Modify your previous query to retrieve the product number, name, and list price of products whose product number begins 'BK-' followed by any character other than 'R’, and ends with a '-' followed by any two numerals
We are back & continuing with the second part of the series.
1) Retrieve a list of cities
Initially, you need to produce a list of all of you customers' locations. Write a Transact-SQL query that queries the Address table and retrieves all values for City and StateProvince, removing duplicates.
SELECT DISTINCT City, StateProvince FROM SalesLT.Address
2)Retrieve the heaviest products
Transportation costs are increasing and you need to identify the heaviest products. Retrieve the names of the top ten percent of products by weight.
select top 10 percent name from SalesLT.Product order by Weight
3)Retrieve the heaviest 100 products not including the heaviest ten
The heaviest ten products are transported by a specialist carrier, therefore you need to modify the previous query to list the heaviest 100 products not including the heaviest ten.
SELECT Name FROM SalesLT.Product ORDER BY Weight DESC OFFSET 10 ROWS FETCH NEXT 100 ROWS ONLY;
The above solution works only if you are running from SQL 2012 if you are on lower versions this needs to be written as below
SELECT Name
FROM
(
SELECT Name,
ROW_NUMBER() OVER (ORDER BY weight desc) AS Seq
FROM SalesLT.Product
)t
WHERE Seq BETWEEN 11 AND 110
4) Retrieve product details for product model 1
Initially, you need to find the names, colors, and sizes of the products with a product model ID 1.
select Name,Color,size from SalesLT.Product where ProductModelID=1
5)Filter products by color and size
Retrieve the product number and name of the products that have a color of 'black', 'red', or 'white' and a size of 'S' or 'M'.
SELECT ProductNumber, Name FROM SalesLT.Product
WHERE Color IN ('Black','Red','White') and Size IN ('S','M')
6)Filter products by product number
Retrieve the product number, name, and list price of products whose product number begins 'BK-'.
SELECT ProductNumber, Name, ListPrice FROM SalesLT.Product
WHERE ProductNumber LIKE 'BK-%';
7) Retrieve specific products by product number
Modify your previous query to retrieve the product number, name, and list price of products whose product number begins 'BK-' followed by any character other than 'R’, and ends with a '-' followed by any two numerals
SELECT ProductNumber, Name, ListPrice FROM SalesLT.Product
WHERE ProductNumber LIKE 'BK-[^R]%-[0-9][0-9]'
Comments