Chapter 10 - Selected Single Row Functions

Why does the function NVL(shipdate, 'Not Shipped') return an error message?

Because it is a date, it won't allow 'Not Shipped'. The argument has to be a date.

What's the name of the table provided by Oracle 12c for completing queries that don't involve a table? DUMDUM DUAL ORAC SYS

DUAL

Which of the following functions can't be used to convert the letter case of a character string? UPPER LOWER INITIALCAP All of the above can be used for case conversion.

INITIALCAP

Which of the following format elements causes months to be displayed as a three-letter abbreviation? MMM MONTH MON none of the above

MON

Which of the following functions can produce different results, depending on the value of a specified column? NVL DECODE UPPER SUBSTR

NVL

Which of the following functions can be used to display the character string FLORIDA in the query results whenever FL is entered in the State field? SUBSTR NVL2 REPLACE TRUNC none of the above

REPLACE

What functions can be used to search character strings for specific patterns of data?

REPLACE and TRANSLATE

Which of the following SQL statements produces "Hello World" as the output? SELECT "Hello World" FROM dual; SELECT INITCAP('HELLO WORLD') FROM dual; SELECT LOWER('HELLO WORLD') FROM dual; both a and b none of the above

SELECT INITCAP('HELLO WORLD') FROM dual;

Which of the following SELECT statements returns 30 as the result? SELECT ROUND(24.37, 2) FROM dual; SELECT TRUNC(29.99, 2) FROM dual; SELECT ROUND(29.01, -1) FROM dual; SELECT TRUNC(29.99, -1) FROM dual;

SELECT ROUND(29.01, -1) FROM dual;

Which of the following lists only the last four digits of the contact person's phone number at American Publishing? SELECT EXTRACT(phone, -4, 1) FROM publisher WHERE name = 'AMERICAN PUBLISHING'; SELECT SUBSTR(phone, -4, 1) FROM publisher WHERE name = 'AMERICAN PUBLISHING'; SELECT EXTRACT(phone, -1, 4) FROM publisher WHERE name = 'AMERICAN PUBLISHING'; SELECT SUBSTR(phone, -4, 4) FROM publisher WHERE name = 'AMERICAN PUBLISHING';

SELECT SUBSTR(phone, -4, 4) FROM publisher WHERE name = 'AMERICAN PUBLISHING';

Which of the following is a valid SQL statement? SELECT SYSDATE; SELECT UPPER(Hello) FROM dual; SELECT TO_CHAR(SYSDATE, 'Month DD, YYYY') FROM dual; all of the above none of the above

SELECT TO_CHAR(SYSDATE, 'Month DD, YYYY') FROM dual;

Which of the following SQL statements is not valid? SELECT TO_CHAR(orderdate, '99/9999') FROM orders; SELECT INITCAP(firstname), UPPER(lastname) FROM customers; SELECT cost, retail, TO_CHAR(retail-cost, '$999.99') profit FROM books; all of the above

SELECT TO_CHAR(orderdate, '99/9999') FROM orders;

Which of the following displays the order date for order 1000 as 03/31? SELECT TO_CHAR(orderdate, 'MM/DD') FROM orders WHERE order# = 1000; SELECT TO_CHAR(orderdate, 'Mth/DD') FROM orders WHERE order# = 1000; SELECT TO_CHAR(orderdate, 'MONTH/YY') FROM orders WHERE order# = 1000; both a and b none of the above

SELECT TO_CHAR(orderdate, 'MM/DD') FROM orders WHERE order# = 1000;

Which of the following is a valid SQL statement? SELECT TRUNC(ROUND(125.38, 1), 0) FROM dual; SELECT ROUND(TRUNC(125.38, 0) FROM dual; SELECT LTRIM(LPAD(state, 5, ' '), 4, -3, "*") FROM dual; SELECT SUBSTR(ROUND(14.87, 2, 1), -4, 1) FROM dual;

SELECT TRUNC(ROUND(125.38, 1), 0) FROM dual;

Which of the following determines how long ago orders that haven't shipped were received? SELECT order#, shipdate-orderdate delay FROM orders; SELECT order#, SYSDATE - orderdate FROM orders WHERE shipdate IS NULL; SELECT order#, NVL(shipdate, 0) FROM orders WHERE orderdate is NULL; SELECT order#, NULL(shipdate) FROM orders;

SELECT order#, SYSDATE - orderdate FROM orders WHERE shipdate IS NULL;

Which of the following functions can be used to extract a portion of a character string? EXTRACT TRUNC SUBSTR INITCAP

SUBSTR

Why are functions in this chapter referred to as "single-row" functions?

Single-row functions return one row of results for each record processed.

Describe a situation that calls for using the DECODE function.

The DECODE function takes a specified value and compares it to values in a list. If a match is found, the specified result is returned. If no match is found, a default result is returned. Ex: JustLee Books is required to collect sales tax from customers who live in Florida and California but not from customers in other states. Suppose Florida's sales tax is 7%, and California's is 8%. So if a customer resides in California, he or she must pay 8% of the total order price as sales tax; if the customer lives in Florida, he or she must pay 7% sales tax; if the customer lives in any other state, no sales tax is paid. DECODE(state, 'CA', .08, 'FL', .07, 0) "State Sales Tax"

How is the TRUNC function different from the ROUND function?

The ROUND function rounds data after the specified position, and the TRUNC function drops any data after the specified position.

What's the difference between the TO_CHAR and TO_DATE functions when working with date values?

The TO_DATE function is of particular interest to application developers. Many database users are uncomfortable entering a date in the default DD-MON-YY format because they're more accustomed to entering dates as MM/DD/YY or Month DD, YYYY. The TO_DATE function allows users to enter a date in any format, and then it converts the entry into the default format used by Oracle 12c. The syntax of the TO_DATE function is TO_DATE ('d', 'f'), where d represents the date entered by the user, and f is the formatting instruction for the date. Ex: TO_DATE('March 18, 2021', 'Month DD, YYYY') The TO_CHAR function is often used to convert dates and numbers to a formatted character string. It's the opposite of the TO_DATE function for handling date data discussed previously. The TO_DATE function allows you to enter a date in any type of format and use the format argument to read the value as a date. The TO_CHAR function, on the other hand, is used to have Oracle 12c display dates in a particular format. The syntax of the TO_CHAR function is TO_CHAR(n, 'f'), where n is the date or number to format, and f is the formatting instruction to use. Ex: TO_CHAR(shipdate, 'Month DD, YYYY')

What's the difference between using the CONCAT function and the concatenation operator (||) in a SELECT clause?

The main difference between the concatenation operator and the CONCAT function is that you can combine a long list of columns and string literals with the concatenation operator; by contrast, you can combine only two items (columns or string literals) with the CONCAT function. Ex: CONCAT('Customer Number: ', customer#) "Customer Number"

Which of the following SQL statements displays a customer's name in all uppercase characters? SELECT UPPER('firstname', 'lastname') FROM customers; SELECT UPPER(firstname, lastname) FROM customers; SELECT UPPER(lastname, ',' firstname) FROM customers; none of the above

none of the above

Which of the following is not a valid format argument for displaying the current time? 'HH:MM:SS' 'HH24:SS' 'HH12:MI:SS' All of the above are valid.

'HH:MM:SS'

What format model should you use to display the date 25-DEC-09 as Dec. 25?

'Mon. DD'

Which functions can be used to convert the letter case of character values?

LOWER, UPPER, INITCAP

Which function can be used to add spaces to a column until it's a specific width? TRIML PADL LWIDTH none of the above

PADL

What's the difference between the NVL and NVL2 functions?

You can use the NVL function to address problems caused when performing arithmetic operations with fields that might contain NULL values. (Recall that a NULL value is the absence of data, not a blank space or a zero.) When a NULL value is used in a calculation, the result is always a NULL value. The NVL function is used to substitute a value for the existing NULL so that the calculation can be completed. The syntax of the NVL function is NVL(x, y), where y represents the value to substitute if x is NULL. In many cases, the substitute for a NULL value in a calculation is zero (0). The NVL2 function is a variation of the NVL function with different options based on whether a NULL value exists. The syntax of the NVL2 function is NVL2(x, y, z), where y represents what should be substituted if x isn't NULL, and z represents what should be substituted if x is NULL. This variation gives you a little more flexibility when working with NULL values. Ex: NVL2(shipdate, 'Shipped', 'Not Shipped') "Status"

If an integer is multiplied by a NULL value, the result is: an integer a whole number a NULL value None of the above—a syntax error message is returned.

a NULL value

Which of the following functions can be used to substitute a value for a NULL value? NVL TRUNC NVL2 SUBSTR both a and d both a and c

both a and c