My Samples
https://tinyurl.com/cbmctsamples
Learning Path
Module: Introduction to Transact-SQL
Data Types
Instead of the STR function, consider using FORMAT. It offers much more functionality.
Nulls
Do not use the phrase "null value." Null is a marker that indicates that the actual value is missing or inapplicable (as per Codd's 3rd rule).
I'm not just being picky here, this is an important distinction. Calling null a "value" leads to cognitive errors, particularly the "null = null --> true" fallacy.
CONCAT, CONCAT_WS
Lab 1 tasked you with using a combination of + and ISNULL. There is a better alternative - CONCAT.
CONCAT has two party tricks. First, it automatically casts everything as a nvarchar. Second, it automatically replaces null markers with an empty string.
SELECT
address + ', ' + city + ', ' + region + ', ' + country AS mailinglabel1,
concat( address + ', ', city + ', ', region + ', ', country ) AS mailinglabel2,
concat_ws( ', ', address, city, region, country ) AS mailinglabel3
FROM
Sales.Customers
WHERE
region is null; ISNULL
Don't use ISNULL(). Instead, use the COALESCE() function. It is better in every way, as well as being ANSI-compliant (which ISNULL is not).
LIKE operator
Lab 4: Use Built-In Functions
Scalar Functions, step 8
SELECT * FROM string_split( 'FR-R92B-58', '-', 1);SELECT
p.ProductNumber,
( SELECT value FROM string_split( p.ProductNumber, '-', 1) WHERE ordinal = 1 ) AS ModelCode,
( SELECT value FROM string_split( p.ProductNumber, '-', 1) WHERE ordinal = 2 ) AS ItemCode,
( SELECT value FROM string_split( p.ProductNumber, '-', 1) WHERE ordinal = 3 ) As SizeCode
FROM
SalesLT.Product AS p;