Beware spoilers!
Lab 1
Challenge 1
SELECT
SalesPerson,
CONCAT( Title, ' ', LastName ) as CustomerGreeting,
Phone AS CustomerPhone
FROM
SalesLT.Customer
ORDER BY
SalesPerson, CustomerGreeting;
The CONCAT() function automatically casts everything to text and treats null markers as empty strings (see challenge 3 below). The CONCAT_WS() function concatenates using a seperator.
Challenge 2
SELECT
CONCAT( CustomerID, ':', CompanyName ) AS Customer
FROM
SalesLT.Customer
ORDER BY
Customer;
SELECT
CONCAT( SalesOrderNumber, ' (', RevisionNumber, ')' ) AS SONumberRevison,
FORMAT( OrderDate, 'yyyy.MM.dd' ) AS OrderDate
FROM
SalesLT.SalesOrderHeader
ORDER BY
SONumberRevison;
The FORMAT() function lets us use a .NET format string.
Challenge 3
-- This is why CONCAT() was invented. :-)
SELECT
FirstName + ' ' + MiddleName + ' ' + LastName AS FullName1,
CONCAT( FirstName, ' ', MiddleName + ' ', LastName ) AS FullName2,
CONCAT_WS( ' ', FirstName, MiddleName, LastName ) AS FullName3
FROM
SalesLT.Customer
ORDER BY
FullName2;
SELECT
CustomerID ,
COALESCE( EmailAddress, Phone, 'No contacts supplied' ) AS PrimaryContact
FROM
SalesLT.Customer
ORDER BY
CustomerID;
The COALESCE() function is a more general version of ISNULL().
SELECT
SalesOrderID,
FORMAT( OrderDate, 'yyyy.MM.dd' ) AS OrderDate,
IIF( ShipDate is null, 'Awaiting shipment', 'Shipped' ) AS ShippingStatus
FROM
SalesLT.SalesOrderHeader
ORDER BY
OrderDate;
The IIF() function is a two-option CASE. Just like the Excel worksheet function IF().
Lab 4
Challenge 1, Step 1
SELECT
SalesOrderID,
format( Freight, '#,##0.00', 'en-NZ' ) AS FreightCost1,
format( Freight, 'c2', 'en-NZ' ) AS FreightCost2
FROM
SalesLT.SalesOrderHeader;