Now that you have the knowledge to write simple SELECT statements, it is time to explore some of the other features of T-SQL that allow you to manipulate how the data is displayed, filtered, or ordered. To create expressions in T-SQL, you use functions and operators along with literal values and columns. The reasons for using expressions in T-SQL code are many. For example, you may want to display only the year of a column of the DATETIME data type on a report, or you may need to calculate a discount based on the order quantity in an order-entry application. Any time the data must be displayed, filtered, or ordered in a way that is different from how it is stored, you can use expressions and functions to manipulate the results.
You will find a very rich and versatile collection of functions and operators available to create expressions that manipulate strings and dates and much more. You can use expressions in the SELECT, WHERE, and ORDER BY clauses as well as in other clauses you will learn about in Chapter 7.
Expressions Using Operators
You learned how to use several comparison operators in the WHERE clause in Chapter 3. In this section, you will learn how to use operators to concatenate strings and perform mathematical calculations in T-SQL queries.
Concatenating Strings
The concatenation operator (+) allows you to connect two strings. The syntax is simple: <string or column name> + <string or column name>. Start up Azure Data Studio if it is not already running and connect to your SQL Server instance and the AdventureWorks2019 database. Open a new query window, and type in and execute the code in Listing 4-1.
Figure 4-1 shows the partial results of running Listing 4-1. Query 1 shows that you can concatenate two strings. Queries 2 and 3 demonstrate concatenating the LastName and FirstName columns along with either a space or a comma and space. Notice that you specified the alias, [Full Name], to provide a column header for the result of the expressions combining FirstName and LastName. If you did not provide the alias, the column header would be (No column name), as in Query 1. In reality, it doesn’t have a name at all, so it is very important that you always alias expressions.
Figure 4-1
The partial results of queries concatenating strings
Concatenating Strings and NULL
In Chapter 3, you learned about the challenges when working with NULL in WHERE clause expressions. When concatenating a string with a NULL, NULL is returned. Listing 4-2 demonstrates this problem. Type the code in Listing 4-2 into a query window and execute it.
Figure 4-2 shows the results of Listing 4-2. The query combines the FirstName, MiddleName, and LastName columns into a Full Name column. The MiddleName column is optional; that is, NULLs are allowed. Only the rows where the MiddleName value has been entered show the expected results. The rows where MiddleName is NULL return NULL. You will learn how to fix this problem later in this chapter.
Figure 4-2
The results of concatenating a string with NULL
CONCAT
SQL Server 2012 introduced another powerful tool for concatenating strings and even nonstring values. The CONCAT function takes any number of values as arguments and automatically concatenates them together. The values can be passed to the CONCAT function as variables, columns, or as literal values. The output is always implicitly converted to a string data type. Run the code in Listing 4-3 to see how to use the CONCAT function. Query section 2 includes two variables. You’ll learn more about variables in Chapter 12.
Query 1 command simply concatenates four separate string values. Query 2 declares two variables and then concatenates those into a single result. Query 3 uses the CONCAT function in a SELECT clause to concatenate table rows. The final example, Query 4, shows that NULLs are ignored when using CONCAT. Figure 4-3 shows the output.
Figure 4-3
Partial results of CONCAT functions
ISNULL and COALESCE
Two functions are available to replace NULLs with another value in the results. The first function, ISNULL, requires two parameters: the value to check and the replacement for NULL. COALESCE works a bit differently. COALESCE will take any number of arguments and return the first non-NULL it finds. T-SQL developers often prefer COALESCE over ISNULL because COALESCE meets ANSI (American National Standards Institute) standards. Microsoft added ISNULL as a proprietary feature. Also, COALESCE is more versatile. Here is the syntax for the two functions:
ISNULL(<value>,<replacement>)
COALESCE(<value1>,<value2>,...,<valueN>)
Type in and execute the code in Listing 4-4 to learn how to use ISNULL and COALESCE.
Figure 4-4 shows the partial results of running the code. Query 1 uses the ISNULL function to replace any missing MiddleName values with an empty string in order to build Full Name. Notice in the results that whenever MiddleName is missing, you end up with two spaces between FirstName and LastName. Line 3 in the results of Query 1 contains two spaces between Kim and Abercrombie because a space is added both before and after the ISNULL function. To correct this problem, move the space inside the ISNULL function instead of before it: ISNULL(' ' + MiddleName,''). Concatenating a space (or anything really) with NULL returns NULL. When the MiddleName value is NULL, the space is eliminated, and no extra spaces show up in your results. Instead of ISNULL, Query 3 contains the COALESCE function. If MiddleName is NULL, the next non-NULL value, the empty string, is returned.
Figure 4-4
The partial results of using ISNULL and COALESCE when concatenating strings
Concatenating Other Data Types to Strings
To concatenate nonstring values to strings, the nonstring value must be converted to a string. If the string value can be implicitly converted to a number, the values will be added together instead. Run this statement to see what happens: SELECT 1 + '1';. If the desired result is 11 instead of 2, the numeric value must be explicitly converted to a string using either the CAST or CONVERT function. If you attempt to concatenate a nonnumeric string and a number without converting, you will receive an error message. Run this example to see the error: SELECT 1 + 'a';. This is because integers have higher precedence than strings.
Note
Instead of using CAST or CONVERT to convert to string data types when concatenating, you can use the CONCAT function. This function, covered in a previous section, automatically converts other data types to strings.
Use one of the functions, CAST or CONVERT, to convert a number or date value to a string. Here is the syntax:
CAST(<value> AS <new data type>)
CONVERT(<new data type>,<value>)
Listing 4-5 demonstrates how to use these functions. Type in and execute the code in a query window.
--1
SELECT CAST(BusinessEntityID AS NVARCHAR) + ': ' + LastName
SELECT BusinessEntityID, BusinessEntityID + 1 AS "Adds 1",
CAST(BusinessEntityID AS NVARCHAR(10)) + '1' AS "Appends 1"
FROM Person.Person;
Listing 4-5
Using CAST and CONVERT
Figure 4-5 shows the partial results of running the code. The functions in Queries 1 and 2 have very different syntaxes, but they accomplish the same result. They both change the BusinessEntityID values from integers into a string data type (NVARCHAR) so that it can be concatenated to a string. Many programmers prefer CAST over CONVERT because CAST is compliant with the ANSI SQL-99 standard. Query 1 specifies just NVARCHAR as the data type without a size. By default, the maximum length will be 30 characters. If you need to cast to a value more than 30 characters, you must specify a length argument greater than 30. As a best practice, always specify the length, because it will default to 1 when not specified when declaring a variable. Query 3 demonstrates the difference between converting the numeric value and not converting it. For more information about CONVERT, take a look at the “CONVERT” section later in the chapter.
Figure 4-5
The partial results of using CAST and CONVERT
Developers must often concatenate strings for reports or for loading data from one system to another. Now practice what you have learned about concatenating strings within a T-SQL query by completing Exercise 4-1.
EXERCISE 4-1
Use the AdventureWorks2019 database to complete Questions 1–4. Complete the rest of the questions using the WideWorldImporters database. You can find the solutions at the end of the chapter.
1.
Write a query that returns data from the Person.Address table in this format AddressLine1 (City PostalCode) from the Person.Address table.
2.
Write a query using the Production.Product table displaying the product ID, color, and name columns. If the color column contains a NULL, replace the color with No Color.
3.
Modify the query written in Question 2 so that the description of the product is returned formatted as Name: Color. Make sure that all rows display a value even if the Color value is missing.
4.
Write a query using the Production.Product table displaying a description with the ProductID: Name format. Hint: You will need to use a function to write this query.
5.
Switch to WideWorldImporters. Write a query using the Application.Cities table. Using the CONCAT() function, put together CityName and LatestRecordedPopulation separating the values with a hyphen.
6.
Write a query against the Application.People table using only FullName and SearchName. Format the output like this:
FullName (SearchName)
Here is an example output:
Bijoya Thakur (Bijoya Bijoya Thakur)
7.
Write a query using the Application.Cities table, returning only CityName, LatestRecordedPopulation. Use COALESCE or ISNULL to return 0 for those cities not reporting a LatestRecordedPopulation.
8.
In the preceding query, what happens if you return N/A instead of 0?
9.
Explain the difference between the ISNULL and COALESCE functions.
Using Mathematical Operators
You can use several operators to perform simple mathematical operations on numeric values. Use the plus symbol (+) to perform addition, the hyphen (–) to perform subtraction, the asterisk (*) to perform multiplication, and the slash (/) to perform division. One operator that may be new to you is the modulo (%) operator, which returns the remainder when division is performed on the two values. For example, 5 % 2 returns 1 because 1 is the remainder when you divide 5 by 2. One common use for modulo is to determine whether a number is odd or even by making the second value in the expression 2. If the result is 1, then the value is odd; if the result is 0, then the value is even. Listing 4-6 shows how to use some of the mathematical operators. Type in and execute the code to see the results.
--1
SELECT 1 + 1 AS ADDITION, 10.0 / 3 AS DIVISION, 10 / 3 AS [Integer Division], 10 % 3 AS MODULO;
Take a look at the results shown in Figure 4-6. Query 1 shows how to perform calculations on literal values. Note that 10 / 3 does not equal 3.333. Because the two operators in that expression are integers, the result is also an integer. Query 2 shows the result of multiplying the values stored in the OrderQty column by 10.
Figure 4-6
The partial results of using mathematical operators
Query 3 replicates the precalculated LineTotal column by calculating the value by using an expression. The LineTotal column is a “computed column.” The actual formula used in the table definition looks a bit more complicated than the one I used since it checks for NULLs. The simplified formula requires parentheses to enforce the logic, causing subtraction to be performed before multiplication. Because multiplication has a higher precedence than subtraction, use parentheses to enforce the intended logic. Query 4 shows how to use the ISNULL function to substitute the value 1000 when the MaxQty is NULL before multiplying by the DiscountPct value.
Practice what you have learned about mathematical operators to complete Exercise 4-2.
EXERCISE 4-2
Use the AdventureWorks2019 database to complete Questions 1 to 3. Switch to the WideWorldImporters database to complete Question 4. You can find the solutions at the end of the chapter.
1.
Write a query using the Sales.SpecialOffer table. Display the difference between the MinQty and MaxQty columns along with the SpecialOfferID and Description columns.
2.
Write a query using the Sales.SpecialOffer table. Multiply the MinQty column by the DiscountPct column. Include the SpecialOfferID and Description columns in the results.
3.
Write a query using the Sales.SpecialOffer table that multiplies the MaxQty column by the DiscountPct column. If the MaxQty value is NULL, replace it with the value 10. Include the SpecialOfferID and Description columns in the results.
4.
Switch to the WideWorldImporters database. Write a query against the Sales.OrderLines table. Include the StockItemID. Calculate the extended price (Quantity times UnitPrice). Calculate the tax (extended price times 0.15). Calculate the extended amount (extended price plus tax).
5.
Describe the difference between division and modulo.
When using operators, you must keep the data types of the values in mind. When performing an operation that involves two different data types, the expression will return values for the data type with the highest precedence if possible. What value can be rolled into the other value? For example, an INT can be converted to a BIGINT, but not the other way around. In other words, if a value can be a valid INT, it is also a valid BIGINT. However, many valid BIGINT values are too big to be converted to INT. Therefore, when an operation is performed on a BIGINT and an INT, the result will be a BIGINT.
It is not always possible to convert the lower precedence data type to the higher precedence data type. A character can’t always be converted to a numeric value. This is why the expression 1 + 'a' fails. For a list of possible data types in order of precedence, see the article “Data Type Precedence” in SQL Server’s online documentation.
Using String Functions
You will find a very rich set of T-SQL functions for manipulating strings. You often have a choice of where a string will be manipulated. If the manipulation will occur on one of the columns in the select list, it might make sense to utilize the client to do the work if the manipulation is complex, but it is possible to do quite a bit of manipulation with T-SQL. You can use the string functions to clean up data before loading them into a database. This section covers many of the commonly used string functions. You can find many more in the online documentation.
RTRIM, LTRIM, and TRIM
The RTRIM and LTRIM functions remove spaces from the right side (RTRIM) or left side (LTRIM) of a string data type, respectively. The TRIM, new with SQL Server 2017, function removes spaces from both sides. You may need to use these functions when working with fixed-length data types (CHAR and NCHAR) or to clean up flat-file data before it is loaded from a staging database into a data warehouse. The syntax is simple:
RTRIM(<string>)
LTRIM(<string>)
TRIM(<string>)
Type in and execute the code in Listing 4-7. The first part of the code creates and populates a temporary table. Don’t worry about understanding that part of the code at this point.
--Create the temp table
CREATE TABLE #trimExample (COL1 VARCHAR(10));
GO
--Populate the table
INSERT INTO #trimExample (COL1)
VALUES ('a'),('b '),(' c'),(' d ');
--Select the values using the functions
SELECT COL1, '*' + RTRIM(COL1) + '*' AS "RTRIM",
'*' + LTRIM(COL1) + '*' AS "LTRIM",
'*' + TRIM(COL1) + '*' AS "TRIM"
FROM #trimExample;
--Clean up
DROP TABLE #trimExample;
Listing 4-7
Using the RTRIM, LTRIM, and TRIM Functions
Figure 4-7 shows the results of the code. The INSERT statement added four rows to the table with no spaces (a), spaces on the right (b), spaces on the left (c), and spaces on both (d). Inside the SELECT statement, you will see that asterisks surround the values to make it easier to see the remaining spaces in the results. The RTRIM function removed the spaces from the right side; the LTRIM function removed the spaces from the left side; the TRIM function removed the spaces from both sides.
Figure 4-7
The results of using the trimming functions
LEFT and RIGHT
The LEFT and RIGHT functions return a specified number of characters on the left or right side of a string, respectively. Developers use these functions to parse strings. For example, you may need to retrieve the three-character extension from file path data by using RIGHT. Take a look at the syntax:
LEFT(<string>,<number of characters)
RIGHT(<string>,<number of characters)
Listing 4-8 demonstrates how to use these functions. Type in and execute the code.
SELECT LastName,LEFT(LastName,5) AS "LEFT",
RIGHT(LastName,4) AS "RIGHT"
FROM Person.Person
WHERE BusinessEntityID IN (293,295,211,297,299,3057,15027);
Listing 4-8
Using the LEFT and RIGHT Functions
Figure 4-8 shows the results. Notice that even if the value contains fewer characters than the number specified in the second parameter, the function still works to return as many characters as possible.
Figure 4-8
The results of using LEFT and RIGHT
LEN and DATALENGTH
Use LEN to return the number of characters in a string. DATALENGTH returns the number of bytes in any data type or expression. DATALENGTH returns the same value as LEN when the string is a CHAR or VARCHAR data type, which takes one byte per character. The difference occurs when using DATALENGTH on NCHAR or NVARCHAR data types, which take up to two bytes per character. In this case, the DATALENGTH value is two times the LEN value. This is not incorrect; the two functions measure different things. The syntax is very simple:
LEN(<string>)
DATALENGTH(<any expression>)
Type in and execute the code in Listing 4-9 to learn how to use LEN and DATALENGTH.
SELECT LastName,LEN(LastName) AS "Length",
DATALENGTH(LastName) AS "Internal Data Length"
FROM Person.Person
WHERE BusinessEntityID IN (293,295,211,297,299,3057,15027);
Listing 4-9
Using the LEN and DATALENGTH Functions
Figure 4-9 shows the results. The Length column displays a count of the characters, while the Internal Data Length column displays the number of bytes.
Figure 4-9
The results of using LEN and DATALENGTH
CHARINDEX
Use CHARINDEX to find the numeric starting position of a search string inside another string. By checking to see whether the value returned by CHARINDEX is greater than zero, you can use the function to just determine whether the search string exists inside the second value. Developers often use CHARINDEX to locate a particular character, such as the at symbol (@) in an email address column, along with other functions when parsing strings. You will learn more about this in the “Nesting Functions” section later in the chapter. The CHARINDEX function requires two parameters: the search string and the string to be searched. An optional parameter, the start location, instructs the function to ignore a given number of characters at the beginning of the string to be searched. The following is the syntax; remember that the third parameter is optional (square brackets surround optional parameters in the syntax):
Listing 4-10 demonstrates how to use CHARINDEX. Type in and execute the code to learn how to use this function.
SELECT LastName, CHARINDEX('e',LastName) AS "Find e",
CHARINDEX('e',LastName,4) AS "Skip 3 Characters",
CHARINDEX('be',LastName) AS "Find be",
CHARINDEX('Be',LastName) AS "Find Be"
FROM Person.Person
WHERE BusinessEntityID IN (293,295,211,297,299,3057,15027);
Listing 4-10
Using the CHARINDEX Function
Figure 4-10 shows the results. The Find e column in the results displays the first location of the letter e in the LastName value. The Skip 3 Characters column displays the first location of the letter e when the first three characters of the LastName value are ignored. Finally, the Find be column demonstrates that you can use the function with search strings that are more than one character in length. There are many versions of AdventureWorks available, and some of older versions are case sensitive. If you are using a case-sensitive version, your results will be slightly different. You’ll see different values returned when searching for be and Be.
Figure 4-10
The results of using CHARINDEX
SUBSTRING
Use SUBSTRING to return a portion of a string starting at a given position and for a specified number of characters. In some ways, it is just a more powerful LEFT function. For example, an order-entry application may assign a customer ID based on the first seven letters of the customer’s last name plus digits 4 to 9 of the phone number. SUBSTRING could be used to find the digits. The SUBSTRING function requires three parameters: the string, a starting location, and the number of characters to retrieve. If the number of characters to retrieve is greater than the length of the string, the function will return as many characters as possible. If the start location is past the end of the string, then an empty string, not a NULL, will be returned. Here is the syntax of SUBSTRING:
SUBSTRING(<string>,<start location>,<length>)
Type in and execute the code in Listing 4-11 to learn how to use SUBSTRING.
SELECT LastName, SUBSTRING(LastName,1,4) AS "First 4",
SUBSTRING(LastName,5,50) AS "Characters 5 and later"
FROM Person.Person
WHERE BusinessEntityID IN (293,295,211,297,299,3057,15027);
Listing 4-11
Using the SUBSTRING Function
Notice in the results shown in Figure 4-11 that if the starting point is located after the available characters (Abel and Bell), an empty string is returned. Otherwise, in this example, the FirstName column is divided into two strings.
Figure 4-11
The results of using SUBSTRING
CHOOSE
CHOOSE allows you to select a value in an array based on an index. The CHOOSE function requires an index value and list of values for the array. Here is the basic syntax for the CHOOSE function:
CHOOSE ( index, val_1, val_2 [, val_n ] )
The index simply points to the position in the array that you want to return. Listing 4-12 shows a basic example.
Figure 4-12 shows the results of running the CHOOSE function. Keep in mind that the results take the highest data type precedence. This means if there is an integer in the list, the CHOOSE function will try to convert any results to an integer. If the value is a string, the CHOOSE command will throw an error. You will need to convert any integer values in the array to VARCHAR to avoid this error. If the position doesn’t exist, a NULL value will be returned.
Figure 4-12
Result from the CHOOSE function
REVERSE
REVERSE returns a string in reverse order. I often use it along with the CHARINDEX and RIGHT functions to find a file name from the file’s path. I use CHARINDEX on the reversed string to find the last backslash in the path, which then tells me how many characters, minus 1, on the right side of the string I need to grab. The same method can be used to parse an email address. To see how to do this, see the example in the “Nesting Functions” later in the chapter. Type in and execute this code to learn how to use REVERSE:
SELECT REVERSE('!dlroW ,olleH');
UPPER and LOWER
Use UPPER and LOWER to change a string to either uppercase or lowercase. You may need to display all uppercase data in a report, for example. The syntax is very simple:
UPPER(<string>)
LOWER(<string>)
Type in and execute the code in Listing 4-13 to learn about using UPPER and LOWER.
SELECT LastName, UPPER(LastName) AS "UPPER",
LOWER(LastName) AS "LOWER"
FROM Person.Person
WHERE BusinessEntityID IN (293,295,211,297,299,3057,15027);
Listing 4-13
Using the UPPER and LOWER Functions
Take a look at the results in Figure 4-13. AllLastName values appear in uppercase in the UPPER column, while they appear in lowercase in the LOWER column.
Figure 4-13
The partial results of using UPPER and LOWER
Note
You may think that you will use UPPER or LOWER often in the WHERE clause to make sure that the case of the value does not affect the results, but usually you don’t need to do this. By default, searching in T-SQL is case insensitive. The collation of the column determines whether the search will be case sensitive. This is defined at the server, but you can specify a different collation of the database, table, or column. See “Working with Collations” in the online documentation for more information.
REPLACE
Use REPLACE to substitute one string value inside another string value. REPLACE has three required parameters, but it is very easy to use. Use REPLACE to clean up data; for example, you may need to replace slashes (/) in a phone number column with hyphens (-) for a report. Here is the syntax:
REPLACE(<string value>,<string to replace>,<replacement>)
Type in and execute the code in Listing 4-14 to learn how to use REPLACE.
--1
SELECT LastName, REPLACE(LastName,'A','Z') AS "Replace A",
REPLACE(LastName,'A','ZZ') AS "Replace with 2 characters",
REPLACE(LastName,'ab','') AS "Remove string"
FROM Person.Person
WHERE BusinessEntityID IN (293,295,211,297,299,3057,15027);
--2
SELECT BusinessEntityID,LastName,MiddleName,
REPLACE(LastName,'a',MiddleName) AS "Replace with MiddleName",
REPLACE(LastName,MiddleName,'a') AS "Replace MiddleName"
FROM Person.Person
WHERE BusinessEntityID IN (285,293,10314);
Listing 4-14
Using the REPLACE Function
Notice in the results shown in Figure 4-14 that the REPLACE function replaces every instance of the string to be replaced. It doesn’t matter if the strings in the second and third parameters are not the same length, as shown in the column Replace with 2 characters. The Remove string column shows a convenient way to remove a character or characters from a string by replacing them with an empty string represented by two single quotes. Because the last name Bell doesn’t contain any of the values to be replaced, the value doesn’t change.
Figure 4-14
The results of using REPLACE
Query 2 demonstrates that the second and third parameters don’t have to be literal values. It uses the MiddleName column, either as the string to replace or as the replacement.
STRING_SPLIT and STRING_AGG
Two interesting functions that work a bit differently than the others are STRING_SPLIT and STRING_AGG. Instead of returning a value, STRING_SPLIT returns a table. It works by taking a string and splitting it into values in multiple rows. In the past, developers would typically create a user-defined function to do the job, but now there is a built-in function to do it. STRING_AGG does the opposite; it operates over a set of rows and concatenates the values into a delimited string. Listing 4-15 demonstrates how to work with STRING_SPLIT and STRING_AGG.
--1
SELECT value
FROM STRING_SPLIT('1,2,3,4,5,6,7,8,9,10',',');
--2
SELECT value
FROM STRING_SPLIT('dog cat fish bird lizard',' ');
--3
SELECT STRING_AGG(Name, ', ') AS List
FROM Production.ProductCategory;
Listing 4-15
Using STRING_SPLIT and STRING_AGG
Figure 4-15 shows the results of running Listing 4-15. In Queries 1 and 2, the STRING_SPLIT function is found in the FROM clause instead of the SELECT since it returns the results in a tabular format, not a scalar value. In Query 3, even though there are four rows in the source, one combined row is returned.
Figure 4-15
The results of using SPLIT_STRING
Nesting Functions
The previous sections showed how to use one function at a time to manipulate strings. If the results of one expression must be used as a parameter of another function call, you can nest functions. For example, if you are using an older version of SQL Server that doesn’t support the TRIM function, you can nest the LTRIM and RTRIM functions to remove the spaces from the beginning and ending of a string like this: LTRIM(RTRIM('test')). Keep in mind when writing nested functions that you work from the inside out. The innermost function is executed first, and the outer functions execute against the results. Take a look at some examples. Type in and execute the example shown in Listing 4-16 to display the domains in a list of email addresses and the file name from a list of file paths.
--1
SELECT EmailAddress,
SUBSTRING(EmailAddress,CHARINDEX('@',EmailAddress) + 1,50) AS DOMAIN
FROM Production.ProductReview;
--2
SELECT physical_name,
RIGHT(physical_name,CHARINDEX('',REVERSE(physical_name))-1) AS FileName
FROM sys.database_files;
Listing 4-16
Nesting Functions
Figure 4-16 shows the results of running this code. Query 1 first uses the CHARINDEX function to find the location of the at symbol (@). The results of that expression are used as a parameter to the outer SUBSTRING function. To display the characters after the @ symbol, add 1 to the position of the @ symbol.
Figure 4-16
The results of using nested functions
Query 2 finds the file name in a list of file paths. The query performs a SELECT command from the system view sys.database_files. After the SELECT command, the inner REVERSE function reverses the string value. Then the outer CHARINDEX finds the location of the backslash () symbol and subtracts one character to remove it from the results. By using that result as the second parameter of the RIGHT function, the query returns the file name. When writing a query like this, take it a step at a time and work from the inside out. You may have to experiment a bit to get it right.
This section covered a sample of the many functions available to manipulate strings in T-SQL. Complete Exercise 4-3 to practice using these functions.
EXERCISE 4-3
Use the AdventureWorks database to complete Questions 1 to 4. Then switch to the WideWorldImporters to complete this exercise. If you need help, refer to the discussion of the functions to help you figure out which ones to use. You can find the solutions to these questions at the end of the chapter.
1.
Write a query that displays the first ten characters of the AddressLine1 column in the Person.Address table.
2.
Write a query that displays characters 10 to 15 of the AddressLine1 column in the Person.Address table.
3.
Write a query displaying the first and last names from the Person.Person table all in uppercase.
4.
The ProductNumber in the Production.Product table contains a hyphen (-). Write a query that uses the SUBSTRING function and the CHARINDEX function to display the characters in the product number following the hyphen. Note: There is also a second hyphen in many of the rows; ignore the second hyphen for this question. Hint: Try writing this statement in two steps, the first using the CHARINDEX function and the second adding the SUBSTRING function.
5.
Switch to the WideWorldImporters database. Write a SELECT statement to the Application.Countries table, creating a new code, which is the first three characters of the CountryName capitalized. Alias the column NewCode, returning only the column created and the IsoAlpha3Code column. Hint: You will use both the UPPER() function and the LEFT() function.
6.
In the CustomerName (located in the Sales.Customer table), return only the portion inside of parentheses, including the parentheses. Hint: See the “Nesting Functions” section. You may need to use a number of built-in functions, such as SUBSTRING(), CHARINDEX(), and LEN().
Using Date and Time Functions
Just as T-SQL features a rich set of functions for working with string data, it also boasts an impressive list of functions for working with date and time data types. In this section, you’ll learn about some of the most commonly used functions for date and time data.
GETDATE and SYSDATETIME
Use GETDATE or SYSDATETIME to return the current date and time of the server. The difference is that SYSDATETIME returns seven decimal places after the second, the datetime2(7) data type, while GETDATE returns only three places, the DATETIME data type.
GETDATE and SYSDATETIME are nondeterministic functions. This means that they return different values each time they are called. Most of the functions in this chapter are deterministic, which means that a function always returns the same value when called with the same parameters and database settings. For example, the code CHARINDEX('B','abcd') will always return 2 if the collation of the database is case insensitive. In a case-sensitive database, the expression will return 0.
Run this code several times to see how these functions work:
SELECT GETDATE(), SYSDATETIME();
DATEADD
Use DATEADD to add a number of time units to a date. The function requires three parameters: the date part, the number, and a date. T-SQL doesn’t have a DATESUBTRACT function, but you can use a negative number to accomplish the same thing. You might use DATEADD to calculate an expiration date or a date that a payment is due, for example. Table 4-1 from the article about DATEADD in SQL Server’s documentation (https://docs.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql?view=sql-server-ver15) lists the possible values for the date part parameter in the DATEADD function and other date functions. Here is the syntax for DATEADD:
DATEADD(<date part>,<number>,<date>)
Table 4-1
The Values for the Date Part Parameter
Date Part
Abbreviation
Year
yy, yyyy
Quarter
qq, q
Month
mm, m
Dayofyear
dy, y
Day
dd, d
Week
wk, ww
Weekday
Dw
Hour
Hh
Minute
mi, n
Second
ss, s
Millisecond
Ms
Microsecond
Mcs
Nanosecond
Ns
Type in and execute the code in Listing 4-17 to learn how to use the DATEADD function.
--1
SELECT OrderDate, DATEADD(year,1,OrderDate) AS OneMoreYear,
DATEADD(month,1,OrderDate) AS OneMoreMonth,
DATEADD(day,-1,OrderDate) AS OneLessDay
FROM Sales.SalesOrderHeader
WHERE SalesOrderID in (43659,43714,60621);
--2
SELECT DATEADD(month,1,'2009-01-29') AS FebDate;
Listing 4-17
Using the DATEADD Function
Figure 4-17 shows the results of Listing 4-17. In Query 1, the DATEADD function adds exactly the time unit specified in each expression to the OrderDate column from the Sales.SalesOrderHeader table. Notice in the results of Query 2 that because there is no 29th day of February 2009, adding one month to January 29, 2009, returns February 28, the last possible day in February that year.
Figure 4-17
The results of using the DATEADD function
DATEDIFF
The DATEDIFF function allows you to find the difference between two dates. The function requires three parameters: the date part and the two dates. The DATEDIFF function might be used to calculate how many days have passed since unshipped orders were taken, for example. Here is the syntax:
DATEDIFF(<datepart>,<early date>,<later date>)
See Table 4-1 for the list of possible date parts. Listing 4-18 demonstrates how to use DATEDIFF. Be sure to type in and execute the code.
--1
SELECT OrderDate, GETDATE() CurrentDateTime,
DATEDIFF(year,OrderDate,GETDATE()) AS YearDiff,
DATEDIFF(month,OrderDate,GETDATE()) AS MonthDiff,
DATEDIFF(d,OrderDate,GETDATE()) AS DayDiff
FROM Sales.SalesOrderHeader
WHERE SalesOrderID in (43659,43714,60621);
--2
SELECT DATEDIFF(year,'2008-12-31','2009-01-01') AS YearDiff,
DATEDIFF(month,'2008-12-31','2009-01-01') AS MonthDiff,
DATEDIFF(d,'2008-12-31','2009-01-01') AS DayDiff;
Listing 4-18
Using the DATEDIFF Function
Figure 4-18 shows the results. Your results from Query 1 will be different from mine because the query uses GETDATE(), a nondeterministic function, instead of hard-coded dates or dates from a table. Remember that you can use the date part name or abbreviate it. The examples abbreviate “day” with “d.” Even though Query 2 compares the difference between two dates that are just one day apart, the differences in years and months are both 1. The DATEDIFF rounds up the result to the nearest integer and doesn’t display decimal results.
Figure 4-18
The results of using DATEDIFF
DATENAME and DATEPART
The DATENAME and DATEPART functions return the part of the date specified. Developers use the DATENAME and DATEPART functions to display just the year or month on reports, for example. DATEPART always returns an integer value. DATENAME returns a character string, the actual name when the date part is the month or the day of the week. Again, you can find the possible date parts in Table 4-1. The syntax for the two functions is similar:
DATENAME(<datepart>,<date>)
DATEPART(<datepart>,<date>)
Type in and execute the code in Listing 4-19 to learn how to use DATENAME and DATEPART.
--1
SELECT OrderDate, DATEPART(year,OrderDate) AS OrderYear,
DATEPART(month,OrderDate) AS OrderMonth,
DATEPART(day,OrderDate) AS OrderDay,
DATEPART(weekday,OrderDate) AS OrderWeekDay
FROM Sales.SalesOrderHeader
WHERE SalesOrderID in (43659,43714,60621);
--2
SELECT OrderDate, DATENAME(year,OrderDate) AS OrderYear,
DATENAME(month,OrderDate) AS OrderMonth,
DATENAME(day,OrderDate) AS OrderDay,
DATENAME(weekday,OrderDate) AS OrderWeekDay
FROM Sales.SalesOrderHeader
WHERE SalesOrderID in (43659,43714,60621);
Listing 4-19
Using the DATENAME and DATEPART Functions
Figure 4-19 displays the results. You will see that the results are the same except for spelling out the month and weekday in Query 2. One other thing to keep in mind is that the value returned from DATEPART is always an integer, while the value returned from DATENAME is always a string, even when the expression returns a number as in OrderDay.
Figure 4-19
Results of using DATENAME and DATEPART
DAY, MONTH, and YEAR
The DAY, MONTH, and YEAR functions work just like DATEPART. These functions are just alternate ways to get the day, month, or year from a date. Here is the syntax:
DAY(<date>)
MONTH(<date>)
YEAR(<date>)
Type in and execute the code in Listing 4-20 to see that this is just another way to get the same results as using the DATEPART function.
SELECT OrderDate, YEAR(OrderDate) AS OrderYear,
MONTH(OrderDate) AS OrderMonth,
DAY(OrderDate) AS OrderDay
FROM Sales.SalesOrderHeader
WHERE SalesOrderID in (43659,43714,60621);
Listing 4-20
Using the DAY, MONTH, and YEAR Functions
Figure 4-20 displays the results of the code from Listing 4-20.
Figure 4-20
The result of using YEAR, MONTH, and DAY
CONVERT
You learned about CONVERT earlier in the chapter when I talked about concatenating strings. To append a number or a date to a string, the number or date must first be cast to a string. The CONVERT function has an optional parameter called style that can be used to format a date or number.
I have frequently seen code that used the DATEPART function to break a date into its parts and then cast the parts into strings and concatenate them back together to format the date. It is so much easier just to use CONVERT to accomplish the same thing! Here is the syntax:
CONVERT(<data type, usually varchar>,<date>,<style>)
Type in and execute the code in Listing 4-21 to compare both methods of formatting dates. Take a look at the online article “CAST and CONVERT” for a list of all the possible formats.
--1 The hard way!
SELECT CAST(DATEPART(YYYY,GETDATE()) AS VARCHAR) + '/' +
CAST(DATEPART(MM,GETDATE()) AS VARCHAR) +
'/' + CAST(DATEPART(DD,GETDATE()) AS VARCHAR) AS DateCast;
--2 The easy way!
SELECT CONVERT(VARCHAR,GETDATE(),111) AS DateConvert;
--3
SELECT CONVERT(VARCHAR,OrderDate,1) AS "1",
CONVERT(VARCHAR,OrderDate,101) AS "101",
CONVERT(VARCHAR,OrderDate,2) AS "2",
CONVERT(VARCHAR,OrderDate,102) AS "102"
FROM Sales.SalesOrderHeader
WHERE SalesOrderID in (43659,43714,60621);
Listing 4-21
Using CONVERT to Format a Date/Time Value
Figure 4-21 shows the results of Listing 4-21. Notice in Query 1 that you not only had to use DATEPART three times, but you also had to cast each result to a VARCHAR in order to concatenate the pieces back together. Query 2 shows the easy way to accomplish the same thing. This method is often used to remove the time from a DATETIME data type. Query 3 demonstrates four different formats, including two US formats. Notice that the three-digit styles always produce four-digit years.
Figure 4-21
The results of formatting dates
The CAST and CONVERT functions are also useful in removing the time portion of datetime or datetime2 values. By casting to a DATE, only a date is returned.
FORMAT
The primary purpose of the FORMAT function is to simplify the conversion of date/time values as string values. Another purpose of the FORMAT function is to convert date/time values to their cultural equivalencies. This function is easy to use and is similar to other programming languages. If performance is important, however, you will want to stick with using the CONVERT function as covered earlier in the chapter. Here is the syntax:
FORMAT(value, format [, culture ])
The FORMAT function greatly simplifies how date/time values are converted. Listing 4-22 shows some examples.
DECLARE @d DATETIME = GETDATE();
SELECT FORMAT( @d, 'dd', 'en-US' ) AS Result;
SELECT FORMAT( @d, 'yyyy-M-d') AS Result;
SELECT FORMAT( @d, 'MM/dd/yyyy', 'en-US' ) AS Result;
Listing 4-22
FORMAT Function Examples
Figure 4-22 shows the results. Keep in mind the letters for each part of the date are case sensitive. For example, if you switch mm for MM, you will get back minutes instead of months.
Figure 4-22
FORMAT function results
DATEFROMPARTS
The DATEFROMPARTS function is a simple method to derive a date, time, or date and time from a list of values. The primary function is called DATEFROMPARTS, but there is also a version of the function for each date or time data type. Listing 4-23 shows some examples.
Figure 4-23 shows the results from each function. The first function returns only the date. The TIMEFROMPARTS function returns a time. Finally, the DATETIME2FROMPARTS returns both a date and a time. If a value is out of the range of either a date or time (e.g., if you were to type 13 for the month value), the function will throw an error.
Figure 4-23
Results from DATEFROMPARTS functions
EOMONTH
The EOMONTH function returns the date of the last day of the supplied month argument. You can also supply an offset to return the end of the month for another month. Here is an example that you can run to see how EOMONTH works:
SELECT EOMONTH(GETDATE()) AS [End of this month],
EOMONTH(GETDATE(),1) AS [End of next month],
EOMONTH('2020-01-01') AS [Another month];
This section covered a sample of the functions available for manipulating dates. Practice what you have learned by completing Exercise 4-4.
EXERCISE 4-4
Use the AdventureWorks2019 database to complete this exercise. You can find the solutions to the questions at the end of the chapter.
1.
Write a query that calculates the number of days between the date an order was placed and the date that it was shipped using the Sales.SalesOrderHeader table. Include the SalesOrderID, OrderDate, and ShipDate columns.
2.
Write a query that displays only the date, not the time, for the order date and ship date in the Sales.SalesOrderHeader table.
3.
Write a query that adds six months to each order date in the Sales.SalesOrderHeader table. Include the SalesOrderID and OrderDate columns.
4.
Write a query that displays the year of each order date and the numeric month of each order date in separate columns in the results. Include the SalesOrderID and OrderDate columns.
5.
Change the query written in Question 4 to display the month name instead.
6.
Write a SELECT statement that returns the date five quarters in the past from today’s date.
Using Mathematical Functions
You can use several mathematical functions on numeric values. These include trigonometric functions such as SIN and TAN and logarithmic functions. This section discusses some of the more commonly used mathematical functions.
ABS
The ABS function returns the absolute value of the number—the distance between the number and zero. Type in and execute this code to see how to use ABS:
SELECT ABS(2) AS "2", ABS(-2) AS "-2";
POWER
The POWER function returns the power of one number to another number. The syntax is simple:
POWER(<number>,<power>)
There may not be many uses for POWER in business applications, but you may use it in scientific or academic applications. Type in and execute the code in Listing 4-24.
SELECT POWER(10,1) AS "Ten to the First",
POWER(10,2) AS "Ten to the Second",
POWER(10,3) AS "Ten to the Third";
Listing 4-24
Using the POWER Function
Figure 4-24 displays the results. Caution must be taken, however, with this function. The results will increase in size very quickly and can cause an overflow error. Try finding the value of 10 to the 10th power to see what can happen. When 10 is the first argument, the function returns an integer, but the value is too large. If you cast the 10 to a float, then you can get the answer.
Figure 4-24
The results of using the POWER function
SQUARE and SQRT
The SQUARE function returns the square of a number, or the number multiplied to itself. The SQRT function returns the opposite, the square root of a number. Type in and execute the code in Listing 4-25 to see how to use these functions.
SELECT SQUARE(10) AS "Square of 10",
SQRT(10) AS "Square Root of 10",
SQRT(SQUARE(10)) AS "The Square Root of the Square of 10";
Listing 4-25
Using the SQUARE and SQRT Functions
Figure 4-25 shows the results. Notice that the third expression in the query is a nested function that squares 10 and then takes the square root of that result.
Figure 4-25
The results of using SQUARE and SQRT functions
ROUND
The ROUND function allows you to round a number to a given precision. The ROUND function is used frequently to display only the number of decimal places required in the report or application. The ROUND function requires two parameters, the number and the length, which can be either positive or negative. It also has an optional third parameter that causes the function to just truncate instead of rounding if a nonzero value is supplied. Here is the syntax:
ROUND(<number>,<length>[,<function>])
Type in and execute the code in Listing 4-26 to learn how to use ROUND.
SELECT ROUND(1234.1294,2) AS "2 places on the right",
ROUND(1234.1294,-2) AS "2 places on the left",
ROUND(1234.1294,2,1) AS "Truncate 2",
ROUND(1234.1294,-2,1) AS "Truncate -2";
Listing 4-26
Using the ROUND Function
You can view the results in Figure 4-26. When the expression contains a negative number as the second parameter, the function rounds on the left side of the decimal point. Notice the difference when 1 is used as the third parameter, causing the function to truncate instead of rounding. When rounding 1234.1294, the expression returns 1234.1300. When truncating 1234.1294, the expression returns 1234.1200. It doesn’t round the value; it just changes the specified digits to zero.
Figure 4-26
The results of using the ROUND function
RAND
RAND returns a float value between 0 and 1. RAND can be used to generate a “random” value. This might be used to generate data for testing an application, for example. The RAND function takes one optional integer parameter, @seed. When the RAND expression contains the seed value, the function returns the same value each time. If the expression doesn’t contain a seed value, SQL Server randomly assigns a seed, effectively providing a pseudo-random number. Type in and execute the code in Listing 4-27 to generate random numbers.
SELECT CAST(RAND() * 100 AS INT) + 1 AS "1 to 100",
CAST(RAND()* 1000 AS INT) + 900 AS "900 to 1900",
CAST(RAND() * 5 AS INT)+ 1 AS "1 to 5";
Listing 4-27
Using the RAND Function
Because the function returns a float value, multiply by the size of the range and add the lower limit. The first expression returns random numbers between 1 and 100. The second expression returns random numbers between 900 and 1900. The third expression returns random values between 1 and 5. My results are shown in Figure 4-27, but you will see something different.
Figure 4-27
The results of generating random numbers with the RAND function
If you supply a seed value to one of the calls to RAND within a batch of statements, that seed affects the other calls. The value is not the same, but the values are predictable. Run this statement several times to see what happens when a seed value is used:
SELECT RAND(3),RAND(),RAND();
If you leave out the seed, you will see different results each time. Another interesting thing is that, even though you see different values in each column, you will see the same values in each row. Run this statement multiple times to see what happens:
SELECT RAND(),RAND(),RAND(),RAND()
FROM sys.objects;
Just like strings and dates, you will find several functions that manipulate numbers. Practice using these functions by completing Exercise 4-5.
EXERCISE 4-5
Use the AdventureWorks2019 database to complete this exercise. You can find the solutions to the questions at the end of the chapter.
1.
Write a query using the Sales.SalesOrderHeader table that displays the SubTotal rounded to two decimal places. Include the SalesOrderID column in the results.
2.
Modify the query from Question 1 so that the SubTotal is rounded to the nearest dollar but still displays two zeros to the right of the decimal place.
3.
Write a query that calculates the square root of the SalesOrderID value from the Sales.SalesOrderHeader table.
4.
Write a statement that generates a random number between 1 and 10 each time it is run.
5.
Without running the queries, supply the values returned by the SELECT statements.
SELECT ROUND(55.6854, 0);
SELECT ROUND(55.6854, 1);
SELECT ROUND(55.6854, 2);
SELECT ROUND(55.6854, 3);
SELECT ROUND(55.6854, 4);
6.
True or False: The SQRT function handles only integer values?
Logical Functions and Expressions
T-SQL contains a number of functions and the CASE expression that allow you to add conditional expressions to a query. You can return a value that depends on another value or the results of an expression. These techniques are similar to using IF. . .THEN, CASE, or SWITCH in other programming languages.
The CASE Expression
Use the CASE expression to evaluate a list of expressions and return the first one that evaluates to true. For example, a report may need to display the season of the year based on one of the date columns in the table. CASE is similar to Select Case or Switch used in other programming languages, but it is used inside the query.
There are two ways to write a CASE expression: simple or searched. The following sections will explain the differences and how to use them.
Simple CASE
To write the simpleCASE expression, come up with an expression that you want to evaluate, often a column name, and a list of possible values. Here is the syntax:
CASE <test expression>
WHEN <comparison expression1> THEN <return value1>
WHEN <comparison expression2> THEN <return value2>
[ELSE <value3>] END
Type in and execute the code in Listing 4-28 to learn how to use the simple version of CASE.
SELECT Title,
CASE Title
WHEN 'Mr.' THEN 'Male'
WHEN 'Ms.' THEN 'Female'
WHEN 'Mrs.' THEN 'Female'
WHEN 'Miss' THEN 'Female'
ELSE 'Unknown' END AS Gender
FROM Person.Person
WHERE BusinessEntityID IN (1,5,6,357,358,11621,423);
Listing 4-28
Using Simple CASE
Figure 4-28 shows the results. Even though the CASE expression took up a lot of room in the query, it is producing only one column in the results. For each row returned, the expression evaluates the Title column to see whether it matches any of the possibilities listed and returns the appropriate value. If the value from Title doesn’t match or is NULL, then whatever is in the ELSE part of the expression is returned. If no ELSE exists, the expression returns NULL.
Figure 4-28
The results of using simple CASE
Searched CASE
Developers often use the searchedCASE syntax when the expression is too complicated for the simple CASE syntax. For example, you might want to compare the value from a column to several values in an IN list or use greater-than or less-than operators. The CASE expression returns the first expression that returns true. This is the syntax for the searched CASE:
CASE WHEN <test expression1> THEN <value1>
[WHEN <test expression2> THEN <value2>]
[ELSE <value3>] END
Type in and execute the code in Listing 4-29 to learn how to use this more flexible method of CASE.
SELECT Title,
CASE WHEN Title IN ('Ms.','Mrs.','Miss') THEN 'Female'
WHEN Title = 'Mr.' THEN 'Male'
ELSE 'Unknown' END AS Gender
FROM Person.Person
WHERE BusinessEntityID IN (1,5,6,357,358,11621,423);
Listing 4-29
Using Searched CASE
This query returns the same results (see Figure 4-29) as the results shown in Listing 4-28. The CASE expression evaluates each WHEN expression independently until finding the first one that returns true. It then returns the appropriate value. If none of the expressions returns true, the function returns the value from the ELSE part or NULL if no ELSE is available.
Figure 4-29
The results of using searched CASE
One very important note about using CASE is that the return values must be of compatible data types. For example, you can’t have one part of the expression returning an integer while another part returns a nonnumeric string. Precedence rules apply as with other operations. Type in and run this example to see what happens:
SELECT Title,
CASE WHEN Title IN ('Ms.','Mrs.','Miss') THEN 1
WHEN Title = 'Mr.' THEN 'Male'
ELSE '1' END AS Gender
FROM Person.Person
WHERE BusinessEntityID IN (1,5,6,357,358,11621,423);
Listing a Column as the Return Value
It is also possible to list a column name instead of hard-coded values in the THEN part of the CASE expression. This means that you can display one column for some of the rows and another column for other rows. Type in and execute the code in Listing 4-30 to see how this works.
SELECT VacationHours,SickLeaveHours,
CASE WHEN VacationHours > SickLeaveHours THEN VacationHours
ELSE SickLeaveHours END AS 'More Hours'
FROM HumanResources.Employee;
Listing 4-30
Returning a Column Name in CASE
In this example (see Figure 4-30), if there are more VacationHours than SickLeaveHours, the query displays the VacationHours column from the HumanResources.Employee table in the More Hours column. Otherwise, the query returns the SickLeaveHours.
Figure 4-30
The partial results of returning a column from CASE
IIF
The IIF function is an easier method of writing a simple CASE expression with two possible outcomes. You can use an IIF function to return a result based on whether a Boolean expression is true or false. To create an expression with the IIF function, you need a Boolean expression and the values to return based on the results. Here is the basic syntax for the IIF function:
Execute the code in Listing 4-31. The first IIF function is a simple execution, while the second IIF shows how you can introduce variables into the statement.
--1 IIF function without variables
SELECT IIF (50 > 20, 'TRUE', 'FALSE') AS RESULT;
--2 IIF function with variables
DECLARE @a INT = 50
DECLARE @b INT = 20
SELECT IIF (@a > @b, 'TRUE', 'FALSE') AS RESULT;
Listing 4-31
Using the IIF Function
Figure 4-31 shows the results. Keep in mind that all rules that apply to CASE expressions also apply to IIF functions.
Figure 4-31
Results of using the IFF function
COALESCE
You learned about COALESCE earlier in the chapter in the “Concatenating Strings and NULL” section. You can use COALESCE with other data types as well and with any number of arguments to return the first non-NULL value. You can use the COALESCE function in place of ISNULL. If a list of values must be evaluated instead of one value, you must use COALESCE instead of ISNULL. COALESCE may be used when concatenating strings or any time that a replacement for NULL must be found. Type in and execute the code in Listing 4-32 to learn more about COALESCE.
SELECT ProductID,Size, Color,
COALESCE(Size, Color,'No color or size') AS 'Description'
FROM Production.Product
WHERE ProductID in (1,2,317,320,680,706);
Listing 4-32
Using the COALESCE Function
Figure 4-32 displays the results. The COALESCE function first checks the Size value and then the Color value to find the first non-NULL value. If both values are NULL, then the string No color or size is returned.
Figure 4-32
The results of using the COALESCE function
Administrative Functions
T-SQL contains many administrative functions that are useful for developers. SQL Server also has many functions that help database administrators manage SQL Server; these functions are beyond the scope of this book. Listing 4-33 shows a few examples of functions that return information about the current connection, such as the database name and application.
SELECT DB_NAME() AS "Database Name",
HOST_NAME() AS "Host Name",
CURRENT_USER AS "Current User",
SUSER_NAME() AS "Login",
USER_NAME() AS "User Name",
APP_NAME() AS "App Name";
Listing 4-33
A Few System Functions
Take a look at Figure 4-33 for my results; your results will probably be different. When I ran the query, I was connected to the AdventureWorks database on a computer named TSQLBook as the dbo (database owner) user while using Management Studio.
Figure 4-33
The results of using administrative system functions
In addition to the functions used to manipulate strings, dates, and numbers, you will find many system functions. Some of these work on different types of data, such as CASE, while others provide information about the current connection. Administrators can manage SQL Server using dozens of system functions not covered in this book. Complete Exercise 4-6 to practice using the logical and system functions covered in the previous sections.
EXERCISE 4-6
Use the AdventureWorks2019 database to complete Questions 1 to 4. Switch to the WideWorldImporters database to complete the exercise. You can find the solutions to the questions at the end of the chapter.
1.
Write a query using the HumanResources.Employee table to display the BusinessEntityID column. Also include a CASE expression that displays Even when the BusinessEntityID value is an even number or Odd when it is odd. Hint: Use the modulo operator.
2.
Write a query using the Sales.SalesOrderDetail table to display a value (Under 10 or 10–19 or 20–29 or 30–39 or 40 and over) based on the OrderQty value by using the CASE expression. Include the SalesOrderID and OrderQty columns in the results.
3.
Using the Person.Person table, build the full names using the Title, FirstName, MiddleName, LastName, and Suffix columns. Check the table definition to see which columns allow NULL values and use the COALESCE function on the appropriate columns.
4.
Look up the SERVERPROPERTY function in SQL Server’s online documentation. Write a statement that displays the edition, instance name, and machine name using this function.
5.
Switch to WideWorldImporters. Write a query against the Purchasing.PurchaseOrders table and return the DeliveryMethodID column. Add a CASE expression that returns Freight if the DeliveryMethodID is equal to 7, 8, 9, or 10. Otherwise, return Other/Courier. Alias this as DeliveryMethod.
6.
Rewrite the query from Question 5, but this time use the IIF function.
Using Functions in the WHERE and ORDER BY Clauses
So far, you have seen functions used in the SELECT list. You may also use functions in the WHERE and ORDER BY clauses. Take a look at Listing 4-34 for several examples.
--1
SELECT FirstName
FROM Person.Person
WHERE CHARINDEX('ke',FirstName) > 0;
--2
SELECT LastName,REVERSE(LastName)
FROM Person.Person
ORDER BY REVERSE(LastName);
--3
SELECT BirthDate
FROM HumanResources.Employee
ORDER BY YEAR(BirthDate);
Listing 4-34
Using Functions in WHERE and ORDER BY
Figure 4-34 shows the results of Listing 4-34. Even though it is very easy to use a function on a column in the WHERE clause, it is important to note that performance may suffer. If the database designer created an index on the searched column, the database engine must evaluate each row one at a time when a function is applied to a column. It may search the column in the index, one value at a time, which could still be better than searching every row of the table.
Figure 4-34
The results of using functions in the WHERE and ORDER BY clauses
Practice using functions in the WHERE and ORDER by clauses by completing Exercise 4-7.
EXERCISE 4-7
Use the AdventureWorks2019 database to complete Questions 1 to 3. Switch to the WideWorldImporters database to complete the exercise. You will find the solutions to the questions at the end of the chapter.
1.
Write a query using the Sales.SalesOrderHeader table to display the orders placed during 2011 by using a function. Include the SalesOrderID and OrderDate columns in the results.
2.
Write a query using the Sales.SalesOrderHeader table listing the sales in order of the month the order was placed and then the year the order was placed. Include the SalesOrderID and OrderDate columns in the results.
3.
Write a query that displays the PersonType and the name columns from the Person.Person table. Sort the results so that rows with a PersonType of IN, SP, or SC sort by LastName. The other rows should sort by FirstName. Hint: Use the CASE expression.
4.
Write a query that returns sales for Saturday in the Sales.Orders table. Return CustomerID, OrderDate, and the result of the DATENAME function in the SELECT list. Alias the new column OrderDay.
5.
Write a SELECT statement, querying the Application.Cities table. Return the CityName and LatestRecordedPopulation and use a CASE statement to ORDER BY the LatestRecordedPopulation and sort the NULL values on the bottom, not the default on top. Try without using DESC.
6.
Write a SELECT statement querying the Application.PaymentMethods table. Using a CASE expression, order the results by the payment method in the following order:
a.
Check
b.
Credit Card
c.
EFT
d.
Cash
The TOP Keyword
Use the TOP keyword to limit the number or percentage of rows returned from a query. Here is the syntax:
The ORDER BY clause is optional, but most of the time, you will use it to determine which rows the query returns when using TOP. Sometimes you will see the same rows returned over and over even when not using an ORDER BY. This doesn’t mean that there is any guarantee about the rows returned. It rarely makes sense to request the TOP N rows without ordering. Usually, one sorts by some criteria in order to get the TOP N rows in that sequence.
The WITH TIES option means that if there are rows that have identical values in the ORDER BY clause, the results will include all the rows even though you now end up with more rows than you expect. Type in and execute the code in Listing 4-35 to learn how to use TOP.
SELECT TOP(2) WITH TIES CustomerID, OrderDate, SalesOrderID
FROM Sales.SalesOrderHeader
ORDER BY OrderDate;
--4
SELECT TOP(2) CustomerID, OrderDate, SalesOrderID
FROM Sales.SalesOrderHeader
ORDER BY NEWID();
Listing 4-35
Limiting Results with TOP
Figure 4-35 shows the results. Query 1 shows that you can use a variable along with TOP. This has been possible since version 2005 of SQL Server. It also demonstrates the use of the PERCENT option. Query 2 is a typical example returning just two rows. Query 3 demonstrates the WITH TIES option. Otherwise, it is identical to Query 2. It returns many more rows because there are many orders placed on the same date. Query 4 demonstrates a trick to get random rows. If you sort by the NEWID function, you will get different rows each time you run the query.
Figure 4-35
The partial results of using TOP
Note
TOP is often used to return pages of data. Microsoft recommends using the OFFSET and FETCH clauses, introduced with SQL Server 2008, instead of TOP as a paging solution and to limit the amount of data sent to a client. OFFSET and FETCH also allow more options, including the use of variables. OFFSET and FETCH are covered in Chapter 16.
Thinking About Performance
In Chapter 3, you learned how to use execution plans in ADS to see which index SQL Server uses and if the index operation is a seek or a scan. Scanning an index takes more resources than seeking. Review the “Thinking About Performance” section in Chapter 3 if you need to take another look at how to use execution plans or to brush up on how SQL Server uses indexes.
Functions can be used in the WHERE clause to filter out unneeded rows. Although I am not saying that you should never include a function in the WHERE clause, you need to realize that including a function that operates on a column may cause a decrease in performance.
The Sales.SalesOrderHeader table does not contain an index on the OrderDate column. Run the following code to create an index on the column. Don’t worry about trying to understand the code at this point. You can also just copy it from the code file from this chapter.
--Add an index
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id =
OBJECT_ID(N'[Sales].[SalesOrderHeader]')
AND name = N'DEMO_SalesOrderHeader_OrderDate')
DROP INDEX [DEMO_SalesOrderHeader_OrderDate]
ON [Sales].[SalesOrderHeader] ;
GO
CREATE NONCLUSTERED INDEX [DEMO_SalesOrderHeader_OrderDate]
ON [Sales].[SalesOrderHeader]
([OrderDate] ASC);
After running the code in Listing 4-36, click the Explain icon to view the execution plan.
--1
SELECT SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader
WHERE OrderDate >= '2011-01-01 00:00:00'
AND OrderDate <= '2012-01-01 00:00:00';
--2
SELECT SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2011;
Listing 4-36
Compare the Performance When Using a Function in the WHERE Clause
Query 1 finds all the orders placed in 2011 without using a function. Query 2 uses the YEAR function to return the same results. When looking at performance differences of queries against the AdventureWorks2019 database, keep in mind that it is a very small database and the queries may seem quick. In real life, tables can contain millions of rows where you will experience performance differences more realistically.
Take a look at the execution plans in Figure 4-36. Query 1 performs an index seek on the nonclustered index that you just created. Query 2 performs an index scan because the YEAR function must be applied to each row.
Figure 4-36
The execution plans showing that using a function in the WHERE clause can affect performance
I’ve told you that seeking is faster than scanning, but you can also figure out the difference between the two queries by hovering over the SELECT operator in each and looking at the Estimated Subtree Cost shown in Figure 4-37. Think of this value as “query bucks” or the estimated amount of resources needed to run the query.
Figure 4-37
The estimated cost of Query 1
The estimated cost of Query 1 is 0.0073654. The estimated cost of Query 2 is 0.0928919. That means that Query 2 took about 93% of the effort. As shown in the figure, the cost is just estimated. It is sometimes not an accurate comparison between two queries. See SQL Server Execution Plans, Third Edition (Redgate, 2018) by Grant Fritchey to learn more.
Remove the index you created for this demonstration by running this code:
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id =
OBJECT_ID(N'[Sales].[SalesOrderHeader]')
AND name = N'DEMO_SalesOrderHeader_OrderDate')
DROP INDEX [DEMO_SalesOrderHeader_OrderDate]
ON [Sales].[SalesOrderHeader];
Run Listing 4-36 again now that the index is gone. Figure 4-38 shows that with no index on the OrderDate column, the database engine must perform a scan of the table (in this case, the clustered index) to find the correct rows in both of the queries. Notice that the execution plan suggests an index to help the performance of Query 1. It doesn’t suggest an index for Query 2 because an index won’t help.
Figure 4-38
The execution plans after removing the index
You can see from these examples that writing queries is more than just getting the correct results; performance is important, too.
Summary
Using expressions in T-SQL with the built-in functions and operators can be very convenient. There is a rich collection of functions for string and date manipulation as well as mathematical and system functions and more. It’s possible to use expressions and functions in the SELECT, WHERE, and ORDER BY clauses. You must use caution when using functions in the WHERE clause; it is possible to decrease performance.
You’ll learn even more about other specialized functions that operate over groups of rows later in the book.
Answers to the Exercises
This section provides solutions to the exercises found on writing queries with expressions.
Solutions to Exercise 4-1: Expressions Using Operators
Use the AdventureWorks2019 database to complete Questions 1 to 4. Complete the rest of the questions using the WideWorldImporters database.
1.
Write a query that returns data from the Person.Address table in this format AddressLine1 (City PostalCode) from the Person.Address table.
Write a query using the Production.Product table displaying the product ID, color, and name columns. If the color column contains a NULL, replace the color with No Color.
SELECT ProductID, ISNULL(Color, 'No Color') AS Color, Name
FROM Production.Product;
3.
Modify the query written in Question 2 so that the description of the product is returned formatted as Name: Color. Make sure that all rows display a value even if the Color value is missing.
SELECT ProductID, Name + ISNULL(': ' + Color,'') AS Description
FROM Production.Product;
You can also use COALESCE in place of ISNULL in both Questions 2 and 3.
4.
Write a query using the Production.Product table displaying a description with the ProductID: Name format. Hint: You will need to use a function to write this query.
Here are two possible answers:
SELECT CAST(ProductID AS VARCHAR) + ': ' + Name AS IDName
FROM Production.Product;
SELECT CONVERT(VARCHAR, ProductID) + ': ' + Name AS IDName
FROM Production.Product;
5.
Switch to WideWorldImporters. Write a query using the Application.Cities table. Using the CONCAT() function, put together CityName and LatestRecordedPopulation separating the values with a hyphen.
SELECT CONCAT(CityName, '-', LatestRecordedPopulation) AS 'City-Pop'
FROM Application.Cities;
Notice how CONCAT handles the conversion of the integer to varchar for you.
6.
Write a query against the Application.People table using only FullName and SearchName. Format the output like this:
FullName (SearchName)
Here is an example output:
Bijoya Thakur (Bijoya Bijoya Thakur)
SELECT CONCAT(FullName, ' (', SearchName, ')')
FROM Application.People;
7.
Write a query to the Application.Cities table, returning only CityName, LatestRecordedPopulation. Use COALESCE or ISNULL to return 0 for those cities not reporting a LatestRecordedPopulation.
SELECT [CityName]
,[LatestRecordedPopulation]
,ISNULL(LatestRecordedPopulation, 0)
FROM [Application].[Cities];
8.
In the preceding query, what happens if you return N/A instead of 0?
This error is returned:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.
9.
Explain the difference between the ISNULL and COALESCE functions.
You can use ISNULL to replace a NULL or column with another value or column. You can use COALESCE to return the first non-NULL from a list of values or columns.
Solutions to Exercise 4-2: Using Mathematical Operators
Use the AdventureWorks2019 database for Questions 1 to 3. Switch to the WideWorldImporters database to complete Question 4.
1.
Write a query using the Sales.SpecialOffer table. Display the difference between the MinQty and MaxQty columns along with the SpecialOfferID and Description columns.
SELECT SpecialOfferID, Description,
MaxQty - MinQty AS Diff
FROM Sales.SpecialOffer;
2.
Write a query using the Sales.SpecialOffer table. Multiply the MinQty column by the DiscountPct column. Include the SpecialOfferID and Description columns in the results.
SELECT SpecialOfferID, Description, MinQty * DiscountPct AS Discount
FROM Sales.SpecialOffer;
3.
Write a query using the Sales.SpecialOffer table that multiplies the MaxQty column by the DiscountPct column. If the MaxQty value is NULL, replace it with the value 10. Include the SpecialOfferID and Description columns in the results.
SELECT SpecialOfferID, Description,
ISNULL(MaxQty,10) * DiscountPct AS Discount
FROM Sales.SpecialOffer;
4.
Switch to the WideWorldImporters database. Write a query against the Sales.OrderLines table. Include the StockItemID. Calculate the extended price (Quantity times UnitPrice). Calculate the tax (extended price times 0.15). Calculate the extended amount (extended price plus tax).
Describe the difference between division and modulo.
When performing division, you divide two numbers, and the result, the quotient, is the answer. If you are using modulo, you divide two numbers, but the remainder is the answer. If the numbers are evenly divisible, the answer will be zero.
Solutions to Exercise 4-3: Using Functions
Use the AdventureWorks2019 database to complete Questions 1 to 4. Switch to the WideWorldImporters database to complete the exercise. If you need help, refer to the discussion of the functions to help you figure out which ones to use.
1.
Write a query that displays the first ten characters of the AddressLine1 column in the Person.Address table.
Here are two possible solutions:
SELECT LEFT(AddressLine1,10) AS Address10
FROM Person.Address;
SELECT SUBSTRING(AddressLine1,1,10) AS Address10
FROM Person.Address;
2.
Write a query that displays characters 10 to 15 of the AddressLine1 column in the Person.Address table.
SELECT SUBSTRING(AddressLine1,10,6) AS Address10to15
FROM Person.Address;
3.
Write a query displaying the first and last names from the Person.Person table all in uppercase.
SELECT UPPER(FirstName) AS FirstName,
UPPER(LastName) AS LastName
FROM Person.Person;
4.
The ProductNumber in the Production.Product table contains a hyphen (-). Write a query that uses the SUBSTRING function and the CHARINDEX function to display the characters in the product number following the hyphen. Note: There is also a second hyphen in many of the rows; ignore the second hyphen for this question. Hint: Try writing this statement in two steps, the first using the CHARINDEX function and the second adding the SUBSTRING function.
SUBSTRING(ProductNumber,CHARINDEX('-',ProductNumber)+1,25) AS ProdNumber
FROM Production.Product;
5.
Switch to the WideWorldImporters database. Write a SELECT statement to the Application.Countries table, creating a new code, which is the first three characters of the CountryName capitalized. Alias the column NewCode, returning only the column created and the IsoAlpha3Code column. Hint: You will use both the UPPER() function and the LEFT() function.
SELECT UPPER(LEFT(CountryName,3)) AS NewCode, IsoAlpha3Code
FROM Application.Countries;
6.
In the CustomerName (located in the Sales.Customers table), return only the portion inside of parentheses, including the parentheses. Hint: See the “Nesting Functions” section. You may need to use a number of built-in functions, such as SUBSTRING(), CHARINDEX(), and LEN().
Solutions to Exercise 4-4: Using Date and Time Functions
Use the AdventureWorks2019 database to complete this exercise.
1.
Write a query that calculates the number of days between the date an order was placed and the date that it was shipped using the Sales.SalesOrderHeader table. Include the SalesOrderID, OrderDate, and ShipDate columns.
SELECT SalesOrderID, OrderDate, ShipDate,
DATEDIFF(day,OrderDate,ShipDate) AS NumberOfDays FROM Sales.SalesOrderHeader;
2.
Write a query that displays only the date, not the time, for the order date and ship date in the Sales.SalesOrderHeader table.
SELECT CONVERT(VARCHAR(12),OrderDate,111) AS OrderDate,
CONVERT(VARCHAR(12), ShipDate,111) AS ShipDate
FROM Sales.SalesOrderHeader;
--another solution
SELECT CAST(OrderDate AS DATE) AS OrderDate,
CAST(ShipDate AS DATE) AS ShipDate
FROM Sales.SalesOrderHeader;
3.
Write a query that adds six months to each order date in the Sales.SalesOrderHeader table. Include the SalesOrderID and OrderDate columns.
SELECT SalesOrderID, OrderDate,
DATEADD(month,6,OrderDate) AS Plus6Months
FROM Sales.SalesOrderHeader;
4.
Write a query that displays the year of each order date and the numeric month of each order date in separate columns in the results. Include the SalesOrderID and OrderDate columns.
Here are two possible solutions:
SELECT SalesOrderID, OrderDate, YEAR(OrderDate) AS OrderYear,
MONTH(OrderDate) AS OrderMonth
FROM Sales.SalesOrderHeader;
SELECT SalesOrderID, OrderDate, DATEPART(yyyy,OrderDate) AS OrderYear,
DATEPART(month,OrderDate) AS OrderMonth
FROM Sales.SalesOrderHeader;
5.
Change the query written in Question 4 to display the month name instead.
SELECT SalesOrderID, OrderDate,
DATEPART(yyyy,OrderDate) AS OrderYear,
DATENAME(month,OrderDate) AS OrderMonth
FROM Sales.SalesOrderHeader;
6.
Write a SELECT statement that returns the date five quarters in the past from today’s date.
SELECT DATEADD(QQ, -5, GETDATE());
Solutions to Exercise 4-5: Using Mathematical Functions
Use the AdventureWorks2019 database to complete this exercise.
1.
Write a query using the Sales.SalesOrderHeader table that displays the SubTotal rounded to two decimal places. Include the SalesOrderID column in the results.
SELECT SalesOrderID, ROUND(SubTotal,2) AS SubTotal
FROM Sales.SalesOrderHeader;
2.
Modify the query from Question 1 so that the SubTotal is rounded to the nearest dollar but still displays two zeros to the right of the decimal place.
SELECT SalesOrderID, ROUND(SubTotal,0) AS SubTotal
FROM Sales.SalesOrderHeader;
3.
Write a query that calculates the square root of the SalesOrderID value from the Sales.SalesOrderHeader table.
SELECT SQRT(SalesOrderID) AS OrderSQRT
FROM Sales.SalesOrderHeader;
4.
Write a statement that generates a random number between 1 and 10 each time it is run.
SELECT CAST(RAND() * 10 AS INT) + 1;
5.
Without running the queries, supply the missing values returned by the SELECT statements.
SELECT ROUND(55.6854, 0); -- 56.0000
SELECT ROUND(55.6854, 1); -- 55.7000
SELECT ROUND(55.6854, 2); -- 55.6900
SELECT ROUND(55.6854, 3); -- 55.6850
SELECT ROUND(55.6854, 4); -- 55.6854
6.
True or False: The SQRT function handles only integer values?
SELECT SQRT(9.3)
The SQRT function works well for both integers and decimals. Use the SQUARE function and square the value—it doesn't exactly equal 9.3…why?
Solutions to Exercise 4-6: Using Logical and System Functions
Use the AdventureWorks2019 database to complete Questions 1 to 4. Switch to the WideWorldImporters database to complete the exercise.
1.
Write a query using the HumanResources.Employee table to display the BusinessEntityID column. Also include a CASE expression that displays Even when the BusinessEntityID value is an even number or Odd when it is odd. Hint: Use the modulo operator.
SELECT BusinessEntityID,
CASE BusinessEntityID % 2
WHEN 0 THEN 'Even' ELSE 'Odd' END
FROM HumanResources.Employee;
2.
Write a query using the Sales.SalesOrderDetail table to display a value (Under 10 or 10–19 or 20–29 or 30–39 or 40 and over) based on the OrderQty value by using the CASE expression. Include the SalesOrderID and OrderQty columns in the results.
SELECT SalesOrderID, OrderQty,
CASE WHEN OrderQty BETWEEN 0 AND 9
THEN 'Under 10'
WHEN OrderQty BETWEEN 10 AND 19
THEN '10-19'
WHEN OrderQty BETWEEN 20 AND 29
THEN '20-29'
WHEN OrderQty BETWEEN 30 AND 39
THEN '30-39'
ELSE '40 and over' end AS range
FROM Sales.SalesOrderDetail;
3.
Using the Person.Person table, build the full names using the Title, FirstName, MiddleName, LastName, and Suffix columns. Check the table definition to see which columns allow NULL values and use the COALESCE function on the appropriate columns.
SELECT COALESCE(Title + ' ', '') + FirstName +
COALESCE(' ' + MiddleName,'') + ' ' + LastName +
COALESCE(', ' + Suffix,'')
FROM Person.Person;
4.
Look up the SERVERPROPERTY function in the online documentation. Write a statement that displays the edition, instance name, and machine name using this function.
SELECT SERVERPROPERTY('Edition'),
SERVERPROPERTY('InstanceName'),
SERVERPROPERTY('MachineName');
5.
Switch to the WideWorldImporters database. Write a query against the Purchasing.PurchaseOrders table and return the DeliveryMethodID column. Add a CASE expression that returns Freight if the DeliveryMethodID is equal to 7, 8, 9, or 10. Otherwise, return Other/Courier. Alias this as DeliveryMethod.
SELECT DeliveryMethodID
,CASE WHEN DeliveryMethodID IN (7,8,9,10) THEN 'Freight'
ELSE 'Courier/Other'
END AS DeliveryMethod
FROM Purchasing.PurchaseOrders;
6.
Rewrite the query from Question 5, but this time use the IIF function.
SELECT DeliveryMethodID
, IIF(DeliveryMethodID IN (7,8,9,10), 'Freight', 'Courier/Other') as DeliveryMethod
FROM Purchasing.PurchaseOrders;
Solutions to Exercise 4-7: Using Functions in the WHERE and ORDER BY Clauses
Use the AdventureWorks2019 database to complete Questions 1 to 3. Switch to the WideWorldImporters database to complete the exercise.
1.
Write a query using the Sales.SalesOrderHeader table to display the orders placed during 2011 by using a function. Include the SalesOrderID and OrderDate columns in the results.
--one possible solution.
SELECT SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2011;
2.
Write a query using the Sales.SalesOrderHeader table listing the sales in order of the month the order was placed and then the year the order was placed. Include the SalesOrderID and OrderDate columns in the results.
SELECT SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader
ORDER BY MONTH(OrderDate), YEAR(OrderDate);
3.
Write a query that displays the PersonType and the name columns from the Person.Person table. Sort the results so that rows with a PersonType of IN, SP, or SC sort by LastName. The other rows should sort by FirstName. Hint: Use the CASE expression.
Switch to WideWorldImporters. Write a query that returns sales for Saturdays in the Sales.Orders table. Return CustomerID, OrderDate, and the result of the DATENAME function in the SELECT list. Alias the new column OrderDay.
SELECT CustomerID, OrderDate, DATENAME(WEEKDAY, OrderDate) AS OrderDay
FROM sales.Orders
WHERE DATENAME(WEEKDAY, OrderDate) = 'saturday';
5.
Write a SELECT statement, querying the Application.Cities table. Return the CityName and LatestRecordedPopulation and use a CASE statement to ORDER BY the LatestRecordedPopulation. Sort the NULL values on the bottom, not the default on top. Try without using DESC.
SELECT CityName
,LatestRecordedPopulation
FROM [WideWorldImporters].[Application].[Cities]
ORDER BY CASE WHEN LatestRecordedPopulation IS NULL THEN 1 ELSE 0 END,
LatestRecordedPopulation;
6.
Write a SELECT statement querying the Application.PaymentMethods table. Using a CASE expression, order the results by the payment method in the following order:
Check
Credit Card
EFT
Cash
SELECT [PaymentMethodID]
,[PaymentMethodName]
,[LastEditedBy]
FROM [WideWorldImporters].[Application].[PaymentMethods]
ORDER BY CASE WHEN PaymentMethodName = 'CHECK' THEN 0
