Fisseha Berhane, PhD

Data Scientist

443-970-2353 fisseha@jhu.edu CV Resume Linkedin GitHub twitter twitter

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
FROM SalesLT.Address
  • 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]';




comments powered by Disqus