Review Questions – Chapter 3
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.
| |||||||||
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
| |||||||||
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.
| |||||||||
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
| |||||||||
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?
base_price = base_price + surcharge +
LEAST(base_price * 0.005
,surcharge * 0.1)
,surcharge = NULL
WHERE division='C'
base_price = base_price + NVL(surcharge,0) +
LEAST(base_price * 0.005
,surcharge * 0.1)
,surcharge = NULL
WHERE division='C'
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.
| ||||||||||
6.
|
Which function(s) accept arguments of any datatype? (Choose all that apply.)
A. SUBSTR
B. NVL
C. ROUND
D. DECODE
E. SIGN
| |||||||||
7.
|
What will be returned by SIGN(ABS(NVL(-32,0)))?
A. 1
B. 32
C. -1
D. 0
E. NULL
| |||||||||
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;
| |||||||||
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
| |||||||||
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?
FROM employees
WHERE MOD(empno,4) = 0;
FROM employees
WHERE MOD(empno, 4) = .25;
FROM employees
WHERE MOD(empno, 4) = 0;
FROM employees
WHERE MOD(empno, 4) = 0;
| |||||||||
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)
| |||||||||
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
| |||||||||
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
| |||||||
,car_sales + warranty_sales total_sales
FROM salesperson_revenue;
,car_sales + NVL2(warranty_sales,0) total_sales
FROM salesperson_revenue;
,NVL2(warranty_sales, car_sales
+ warranty_sales, car_sales) total_sales
FROM salesperson_revenue;
,car_sales + COALESCE(car_sales, warranty_sales,
car_sales
+ warranty_sales) total_sales
FROM salesperson_revenue;
| ||||||||||
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
| |||||||||
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.
| |||||||||
16.
|
What will the following SQL statement return?
SELECT COALESCE(NULL,'Oracle ','Certified') FROM dual;
A. NULL
B. Oracle
C. Certified
D. Oracle Certified
| |||||||||
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')
| |||||||||
18.
|
Which function will return a TIMESTAMP WITH TIME ZONE datatype?
A. CURRENT_TIMESTAMP
B. LOCALTIMESTAMP
C. CURRENT_DATE
D. SYSDATE
| |||||||||
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
| |||||||||
20.
|
Which function implements IF..THEN…ELSE logic?
A. INITCAP()
B. REPLACE()
C. DECODE()
D. IFELSE()
| |||||||||
Answers
1.
|
C. Double quotation marks must surround literal strings like "Week".
|
2.
|
D. 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.
|
3.
|
C. Both statements are equivalent.
|
4.
|
B. INSTR returns a number. LPAD adds to a character string. LEAST does not change an input string.
|
5.
|
C. 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.
|
6.
|
B, D. ROUND does not accept character arguments. SUBSTR accepts only character arguments. SIGN accepts only numeric arguments.
|
7.
|
A. The functions are evaluated from the innermost to outermost, as follows:
SIGN(ABS(NVL(-32,0))) = SIGN(ABS(-32)) = SIGN(32) = 1
|
8.
|
A. 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.
|
9.
|
A, B. RTRIM removes trailing (not leading) characters. The others return numbers.
|
10.
|
D. 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.
|
11.
|
D. 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.
|
12.
|
A. The CURRENT_TIMESTAMP function returns the session date, session time, and session time zone offset.
|
13.
|
C. 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.
|
14.
|
C. 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.
|
15.
|
B. Oracle trigonometric functions operate only on radians.
|
16.
|
B. The COALESCE function returns the first non-NULL parameter, which is the character string 'Oracle '.
|
17.
|
B. 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.
|
18.
|
A. LOCALTIMESTAMP does not return the time zone. CURRENT_DATE and SYSDATE return neither fractional seconds nor a time zone.
|
19.
|
D. 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 'I like SUN'.
|
20.
|
C. 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.
|
No comments:
Post a Comment