Aco 2

Front 4

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, 2008


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.

Back 4

1. C. Double quotation marks must surround literal strings like .

Front 5

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 five hours

B. Employees hired within the past five days

C. Employees hired more than five hours ago

D. Employees hired more than five days ago

Back 5

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.

Front 6

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. I f REGION_CODE is NULL, the first statement will not include that customer’s PHONE_NUMBER.

B. I f 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 error if REGION_CODE is NULL for any customer.

Back 6

3. C. The two statements are equivalent.

Front 7

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

Back 7

4. B. SUBSTR returns part of the string. INSTR returns a number. LPAD adds to a character string. LEAST does not change an input string.

Front 8

5. The data in the PRODUCT table is as described here. The bonus amount is calculated as the lesser of 5 percent of the base price or 20 percent of
the surcharge.
sku name division base_price surcharge
1001 PROD-1001 A 200 50
1002 PROD-1002 C 250
1003 PROD-1003 C 240 20
1004 PROD-1004 A 320
1005 PROD-1005 C 225 40

Which of the following statements will achieve the desired results?
A. SELECT sku, name, LEAST(base_price * 1.05, surcharge * 1.2)FROM products;
B. SELECT sku, name, LEAST(NVL(base_price,0) * 1.05, surcharge * 1.2)FROM products;
C. SELECT sku, name, COALESCE(LEAST(base_price*1.05, surcharge * 1.2), base_price * 1.05)FROM products;
D. A, B, and C will all achieve the desired results.
E. None of these statements will achieve the desired results.

Back 8

5. C. Options A and B do not account for NULL surcharges correctly and will set the bonus to NULL where the surcharge is NULL. In option B, the NVL
function is applied to the base_price column instead of the surcharge column. In option C, the LEAST function will return a NULL if surcharge is NULL, in which
case BASE_PRICE * 1.05 would be returned from the COALESCE function.

Front 9

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

B. NVL

C. ROUND

D. DECODE

E. SIGN

Back 9

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

Front 10

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

B. 32

C. –1

D. 0

E. NULL

Back 10

7. A. The functions are evaluated from the innermost to outermost, as follows:
SIGN(ABS(NVL(-32,0))) = SIGN(ABS(-32)) = SIGN(32) = 1

Front 11

8. The SALARY table has the following data:
LAST_NAME FIRST_NAME SALARY
------------ -------------------- ----------
Mavris Susan 6500
Higgins Shelley 12000
Tobias Sigal
Colmenares Karen 2500
Weiss Matthew 8000
Mourgos Kevin 5800
Rogers Michael 2900
Stiles Stephen 3200


Consider the following SQL, and choose the best option:
SELECT last_name, NVL2(salary, salary, 0) N1,
NVL(salary,0) N2
FROM salary;


A. Column N1 and N2 will have different results.

B. Column N1 will show zero for all rows, and column N2 will show the correct salary values, and zero for Tobias.

C. The SQL will error out because the number of arguments in the NVL2 function is incorrect.

D. Columns N1 and N2 will show the same result.

Back 11

8. D. The NVL function returns zero if the salary value is NULL, or else it returns the original value. The NVL2 function returns the second argument if the
salary value is not NULL. I f NULL, the third argument is returned.

Front 12

9. Which two functions could you use to strip leading characters from a character string? (Choose two.)
A. LTRIM

B. SUBSTR

C. RTRIM

D. INSTR

E. STRIP

Back 12

9. A, B. RTRIM removes trailing (not leading) characters. INSTR returns a number. STRIP is not a valid Oracle function. SUBSTR with second argument
greater than 1 removes leading characters from a string.

Front 13

10. What is the result of MOD(x1, 4), if x1 is 11?

A. –1
B. 3
C. 1
D. REMAINDER(11,4)

Back 13

10. B. MOD returns the number remainder after division. The REMAINDER function is similar to MOD but will use the ROUND function in the algorithm; hence, the
result of REMAINDER(11,4) would be –1. MOD uses FLOOR in the algorithm.

Front 14

11. Which two SQL statements will replace the last two characters of last_name with 'XX' in the employees table when executed? (Choose two.)
A. SELECT RTRIM(last_name, SUBSTR(last_name, LENGTH(last_name)-1)) || 'XX' new_col FROM employees;

B. SELECT REPLACE(last_name, SUBSTR(last_name, LENGTH(last_name)-1), 'XX') new_col FROM employees;

C. SELECT REPLACE(SUBSTR(last_name, LENGTH(last_name)-1), 'XX') new_col FROM employees;

D. SELECT CONCAT(SUBSTR(last_name, 1,LENGTH(last_name)-2), 'XX') new_col FROM employees;

Back 14

11. A, D. The SUBSTR function in option A would return the last two characters of the last name. These two characters are right-trimmed using the RTRIM
function. The result would be the first portion of the last name and is concatenated to 'XX'. Option B also would do the same as A, but would replace
all the occurrences of the last two characters (Paululul will be PaXXXXXX instead of PaululXX). Option C would return only the last two characters of the last
name. The SUBSTR function in option D would return the first character through the last –2 characters. 'XX' is concatenated to the result.

Front 15

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

Back 15

12. A. The CURRENT_TIMESTAMP function returns the session date, session time, and session time zone offset. The return datatype is TIMESTAMP WITH
TIME ZONE.

Front 16

13. Using the SALESPERSON_REVENUE table described here, 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 10 11,2 11,2

A. SELECT salesperson_id, car_sales, warranty_sales, car_sales + warranty_sales total_salesFROM salesperson_revenue;

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

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

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

Back 16

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.

Front 17

14. What will be the result of executing the following SQL, if today’s date is February 28, 2009?
SELECT ADD_MONTHS('28-FEB-09', -12) from dual;


A. 28-FEB-10

B. 28-FEB-08

C. 29-FEB-08

D. 28-JAN-08

Back 17

14. C. The ADD_MONTHS function returns the date d plus i months. I f <d> is the last day of the month or the resulting month has fewer days, then the result
is the last day of the resulting month.

Front 18

15. Consider the following two SQL statements, and choose the best option:
1. SELECT TO_DATE('30-SEP-07','DD-MM-YYYY') from dual;
2. SELECT TO_DATE('30-SEP-07','DD-MON-RRRR') from dual;


A. Statement 1 will error; 2 will produce result.

B. The resulting date value from the two statements will be the same.

C. The resulting date value from the two statements will be different.

D. Both statements will generate an error.

Back 18

15. C. Statement 1 will result in 30-SEP-0007, and statement 2 will result in 30-SEP-2007. The RR and RRRR formats derive the century based on the curren
date if the century is not specified. The YY format will use the current century, and the YYYY format expects the century in the input.

Front 19

16. What will the following SQL statement return?
SELECT COALESCE(NULL,'Oracle ','Certified') FROM dual;


A. NULL

B. Oracle

C. Certified

D. Oracle Certified

Back 19

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

Front 20

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')

E. None of the above

Back 20

17. E. Option A will not work if there is a February 29 (leap year) in the next 365 days. Option B will always add one year to the present date, except
if the current date is February 29 (leap year). Option C will return the date one day later. NEW_TIME is used to return the date/time in a different time
zone. ADD_MONTHS (SYSDATE,12) can be used to achieve the desired result.

Front 21

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

B. LOCALTIMESTAMP

C. CURRENT_DATE

D. SYSDATE

Back 21

18. A. LOCALTIMESTAMP does not return the time zone. CURRENT_DATE and SYSDATE return neither fractional seconds nor a time zone; they both return the DATE
datatype.

Front 22

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

Back 22

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 'S like SUN'.

Front 23

20. Which function implements IF…THEN…ELSE logic?
A. INITCAP

B. REPLACE

C. DECODE

D. IFELSE

Back 23

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.

Front 24

1. How will the results of the following two statements differ?
Statement 1:
SELECT MAX(longitude), MAX(latitude)
FROM zip_state_city;

Statement 2:
SELECT MAX(longitude), MAX(latitude)
FROM zip_state_city
GROUP BY state;
A. Statement 1 will fail because it is missing a GROUP BY clause.
B. Statement 2 will return one row, and statement 1 may return more than one row.
C. Statement 2 will fail because it does not have the columns used in the GROUP BY clause in the SELECT cl
D. Statement 1 will display two columns, and statement 2 will display two values for each state.

Back 24

1. D. Though you do not have a state column in the SELECT clause, having it in the GROUP BY clause will group the results by state, so you end up getting
two values (two columns) for each state.

Front 25

2. Using the SALES table described here, you need to report the following:

Gross, net, and earned revenue for the second and third quarters of 1999
Gross, net, and earned revenue for sales in the states of I llinois, California, and Texas (codes IL, CA, and TX)
Column Name state_code sales_date gross net earned
Key Type PK PK
Nulls/Unique NN NN NN NN NN
FK Table
Datatype VARCHAR2 DATE NUMBER NUMBER NUMBER
Length 2 11,2 11,2 11,2

Will all the requirements be met with the following SQL statement?
SELECT state_code, SUM(ALL gross), SUM(net), SUM(earned)
FROM sales_detail
WHERE TRUNC(sales_date,'Q') BETWEEN
TO_DATE('01-Apr-1999','DD-Mon-YYYY')
AND TO_DATE('01-Sep-1999','DD-Mon-YYYY')
AND state_cd IN ('IL','CA','TX')
GROUP BY state_code;


A. The statement meets all three requirements.

B. The statement meets two of the three requirements.

C. The statement meets one of the three requirements.

D. The statement meets none of the three requirements.

E. The statement will raise an exception.

Back 25

2. A. All requirements are met. The gross-, net-, and earned-revenue requirements are satisfied with the SELECT clause. The second- and third-
quarter sales requirement is satisfied with the first predicate of the WHERE clause—the sales date will be truncated to the first day of a quarter; thus,
01-Apr-1999 or 01-Jul-1999 for the required quarters (which are both between 01-Apr-1999 and 01-Sep-1999). The state codes requirement is satisfied by thesecond predicate in the WHERE clause. This question is intentionally misleading, but so are some exam questions (and, unfortunately, some of the
code in some shops).

Front 26

3. Which line in the following SQL has an error?
1 SELECT department_id, SUM(salary)
2 FROM employees
3 WHERE department_id <> 40
4 ORDER BY department_id;


A. 1

B. 3

C. 4

D. No errors in SQL

Back 26

3. C. Since the department_id column does not have any aggregate function applied to it, it must appear in the GROUP BY clause. The ORDER BY clause in
the SQL must be replaced with a GROUP BY clause to make the query work.

Front 27

4. John is trying to find out the average salary of employees in each department. He noticed that the SALARY column can have NULL values, and he
does not want the NULLs included when calculating the average. Identify the correct SQL that will produce the desired results.
A. SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;

B. SELECT department_id, AVG(NVL(salary,0))
FROM employees
GROUP BY department_id;

C. SELECT department_id, NVL(AVG(salary), 0)
FROM employees
GROUP BY department_id;

D. SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING salary IS NOT NULL;

Back 27

4. A. Since group functions do not include NULL values in their calculation, you do not have to do anything special to exclude the NULL values. Only
COUNT(*) includes NULL values.

Front 28

5. Review the following two SQL statements, and choose the appropriate option.
1. SELECT department_id, COUNT(*)
FROM employees
HAVING COUNT(*) > 10
GROUP BY department_id;
2. SELECT department_id, COUNT(*)
FROM employees
WHERE COUNT(*) > 10
GROUP BY department_id;


A. Statement 1 and statement 2 will produce the same results.

B. Statement 1 will succeed, and statement 2 will fail.

C. Statement 2 will succeed, and statement 1 will fail.

D. Both statements fail.

Back 28

5. B. An aggregate function is not allowed in the WHERE clause. Y ou can have the GROUP BY and HAVING clauses in any order, but they must appear after
the WHERE clause.

Front 29

6. Read the following SQL carefully, and choose the appropriate option. The JOB_ID column shows the various jobs.
SELECT MAX(COUNT(*))
FROM employees
GROUP BY job_id, department_id;


A. Aggregate functions cannot be nested.

B. The columns in the GROUP BY clause must appear in the SELECT clause for the query to work.

C. The GROUP BY clause is not required in this query.

D. The SQL will produce the highest number of jobs within a department.

Back 29

6. D. The SQL will work fine and produce the result. Since group functions are nested, a GROUP BY clause is required.

Front 30

7. Identify the SQL that produces the correct result.
A. SELECT department_id, SUM(salary)
FROM employees
WHERE department_id <> 50
GROUP BY department_id
HAVING COUNT(*) > 30;

B. SELECT department_id, SUM(salary) sum_sal
FROM employees
WHERE department_id <> 50
GROUP BY department_id
HAVING sum_sal > 3000;

C. SELECT department_id, SUM(salary) sum_sal
FROM employees
WHERE department_id <> 50
AND sum_sal > 3000
GROUP BY department_id;

D. SELECT department_id, SUM(salary)
FROM employees
WHERE department_id <> 50
AND SUM(salary) > 3000
GROUP BY department_id;

Back 30

7. A. I t is perfectly alright to have one function in the SELECT clause and another function in the HAVING clause of the query. Options B and C are trying to
use the alias name, which is not allowed. Option D has a group function in the WHERE clause, which is also not allowed.

Front 31

8. Consider the following SQL, and choose the most appropriate option.
SELECT COUNT(DISTINCT SUBSTR(first_name, 1,1))
FROM employees;


A. A single-row function nested inside a group function is not allowed.

B. The GROUP BY clause is required to successfully run this query.

C. Removing the DISTINCT qualifier will fix the error in the query.

D. The query will execute successfully without any modification.

Back 31

8. D. The query will return how many distinct alphabets are used to begin names in the EMPLOYEES table. Y ou can nest a group function inside a single-
row function, and vice versa.

Front 32

9. The sales order number (ORDER_NO) is the primary key in the table SALES_ORDERS. Which query will return the total number of orders in the SALES_ORDERS
table?
A. SELECT COUNT(ALL order_no) FROM sales_orders;
B. SELECT COUNT(DISTINCT order_no) FROM sales_or
C. SELECT COUNT(order_no) FROM sales_orders;
D. SELECT COUNT(NVL(order_no,0) FROM sales_order
E. All of the above
F. A and C

Back 32

9. E. All the queries will return the same result. Since ORDER_NO is the primary key, there cannot be NULL values in the column. Hence, ALL and DISTINCT will
give the same result.

Front 33

10. Sheila wants to find the highest salary within each department of the EMPLOYEES table. Which query will help her get what she wants?
A. SELECT MAX(salary) FROM employees;
B. SELECT MAX(salary BY department_id) FROM employees;
C. SELECT department_id, MAX(salary) max_sal FROM employees;
D. SELECT department_id, MAX(salary) FROM employees GROUP BY department_id;
E. SELECT department_id, MAX(salary) FROM employees USING department_id;

Back 33

10. D. Option A will display the highest salary of all the employees. Options B and E use invalid syntax keywords. Option C does not have a GROUP BY
clause.