Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Data Analyst > CHAPTER 4: Using Built-in Functions and Expressions > Flashcards

CHAPTER 4: Using Built-in Functions and Expressions Flashcards

1
Q

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

A

A: The + operator is used to concatenate strings.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

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

A

A: The result is NULL.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Q: Why is it important to alias concatenated expressions?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Q: How does the CONCAT function handle NULL values?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Q: Can the CONCAT function handle nonstring data types?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Q: How can CONCAT be used with table data?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Q: How does this query handle NULLs?

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Q: How does the COALESCE function differ from ISNULL?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Q: Why might developers prefer COALESCE over ISNULL?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

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

A

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;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

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

A

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).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

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

A

A: The + operator.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

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

A

A: The - (hyphen) operator.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

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

A

A: The * operator.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

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

A

A: The / operator.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

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

A

Use % 2:

If the result is 1, the number is odd.
If the result is 0, the number is even.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q
A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
45
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
46
Q

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

A

A: Removes spaces from both sides of a string.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

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

A

RTRIM(<string>)
LTRIM(<string>)
TRIM(<string>)</string></string></string>

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

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

A

SELECT RTRIM(‘Hello ‘) AS TrimmedString;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

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

A

LEFT(<string>, <number>)
RIGHT(<string>, <number>)</number></string></number></string>

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
48
Q

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

A

SELECT LEFT(‘AdventureWorks’, 5) AS FirstFive;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q

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

A

SELECT RIGHT(‘AdventureWorks’, 4) AS LastFour;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q

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

A

SELECT TRIM(‘ Hello World ‘) AS CleanedString;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
50
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
51
Q

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

A

A: The number of characters in a string.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
52
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
53
Q

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

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
54
Q

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

A

LEN(<string>)
DATALENGTH(<any>)</any></string>

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
55
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
56
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
57
Q

Q: What is the syntax for the CHARINDEX function?

A

CHARINDEX(<search>, <target>[, <start>])</start></target></search>

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
58
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
59
Q

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

A

CHARINDEX(‘e’, LastName)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
60
Q

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

A

CHARINDEX(‘e’, LastName, 4)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
61
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
62
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
63
Q

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

A

SELECT CHARINDEX(‘be’, LastName) AS “Find be”
FROM Person.Person;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
64
Q

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

A

SELECT CHARINDEX(‘e’, LastName, 4) AS “Skip 3 Characters”
FROM Person.Person;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
65
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
66
Q

Q: What is the syntax for the SUBSTRING function?

A

SUBSTRING(<string>, <start>, <length>)</length></start></string>

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
67
Q

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

A

A: An empty string is returned.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
68
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
69
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
70
Q

Q: What is the syntax for the CHOOSE function?

A

CHOOSE(<index>, <val_1>, <val_2>, ..., <val_n>)</val_n></val_2></val_1></index>

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
71
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
72
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
73
Q

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

A

SELECT REVERSE(‘Hello, World!’) AS ReversedString;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
74
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
75
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
76
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
77
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
78
Q

Q: What is the syntax for the REPLACE function?

A

REPLACE(<string>, <string>, <replacement>)</replacement></string></string>

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
79
Q

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

A

SELECT REPLACE(‘AdventureWorks’, ‘Works’, ‘Shop’) AS ReplacedString;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
80
Q

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

A

A: Replace it with an empty string (‘’), e.g.:

SELECT REPLACE(LastName, ‘a’, ‘’) AS “Remove a”;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
81
Q

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

A

SELECT REPLACE(LastName, ‘a’, MiddleName) AS “Replace with MiddleName”;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
82
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
83
Q

Q: What is the syntax for using STRING_SPLIT?

A

STRING_SPLIT(<string>, <delimiter>)</delimiter></string>

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
84
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
85
Q

Q: Can STRING_SPLIT handle delimiters other than commas?

A

A: Yes, any character can be used as the delimiter, e.g.,

SELECT value
FROM STRING_SPLIT(‘dog cat bird’, ‘ ‘);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
85
Q

Q: What is the syntax for using STRING_AGG?

A

STRING_AGG(<expression>, <delimiter>)</delimiter></expression>

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
86
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
87
Q
A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
87
Q
A
87
Q
A
87
Q
A
87
Q
A
88
Q
A
89
Q

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

A

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

89
Q

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

A

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

89
Q

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

A

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

89
Q

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

A

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

89
Q

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

A

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

89
Q

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

A

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

90
Q

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

A

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

91
Q

Q: How does STRING_AGG handle rows in a query?

A

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

92
Q

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

A

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

93
Q

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

A

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

94
Q

Q: How do GETDATE and SYSDATETIME differ in precision?

A

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

95
Q

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

A

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

95
Q

Q: Are GETDATE and SYSDATETIME deterministic functions?

A

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

95
Q

Q: What is the syntax for using DATEADD?

A

DATEADD(<datepart>, <number>, <date>)</date></number></datepart>

96
Q

Q: How can you subtract time using DATEADD?

A

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

97
Q

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

A

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

98
Q

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

A

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

99
Q

Q: What is the syntax for using DATEDIFF?

A

DATEDIFF(<datepart>, <early>, <later>)</later></early></datepart>

100
Q

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

A

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

101
Q

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

A

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

102
Q

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

A

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

103
Q

Q: What is the syntax for DATENAME and DATEPART?

A

DATENAME(<datepart>, <date>)
DATEPART(<datepart>, <date>)</date></datepart></date></datepart>

104
Q

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

A

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

105
Q

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

A

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

105
Q

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

A

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

105
Q

Q: Are the results of DATEDIFF rounded?

A

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

105
Q

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

A

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

105
Q

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

A

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

105
Q

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

A

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

105
Q

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

A

DAY(<date>)
MONTH(<date>)
YEAR(<date>)</date></date></date>

106
Q

Q: What is the syntax for the CONVERT function?

A

CONVERT(<data>, <value>, <style>)</style></value></data>

107
Q

Q: What is a common use of CONVERT with DATETIME values?

A

A: Removing the time portion by casting the value to a DATE, e.g.:

SELECT CAST(GETDATE() AS DATE) AS DateOnly;

107
Q

Q: How is the CONVERT function used to format dates?

A

A: Use the optional style parameter to specify the format, e.g.:

CONVERT(VARCHAR, GETDATE(), 111) AS DateFormat;

107
Q

Q: Provide an example of using CONVERT with different date styles.

A

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;

108
Q

Q: How does CONVERT simplify date formatting compared to using DATEPART and CAST?

A

A: Instead of extracting parts of a date and concatenating them, CONVERT directly formats the date in one step.

108
Q

Q: Provide an example of formatting a date manually using DATEPART and CAST.

A

SELECT CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR) + ‘/’ +
CAST(DATEPART(MONTH, GETDATE()) AS VARCHAR) + ‘/’ +
CAST(DATEPART(DAY, GETDATE()) AS VARCHAR) AS DateFormatted;

109
Q
A
110
Q

Q: How can CONVERT be used to remove the time portion from a DATETIME value?

A

SELECT CONVERT(VARCHAR, GETDATE(), 101) AS DateOnly; – US format MM/DD/YYYY

111
Q

Q: What style number in CONVERT produces a four-digit year in US date format?

A

A: Style 101.

112
Q

Q: How does style 111 in CONVERT format a date?

A

A: It formats the date as YYYY/MM/DD.

113
Q

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

A

A: To convert date/time values into string representations, including cultural-specific formats.

114
Q

Q: What is the syntax for the FORMAT function?

A

FORMAT(value, format [, culture])

115
Q

Q: Provide an example of formatting the current date to “MM/dd/yyyy” in the US culture.

A

SELECT FORMAT(GETDATE(), ‘MM/dd/yyyy’, ‘en-US’) AS Result;

116
Q

Q: What happens if you use mm instead of MM in the FORMAT function?

A

A: mm returns minutes, while MM returns months. The letters are case-sensitive.

117
Q

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

A

A: It creates a date from individual year, month, and day values.

118
Q

Q: Provide an example of creating a date using DATEFROMPARTS.

A

SELECT DATEFROMPARTS(2022, 12, 25) AS ChristmasDate;

119
Q

Q: What is the difference between DATEFROMPARTS and DATETIME2FROMPARTS?

A

A: DATEFROMPARTS creates only a date, while DATETIME2FROMPARTS includes date and time values.

120
Q

Q: Provide an example of using DATETIME2FROMPARTS to create a date and time.

A

SELECT DATETIME2FROMPARTS(2022, 12, 25, 18, 30, 0, 0, 0) AS ChristmasEvening;

121
Q

Q: What happens if an invalid value is supplied to DATEFROMPARTS (e.g., month = 13)?

A

A: The function throws an error.

121
Q

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

A

A: It returns the last date of the specified month.

121
Q

Q: Provide an example of finding the last day of the current month.

A

SELECT EOMONTH(GETDATE()) AS EndOfMonth;

121
Q

Q: How do you use EOMONTH to find the last day of the next month?

A

SELECT EOMONTH(GETDATE(), 1) AS EndOfNextMonth;

121
Q

Q: Can EOMONTH handle specific date inputs? Provide an example.

A

A: Yes, e.g.:

SELECT EOMONTH(‘2023-02-15’) AS EndOfFebruary;

122
Q

Q: How can you format a date as “yyyy-MM-dd” using FORMAT?

A

SELECT FORMAT(GETDATE(), ‘yyyy-MM-dd’) AS FormattedDate;

122
Q

Q: How can you create a time value using TIMEFROMPARTS?

A

SELECT TIMEFROMPARTS(14, 30, 0, 0, 0) AS AfternoonTime;

122
Q

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

A

A: It returns the absolute value of a number, which is the distance from zero.

122
Q

Q: Provide an example of using the ABS function.

A

SELECT ABS(-5) AS AbsoluteValue;

122
Q

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

A

A: It raises a number to the power of another number.

123
Q

Q: What is the syntax for the POWER function?

A

POWER(<number>, <power>)</power></number>

124
Q

Q: What happens if the result of the POWER function exceeds the maximum value for its data type?

A

A: An overflow error occurs. You can cast the base number to a larger data type (e.g., FLOAT) to avoid this.

125
Q

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

A

A: It returns the square of a number, equivalent to the number multiplied by itself.

125
Q

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

A

A: It returns the square root of a number.

125
Q

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

A

A: It rounds a number to a specified precision or truncates it if specified.

125
Q

Q: Provide an example of using SQUARE and SQRT functions together.

A

SELECT SQRT(SQUARE(10)) AS “SquareRootOfSquare”;

125
Q

Q: What is the syntax for the ROUND function?

A

ROUND(<number>, <length>[, <function>])</function></length></number>

125
Q

Q: Provide an example of rounding a number to two decimal places.

A

SELECT ROUND(1234.5678, 2) AS RoundedValue;

125
Q

Q: What does a negative value for the length parameter in ROUND do?

A

A: It rounds the number to the left of the decimal point.

126
Q

Q: How does the optional third parameter in the ROUND function affect its behavior?

A

A: If the third parameter is nonzero, the function truncates instead of rounding.

127
Q

Q: Provide an example of truncating a number instead of rounding.

A

SELECT ROUND(1234.5678, 2, 1) AS TruncatedValue;

128
Q
A
129
Q

Q: What is the difference between rounding and truncating in the ROUND function?

A

Rounding adjusts the number to the nearest value based on the specified precision.

Truncating removes the digits beyond the specified precision without rounding.

130
Q

Q: How can you use the ROUND function to round to the nearest hundred?

A

SELECT ROUND(1234.5678, -2) AS NearestHundred;

131
Q

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

A

A: It generates a pseudo-random float value between 0 and 1.

132
Q

Q: What is the purpose of the optional seed parameter in the RAND function?

A

A: The seed ensures that the function generates the same sequence of random numbers each time it is used.

133
Q

Q: What happens if you omit the seed in the RAND function?

A

A: SQL Server assigns a random seed, generating different results for each call.

134
Q

Q: How can you generate a random integer between 1 and 100 using the RAND function?

A

SELECT CAST(RAND() * 100 AS INT) + 1 AS “1 to 100”;

135
Q

Q: Provide an example of generating a random integer between 900 and 1900.

A

SELECT CAST(RAND() * 1000 AS INT) + 900 AS “900 to 1900”;

136
Q

Q: How does RAND behave when a seed is used within a batch?

A

A: The seed affects all subsequent calls to RAND in the batch, producing predictable (but not identical) values.

137
Q

Q: Provide an example of using a seed with RAND.

A

SELECT RAND(3), RAND(), RAND();

138
Q

Q: What happens if you run a query with RAND multiple times but without specifying a seed?

A

A: The results will be different each time because the function uses a random seed.

139
Q

Q: How can you generate a random integer between 1 and 5?

A

SELECT CAST(RAND() * 5 AS INT) + 1 AS “1 to 5”;

140
Q

Q: What happens when you use multiple calls to RAND in a query without specifying a seed?

A

A: Each column will show different random values, but the values will be the same for every row in the result set.

141
Q

Q: What is the purpose of the CASE expression in T-SQL?

A

A: It evaluates a list of conditions and returns a value for the first condition that evaluates to true.

142
Q

Q: What is the syntax for a simple CASE expression?

A

CASE <test>
WHEN <comparison> THEN <return>
WHEN <comparison> THEN <return>
[ELSE <default>] END</default></return></comparison></return></comparison></test>

142
Q

Q: What are the two types of CASE expressions in T-SQL?

A

Simple CASE

Searched CASE

143
Q

Q: Provide an example of a simple CASE expression to determine gender based on a title.

A

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;

144
Q

Q: What happens if no ELSE clause is provided in a simple CASE expression?

A

A: The CASE expression returns NULL for unmatched values.

145
Q

Q: What is the syntax for a searched CASE expression?

A

CASE
WHEN <condition1> THEN <return>
WHEN <condition2> THEN <return>
[ELSE <default>] END</default></return></condition2></return></condition1>

146
Q

Q: What is the key difference between simple and searched CASE expressions?

A

Simple CASE: Compares a single expression to a list of values.

Searched CASE: Allows more complex conditions, such as IN lists or comparison operators.

147
Q

Q: Provide an example of a searched CASE expression to determine gender based on multiple conditions.

A

SELECT Title,
CASE
WHEN Title IN (‘Ms.’, ‘Mrs.’, ‘Miss’) THEN ‘Female’
WHEN Title = ‘Mr.’ THEN ‘Male’
ELSE ‘Unknown’
END AS Gender
FROM Person.Person;

148
Q

Q: What happens if the return values in a CASE expression are of incompatible data types?

A

A: The query will result in an error because all return values must be of compatible data types.

149
Q

Q: What happens if multiple conditions in a CASE expression are true?

A

A: The CASE expression returns the value for the first true condition and skips evaluating the remaining conditions.

150
Q

Q: Can a CASE expression mix numeric and string return values? Why or why not?

A

A: No, all return values must be of compatible data types, as T-SQL enforces precedence rules.

151
Q

Q: Provide an example where a CASE expression would result in an error due to incompatible return types.

A

SELECT Title,
CASE
WHEN Title = ‘Mr.’ THEN 1
WHEN Title = ‘Ms.’ THEN ‘Female’
ELSE ‘Unknown’
END AS Result
FROM Person.Person;

152
Q

Q: Can you use a column name as the return value in a CASE expression?

A

A: Yes, you can return one column’s value for some rows and another column’s value for others.

153
Q

Q: Provide an example of a CASE expression that returns one column or another based on a condition.

A

SELECT VacationHours, SickLeaveHours,
CASE
WHEN VacationHours > SickLeaveHours THEN VacationHours
ELSE SickLeaveHours
END AS ‘More Hours’
FROM HumanResources.Employee;

153
Q

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

A

A: It evaluates a Boolean expression and returns one value if true and another if false.

154
Q

Q: What is the syntax for the IIF function?

A

IIF(boolean_expression, true_value, false_value)

155
Q

Q: Provide an example of a simple IIF function to compare two numbers.

A

SELECT IIF(50 > 20, ‘TRUE’, ‘FALSE’) AS RESULT;

156
Q

Q: Provide an example of using the IIF function with variables.

A

DECLARE @a INT = 50;
DECLARE @b INT = 20;
SELECT IIF(@a > @b, ‘TRUE’, ‘FALSE’) AS RESULT;

157
Q

Q: What rules of CASE expressions also apply to IIF functions?

A

A: The return values must be of compatible data types.

158
Q

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

A

A: It returns the first non-NULL value from a list of arguments.

159
Q

Q: How does COALESCE differ from ISNULL?

A

ISNULL evaluates a single value for NULL.

COALESCE evaluates a list of values and returns the first non-NULL value.

160
Q

Q: Provide an example of using COALESCE to handle NULL values in multiple columns.

A

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);

161
Q

Q: What happens if all arguments in the COALESCE function are NULL?

A

A: The COALESCE function returns NULL unless a default value is explicitly specified.

162
Q
A
163
Q
A
164
Q
A
165
Q
A
166
Q

Q: How can you find the larger of two numeric columns using a CASE expression?

A

SELECT
CASE
WHEN ColumnA > ColumnB THEN ColumnA
ELSE ColumnB
END AS LargerValue
FROM TableName;

166
Q

Q: How can COALESCE be used when concatenating strings?

A

A: It ensures NULL values are replaced with a default string during concatenation.

166
Q

Q: What does the DB_NAME() function return in T-SQL?

A

A: The name of the current database.

167
Q

Q: What does the HOST_NAME() function return in T-SQL?

A

A: The name of the computer or client machine connected to SQL Server.

168
Q

Q: What does the CURRENT_USER function return?

A

A: The name of the current user executing the query.

169
Q

Q: What is the purpose of the SUSER_NAME() function?

A

A: It returns the login name of the user connected to SQL Server.

170
Q

Q: What does the APP_NAME() function return?

A

A: The name of the application used to connect to SQL Server.

171
Q

Q: Can functions be used in the WHERE clause? Provide an example.

A

A: Yes, for example:

SELECT FirstName
FROM Person.Person
WHERE CHARINDEX(‘ke’, FirstName) > 0;

172
Q
A
173
Q

Q: Can functions be used in the ORDER BY clause? Provide an example.

A

A: Yes, for example:

SELECT LastName, REVERSE(LastName)
FROM Person.Person
ORDER BY REVERSE(LastName);

174
Q

Q: What impact do functions on indexed columns in the WHERE clause have on performance?

A

A: They may degrade performance because the database engine must evaluate each row individually.

175
Q

Q: What does the TOP keyword do in T-SQL?

A

A: It limits the number or percentage of rows returned by a query.

176
Q

Q: What is the syntax for the TOP keyword?

A

SELECT TOP(<number>) [PERCENT] [WITH TIES] <columns>
FROM <table> [ORDER BY <column>]</column></columns></number>

177
Q

Q: What does the WITH TIES option in the TOP keyword do?

A

A: It includes all rows that have identical values in the ORDER BY column, even if this exceeds the specified limit.

178
Q

Q: How can the TOP keyword be used with a variable?

A

DECLARE @Percent INT = 2;
SELECT TOP(@Percent) PERCENT CustomerID, OrderDate
FROM Sales.SalesOrderHeader
ORDER BY SalesOrderID;

179
Q

Q: How can you use the TOP keyword to retrieve random rows?

A

A: Sort the rows by the NEWID() function:

SELECT TOP(2) CustomerID, OrderDate
FROM Sales.SalesOrderHeader
ORDER BY NEWID();

180
Q

Q: Why is it recommended to use OFFSET and FETCH instead of TOP for paging data?

A

A: OFFSET and FETCH offer more options, including the use of variables, and provide better control over data paging.

181
Q

Q: Provide an example of the TOP keyword with the PERCENT option.

A

SELECT TOP(10) PERCENT CustomerID, OrderDate
FROM Sales.SalesOrderHeader
ORDER BY SalesOrderID;

182
Q

Q: Provide an example of using TOP with WITH TIES.

A

SELECT TOP(2) WITH TIES CustomerID, OrderDate
FROM Sales.SalesOrderHeader
ORDER BY OrderDate;

183
Q

Q: What is the difference between an index seek and an index scan?

A

Index Seek: Efficiently retrieves specific rows using an index.

Index Scan: Reads all rows in an index, which can be slower.

184
Q

Q: How can using a function in the WHERE clause affect query performance?

A

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.

185
Q

Q: What is the purpose of the following code?

CREATE NONCLUSTERED INDEX [DEMO_SalesOrderHeader_OrderDate]
ON [Sales].[SalesOrderHeader] ([OrderDate] ASC);

A

A: It creates a nonclustered index on the OrderDate column to improve query performance for searches on this column.

186
Q

Q: How can you remove the index created on the OrderDate column?

A

DROP INDEX [DEMO_SalesOrderHeader_OrderDate] ON [Sales].[SalesOrderHeader];

187
Q

Q: Why does Query 1 perform better than Query 2 in the example?

A

A: Query 1 uses an index seek by directly comparing OrderDate, while Query 2 uses the YEAR function, requiring an index scan.

188
Q

Q: What is the estimated subtree cost in an execution plan?

A

A: It represents the estimated amount of resources required to execute the query, useful for comparing query performance.

189
Q

Q: How does the absence of an index affect Query 1 in the example?

A

A: Without the index, Query 1 performs a table scan, increasing the query’s resource consumption.

190
Q

Q: Why doesn’t the execution plan suggest an index for Query 2 when the index is removed?

A

A: Because an index wouldn’t help optimize Query 2 due to the use of the YEAR function on the indexed column.

191
Q

Q: How can you rewrite this query to improve performance?

SELECT SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2011;

A

SELECT SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader
WHERE OrderDate >= ‘2011-01-01 00:00:00’ AND OrderDate < ‘2012-01-01 00:00:00’;

192
Q
A
193
Q
A
194
Q

Q: Why is it important to test query performance in a realistic environment?

A

A: Small databases may not show significant performance differences, but large databases with millions of rows will highlight inefficiencies.

194
Q

Q: What should you consider beyond just getting correct results when writing queries?

A

A: Performance optimization, including minimizing resource consumption and ensuring efficient use of indexes.

Data Analyst (6 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions