Learning Transact-SQL exercise 1

Introduction to Transact-SQL

AdventureWorks Cycles is a company that sells directly to retailers, who then sell products to consumers. Each retailer that is an AdventureWorks customer has provided a named contact for all communication from AdventureWorks.

The sales manager at AdventureWorks has asked you to generate some reports containing details of the company's customers to support a direct sales campaign. Let's start with some basic exploration.

Instructions

Familiarize yourself with the Customer table by writing a Transact-SQL query that retrieves all columns for all customers.

Solution:👀

	-- select all columns
	SELECT * FROM SalesLT.Customer;
	-- from the SalesLT.Customer table

view raw Select*.sql hosted with ❤ by GitHub

Create List of Customer Contacts

As a next step, it would be good to have a structured view of the names of your customer contacts.

Create a table that lists all customer contact names. The table should include

the Title,FirstName,MiddleName,LastNamandSuffixof all customers.

Solution:👀

	-- select the Title, FirstName, MiddleName, LastName and Suffix columns
	-- from the Customer table
	SELECT Title, firstname, middlename, lastname, suffix
	FROM SalesLT.Customer;

view raw CreateCustomer.sql hosted with ❤ by GitHub

Title	firstname	middlename	lastname	suffix
Mr.	Orlando	N.	Gee	null
Mr.	Keith	null	Harris	null
Ms.	Donna	F.	Carreras	null
Ms.	Janet	M.	Gates	null

Create List of Customer Contacts (2)

Each customer has an assigned salesperson. Can you finish the query to include the salesperson and a nicely structured display of the customers' names?

• Complete the query to 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 + ' ' + and use the alias provided.

Solution:👀

	-- finish the query
	SELECT Salesperson, Title + ' ' + LastName AS CustomerName, Phone
	FROM SalesLT.Customer;

view raw CreateCustomer2.sql hosted with ❤ by GitHub

Salesperson	CustomerName	Phone
adventure-works\pamela0	Mr. Gee	245-555-0173
adventure-works\david8	Mr. Harris	170-555-0127
adventure-works\jillian0	Ms. Carreras	279-555-0130
adventure-works\jillian0	Ms. Gates	710-555-0173
adventure-works\shu0	Mr. Harrington	828-555-0186
adventure-works\linda3	Ms. Carroll	244-555-0112

Retrieving Customer and Sales Data

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 <Customer ID>: <Company Name> (e.g. 78: Preferred Bikes). You'll need to use both CAST() and VARCHAR in your solution. Don't forget to use the alias provided.

Solution:👀

	-- cast the CustomerID column to a VARCHAR and concatenate with the CompanyName column
	SELECT CAST(CustomerID AS varchar) + ': ' + CompanyName AS CustomerCompany
	FROM SalesLT.Customer;

view raw RetrievingCSdata.sql hosted with ❤ by GitHub

CustomerCompany
1: A Bike Store
2: Progressive Sports
3: Advanced Bike Components
4: Modular Cycle Systems
5: Metropolitan Sports Supply

Retrieving Customer and Sales Data (2)

The SalesLT.SalesOrderHeader table contains records of sales orders. You have been asked to retrieve data for a report that shows:

• The sales order number and revision number in the format <Order Number> (<Revision>) (e.g. SO71774 (2)).
• The order date converted to ANSI standard format yyyy.mm.dd (e.g. 2015.01.31).

Solution:👀

	-- finish the query
	SELECT SalesOrderNumber + ' (' + STR(RevisionNumber, 1) + ')' AS OrderRevision,
	CONVERT(nvarchar(30), OrderDate, 102) AS OrderDate
	FROM SalesLT.SalesOrderHeader;

view raw RetrievingCSdata2.sql hosted with ❤ by GitHub

OrderRevision	OrderDate
SO71774 (1)	2004.06.01
SO71776 (1)	2004.06.01
SO71780 (1)	2004.06.01
SO71782 (1)	2004.06.01
SO71783 (1)	2004.06.01

Retrieving Customer Contact Names

In this exercise, you'll write a query that returns a list of customer names.

The list should consist of a single field in the format:

• <first name> <last name> (e.g. Keith Harris) if the middle name is unknown,
• <first name> <middle name> <last name> (e.g. Jane M. Gates) if a middle name is stored in the database.

Solution:👀

	-- use ISNULL to check for middle names and concatenate with FirstName and LastName
	SELECT FirstName + ' ' + ISNULL(MiddleName + ' ', '') + LastName
	AS CustomerName
	FROM SalesLT.Customer;

view raw RetrievingCName.sql hosted with ❤ by GitHub

CustomerName
Orlando N. Gee
Keith Harris
Donna F. Carreras
Janet M. Gates
Lucy Harrington

Retrieving Primary Contact Details

Customers may provide AdventureWorks with an email address, a phone number, or both. If an email address is available, then it should be used as the primary contact method; if not, then the phone number should be used.

Here, you will write a query that returns a list of customer IDs in one column, and a second column named PrimaryContact that contains the email address if known, and otherwise the phone number.

Note: In the sample data provided in AdventureWorksLT, there are no customer records without an email address. Therefore, to verify that your query works as expected, we have run the following UPDATE statement to remove some existing email addresses before you write your query. (Don't worry, you'll learn about UPDATE statements later in the course!)

UPDATE SalesLT.Customer
SET EmailAddress = NULL
WHERE CustomerID % 7 = 1;

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.

Solution:👀

	-- select the CustomerID, and use COALESCE with EmailAddress and Phone columns
	SELECT CustomerID, COALESCE(EmailAddress, Phone) AS PrimaryContact
	FROM SalesLT.Customer;

view raw RetrievingPrimaryContact hosted with ❤ by GitHub

CustomerID	PrimaryContact
1	245-555-0173
2	keith0@adventure-works.com
3	donna0@adventure-works.com
4	janet1@adventure-works.com
5	lucy0@adventure-works.com

Retrieving Shipping Status

You have been asked to create a query that returns a list of 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.

Note: In the sample data provided in AdventureWorksLT, there are no SalesOrderHeader records without a ship date. Therefore, to verify that your query works as expected, we have run the following UPDATE statement to remove some existing ship dates before you write your query. (Again, don't worry, you'll learn about UPDATEstatements later in the course!)

UPDATE SalesLT.SalesOrderHeader
SET ShipDate = NULL
WHERE SalesOrderID > 71899;

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

Solution:👀

	SELECT SalesOrderID, OrderDate,
	CASE
	WHEN ShipDate IS NULL THEN 'Awaiting Shipment'
	ELSE 'Shipped'
	END AS ShippingStatus
	FROM SalesLT.SalesOrderHeader;

view raw RetrievingSStatus hosted with ❤ by GitHub

SalesOrderID	OrderDate	ShippingStatus
71774	2004-06-01 00:00:00	Shipped
71776	2004-06-01 00:00:00	Shipped
71780	2004-06-01 00:00:00	Shipped
71782	2004-06-01 00:00:00	Shipped
71783	2004-06-01 00:00:00	Shipped