SQL Chap 3 - Single Row Functions

Chapter 3 - Single-Row Functions
OCA/OCP: Introduction to Oracle9i SQL Study Guide
by Chip Dawes and Biju Thomas


Review Questions

1. You want to display each project's start date as the day, week, number, and year. Which statement will give output like the following?

Tuesday Week 23, 2002

A. Select proj_id, to_char(start_date, 'DOW Week WOY YYYY') from projects
B. Select proj_id, to_char(start_date,'Day'||' Week'||' WOY, YYYY') from projects;
C. Select proj_id, to_char(start_date,'Day" Week" WW, YYYY') from projects;
D. Select proj_id, to_char(start_date,'Day Week# , YYYY') from projects;
E. You can't calculate week numbers with Oracle.
------

Double quotation marks must surround literal strings like "Week".
Ans: C


2. What will the following statement return?


SELECT last_name, first_name, start_date
FROM employees
WHERE hire_date < TRUNC(SYSDATE) - 5;

A. Employees hired within the past 5 years
B. Employees hired within the past 5 days
C. Employees hired more than 5 years ago
D. Employees hired more than 5 days ago
-------
The TRUNC function removes the time portion of a date by default, and whole numbers added to or subtracted from dates represent days added or subtracted from that date. TRUNC(SYSDATE) -5 means five days ago at midnight.

Ans: D


3. Which assertion about the following statements is most true?


SELECT name, region_code||phone_number
FROM customers;
SELECT name, CONCAT(region_code,phone_number)
FROM customers;

A. If the REGION_CODE is NULL, the first statement will not include that customer's PHONE_NUMBER.
B. If the REGION_CODE is NULL, the second statement will not include that customer's PHONE_NUMBER.
C. Both statements will return the same data.
D. The second statement will raise an exception if the REGION_CODE is NULL for any customer.
-----
Ans: C. Both statements are equivalent.

Notes by Denis:
Only the functions CONCAT, DECODE, DUMP, NVL, NVL2, and REPLACE can return non-NULL values when called with a NULL argument.


4. Which single-row function could you use to return a specific portion of a character string?


A. INSTR
B. SUBSTR
C. LPAD
D. LEAST
----

Ans: B. INSTR returns a number. LPAD adds to a character string. LEAST does not change an input string.
INSTR - Finds the numeric starting position of a string within a string
LPAD - Left-fills a string to a set length using a specified character



5. The Sales department is simplifying the pricing policy for all products.
All surcharges are being incorporated into the base price for all products in the consumer division (code C),
and the new base price is increasing by the lesser of 0.5 percent of the old base price or
10 percent of the old surcharge. Using the PRODUCT table described below, you need to implement this change.

-----------------------------------------------------------
Column Name   sku    name     division base_price surcharge
------------------------------------------------------------
Key Type      pk
NULLs/Unique  NN     NN       NN       NN
FK Table 
Datatype      NUMBER VARCHAR2 VARCHAR2 NUMBER     NUMBER
Length        16     16       4        11,2       11,2
-----------------------------------------------------------

Which of the following statements will achieve the desired results?

A. 
UPDATE product SET
   base_price = base_price + surcharge +
       LEAST(base_price * 0.005
            ,surcharge * 0.1)
,surcharge = NULL
WHERE division='C'

B.
UPDATE product SET
 base_price = base_price + NVL(surcharge,0) +
       LEAST(base_price * 0.005
            ,surcharge * 0.1)
,surcharge = NULL
WHERE division='C'

C.
UPDATE product SET
base_price = base_price + NVL(surcharge,0) +
  COALESCE(LEAST(base_price*0.005
                 ,surcharge * 0.1)
           ,base_price * 0.005)
,surcharge = NULL
WHERE division='C'


D.  A, B, and C will all achieve the desired results.
E.  None of these statements will achieve the desired results.

----

Statements A and B do not account for NULL surcharges correctly and will set the base price to NULL where the surcharge is NULL. In statement C, the LEAST function will return a NULL if surcharge is NULL, in which case the BASE_PRICE * 0.005 would be added.
Ans:C

Notes by Denis:
COALESCE returns the first non-null expr in the expression list. At least one expr must not be the literal NULL. If all occurrences of expr evaluate to null, then the function returns null.


6. Which function(s) accept arguments of any datatype? (Choose all that apply.)

A. SUBSTR
B. NVL
C. ROUND
D. DECODE
E. SIGN

ROUND does not accept character arguments. SUBSTR accepts only character arguments. SIGN accepts only numeric arguments.
Ans: B, D


7. What will be returned by SIGN(ABS(NVL(-32,0)))?

A. 1
B. 32
C. -1
D. 0
E. NULL
----
Ans: A. The functions are evaluated from the innermost to outermost, as follows:
SIGN(ABS(NVL(-32,0))) = SIGN(ABS(-32)) = SIGN(32) = 1

Notes:
SIGN returns -1 if n<0. If n=0, then the function returns 0. If n>0, then SIGN returns 1.


8. One of your database users asked you to provide a command that will show her the NLS_DATE_FORMAT that is currently set in her session. Which command would you recommend?


A. SELECT SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT') FROM dual;
B. SELECT SYS_CONTEXT('NLS_DATE_FORMAT') FROM dual;
C. SELECT SYS_CONTEXT('NLS_DATE_FORMAT','USERENV') FROM dual;
D. SELECT NLS_DATE_FORMAT FROM dual;
----
The syntax for the SYS_CONTEXT function requires that the first argument be the namespace and the second argument be the parameter. There is no pseudo-column NLS_DATE_FORMAT, so it cannot be selected from DUAL.

Ans: A.


9. Which two functions could you use to strip leading characters from a character string?


A. LTRIM
B. SUBSTR
C. RTRIM
D. INSTR
E. MOD
----

Ans: A, B. RTRIM removes trailing (not leading) characters. The others return numbers.


10. You have been asked to randomly assign 25 percent of the employees to a new training program. Employee numbers are assigned as consecutive numbers to the employees. Which statement below will print the employee number and name of every fourth employee?


A.
SELECT MOD(empno, 4), ename
FROM employees
WHERE MOD(empno,4) = 0;

B.
SELECT empno, ename
FROM employees
WHERE MOD(empno, 4) = .25;

C.
SELECT MOD(empno, 4) ename
FROM employees
WHERE MOD(empno, 4) = 0;

D.
SELECT empno, ename
FROM employees
WHERE MOD(empno, 4) = 0;
-----

MOD returns the number remainder after division. Answers A and C don't return the employee number, and MOD(empno,4) won't return a decimal.

Ans: D.


11. Which function will convert the ASCII code 97 to its equivalent letter a?

A. ASC(97)
B. ASCIISTR(97)
C. ASCII(97)
D. CHR(97)
----
The CHR function converts an ASCII code to a letter. ASC does the inverse, converting a letter into its ASCII code. ASCIISTR converts a string to its ASCII equivalent. There is no ASCII function.
Ans: D.


12. Which date components does the CURRENT_TIMESTAMP function display?


A. Session date, session time, and session time zone offset
B. Session date and session time
C. Session date and session time zone offset
D. Session time zone offset
----
The CURRENT_TIMESTAMP function returns the session date, session time, and session time zone offset.
Ans: A.


13. Using the SALESPERSON_REVENUE table described below, which statements will properly display the TOTAL_REVENUE (CAR_SALES + WARRANTY_SALES) of each salesperson?

--------------------------------------------------------
Column Name   salesperson_id car_sales warranty_sales
--------------------------------------------------------
Key Type      pk
NULLs/Unique  NN             NN
FK Table 
Datatype      NUMBER         NUMBER    NUMBER 
Length        11,2           11,2      11,2
--------------------------------------------------------
 
A.  SELECT salesperson_id,car_sales,warranty_sales
      ,car_sales + warranty_sales total_sales
    FROM salesperson_revenue;

B.  SELECT salesperson_id,car_sales,warranty_sales
      ,car_sales + NVL2(warranty_sales,0) total_sales
    FROM salesperson_revenue;

C.  SELECT salesperson_id,car_sales,warranty_sales
      ,NVL2(warranty_sales, car_sales
      + warranty_sales, car_sales) total_sales
    FROM salesperson_revenue;

D.  SELECT salesperson_id,car_sales,warranty_sales
      ,car_sales + COALESCE(car_sales, warranty_sales,
      car_sales + warranty_sales) total_sales
    FROM salesperson_revenue;

-------

Option A will result in NULL TOTAL_SALES for rows where there are NULL WARRANTY_SALES. Option B is not the correct syntax for NVL2, because it requires three arguments. With option C, if WARRANTY_SALES is NULL, then CAR_SALES is returned; otherwise, CAR_SALES+WARRANTY_SALES is returned. The COALESCE function returns the first non-NULL argument and could be used to obtain the desired results, but the first argument here is CAR_SALES, which is not NULL, and therefore COALESCE will always return CAR_SALES.
Ans: C

14. Which function could be used to return the IP address for the machine where the client session connected from?

A. COOKIE
B. NETINFO
C. SYS_CONTEXT
D. SYS_CONNECT_BY_PATH
---

The COOKIE and NETINFO functions do not exist. The SYS_CONTEXT function returns session information, and one of the parameters in the USERENV namespace is IP_ADDRESS, which returns the IP address for the machine where the client connected from. The SYS_CONNECT_BY_PATH function is used for CONNECT BY (hierarchical) queries.
Ans: C


15. In Oracle, what do trigonometric functions operate on?


A. Degrees
B. Radians
C. Gradients
D. The default is radians, but degrees or gradients can be specified.
---

Oracle trigonometric functions operate only on radians.
Ans: B


16. What will the following SQL statement return?


SELECT COALESCE(NULL,'Oracle ','Certified') FROM dual;

A. NULL
B. Oracle
C. Certified
D. Oracle Certified
---

Ans: B. The COALESCE function returns the first non-NULL parameter, which is the character string 'Oracle '.


17. Which expression will always return the date one year later than the current date?


A. SYSDATE + 365
B. SYSDATE + TO_YMINTERVAL('01-00')
C. CURRENT_DATE + 1
D. NEW_TIME(CURRENT_DATE,1,'YEAR')
-----

Option A will not work if there is a Feb 29 (leap year) in the next 365 days. Option B will always add one year to the present date. Option C will return the date one day later. NEW_TIME is used to return the date/time in a different time zone.
Ans: B.


18. Which function will return a TIMESTAMP WITH TIME ZONE datatype?

A. CURRENT_TIMESTAMP
B. LOCALTIMESTAMP
C. CURRENT_DATE
D. SYSDATE
----
LOCALTIMESTAMP does not return the time zone. CURRENT_DATE and SYSDATE return neither fractional seconds nor a time zone.
Ans: A.


19. Which statement would change all occurrences of the string 'IBM' to the string 'SUN' in the DESCRIPTION column of the VENDOR table?

A. SELECT TRANSLATE(description, 'IBM', 'SUN') FROM vendor
B. SELECT CONVERT(description, 'IBM', 'SUN') FROM vendor
C. SELECT EXTRACT(description, 'IBM', 'SUN') FROM vendor
D. SELECT REPLACE(description, 'IBM', 'SUN') FROM vendor
----
CONVERT is used to change from one character set to another. EXTRACT works on date/time datatypes. TRANSLATE changes all occurrences of each character with a positionally corresponding character, so 'I like IBM' would become 'S like SUN'.
Ans: D


20. Which function implements IF..THEN ELSE logic?


A. INITCAP()
B. REPLACE()
C. DECODE()
D. IFELSE()

------

The INITCAP function capitalizes the first letter in each word. The REPLACE function performs search-and-replace string operations. There is no IFELSE function. The DECODE function is the one that implements IF...THEN...ELSE logic.
Ans: C