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