# Fisseha Berhane, PhD

#### Data Scientist

443-970-2353 fisseha@jhu.edu CV Resume

### Querying Tables with SELECT¶

This post is lab exercise I did on Querying with Transact-SQL edx course.

In this lab, I will use SELECT queries to retrieve, sort, and filter data from the AdventureWorksLT database.

#### Retrieve Data for Transportation Reports¶

• 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.
In [ ]:
SELECT DISTINCT City, StateProvince

• 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.
In [ ]:
SELECT TOP 10 PERCENT Name FROM SalesLT.Product ORDER BY Weight DESC;

• 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.
In [ ]:
SELECT Name FROM SalesLT.Product ORDER BY Weight DESC
OFFSET 10 ROWS FETCH NEXT 100 ROWS ONLY;

• 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.
In [ ]:
SELECT Name, Color, Size
FROM SalesLT.Product
WHERE ProductModelID = 1;

• 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'.
In [ ]:
SELECT ProductNumber, Name
FROM SalesLT.Product
WHERE Color IN ('Black','Red','White') and Size IN ('S','M');

• Filter products by product number
Retrieve the product number, name, and list price of products whose product number begins 'BK-'.
In [ ]:
SELECT ProductNumber, Name, ListPrice
FROM SalesLT.Product
WHERE ProductNumber LIKE 'BK-%';

• 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.
In [ ]:
SELECT ProductNumber, Name, ListPrice
FROM SalesLT.Product
WHERE ProductNumber LIKE 'BK-[^R]%-[0-9][0-9]';