Subqueries, Functions - Week 5
If CustomerAddress contains " 178 E Center Street ", what will the Solution column contain when this code is executed? LEN(LTRIM(RTRIM(CustomerAddress))) AS Solution a. 19 b. 23 c. 21 d. 16
19
When you use weekday with the DATEPART function, it returns an integer that indicates the day of the week where a. 1=Sunday, 2=Monday, etc. b. 7=Sunday, 1=Monday, etc. c. 0=Sunday, 1=Monday, etc. d. none of the above
1=Sunday, 2=Monday, etc.
If InvoiceTotal contains a value of 250.00, what will the Solution column contain when this code is executed? CASE WHEN InvoiceTotal > 500 THEN InvoiceTotal - ROUND(InvoiceTotal * .20, 2) WHEN InvoiceTotal >= 250 THEN InvoiceTotal - ROUND(InvoiceTotal * .10, 2) ELSE 0 END AS Solution a. 200.00 b. 0 c. 25 d. 225.00
225.00
If ExpirationDate contains a value that's equivalent to June 2, 2016 and the GetDate function returns a value that's equivalent to July 17, 2016, what will the Solution column contain when this code is executed? DATEDIFF(day, ExpirationDate, GetDate()) AS Solution a. 30 b. 15 c. 1 d. 45
45
If RegistrationDate contains a value that's equivalent to August 10, 2016, what will the Solution column contain when this code is executed? DATEPART(month, RegistrationDate) AS Solution a. Aug b. August c. August, 2016 d. 8
8
If a string consists of one or more components, you can parse it into its individual components. To locate the characters that separate the components, you would use which function? a. SUBSTRING b. PATINDEX c. REVERSE d. CHARINDEX
CHARINDEX
To locate the index of the first character of the first occurence of a substring within another string, you would use which function? a. PATINDEX b. REVERSE c. CHARINDEX d. SUBSTRING
CHARINDEX
Which statements are true about the ROUND function? a. To truncate the function rather than round it, code a zero value for the function d. If the length is positive, the digits to the left of the decimal point are rounded c. It returns the number rounded to the specified precision d. all of the above
It returns the number rounded to the specified precision
Which function would you use to retrieve data from a subsequent row in a result set? And which function would you use to retrieve data from a previous row? a. LEAD, LAG b. LTRIM, RTRIM c. LEFT, RIGHT d. FIRST, LAST
LEAD, LAG
After locating the characters that separate the components of a string you wish to parse, you can use which functions to extract the individual components? a. LEFT, RIGHT, SUBSTRING, and LEN b. REPLACE, REVERSE, CONCAT, and SUBSTRING c. LEFT, RIGHT, CONCAT, and LEN d. LTRIM, RTRIM, SUBSTRING, and CONCAT
LEFT, RIGHT, SUBSTRING, and LEN
Which function returns the string with any leading spaces removed? a. LEFT b. REPLACE c. RTRIM d. LTRIM
LTRIM
Which function would you use to calculate the rank of the values in a sorted set of values as a percent? a. PERCENTILE_CONT b. PERCENTILE_DIST c. PERCENT_DIST d. PERCENT_RANK
PERCENT_RANK
Which function returns the specified number of characters from the string starting at the specified position? a. RIGHT b. SPACE c. SUBSTRING d. REPLACE
SUBSTRING
The IIF function determines the value it returns based on what type of expression? a. conditional b. string c. numeric d. none of the above
conditional
If FirstName contains "Edward" and LastName contains "Williams", what will the Solution column contain when this code is executed? LOWER(LEFT(FirstName,1) + LEFT(LastName,7)) AS Solution a. EWilliam b. ewilliam c. ewilliams d. EWilliams
ewilliam
The COALESCE function provides one way to substitute constant values for which values? a. string b. null c. decimal d. real
null
The GROUPING function lets you substitute another value for a/an ____________________ value when you use the ROLLUP or CUBE operator. a. string b. decimal c. null d. date
null
The ranking functions make it easy to include a column in a result set that provides the sequential ranking number of each row within a ___________________________. a. clause b. partition c. subquery d. function
partition
If ZipCode is a varchar column that contains the value 93702, what will the Solution column evaluate to? ISNUMERIC(ZipCode) AS Solution a. false b. null c. true d. none
true
Which of the following is not a valid way to avoid search problems when you want to search for rows that have a specific date in a column that's defined with the datetime data type and which might include time values? a. use the DatePart function to extract just the date from each datetime value b. search for datetime values that fall between the day before and the day after the date you want c. use the Day, Month, and Year functions to do the search d. convert each datetime value to a date value
use the DatePart function to extract just the date from each datetime value