CHAPTER 4: Using Built-in Functions and Expressions Flashcards
Q: What operator is used to concatenate strings in T-SQL?
A: The + operator is used to concatenate strings.
Q: How can you concatenate columns in a query with a space in between?
SELECT FirstName + ‘ ‘ + LastName AS [Full Name] FROM Person.Person;
Q: What happens if you concatenate a string with a NULL using the + operator?
A: The result is NULL.
Q: Why is it important to alias concatenated expressions?
A: Without an alias, the resulting column header will be “(No column name),” making the results harder to interpret.
Q: What is the purpose of the CONCAT function in T-SQL?
A: The CONCAT function concatenates multiple values into a single string and automatically handles NULLs by ignoring them.
Q: How does the CONCAT function handle NULL values?
A: NULL values are ignored, and the function concatenates the remaining values.
Q: What is an example of using CONCAT with literal strings?
SELECT CONCAT(‘I ‘, ‘love’, ‘ T-SQL’) AS RESULT;
Q: Can the CONCAT function handle nonstring data types?
A: Yes, nonstring values are implicitly converted to strings before concatenation.
Q: What is an example of using CONCAT with variables?
DECLARE @a VARCHAR(30) = ‘My birthday is on ‘;
DECLARE @b DATE = ‘1980-08-25’;
SELECT CONCAT(@a, @b) AS RESULT;
Q: How can CONCAT be used with table data?
SELECT CONCAT(AddressLine1, PostalCode) AS Address FROM Person.Address;
Q: How does this query handle NULLs?
SELECT CONCAT(‘This’, NULL, ‘ works’) AS RESULT;
A: The result is “This works” because NULL values are ignored.
Q: Why might you prefer CONCAT over the + operator for string concatenation?
A: CONCAT handles NULL values gracefully by ignoring them, whereas + returns NULL if any value is NULL.
Q: What is the purpose of the ISNULL function in T-SQL?
A: The ISNULL function replaces NULL values with a specified replacement value.
Q: How does the COALESCE function differ from ISNULL?
A: COALESCE accepts multiple arguments and returns the first non-NULL value, while ISNULL accepts only two arguments.
Q: Provide an example of using ISNULL to handle NULLs in a concatenation.
SELECT FirstName + ISNULL(‘ ‘ + MiddleName, ‘’) + ‘ ‘ + LastName AS [Full Name]
FROM Person.Person;
Q: Why might developers prefer COALESCE over ISNULL?
A: COALESCE is ANSI-compliant, more versatile, and can handle multiple arguments, whereas ISNULL is a proprietary SQL Server feature.
Q: How does COALESCE work with NULL values in concatenation?
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;
Q: What issue can occur when using ISNULL incorrectly in concatenation?
A: Extra spaces may appear if the space is not included within the ISNULL function.
Q: What must you do to concatenate nonstring values to strings in T-SQL?
A: Convert the nonstring values to strings using CAST, CONVERT, or CONCAT.
Q: What happens if you attempt to concatenate a string and a number without converting the number?
A: An error will occur because integers have higher precedence than strings.
Q: What is the syntax for using the CAST function to convert a value to a string?
CAST(<value> AS <new>)</new></value>
Q: What is the syntax for using the CONVERT function to convert a value to a string?
CONVERT(<new>, <value>)</value></new>
Q: Provide an example of using CAST to convert and concatenate a value.
SELECT CAST(BusinessEntityID AS NVARCHAR) + ‘: ‘ + LastName + ‘, ‘ + FirstName AS ID_Name
FROM Person.Person;
Q: Provide an example of using CONVERT to convert and concatenate a value.
SELECT CONVERT(NVARCHAR(10), BusinessEntityID) + ‘: ‘ + LastName + ‘, ‘ + FirstName AS ID_Name
FROM Person.Person;
Q: What is the difference between appending and adding a numeric value in concatenation?
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
Q: Why should you always specify the length when using CAST or CONVERT?
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).
Q: When should you use CONCAT instead of CAST or CONVERT?
A: Use CONCAT when concatenating multiple values because it automatically handles NULLs and converts data types to strings.
Q: What operator is used for addition in T-SQL?
A: The + operator.
Q: What operator is used for subtraction in T-SQL?
A: The - (hyphen) operator.
Q: What operator is used for multiplication in T-SQL?
A: The * operator.
Q: What operator is used for division in T-SQL?
A: The / operator.
Q: What is the purpose of the modulo (%) operator in T-SQL?
A: It returns the remainder of a division operation. For example, 5 % 2 returns 1.
Q: How can the modulo operator determine if a number is odd or even?
Use % 2:
If the result is 1, the number is odd.
If the result is 0, the number is even.
Q: What is the result of 10 / 3 in T-SQL if both operands are integers?
A: The result is 3 because integer division truncates the decimal part.
Q: How can you perform division in T-SQL to return a decimal value?
A: Use at least one operand as a decimal value, e.g., 10.0 / 3 returns 3.333.
Q: Write a query to multiply a column by 10.
SELECT OrderQty, OrderQty * 10 AS Times10
FROM Sales.SalesOrderDetail;
Q: How do you calculate a discounted total in T-SQL?
Use a formula like:
SELECT OrderQty * UnitPrice * (1.0 - UnitPriceDiscount) AS Calculated
FROM Sales.SalesOrderDetail;
Q: What is the purpose of parentheses in mathematical expressions in T-SQL?
A: Parentheses enforce the intended order of operations, overriding default operator precedence.
Q: Provide an example of using ISNULL with mathematical operators.
SELECT DiscountPct * ISNULL(MaxQty, 1000) AS MaxDiscount
FROM Sales.SpecialOffer;
Q: What is the default precedence for multiplication, division, and subtraction in T-SQL?
A: Multiplication (*) and division (/) have higher precedence than subtraction (-).
Q: How do parentheses affect the order of operations in T-SQL?
A: They change the order to ensure specific parts of the expression are calculated first.
Q: Write a query to calculate the maximum discount with a fallback for NULL values.
SELECT SpecialOfferID, MaxQty, DiscountPct,
DiscountPct * ISNULL(MaxQty, 1000) AS MaxDiscount
FROM Sales.SpecialOffer;
Q: What happens when performing an operation on two different data types in T-SQL?
A: The result will be of the data type with the highest precedence, if conversion is possible.
Q: Why does the expression 1 + ‘a’ fail in T-SQL?
A: A character (‘a’) cannot be converted to a numeric value, leading to a data type mismatch error.
Q: What does the RTRIM function do in T-SQL?
A: Removes spaces from the right side of a string.
Q: What does the LTRIM function do in T-SQL?
A: Removes spaces from the left side of a string.
Q: What does the TRIM function do in T-SQL?
A: Removes spaces from both sides of a string.
Q: What is the syntax for using the RTRIM, LTRIM, and TRIM functions?
RTRIM(<string>)
LTRIM(<string>)
TRIM(<string>)</string></string></string>
Q: Provide an example of using RTRIM to remove trailing spaces.
SELECT RTRIM(‘Hello ‘) AS TrimmedString;
Q: What does the LEFT function do in T-SQL?
A: Returns a specified number of characters from the left side of a string.
Q: What does the RIGHT function do in T-SQL?
A: Returns a specified number of characters from the right side of a string.
Q: What is the syntax for the LEFT and RIGHT functions?
LEFT(<string>, <number>)
RIGHT(<string>, <number>)</number></string></number></string>
Q: Provide an example of using LEFT to extract the first 5 characters of a string.
SELECT LEFT(‘AdventureWorks’, 5) AS FirstFive;
Q: Provide an example of using RIGHT to extract the last 4 characters of a string.
SELECT RIGHT(‘AdventureWorks’, 4) AS LastFour;
Q: What happens if the number of characters specified in LEFT or RIGHT exceeds the length of the string?
A: The function returns as many characters as possible without causing an error.
Q: How can TRIM be used to clean up string data in a query?
SELECT TRIM(‘ Hello World ‘) AS CleanedString;
Q: Write a query using LEFT and RIGHT functions to parse a name.
SELECT LastName, LEFT(LastName, 3) AS FirstThree, RIGHT(LastName, 3) AS LastThree
FROM Person.Person;
Q: What does the LEN function return in T-SQL?
A: The number of characters in a string.
Q: What does the DATALENGTH function return in T-SQL?
A: The number of bytes used by a data type or expression.
Q: How does DATALENGTH differ from LEN when working with NCHAR or NVARCHAR data types?
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.
Q: What is the syntax for the LEN and DATALENGTH functions?
LEN(<string>)
DATALENGTH(<any>)</any></string>
Q: Provide an example of using LEN and DATALENGTH in a query.
SELECT LastName, LEN(LastName) AS “Length”, DATALENGTH(LastName) AS “Internal Data Length”
FROM Person.Person;
Q: What does the CHARINDEX function do in T-SQL?
A: It finds the numeric starting position of a search string within another string.
Q: What is the syntax for the CHARINDEX function?
CHARINDEX(<search>, <target>[, <start>])</start></target></search>
Q: What does the optional third parameter in CHARINDEX do?
A: It specifies the starting position to begin the search, ignoring the initial characters up to that position.
Q: How can you use CHARINDEX to find the first occurrence of the letter e in a string?
CHARINDEX(‘e’, LastName)
Q: Provide an example of using CHARINDEX to skip the first three characters in a search.
CHARINDEX(‘e’, LastName, 4)
Q: What happens when searching for a string in a case-sensitive database using CHARINDEX?
A: The function differentiates between cases; for example, searching for be and Be might return different results.
Q: Can CHARINDEX search for substrings longer than one character?
A: Yes, you can search for substrings like ‘be’ or ‘test’.
Q: Write a query to find the first occurrence of the substring be in a column.
SELECT CHARINDEX(‘be’, LastName) AS “Find be”
FROM Person.Person;
Q: Write a query to find the position of e after skipping the first three characters.
SELECT CHARINDEX(‘e’, LastName, 4) AS “Skip 3 Characters”
FROM Person.Person;
Q: What does the SUBSTRING function do in T-SQL?
A: It returns a portion of a string starting at a specified position and for a given length.
Q: What is the syntax for the SUBSTRING function?
SUBSTRING(<string>, <start>, <length>)</length></start></string>
Q: What happens if the start location in SUBSTRING is beyond the end of the string?
A: An empty string is returned.
Q: Provide an example of using SUBSTRING to extract the first 4 characters of a string.
SELECT SUBSTRING(LastName, 1, 4) AS “First 4 Characters” FROM Person.Person;
Q: What does the CHOOSE function do in T-SQL?
A: It selects a value from a list based on an index.
Q: What is the syntax for the CHOOSE function?
CHOOSE(<index>, <val_1>, <val_2>, ..., <val_n>)</val_n></val_2></val_1></index>
Q: Provide an example of using CHOOSE to select a value from an array.
SELECT CHOOSE(3, ‘a’, ‘b’, ‘c’, ‘d’) AS Result;
Q: What does the REVERSE function do in T-SQL?
A: It returns a string with its characters in reverse order.
Q: Provide an example of using REVERSE to reverse a string.
SELECT REVERSE(‘Hello, World!’) AS ReversedString;
Q: What do the UPPER and LOWER functions do in T-SQL?
A: UPPER converts a string to uppercase, and LOWER converts a string to lowercase.
Q: Provide an example of using UPPER and LOWER on a column.
SELECT UPPER(LastName) AS “UPPER”, LOWER(LastName) AS “LOWER” FROM Person.Person;
Q: Are searches in T-SQL case-sensitive by default?
A: No, searches are case-insensitive by default unless the column’s collation specifies case sensitivity.
Q: What does the REPLACE function do in T-SQL?
A: It replaces all occurrences of a specified string within another string.
Q: What is the syntax for the REPLACE function?
REPLACE(<string>, <string>, <replacement>)</replacement></string></string>
Q: Provide an example of using REPLACE to substitute one string for another.
SELECT REPLACE(‘AdventureWorks’, ‘Works’, ‘Shop’) AS ReplacedString;
Q: How can you use REPLACE to remove a string?
A: Replace it with an empty string (‘’), e.g.:
SELECT REPLACE(LastName, ‘a’, ‘’) AS “Remove a”;
Q: Can REPLACE use column values as the replacement string?
SELECT REPLACE(LastName, ‘a’, MiddleName) AS “Replace with MiddleName”;
Q: What does the STRING_SPLIT function do in T-SQL?
A: It splits a string into multiple rows based on a specified delimiter and returns the results as a table.
Q: What is the syntax for using STRING_SPLIT?
STRING_SPLIT(<string>, <delimiter>)</delimiter></string>
Q: Provide an example of splitting a comma-separated string into rows.
SELECT value
FROM STRING_SPLIT(‘1,2,3,4,5’, ‘,’);
Q: Can STRING_SPLIT handle delimiters other than commas?
A: Yes, any character can be used as the delimiter, e.g.,
SELECT value
FROM STRING_SPLIT(‘dog cat bird’, ‘ ‘);
Q: What is the syntax for using STRING_AGG?
STRING_AGG(<expression>, <delimiter>)</delimiter></expression>
Q: What does the STRING_AGG function do in T-SQL?
A: It concatenates values from multiple rows into a single delimited string.
Q: Provide an example of concatenating values into a comma-separated string using STRING_AGG.
SELECT STRING_AGG(Name, ‘, ‘) AS List
FROM Production.ProductCategory;
Q: What does it mean to nest functions in T-SQL?
A: Using the result of one function as a parameter for another function.
Q: What is an example of nesting the RTRIM and LTRIM functions?
SELECT LTRIM(RTRIM(‘ test ‘)) AS TrimmedString;
Q: Provide an example of extracting the domain from an email address using nested functions.
SELECT EmailAddress,
SUBSTRING(EmailAddress, CHARINDEX(‘@’, EmailAddress) + 1, 50) AS DOMAIN
FROM Production.ProductReview;
Q: Provide an example of finding a file name from a file path using nested functions.
SELECT physical_name,
RIGHT(physical_name, CHARINDEX(‘', REVERSE(physical_name)) - 1) AS FileName
FROM sys.database_files;
Q: What is a good strategy for writing and debugging nested functions?
A: Work from the inside out, testing each inner function before integrating it into the outer functions.
Q: Where is the STRING_SPLIT function typically used in a query?
A: In the FROM clause, as it returns a table.
Q: How does STRING_AGG handle rows in a query?
A: It aggregates all rows into a single string, separated by the specified delimiter.
Q: Why is nesting functions useful in T-SQL?
A: It allows complex data manipulations by combining multiple function operations into a single query.
Q: What do the GETDATE and SYSDATETIME functions return in T-SQL?
A: They return the current date and time of the server.
Q: How do GETDATE and SYSDATETIME differ in precision?
A: GETDATE returns three decimal places for seconds, while SYSDATETIME returns seven decimal places.
Q: What does the DATEADD function do in T-SQL?
A: It adds a specified number of time units to a date.
Q: Are GETDATE and SYSDATETIME deterministic functions?
A: No, they are nondeterministic, returning different values each time they are called.
Q: What is the syntax for using DATEADD?
DATEADD(<datepart>, <number>, <date>)</date></number></datepart>
Q: How can you subtract time using DATEADD?
A: Use a negative number as the second parameter, e.g., DATEADD(day, -1, OrderDate).
Q: Provide an example of using DATEADD to add one month to a date.
SELECT DATEADD(month, 1, ‘2009-01-29’) AS FebDate;
Q: What does the DATEDIFF function do in T-SQL?
A: It calculates the difference between two dates in a specified unit of time.
Q: What is the syntax for using DATEDIFF?
DATEDIFF(<datepart>, <early>, <later>)</later></early></datepart>
Q: Provide an example of calculating the number of days between two dates.
SELECT DATEDIFF(day, ‘2008-12-31’, ‘2009-01-01’) AS DayDiff;
Q: What does the DATENAME function return in T-SQL?
A: It returns the name of a specified date part (e.g., month or weekday) as a string.
Q: What does the DATEPART function return in T-SQL?
A: It returns the value of a specified date part as an integer.
Q: What is the syntax for DATENAME and DATEPART?
DATENAME(<datepart>, <date>)
DATEPART(<datepart>, <date>)</date></datepart></date></datepart>
Q: Provide an example of using DATENAME to get the name of the month from a date.
SELECT DATENAME(month, OrderDate) AS OrderMonth FROM Sales.SalesOrderHeader;
Q: What happens when adding one month to January 29, 2009, using DATEADD?
A: It returns February 28, 2009, since February 29 doesn’t exist that year.
Q: Provide an example of using DATEPART to get the day of the month from a date.
SELECT DATEPART(day, OrderDate) AS OrderDay FROM Sales.SalesOrderHeader;
Q: Are the results of DATEDIFF rounded?
A: No, DATEDIFF returns the integer difference without decimal points.
Q: What do the DAY, MONTH, and YEAR functions do in T-SQL?
A: They extract the day, month, or year from a date value.
Q: What does the CONVERT function do in T-SQL?
A: It converts a value from one data type to another and can format dates using an optional style parameter.
Q: Provide an example of using the DAY, MONTH, and YEAR functions.
SELECT YEAR(OrderDate) AS OrderYear, MONTH(OrderDate) AS OrderMonth, DAY(OrderDate) AS OrderDay
FROM Sales.SalesOrderHeader;
Q: What is the syntax for the DAY, MONTH, and YEAR functions?
DAY(<date>)
MONTH(<date>)
YEAR(<date>)</date></date></date>
Q: What is the syntax for the CONVERT function?
CONVERT(<data>, <value>, <style>)</style></value></data>
Q: What is a common use of CONVERT with DATETIME values?
A: Removing the time portion by casting the value to a DATE, e.g.:
SELECT CAST(GETDATE() AS DATE) AS DateOnly;
Q: How is the CONVERT function used to format dates?
A: Use the optional style parameter to specify the format, e.g.:
CONVERT(VARCHAR, GETDATE(), 111) AS DateFormat;
Q: Provide an example of using CONVERT with different date styles.
SELECT CONVERT(VARCHAR, OrderDate, 1) AS “Style 1”,
CONVERT(VARCHAR, OrderDate, 101) AS “Style 101”,
CONVERT(VARCHAR, OrderDate, 2) AS “Style 2”,
CONVERT(VARCHAR, OrderDate, 102) AS “Style 102”
FROM Sales.SalesOrderHeader;
Q: How does CONVERT simplify date formatting compared to using DATEPART and CAST?
A: Instead of extracting parts of a date and concatenating them, CONVERT directly formats the date in one step.
Q: Provide an example of formatting a date manually using DATEPART and CAST.
SELECT CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR) + ‘/’ +
CAST(DATEPART(MONTH, GETDATE()) AS VARCHAR) + ‘/’ +
CAST(DATEPART(DAY, GETDATE()) AS VARCHAR) AS DateFormatted;
Q: How can CONVERT be used to remove the time portion from a DATETIME value?
SELECT CONVERT(VARCHAR, GETDATE(), 101) AS DateOnly; – US format MM/DD/YYYY
Q: What style number in CONVERT produces a four-digit year in US date format?
A: Style 101.
Q: How does style 111 in CONVERT format a date?
A: It formats the date as YYYY/MM/DD.
Q: What is the purpose of the FORMAT function in T-SQL?
A: To convert date/time values into string representations, including cultural-specific formats.
Q: What is the syntax for the FORMAT function?
FORMAT(value, format [, culture])
Q: Provide an example of formatting the current date to “MM/dd/yyyy” in the US culture.
SELECT FORMAT(GETDATE(), ‘MM/dd/yyyy’, ‘en-US’) AS Result;
Q: What happens if you use mm instead of MM in the FORMAT function?
A: mm returns minutes, while MM returns months. The letters are case-sensitive.
Q: What does the DATEFROMPARTS function do in T-SQL?
A: It creates a date from individual year, month, and day values.
Q: Provide an example of creating a date using DATEFROMPARTS.
SELECT DATEFROMPARTS(2022, 12, 25) AS ChristmasDate;
Q: What is the difference between DATEFROMPARTS and DATETIME2FROMPARTS?
A: DATEFROMPARTS creates only a date, while DATETIME2FROMPARTS includes date and time values.
Q: Provide an example of using DATETIME2FROMPARTS to create a date and time.
SELECT DATETIME2FROMPARTS(2022, 12, 25, 18, 30, 0, 0, 0) AS ChristmasEvening;
Q: What happens if an invalid value is supplied to DATEFROMPARTS (e.g., month = 13)?
A: The function throws an error.
Q: What does the EOMONTH function do in T-SQL?
A: It returns the last date of the specified month.
Q: Provide an example of finding the last day of the current month.
SELECT EOMONTH(GETDATE()) AS EndOfMonth;
Q: How do you use EOMONTH to find the last day of the next month?
SELECT EOMONTH(GETDATE(), 1) AS EndOfNextMonth;
Q: Can EOMONTH handle specific date inputs? Provide an example.
A: Yes, e.g.:
SELECT EOMONTH(‘2023-02-15’) AS EndOfFebruary;
Q: How can you format a date as “yyyy-MM-dd” using FORMAT?
SELECT FORMAT(GETDATE(), ‘yyyy-MM-dd’) AS FormattedDate;
Q: How can you create a time value using TIMEFROMPARTS?
SELECT TIMEFROMPARTS(14, 30, 0, 0, 0) AS AfternoonTime;
Q: What does the ABS function do in T-SQL?
A: It returns the absolute value of a number, which is the distance from zero.
Q: Provide an example of using the ABS function.
SELECT ABS(-5) AS AbsoluteValue;
Q: What does the POWER function do in T-SQL?
A: It raises a number to the power of another number.
Q: What is the syntax for the POWER function?
POWER(<number>, <power>)</power></number>
Q: What happens if the result of the POWER function exceeds the maximum value for its data type?
A: An overflow error occurs. You can cast the base number to a larger data type (e.g., FLOAT) to avoid this.
Q: What does the SQUARE function do in T-SQL?
A: It returns the square of a number, equivalent to the number multiplied by itself.
Q: What does the SQRT function do in T-SQL?
A: It returns the square root of a number.
Q: What does the ROUND function do in T-SQL?
A: It rounds a number to a specified precision or truncates it if specified.
Q: Provide an example of using SQUARE and SQRT functions together.
SELECT SQRT(SQUARE(10)) AS “SquareRootOfSquare”;
Q: What is the syntax for the ROUND function?
ROUND(<number>, <length>[, <function>])</function></length></number>
Q: Provide an example of rounding a number to two decimal places.
SELECT ROUND(1234.5678, 2) AS RoundedValue;
Q: What does a negative value for the length parameter in ROUND do?
A: It rounds the number to the left of the decimal point.
Q: How does the optional third parameter in the ROUND function affect its behavior?
A: If the third parameter is nonzero, the function truncates instead of rounding.
Q: Provide an example of truncating a number instead of rounding.
SELECT ROUND(1234.5678, 2, 1) AS TruncatedValue;
Q: What is the difference between rounding and truncating in the ROUND function?
Rounding adjusts the number to the nearest value based on the specified precision.
Truncating removes the digits beyond the specified precision without rounding.
Q: How can you use the ROUND function to round to the nearest hundred?
SELECT ROUND(1234.5678, -2) AS NearestHundred;
Q: What does the RAND function do in T-SQL?
A: It generates a pseudo-random float value between 0 and 1.
Q: What is the purpose of the optional seed parameter in the RAND function?
A: The seed ensures that the function generates the same sequence of random numbers each time it is used.
Q: What happens if you omit the seed in the RAND function?
A: SQL Server assigns a random seed, generating different results for each call.
Q: How can you generate a random integer between 1 and 100 using the RAND function?
SELECT CAST(RAND() * 100 AS INT) + 1 AS “1 to 100”;
Q: Provide an example of generating a random integer between 900 and 1900.
SELECT CAST(RAND() * 1000 AS INT) + 900 AS “900 to 1900”;
Q: How does RAND behave when a seed is used within a batch?
A: The seed affects all subsequent calls to RAND in the batch, producing predictable (but not identical) values.
Q: Provide an example of using a seed with RAND.
SELECT RAND(3), RAND(), RAND();
Q: What happens if you run a query with RAND multiple times but without specifying a seed?
A: The results will be different each time because the function uses a random seed.
Q: How can you generate a random integer between 1 and 5?
SELECT CAST(RAND() * 5 AS INT) + 1 AS “1 to 5”;
Q: What happens when you use multiple calls to RAND in a query without specifying a seed?
A: Each column will show different random values, but the values will be the same for every row in the result set.
Q: What is the purpose of the CASE expression in T-SQL?
A: It evaluates a list of conditions and returns a value for the first condition that evaluates to true.
Q: What is the syntax for a simple CASE expression?
CASE <test>
WHEN <comparison> THEN <return>
WHEN <comparison> THEN <return>
[ELSE <default>] END</default></return></comparison></return></comparison></test>
Q: What are the two types of CASE expressions in T-SQL?
Simple CASE
Searched CASE
Q: Provide an example of a simple CASE expression to determine gender based on a title.
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;
Q: What happens if no ELSE clause is provided in a simple CASE expression?
A: The CASE expression returns NULL for unmatched values.
Q: What is the syntax for a searched CASE expression?
CASE
WHEN <condition1> THEN <return>
WHEN <condition2> THEN <return>
[ELSE <default>] END</default></return></condition2></return></condition1>
Q: What is the key difference between simple and searched CASE expressions?
Simple CASE: Compares a single expression to a list of values.
Searched CASE: Allows more complex conditions, such as IN lists or comparison operators.
Q: Provide an example of a searched CASE expression to determine gender based on multiple conditions.
SELECT Title,
CASE
WHEN Title IN (‘Ms.’, ‘Mrs.’, ‘Miss’) THEN ‘Female’
WHEN Title = ‘Mr.’ THEN ‘Male’
ELSE ‘Unknown’
END AS Gender
FROM Person.Person;
Q: What happens if the return values in a CASE expression are of incompatible data types?
A: The query will result in an error because all return values must be of compatible data types.
Q: What happens if multiple conditions in a CASE expression are true?
A: The CASE expression returns the value for the first true condition and skips evaluating the remaining conditions.
Q: Can a CASE expression mix numeric and string return values? Why or why not?
A: No, all return values must be of compatible data types, as T-SQL enforces precedence rules.
Q: Provide an example where a CASE expression would result in an error due to incompatible return types.
SELECT Title,
CASE
WHEN Title = ‘Mr.’ THEN 1
WHEN Title = ‘Ms.’ THEN ‘Female’
ELSE ‘Unknown’
END AS Result
FROM Person.Person;
Q: Can you use a column name as the return value in a CASE expression?
A: Yes, you can return one column’s value for some rows and another column’s value for others.
Q: Provide an example of a CASE expression that returns one column or another based on a condition.
SELECT VacationHours, SickLeaveHours,
CASE
WHEN VacationHours > SickLeaveHours THEN VacationHours
ELSE SickLeaveHours
END AS ‘More Hours’
FROM HumanResources.Employee;
Q: What does the IIF function do in T-SQL?
A: It evaluates a Boolean expression and returns one value if true and another if false.
Q: What is the syntax for the IIF function?
IIF(boolean_expression, true_value, false_value)
Q: Provide an example of a simple IIF function to compare two numbers.
SELECT IIF(50 > 20, ‘TRUE’, ‘FALSE’) AS RESULT;
Q: Provide an example of using the IIF function with variables.
DECLARE @a INT = 50;
DECLARE @b INT = 20;
SELECT IIF(@a > @b, ‘TRUE’, ‘FALSE’) AS RESULT;
Q: What rules of CASE expressions also apply to IIF functions?
A: The return values must be of compatible data types.
Q: What does the COALESCE function do in T-SQL?
A: It returns the first non-NULL value from a list of arguments.
Q: How does COALESCE differ from ISNULL?
ISNULL evaluates a single value for NULL.
COALESCE evaluates a list of values and returns the first non-NULL value.
Q: Provide an example of using COALESCE to handle NULL values in multiple columns.
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);
Q: What happens if all arguments in the COALESCE function are NULL?
A: The COALESCE function returns NULL unless a default value is explicitly specified.
Q: How can you find the larger of two numeric columns using a CASE expression?
SELECT
CASE
WHEN ColumnA > ColumnB THEN ColumnA
ELSE ColumnB
END AS LargerValue
FROM TableName;
Q: How can COALESCE be used when concatenating strings?
A: It ensures NULL values are replaced with a default string during concatenation.
Q: What does the DB_NAME() function return in T-SQL?
A: The name of the current database.
Q: What does the HOST_NAME() function return in T-SQL?
A: The name of the computer or client machine connected to SQL Server.
Q: What does the CURRENT_USER function return?
A: The name of the current user executing the query.
Q: What is the purpose of the SUSER_NAME() function?
A: It returns the login name of the user connected to SQL Server.
Q: What does the APP_NAME() function return?
A: The name of the application used to connect to SQL Server.
Q: Can functions be used in the WHERE clause? Provide an example.
A: Yes, for example:
SELECT FirstName
FROM Person.Person
WHERE CHARINDEX(‘ke’, FirstName) > 0;
Q: Can functions be used in the ORDER BY clause? Provide an example.
A: Yes, for example:
SELECT LastName, REVERSE(LastName)
FROM Person.Person
ORDER BY REVERSE(LastName);
Q: What impact do functions on indexed columns in the WHERE clause have on performance?
A: They may degrade performance because the database engine must evaluate each row individually.
Q: What does the TOP keyword do in T-SQL?
A: It limits the number or percentage of rows returned by a query.
Q: What is the syntax for the TOP keyword?
SELECT TOP(<number>) [PERCENT] [WITH TIES] <columns>
FROM <table> [ORDER BY <column>]</column></columns></number>
Q: What does the WITH TIES option in the TOP keyword do?
A: It includes all rows that have identical values in the ORDER BY column, even if this exceeds the specified limit.
Q: How can the TOP keyword be used with a variable?
DECLARE @Percent INT = 2;
SELECT TOP(@Percent) PERCENT CustomerID, OrderDate
FROM Sales.SalesOrderHeader
ORDER BY SalesOrderID;
Q: How can you use the TOP keyword to retrieve random rows?
A: Sort the rows by the NEWID() function:
SELECT TOP(2) CustomerID, OrderDate
FROM Sales.SalesOrderHeader
ORDER BY NEWID();
Q: Why is it recommended to use OFFSET and FETCH instead of TOP for paging data?
A: OFFSET and FETCH offer more options, including the use of variables, and provide better control over data paging.
Q: Provide an example of the TOP keyword with the PERCENT option.
SELECT TOP(10) PERCENT CustomerID, OrderDate
FROM Sales.SalesOrderHeader
ORDER BY SalesOrderID;
Q: Provide an example of using TOP with WITH TIES.
SELECT TOP(2) WITH TIES CustomerID, OrderDate
FROM Sales.SalesOrderHeader
ORDER BY OrderDate;
Q: What is the difference between an index seek and an index scan?
Index Seek: Efficiently retrieves specific rows using an index.
Index Scan: Reads all rows in an index, which can be slower.
Q: How can using a function in the WHERE clause affect query performance?
A: It can degrade performance by requiring the database engine to evaluate the function for each row, potentially leading to an index scan instead of a seek.
Q: What is the purpose of the following code?
CREATE NONCLUSTERED INDEX [DEMO_SalesOrderHeader_OrderDate]
ON [Sales].[SalesOrderHeader] ([OrderDate] ASC);
A: It creates a nonclustered index on the OrderDate column to improve query performance for searches on this column.
Q: How can you remove the index created on the OrderDate column?
DROP INDEX [DEMO_SalesOrderHeader_OrderDate] ON [Sales].[SalesOrderHeader];
Q: Why does Query 1 perform better than Query 2 in the example?
A: Query 1 uses an index seek by directly comparing OrderDate, while Query 2 uses the YEAR function, requiring an index scan.
Q: What is the estimated subtree cost in an execution plan?
A: It represents the estimated amount of resources required to execute the query, useful for comparing query performance.
Q: How does the absence of an index affect Query 1 in the example?
A: Without the index, Query 1 performs a table scan, increasing the query’s resource consumption.
Q: Why doesn’t the execution plan suggest an index for Query 2 when the index is removed?
A: Because an index wouldn’t help optimize Query 2 due to the use of the YEAR function on the indexed column.
Q: How can you rewrite this query to improve performance?
SELECT SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2011;
SELECT SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader
WHERE OrderDate >= ‘2011-01-01 00:00:00’ AND OrderDate < ‘2012-01-01 00:00:00’;
Q: Why is it important to test query performance in a realistic environment?
A: Small databases may not show significant performance differences, but large databases with millions of rows will highlight inefficiencies.
Q: What should you consider beyond just getting correct results when writing queries?
A: Performance optimization, including minimizing resource consumption and ensuring efficient use of indexes.
