Is640 chapter 10

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

'Mon. DD'

MC 18 18. 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? a. SUBSTR b. NVL2 c. REPLACE d. TRUNC e. none of the above

18 c. c. REPLACE

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

19 b. B. DUAL

R2 2. What's the difference between the NVL and NVL2 functions?

? The NVL function is used to substitute a value for a NULL; the NVL2 function allows taking different actions, depending on the existence of a NULL value.

Syntax 15 ABS Returns the absolute value of a numeric value.

ABS(n) n = Numeric value

Syntax 18 ADD_ MONTHS Determines the number of months between two dates.

ADD_MONTHS(d, m) d = Date (beginning) for the calculation m = Months—number of months to add to the date

R8 8. Describe a situation that calls for using the DECODE function.

Answers will vary. Example: If a customer list by region is needed and region is assigned to customers based on the state they live in, DECODE could be used to identify the region based on the state value.

Searched CASE expression Evaluates a given value with conditions to determine a resulting value.

CASE WHEN V1 cond THEN R1 WHEN V2 cond THEN R2 ELSE D END V1 = First value evaluated cond = Condition to evaluate R1 = Result to return if cond for V1 is TRUE D = Default result to return if no cond is TRUE

Syntax 11 CONCAT Used to concatenate two data items.

CONCAT(c1, c2) c1 = First data item to concatenate c2 = Second data item to concatenate

DECODE Compares a given value 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. If no default result is defined, a NULL value is returned.

DECODE(V, L1, R1, L2, R2,..., D) V = Value to search for L1 = First value in the list R1 = Result to return if L1 and V match D = Default result to return if no match is found

Syntax 3 INITCAP Converts words to mixed case (with initial capital letters).

INITCAP(c) c = Character string or field to convert to mixed case

Syntax 5 INSTR Identifies the position of the search string.

INSTR(c, s, p, o) c = Character string to search s = Search string p = Search starting position o = Occurrence of search string to identify

Syntax 6 LENGTH Returns the numbers of characters in a string.

LENGTH(c) c = Character string to analyze

Syntax 1 LOWER Converts characters to lowercase letters.

LOWER(c) c = Character string or field to convert to lowercase

Syntax 7 LPAD and RPAD Pads, or fills in, the area to the left (or right) of a character string, using a specific character—or even a blank space.

LPAD(c, 1, s) c = Character string to be pad 1 = Length of character string after padding s = Symbol or character used as padding

Syntax 8 RTRIM and LTRIM Trims, or removes, a specific string of characters from the right (or left) of data.

LTRIM(c, s) c = Characters to modify s = String to be removed from the left of data

Mc 10 10. Which of the following functions can produce different results, depending on the value of a specified column? a. NVL b. DECODE c. UPPER d. SUBSTR

MC 10 b. b. DECODE

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

MC 11. a. a. SELECT TO_CHAR(orderdate, '99/9999') FROM orders;

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

MC 12. d. d. none of the above

Mc 13 13. Which of the following SELECT statements returns 30 as the result? a. SELECT ROUND(24.37, 2) FROM dual; b. SELECT TRUNC(29.99, 2) FROM dual; c. SELECT ROUND(29.01, -1) FROM dual; d. SELECT TRUNC(29.99, -1) FROM dual;

MC 13 c. c. SELECT ROUND(29.01, -1) FROM dual;

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

MC 14 a. a. SELECT TRUNC(ROUND(125.38, 1), 0) FROM dual;

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

MC 15. c. c. INITIALCAP

MC 16 16. Which of the following format elements causes months to be displayed as a three-letter abbreviation? a. MMM b. MONTH c. MON d. none of the above

MC 16. c c. MON

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

MC 17 d. d. none of the above

Mc2 2. Which of the following functions can be used to extract a portion of a character string? a. EXTRACT b. TRUNC c. SUBSTR d. INITCAP

MC 2 c. c. SUBSTR

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

MC 3. b. b. SELECT order#, SYSDATE -orderdate FROM orders WHERE shipdate IS NULL;

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

MC 6 a. a. 'HH:MM:SS'

Mc 8 8. Which of the following functions can be used to determine how many months a book has been available? a. MONTH b. MON c. MONTH_BETWEEN d. none of the above

MC 8 d. d. none of the above

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

MC 9 a. a. SELECT TO_CHAR(orderdate, 'MM/DD') FROM orders WHERE order# ¼ 1000;

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

MC4 b. b. SELECT INITCAP('HELLO WORLD') FROM dual;

Mc 5 5. Which of the following functions can be used to substitute a value for a NULL value? a. NVL b. TRUNC c. NVL2 d. SUBSTR e. both a and d f. both a and c

MC5. f. f. both a and c

Mc 7 7. Which of the following lists only the last four digits of the contact person's phone number at American Publishing? a. SELECT EXTRACT(phone, -4, 1) FROM publisher WHERE name ¼ 'AMERICAN PUBLISHING'; b. SELECT SUBSTR(phone, -4, 1) FROM publisher WHERE name ¼ 'AMERICAN PUBLISHING'; c. SELECT EXTRACT(phone, -1, 4) FROM publisher WHERE name ¼ 'AMERICAN PUBLISHING'; d. SELECT SUBSTR(phone, -4, 4) FROM publisher WHERE name ¼ 'AMERICAN PUBLISHING';

MC7 d. d. SELECT SUBSTR(phone, -4, 4) FROM publisher WHERE name ¼ 'AMERICAN PUBLISHING';

syntax 14 MOD Returns the remainder of a division operation.

MOD(n, d) n = Numerator d = Denominator

Syntax 17 MONTHS_ BETWEEN Determines the number of months between two dates.

MONTHS_BETWEEN(dl, d2) dl and d2 = Dates in question d2 is subtracted from dl

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

Mc 20. c. c. a NULL value

Syntax 20 NEXT_DAY Determines the next day—a specific day of the week after a given date.

NEXT_DAY(d, DAY) d = Date (starting) DAY = Day of the week to be identified

NULLIF Returns a NULL value if the given values equate; otherwise, returns the first given value. NULLIF(x, y) x and y ¼ Values to compare

NULLIF(x, y) x and y = Values to compare

Syntax 25 NVL Solves problems caused by performing arithmetic operations with fields that might contain NULL values. When a NULL value is used in a calculation, the result is a NULL value. The NVL function is used to substitute a value for the existing NULL.

NVL(x, y) y = Value to be substituted if x is NULL

Syntax 26 NVL2 Provides options based on whether a NULL value exists.

NVL2(x, y, z) y = What should be substituted if x is not NULL z = What should be substituted if x is NULL

Syntax 16 POWER Raises a number to a specified power.

POWER(x, y) x = Number to raise y = Power to which the number should be raised

Syntax 23 REGEXP_ LIKE Searches for pattern values in a character string.

REGEXP_LIKE(c, p) c = Character string p = Pattern operators

Syntax 24 REGEXP_ SUBSTR Extracts the part of a string that matches a pattern of values.

REGEXP_SUBSTR(c, p) c = Character string p = Pattern operators

Syntax 9 REPLACE Performs a search and replace of displayed results.

REPLACE(c, s, r) c = Data or column to search s = String of characters to find r = String of characters to substitute for s

Syntax 12 ROUND Rounds numeric fields.

ROUND(n, p) n = Numeric data or field to round p = Position to which the data should be rounded

Hoa 7 7. Determine the length of data stored in the ISBN field of the BOOKS table. Make sure each different length value is displayed only once (not once for each book).

SELECT DISTINCT LENGTH(isbn) FROM books

Hands on assignment 1 1. Produce a list of all customer names in which the first letter of the first and last names is in uppercase and the rest are in lowercase.

SELECT INITCAP(firstname), INITCAP(lastname) FROM customers;

Hoa 9 9. Determine the calendar date of the next occurrence of Wednesday, based on today's date.

SELECT NEXT_DAY(SYSDATE, 'WEDNESDAY') FROM dual;

Hoa 5 5. Display the current day of the week, hour, minutes, and seconds of the current date setting on the computer you're using.

SELECT TO_CHAR(CURRENT_DATE, 'DAY, HH:MI:SS') FROM dual;

Hoa 10 10. Produce a list of each customer number and the third and fourth digits of his or her zip code. The query should also display the position of the first occurrence of a 3 in the customer number, if it exists.

SELECT customer#, SUBSTR(zip, 3, 2), INSTR(customer#, 3) FROM customers;

Hoa 2 2. Create a list of all customer numbers along with text indicating whether the customer has been referred by another customer. Display the text "NOT REFERRED" if the customer wasn't referred to JustLee Books by another customer or "REFERRED" if the customer was referred.

SELECT firstname, lastname, NVL2(referred, 'REFERRED', 'NOT REFERRED') FROM customers;

Hoa 6 6. Create a list of all book titles and costs. Precede each book's cost with asterisks so that the width of the displayed Cost field is 12.

SELECT title, LPAD(cost, 12, '*') FROM books;

Hoa 4 4. Display a list of all book titles and the percentage of markup for each book. The percentage of markup should be displayed as a whole number (that is, multiplied by 100) with no decimal position, followed by a percent sign (for example, .2793 = 28%). (The percentage of markup should reflect the difference between the retail and cost amounts as a percent of the cost.)

SELECT title, ROUND((retail-cost)/cost *100, 0)||'%' FROM books;

Hoa 3 3. Determine the amount of total profit generated by the book purchased on order 1002. Display the book title and profit. The profit should be formatted to display a dollar sign and two decimal places. Take into account that the customer might not pay the full retail price, and each item ordered can involve multiple copies.

SELECT title, TO_CHAR(quantity*(paideach-cost), '$999.99') FROM books JOIN orderitems USING (isbn) WHERE order# = 1002;

Hoa 8 8. Using today's date, determine the age (in months) of each book that JustLee sells. Make sure only whole months are displayed; ignore any portions of months. Display the book title, publication date, current date, and age.

SELECT title, pubdate, SYSDATE, TRUNC(MONTHS_BETWEEN(SYSDATE, pubdate),0) Age FROM books;

SOUNDEX Converts alphabetic characters to their phonetic pronunciation, using an alphanumeric algorithm.

SOUNDEX(c) c = Characters to represent phonetically

Syntax 4 SUBSTR Returns a substring (portion of a string) in the output.

SUBSTR(c, p, 1) c = Character string P = Position (beginning) for the extraction 1 = Length of output string

TO_CHAR Converts dates and numbers to a formatted character string.

TO_CHAR(n, 'f') n = Number or date to format f = Format argument to use

Syntax 21 LAST_DAY Determines the last day of the month for the month of a given date.

TO_DATE(d, 'f') d = Date entered by the user f = Format argument to use

Syntax 22 TO_DATE Converts a date in a specified format to the default date format.

TO_DATE(d, 'f') d = Date entered by the user f = Format argument to use

TO_NUMBER Converts numeric digits stored in a date or character value to a number.

TO_NUMBER(v) v = Value to convert

Syntax 10 TRANSLATE Converts single characters to a substitution value.

TRANSLATE(c, s, r) c = Character string to search s = Search character r = Substitution character

Syntax 13 TRUNC Truncates, or cuts, numbers to a specific position.

TRUNC(n, p) n = Numeric data or field to truncate p = Position to which the data should be truncated

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

The LOWER, UPPER, and INITCAP functions

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

The NVL function doesn't allow substituting text for a date, and the Shipdate column is defined as a DATE datatype

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

The REPLACE, TRANSLATE, and REGEXP_LIKE functions involve searching a string value. The REGEXP_LIKE function enables searching for patterns, but the others allow searching only for a specific character string value.

R4 4. 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

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

The TO_CHAR function formats a date value for display, and the TO_DATE function changes a user-entered date value to the Oracle format so that it can be entered in the database.

Review question 1 1. Why are functions in this chapter referred to as "single-row" functions?

These functions return one row of results for each record processed.

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

To concatenate more than two columns or string literals, the CONCAT function must be nested inside other CONCAT functions. The concatenation operator can be used in a series to concatenate several columns or string literals.

Syntax 2 UPPER Converts characters to uppercase letters.

UPPER(c) c = Character string or field to convert to uppercase

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

mc 1. c c. SELECT TO_CHAR(SYSDATE, 'Month DD, YYYY') FROM dual;