CHAPTER 4: Using Built-in Functions and Expressions

Question 1

Q: What operator is used to concatenate strings in T-SQL?

Answer 1

A: The + operator is used to concatenate strings.

Question 2

Q: How can you concatenate columns in a query with a space in between?

Answer 2

SELECT FirstName + ‘ ‘ + LastName AS [Full Name] FROM Person.Person;

Question 3

Q: What happens if you concatenate a string with a NULL using the + operator?

Answer 3

A: The result is NULL.

Question 4

Q: Why is it important to alias concatenated expressions?

Answer 4

A: Without an alias, the resulting column header will be “(No column name),” making the results harder to interpret.

Question 5

Q: What is the purpose of the CONCAT function in T-SQL?

Answer 5

A: The CONCAT function concatenates multiple values into a single string and automatically handles NULLs by ignoring them.

Question 6

Q: How does the CONCAT function handle NULL values?

Answer 6

A: NULL values are ignored, and the function concatenates the remaining values.

Question 7

Q: What is an example of using CONCAT with literal strings?

Answer 7

SELECT CONCAT(‘I ‘, ‘love’, ‘ T-SQL’) AS RESULT;

Question 8

Q: Can the CONCAT function handle nonstring data types?

Answer 8

A: Yes, nonstring values are implicitly converted to strings before concatenation.

Question 9

Q: What is an example of using CONCAT with variables?

Answer 9

DECLARE @a VARCHAR(30) = ‘My birthday is on ‘;
DECLARE @b DATE = ‘1980-08-25’;
SELECT CONCAT(@a, @b) AS RESULT;

Question 10

Q: How can CONCAT be used with table data?

Answer 10

SELECT CONCAT(AddressLine1, PostalCode) AS Address FROM Person.Address;

Question 11

Q: How does this query handle NULLs?

SELECT CONCAT(‘This’, NULL, ‘ works’) AS RESULT;

Answer 11

A: The result is “This works” because NULL values are ignored.

Question 12

Q: Why might you prefer CONCAT over the + operator for string concatenation?

Answer 12

A: CONCAT handles NULL values gracefully by ignoring them, whereas + returns NULL if any value is NULL.

Question 13

Q: What is the purpose of the ISNULL function in T-SQL?

Answer 13

A: The ISNULL function replaces NULL values with a specified replacement value.

Question 14

Q: How does the COALESCE function differ from ISNULL?

Answer 14

A: COALESCE accepts multiple arguments and returns the first non-NULL value, while ISNULL accepts only two arguments.

Question 14

Q: Provide an example of using ISNULL to handle NULLs in a concatenation.

Answer 14

SELECT FirstName + ISNULL(‘ ‘ + MiddleName, ‘’) + ‘ ‘ + LastName AS [Full Name]
FROM Person.Person;

Question 15

Q: Why might developers prefer COALESCE over ISNULL?

Answer 15

A: COALESCE is ANSI-compliant, more versatile, and can handle multiple arguments, whereas ISNULL is a proprietary SQL Server feature.

Question 16

Q: How does COALESCE work with NULL values in concatenation?

Answer 16

A: It skips NULL values and uses the first non-NULL value, e.g.:

SELECT FirstName + COALESCE(‘ ‘ + MiddleName, ‘’) + ‘ ‘ + LastName AS [Full Name]
FROM Person.Person;

Question 17

Q: What issue can occur when using ISNULL incorrectly in concatenation?

Answer 17

A: Extra spaces may appear if the space is not included within the ISNULL function.

Question 18

Q: What must you do to concatenate nonstring values to strings in T-SQL?

Answer 18

A: Convert the nonstring values to strings using CAST, CONVERT, or CONCAT.

Question 19

Q: What happens if you attempt to concatenate a string and a number without converting the number?

Answer 19

A: An error will occur because integers have higher precedence than strings.

Question 20

Q: What is the syntax for using the CAST function to convert a value to a string?

Answer 20

CAST(<value> AS <new>)</new></value>

Question 21

Q: What is the syntax for using the CONVERT function to convert a value to a string?

Answer 21

CONVERT(<new>, <value>)</value></new>

Question 22

Q: Provide an example of using CAST to convert and concatenate a value.

Answer 22

SELECT CAST(BusinessEntityID AS NVARCHAR) + ‘: ‘ + LastName + ‘, ‘ + FirstName AS ID_Name
FROM Person.Person;

Question 23

Q: Provide an example of using CONVERT to convert and concatenate a value.

Answer 23

SELECT CONVERT(NVARCHAR(10), BusinessEntityID) + ‘: ‘ + LastName + ‘, ‘ + FirstName AS ID_Name
FROM Person.Person;

Question 24

Q: What is the difference between appending and adding a numeric value in concatenation?

Answer 24

A: Appending requires converting the number to a string, while adding combines the numeric values: CAST(BusinessEntityID AS NVARCHAR(10)) + '1'; -- Appends "1" BusinessEntityID + 1; -- Adds 1

Question 25

Q: Why should you always specify the length when using CAST or CONVERT?

Answer 25

A: To ensure the correct size for the output string, as the default may not meet requirements (e.g., 30 for NVARCHAR or 1 for variables).

Question 26

Q: When should you use CONCAT instead of CAST or CONVERT?

Answer 26

A: Use CONCAT when concatenating multiple values because it automatically handles NULLs and converts data types to strings.

Question 27

Q: What operator is used for addition in T-SQL?

Answer 27

A: The + operator.

Question 28

Q: What operator is used for subtraction in T-SQL?

Answer 28

A: The - (hyphen) operator.

Question 29

Q: What operator is used for multiplication in T-SQL?

Answer 29

A: The * operator.

Question 30

Q: What operator is used for division in T-SQL?

Answer 30

A: The / operator.

Question 31

Q: What is the purpose of the modulo (%) operator in T-SQL?

Answer 31

A: It returns the remainder of a division operation. For example, 5 % 2 returns 1.

Question 32

Q: How can the modulo operator determine if a number is odd or even?

Answer 32

Use % 2: If the result is 1, the number is odd. If the result is 0, the number is even.

Question 33

Q: What is the result of 10 / 3 in T-SQL if both operands are integers?

Answer 33

A: The result is 3 because integer division truncates the decimal part.

Question 34

Q: How can you perform division in T-SQL to return a decimal value?

Answer 34

A: Use at least one operand as a decimal value, e.g., 10.0 / 3 returns 3.333.

Question 34

Q: Write a query to multiply a column by 10.

Answer 34

SELECT OrderQty, OrderQty * 10 AS Times10 FROM Sales.SalesOrderDetail;

Question 35

Q: How do you calculate a discounted total in T-SQL?

Answer 35

Use a formula like: SELECT OrderQty * UnitPrice * (1.0 - UnitPriceDiscount) AS Calculated FROM Sales.SalesOrderDetail;

Question 36

Q: What is the purpose of parentheses in mathematical expressions in T-SQL?

Answer 36

A: Parentheses enforce the intended order of operations, overriding default operator precedence.

Question 37

Q: Provide an example of using ISNULL with mathematical operators.

Answer 37

SELECT DiscountPct * ISNULL(MaxQty, 1000) AS MaxDiscount FROM Sales.SpecialOffer;

Question 38

Q: What is the default precedence for multiplication, division, and subtraction in T-SQL?

Answer 38

A: Multiplication (*) and division (/) have higher precedence than subtraction (-).

Question 39

Q: How do parentheses affect the order of operations in T-SQL?

Answer 39

A: They change the order to ensure specific parts of the expression are calculated first.

Question 40

Q: Write a query to calculate the maximum discount with a fallback for NULL values.

Answer 40

SELECT SpecialOfferID, MaxQty, DiscountPct, DiscountPct * ISNULL(MaxQty, 1000) AS MaxDiscount FROM Sales.SpecialOffer;

Question 41

Q: What happens when performing an operation on two different data types in T-SQL?

Answer 41

A: The result will be of the data type with the highest precedence, if conversion is possible.

Question 43

Q: Why does the expression 1 + 'a' fail in T-SQL?

Answer 43

A: A character ('a') cannot be converted to a numeric value, leading to a data type mismatch error.

Question 44

Q: What does the RTRIM function do in T-SQL?

Answer 44

A: Removes spaces from the right side of a string.

Question 45

Q: What does the LTRIM function do in T-SQL?

Answer 45

A: Removes spaces from the left side of a string.

Question 46

Q: What does the TRIM function do in T-SQL?

Answer 46

A: Removes spaces from both sides of a string.

Question 47

Q: What is the syntax for using the RTRIM, LTRIM, and TRIM functions?

Answer 47

RTRIM() LTRIM() TRIM()

Question 47

Q: Provide an example of using RTRIM to remove trailing spaces.

Answer 47

SELECT RTRIM('Hello ') AS TrimmedString;

Question 47

Q: What does the LEFT function do in T-SQL?

Answer 47

A: Returns a specified number of characters from the left side of a string.

Question 47

Q: What does the RIGHT function do in T-SQL?

Answer 47

A: Returns a specified number of characters from the right side of a string.

Question 47

Q: What is the syntax for the LEFT and RIGHT functions?

Answer 47

LEFT(, ) RIGHT(, )

Question 48

Q: Provide an example of using LEFT to extract the first 5 characters of a string.

Answer 48

SELECT LEFT('AdventureWorks', 5) AS FirstFive;

Question 49

Q: Provide an example of using RIGHT to extract the last 4 characters of a string.

Answer 49

SELECT RIGHT('AdventureWorks', 4) AS LastFour;

Question 49

Q: What happens if the number of characters specified in LEFT or RIGHT exceeds the length of the string?

Answer 49

A: The function returns as many characters as possible without causing an error.

Question 49

Q: How can TRIM be used to clean up string data in a query?

Answer 49

SELECT TRIM(' Hello World ') AS CleanedString;

Question 50

Q: Write a query using LEFT and RIGHT functions to parse a name.

Answer 50

SELECT LastName, LEFT(LastName, 3) AS FirstThree, RIGHT(LastName, 3) AS LastThree FROM Person.Person;

Question 51

Q: What does the LEN function return in T-SQL?

Answer 51

A: The number of characters in a string.

Question 52

Q: What does the DATALENGTH function return in T-SQL?

Answer 52

A: The number of bytes used by a data type or expression.

Question 53

Q: How does DATALENGTH differ from LEN when working with NCHAR or NVARCHAR data types?

Answer 53

A: For NCHAR or NVARCHAR, DATALENGTH returns a value up to two times the LEN value because these data types use up to two bytes per character.

Question 54

Q: What is the syntax for the LEN and DATALENGTH functions?

Answer 54

LEN() DATALENGTH()

Question 55

Q: Provide an example of using LEN and DATALENGTH in a query.

Answer 55

SELECT LastName, LEN(LastName) AS "Length", DATALENGTH(LastName) AS "Internal Data Length" FROM Person.Person;

Question 56

Q: What does the CHARINDEX function do in T-SQL?

Answer 56

A: It finds the numeric starting position of a search string within another string.

Question 57

Q: What is the syntax for the CHARINDEX function?

Answer 57

CHARINDEX(, [, ])

Question 58

Q: What does the optional third parameter in CHARINDEX do?

Answer 58

A: It specifies the starting position to begin the search, ignoring the initial characters up to that position.

Question 59

Q: How can you use CHARINDEX to find the first occurrence of the letter e in a string?

Answer 59

CHARINDEX('e', LastName)

Question 60

Q: Provide an example of using CHARINDEX to skip the first three characters in a search.

Answer 60

CHARINDEX('e', LastName, 4)

Question 61

Q: What happens when searching for a string in a case-sensitive database using CHARINDEX?

Answer 61

A: The function differentiates between cases; for example, searching for be and Be might return different results.

Question 62

Q: Can CHARINDEX search for substrings longer than one character?

Answer 62

A: Yes, you can search for substrings like 'be' or 'test'.

Question 63

Q: Write a query to find the first occurrence of the substring be in a column.

Answer 63

SELECT CHARINDEX('be', LastName) AS "Find be" FROM Person.Person;

Question 64

Q: Write a query to find the position of e after skipping the first three characters.

Answer 64

SELECT CHARINDEX('e', LastName, 4) AS "Skip 3 Characters" FROM Person.Person;

Question 65

Q: What does the SUBSTRING function do in T-SQL?

Answer 65

A: It returns a portion of a string starting at a specified position and for a given length.

Question 66

Q: What is the syntax for the SUBSTRING function?

Answer 66

SUBSTRING(, , )

Question 67

Q: What happens if the start location in SUBSTRING is beyond the end of the string?

Answer 67

A: An empty string is returned.

Question 68

Q: Provide an example of using SUBSTRING to extract the first 4 characters of a string.

Answer 68

SELECT SUBSTRING(LastName, 1, 4) AS "First 4 Characters" FROM Person.Person;

Question 69

Q: What does the CHOOSE function do in T-SQL?

Answer 69

A: It selects a value from a list based on an index.

Question 70

Q: What is the syntax for the CHOOSE function?

Answer 70

CHOOSE(, , , ..., )

Question 71

Q: Provide an example of using CHOOSE to select a value from an array.

Answer 71

SELECT CHOOSE(3, 'a', 'b', 'c', 'd') AS Result;

Question 72

Q: What does the REVERSE function do in T-SQL?

Answer 72

A: It returns a string with its characters in reverse order.

Question 73

Q: Provide an example of using REVERSE to reverse a string.

Answer 73

SELECT REVERSE('Hello, World!') AS ReversedString;

Question 74

Q: What do the UPPER and LOWER functions do in T-SQL?

Answer 74

A: UPPER converts a string to uppercase, and LOWER converts a string to lowercase.

Question 75

Q: Provide an example of using UPPER and LOWER on a column.

Answer 75

SELECT UPPER(LastName) AS "UPPER", LOWER(LastName) AS "LOWER" FROM Person.Person;

Question 76

Q: Are searches in T-SQL case-sensitive by default?

Answer 76

A: No, searches are case-insensitive by default unless the column’s collation specifies case sensitivity.

Question 77

Q: What does the REPLACE function do in T-SQL?

Answer 77

A: It replaces all occurrences of a specified string within another string.

Question 78

Q: What is the syntax for the REPLACE function?

Answer 78

REPLACE(, , )

Question 79

Q: Provide an example of using REPLACE to substitute one string for another.

Answer 79

SELECT REPLACE('AdventureWorks', 'Works', 'Shop') AS ReplacedString;

Question 80

Q: How can you use REPLACE to remove a string?

Answer 80

A: Replace it with an empty string (''), e.g.: SELECT REPLACE(LastName, 'a', '') AS "Remove a";

Question 81

Q: Can REPLACE use column values as the replacement string?

Answer 81

SELECT REPLACE(LastName, 'a', MiddleName) AS "Replace with MiddleName";

Question 82

Q: What does the STRING_SPLIT function do in T-SQL?

Answer 82

A: It splits a string into multiple rows based on a specified delimiter and returns the results as a table.

Question 83

Q: What is the syntax for using STRING_SPLIT?

Answer 83

STRING_SPLIT(, )

Question 84

Q: Provide an example of splitting a comma-separated string into rows.

Answer 84

SELECT value FROM STRING_SPLIT('1,2,3,4,5', ',');

Question 85

Q: Can STRING_SPLIT handle delimiters other than commas?

Answer 85

A: Yes, any character can be used as the delimiter, e.g., SELECT value FROM STRING_SPLIT('dog cat bird', ' ');

Question 85

Q: What is the syntax for using STRING_AGG?

Answer 85

STRING_AGG(, )

Question 86

Q: What does the STRING_AGG function do in T-SQL?

Answer 86

A: It concatenates values from multiple rows into a single delimited string.

Question 89

Q: Provide an example of concatenating values into a comma-separated string using STRING_AGG.

Answer 89

SELECT STRING_AGG(Name, ', ') AS List FROM Production.ProductCategory;

Question 89

Q: What does it mean to nest functions in T-SQL?

Answer 89

A: Using the result of one function as a parameter for another function.

Question 89

Q: What is an example of nesting the RTRIM and LTRIM functions?

Answer 89

SELECT LTRIM(RTRIM(' test ')) AS TrimmedString;

Question 89

Q: Provide an example of extracting the domain from an email address using nested functions.

Answer 89

SELECT EmailAddress, SUBSTRING(EmailAddress, CHARINDEX('@', EmailAddress) + 1, 50) AS DOMAIN FROM Production.ProductReview;

Question 89

Q: Provide an example of finding a file name from a file path using nested functions.

Answer 89

SELECT physical_name, RIGHT(physical_name, CHARINDEX('\', REVERSE(physical_name)) - 1) AS FileName FROM sys.database_files;

Question 89

Q: What is a good strategy for writing and debugging nested functions?

Answer 89

A: Work from the inside out, testing each inner function before integrating it into the outer functions.

Question 90

Q: Where is the STRING_SPLIT function typically used in a query?

Answer 90

A: In the FROM clause, as it returns a table.

Question 91

Q: How does STRING_AGG handle rows in a query?

Answer 91

A: It aggregates all rows into a single string, separated by the specified delimiter.

Question 92

Q: Why is nesting functions useful in T-SQL?

Answer 92

A: It allows complex data manipulations by combining multiple function operations into a single query.

Question 93

Q: What do the GETDATE and SYSDATETIME functions return in T-SQL?

Answer 93

A: They return the current date and time of the server.

Question 94

Q: How do GETDATE and SYSDATETIME differ in precision?

Answer 94

A: GETDATE returns three decimal places for seconds, while SYSDATETIME returns seven decimal places.

Question 95

Q: What does the DATEADD function do in T-SQL?

Answer 95

A: It adds a specified number of time units to a date.

Question 95

Q: Are GETDATE and SYSDATETIME deterministic functions?

Answer 95

A: No, they are nondeterministic, returning different values each time they are called.

Question 95

Q: What is the syntax for using DATEADD?

Answer 95

DATEADD(, , )

Question 96

Q: How can you subtract time using DATEADD?

Answer 96

A: Use a negative number as the second parameter, e.g., DATEADD(day, -1, OrderDate).

Question 97

Q: Provide an example of using DATEADD to add one month to a date.

Answer 97

SELECT DATEADD(month, 1, '2009-01-29') AS FebDate;

Question 98

Q: What does the DATEDIFF function do in T-SQL?

Answer 98

A: It calculates the difference between two dates in a specified unit of time.

Question 99

Q: What is the syntax for using DATEDIFF?

Answer 99

DATEDIFF(, , )

Question 100

Q: Provide an example of calculating the number of days between two dates.

Answer 100

SELECT DATEDIFF(day, '2008-12-31', '2009-01-01') AS DayDiff;

Question 101

Q: What does the DATENAME function return in T-SQL?

Answer 101

A: It returns the name of a specified date part (e.g., month or weekday) as a string.

Question 102

Q: What does the DATEPART function return in T-SQL?

Answer 102

A: It returns the value of a specified date part as an integer.

Question 103

Q: What is the syntax for DATENAME and DATEPART?

Answer 103

DATENAME(, ) DATEPART(, )

Question 104

Q: Provide an example of using DATENAME to get the name of the month from a date.

Answer 104

SELECT DATENAME(month, OrderDate) AS OrderMonth FROM Sales.SalesOrderHeader;

Question 105

Q: What happens when adding one month to January 29, 2009, using DATEADD?

Answer 105

A: It returns February 28, 2009, since February 29 doesn’t exist that year.

Question 105

Q: Provide an example of using DATEPART to get the day of the month from a date.

Answer 105

SELECT DATEPART(day, OrderDate) AS OrderDay FROM Sales.SalesOrderHeader;

Question 105

Q: Are the results of DATEDIFF rounded?

Answer 105

A: No, DATEDIFF returns the integer difference without decimal points.

Question 105

Q: What do the DAY, MONTH, and YEAR functions do in T-SQL?

Answer 105

A: They extract the day, month, or year from a date value.

Question 105

Q: What does the CONVERT function do in T-SQL?

Answer 105

A: It converts a value from one data type to another and can format dates using an optional style parameter.

Question 105

Q: Provide an example of using the DAY, MONTH, and YEAR functions.

Answer 105

SELECT YEAR(OrderDate) AS OrderYear, MONTH(OrderDate) AS OrderMonth, DAY(OrderDate) AS OrderDay FROM Sales.SalesOrderHeader;

Question 105

Q: What is the syntax for the DAY, MONTH, and YEAR functions?

Answer 105

DAY() MONTH() YEAR()

Question 106

Q: What is the syntax for the CONVERT function?

Answer 106

CONVERT(, ,