I’ve been taking Microsoft courses in Redmond following the Data Science Professional Certification. Part of the course includes two T-SQL components, it’s been really fun and a great refresher, you can find an online version here. Here are a few notes and labs for reference, starting from the basics:
Lab 1
1. Write a Transact-SQL query that retrieves all columns for all customers.
SELECT *
FROM SalesLT.Customer;
2. Create a table that lists all customer contact names. The table should include the Title, FirstName, MiddleName, LastName and Suffix of all customers.
SELECT Title, FirstName, MiddleName, LastName, Suffix
FROM SalesLT.Customer;
3. List the following elements for all customers: The salesperson, A column named CustomerName that displays how the customer contact should be greeted (e.g. “Mr Smith”). The customer’s phone number (Phone). Don’t forget to space out the contents of your CustomerName column with + ‘ ‘ +
SELECT SalesPerson, Title + '' + LastName AS CustomerName, Phone
FROM SalesLT.Customer;
4. As a reminder, if you want to build a string with an integer (e.g. id) you can use: CAST(id AS VARCHAR)
Provide a list of all customer companies in the format : (e.g. 78: Preferred Bikes). You’ll need to use VARCHAR in your solution.
SELECT CAST(CustomerID AS VARCHAR) + ': ' + CompanyName AS CustomerCompany
FROM SalesLT.Customer;
5. Retrieve data for a report that shows:
The sales order number and revision number in the format () (e.g. SO71774 (2)).
The order date converted to ANSI standard format yyyy.mm.dd (e.g. 2015.01.31). Create the 2-column table that’s specified above.
SELECT SalesOrderNumber + '(' + STR(RevisionNumber, 1) + ')' AS OrderRevision CONVERT(nvarchar(30), OrderDate, 102) AS OrderDate
FROM SalesLT.SalesOrderHeader;
6. Write a query that returns a list of customer names. The list must consist of a single field in the format (e.g. Keith Harris) if the middle name is unknown, or (e.g. Jane M. Gates) if a middle name is stored in the database. Retrieve customer contact names including middle names when they’re known.
SELECT FirstName + ''+ ISNULL(MiddleName + ' ', '') + LastName AS CustomerName
FROM SalesLT.Costomer;
7. Write a query that returns a list of customer IDs in one column, and a second column called PrimaryContact that contains the email address if known, and otherwise the phone number.
SELECT CustomerID, COALESCE(EmailAddress, Phone) AS PrimaryContact
FROM SalesLT.Costomer;
- COALESCE: expression that can be evaluated multiple times, takes a variable number of parameters, there must be at least one NULL and you must provide a data type
- ISNULL: function that is only evaluates once, only takes two parameters, NULL is converted to int
8. Write a query to list sales order IDs and order dates with a column named ShippingStatus that contains the text ‘Shipped’ for orders with a known ship date, and ‘Awaiting Shipment’ for orders with no ship date.
SELECT SalesOrderID, OrderDate,
CASE
WHEN ShipDate IS NULL THEN 'Awaiting Shipment'
ELSE 'Shipped'
END AS ShippingStatus
FROM SalesLY.SalesOrderHeader
Lab 2
Retrieving Transportation Report Data
1. Return the Address table and retrieve all values for City and StateProvince, without duplicates.
SELECT DISTINCT City, StateProvince
FROM SalesLT.Address;
2. Retrieve the names of the top ten percent of products by weight.
SELECT TOP 10 PERCENT Name, Weight
FROM SalesLT.Product
ORDER BY Weight Desc;
3. List the heaviest 100 products not including the ten most heavy ones.
SELECT Name, Weight
FROM SalesLT.Product
ORDER BY Weight DESC
OFFSET 10 ROWS
FETCH NEXT 100 ROWS ONLY;
Retrieving Product Data
4. Write a query to find the names, colors, and sizes of the products with a product model ID of 1.
SELECT Name, Color,
FROM SalesLT.Product
WHERE ProductModelID= 1;
5. 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. Retrieve the product number, name, and list price of products that have a product number beginning with ‘BK-‘.
SELECT ProductNumber, Name, ListPrice
FROM SalesLT.Product
WHERE ProductNumber LIKE ‘BK%';
7. Modify your previous query to retrieve the product number, name, and list price of products with product number beginning with ‘BK-‘ followed by any character other than ‘R’, and ending with a ‘-‘ followed by any two numerals.
Remember:
- to match any string of zero or more characters, use %
- to match characters that are not R, use [^R]
- to match a numeral, use [0-9]
SELECT ProductNumber, Name, ListPrice
FROM SalesLT.Product
WHERE ProductNumber LIKE ‘BK-[^R]%-[0-9][0-9]';
Lab 3
1. Write a query that returns the company name from the SalesLT.Customer table, the sales order ID and total due from the SalesLT.SalesOrderHeadertable.
SELECT c.CompanyName, oh.SalesOrderID, oh.TotalDue
FROM SalesLT.Customer AS c
JOIN SalesLT.SalesOrderHeader AS oh
ON (c.CustomerID = oh.CustomerID);
2. Extend your customer orders query to include the main office address for each customer, including the full street address, city, state or province, postal code, and country or region. Make sure to use the aliases provided, and default column names elsewhere.
SELECT c.CompanyName, a.AddressLine1, ISNULL(a.AddressLine2, '') AS AddressLine2, a.City, a.StateProvince, a.PostalCode, a.CountryRegion, oh.SalesOrderID, oh.TotalDue
FROM SalesLT.Customer AS c
JOIN SalesLT.SalesOrderHeader AS oh
ON oh.CustomerID = c.CustomerID
JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID AND AddressType = ca.AddressType
JOIN SalesLT.Address AS a
ON ca.AddressID = a.AddressID;
3. Customers who have not placed any orders should be included at the bottom of the list with NULL values for the order ID and total due. Make sure to use the aliases provided, and default column names elsewhere.
SELECT c.CompanyName, c.FirstName, c.LastName, oh.SalesOrderID, oh.TotalDue
FROM SalesLT.Customer AS c
LEFT JOIN SalesLT.SalesOrderHeader AS oh
ON oh.CustomerID = c.CustomerID
ORDER by oh.SalesOrderID DESC;
4. Write a query that returns a list of customer IDs, company names, contact names (first name and last name), and phone numbers for customers with no address stored in the database. Make sure to use the aliases provided, and default column names elsewhere.
SELECT c.CompanyName, c.FirstName, c.LastName, c.Phone
FROM SalesLT.Customer AS c
LEFT JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
WHERE ca.AddressID IS NULL;
5.
- Write a query that returns a column of customer IDs for customers who have never placed an order, and a column of product IDs for products that have never been ordered.
- Each row with a customer ID should have a
NULLproduct ID (because the customer has never ordered a product) and each row with a product ID should have aNULLcustomer ID (because the product has never been ordered by a customer). - Make sure to use the aliases provided, and default column names elsewhere.
SELECT c.CustomerID, p.ProductID
FROM SalesLT.Customer AS c
FULL JOIN SalesLT.SalesOrderHeader AS oh
ON c.CustomerID = oh.CustomerID
FULL JOIN SalesLT.SalesOrderDetail AS od
ON od.SalesOrderID = oh.SalesOrderID
FULL JOIN SalesLT.Product AS p
ON p.ProductID = od.ProductID
WHERE oh.SalesOrderID IS NULL
ORDER BY ProductID, CustomerID;
Different Types of Joins examples are taken from Microsoft’s Join documentation using book titles and publishers as left and right tables:
Left (Outer) Join: Unmatched rows in the right table are not included
SELECT titles.title_id,
titles.title,
publishers.pub_name
FROM titles LEFT OUTER JOIN publishers
ON titles.pub_id
= publishers.pub_id
Left-Inner Join: Shows the left table and intersections in the right table
Right (Outer) Join: Unmatched rows in the left table are not included
SELECT titles.title_id,
titles.title,
publishers.pub_name
FROM titles RIGHT OUTER JOIN publishers
ON titles.pub_id
= publishers.pub_id
Right-Inner Join: Shows the right table and intersections in the left table
Inner Join: Only shows rows where the two tables intersect
SELECT title, pub_name FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id
Full-Outer Join: All rows in all tables are included, even if they don’t have a match in the following table
SELECT titles.title_id,
titles.title,
publishers.pub_name
FROM titles FULL OUTER JOIN publishers
ON titles.pub_id
= publishers.pub_id
Cross-Join: Shows all possible pairings, probably only used for generating large test datasets
SELECT * FROM authors CROSS JOIN publishers
June Cadenhead 