Oracle Database Programming with SQL Section 4
1. CASE and DECODE evaluate expressions in a similar way to IF-THEN-ELSE logic. However, DECODE is specific to Oracle syntax. True or False?
♦True (*)
♦False
♦False
2. For the given data from Employees (last_name, manager_id) what is the result of the following statement:
DATA:( King, null
Kochhar, 100
De Haan, 100
Hunold, 102
Ernst, 103)
SELECT last_name,
DECODE(manager_id, 100, ‘King’, ‘A N Other’) “Works For?”
FROM employees
DATA:( King, null
Kochhar, 100
De Haan, 100
Hunold, 102
Ernst, 103)
SELECT last_name,
DECODE(manager_id, 100, ‘King’, ‘A N Other’) “Works For?”
FROM employees
♦King, A N Other
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other
(*)
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other
(*)
♦King, A N Other
Kochhar, King
De Haan, King
Hunold, Kochhar
Ernst, De Haan
Kochhar, King
De Haan, King
Hunold, Kochhar
Ernst, De Haan
♦Invalid statement.
♦King, Null
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other
3. Which of the following is a conditional expression used in SQL?
♦NULLIF
♦WHERE
♦CASE (*)
♦DESCRIBE
♦WHERE
♦CASE (*)
♦DESCRIBE
4. A table has the following definition: EMPLOYEES(
EMPLOYEE_ID NUMBER(6) NOT NULL,
NAME VARCHAR2(20) NOT NULL,
MANAGER_ID VARCHAR2(6))
EMPLOYEE_ID NUMBER(6) NOT NULL,
NAME VARCHAR2(20) NOT NULL,
MANAGER_ID VARCHAR2(6))
and contains the following rows:
(1001, ‘Bob Bevan’, ‘200’)
(200, ‘Natacha Hansen’, null)
(200, ‘Natacha Hansen’, null)
Will the folloiwng query work?
SELECT *
FROM employees
WHERE employee_id = manager_id;
FROM employees
WHERE employee_id = manager_id;
♦Yes, Oracle will perform implicit datatype conversion, and the query will return one row of data.
♦Yes, Oracle will perform implicit datatype conversion, but the WHERE clause will not find any matching data. (*)
♦No.? You will have to re-write the statement and perform explicit datatype conversion.
♦ No, because the datatypes of EMPLOYEE_ID and MANAGER_ID are different.
5. If you use the RR format when writing a query using the date 27-Oct-17 and the year is 2001, what year would be the result?
♦1901
♦2017 (*)
♦2001
♦1917
♦2017 (*)
♦2001
♦1917
6. Which arithmetic operation will return a numeric value?
♦SYSDATE + 30 / 24
♦NEXT_DAY(hire_date) + 5
♦SYSDATE – 6
♦TO_DATE(’01-Jun-2004′) – TO_DATE(’01-Oct-2004′) (*)
♦NEXT_DAY(hire_date) + 5
♦SYSDATE – 6
♦TO_DATE(’01-Jun-2004′) – TO_DATE(’01-Oct-2004′) (*)
7. Which SQL Statement should you use to display the prices in this format: “$00.30”?
♦SELECT TO_CHAR(price, ‘$99,900.99’)
FROM product;
(*)
FROM product;
(*)
♦SELECT TO_NUMBER(price, ‘$99,900.99’)
FROM product;
FROM product;
♦SELECT TO_CHAR(price, ‘$99,990.99’)
FROM product;
FROM product;
♦SELECT TO_CHAR(price, ‘$99,999.99’)
FROM product;
FROM product;
8. Which two statements concerning SQL functions are true? (Choose two.)
(Choose all correct answers)
♦Single-row functions manipulate groups of rows to return one result per group of rows.
♦Conversion functions convert a value from one data type to another data type. (*)
♦Not all date functions return date values. (*)
♦Character functions can accept numeric input.
♦Number functions can return number or character values.
(Choose all correct answers)
♦Single-row functions manipulate groups of rows to return one result per group of rows.
♦Conversion functions convert a value from one data type to another data type. (*)
♦Not all date functions return date values. (*)
♦Character functions can accept numeric input.
♦Number functions can return number or character values.
9. You need to display the HIRE_DATE values in this format: 25th of July 2002. Which SELECT statement would you use?
♦SELECT TO_CHAR(hire_date, ‘DDspth ‘of’ Month RRRR’)
FROM employees;
FROM employees;
♦SELECT TO_CHAR(hire_date, ‘DDTH “of” Month YYYY’)
FROM employees;
FROM employees;
♦SELECT TO_CHAR(hire_date, ‘ddth “of” Month YYYY’)
FROM employees;
(*)
FROM employees;
(*)
♦SELECT enroll_date(hire_date, ‘DDspth “of” Month YYYY’)
FROM employees;
FROM employees;
10. Which function compares two expressions?
♦NVL
♦NVL2
♦NULL
♦NULLIF (*)
♦NVL2
♦NULL
♦NULLIF (*)
11. Which of the following General Functions will return the first non-null expression in the expression list?
♦NULLIF
♦COALESCE (*)
♦NVL2
♦NVL
♦COALESCE (*)
♦NVL2
♦NVL
12. When executed, which statement displays a zero if the TUITION_BALANCE value is zero and the HOUSING_BALANCE value is null?
♦SELECT NVL (tuition_balance + housing_balance, 0) “Balance Due”
FROM student_accounts;
(*)
FROM student_accounts;
(*)
♦SELECT tuition_balance + housing_balance
FROM student_accounts;
FROM student_accounts;
♦SELECT TO_NUMBER(tuition_balance, 0), TO_NUMBER (housing_balance, 0), tutition_balance + housing_balance “Balance Due”
FROM student_accounts;
FROM student_accounts;
♦SELECT NVL(tuition_balance, 0), NVL (housing_balance), tuition_balance + housing_balance “Balance Due”
FROM student_accounts;
FROM student_accounts;
13. Which statement about group functions is true?
♦NVL and COALESCE, but not NVL2, can be used with group functions to replace null values.
♦COALESCE, but not NVL and NVL2, can be used with group functions to replace null values.
♦NVL and NVL2, but not COALESCE, can be used with group functions to replace null values.
♦NVL, NVL2, and COALESCE can be used with group functions to replace null values. (*)
♦COALESCE, but not NVL and NVL2, can be used with group functions to replace null values.
♦NVL and NVL2, but not COALESCE, can be used with group functions to replace null values.
♦NVL, NVL2, and COALESCE can be used with group functions to replace null values. (*)
14. Consider the following data in the Employees table: (last_name, commission_pct, manager_id)
DATA:
King, null, null
Kochhar, null, 100
Vargas, null, 124
Zlotkey, .2, 100
What is the result of the following statement:
SELECT last_name, COALESCE(commission_pct, manager_id, -1) comm
FROM employees ;
DATA:
King, null, null
Kochhar, null, 100
Vargas, null, 124
Zlotkey, .2, 100
What is the result of the following statement:
SELECT last_name, COALESCE(commission_pct, manager_id, -1) comm
FROM employees ;
♦King, null
Kochhar, 100
Vargas, 124
Zlotkey, .2
Kochhar, 100
Vargas, 124
Zlotkey, .2
♦King, -1
Kochhar, 100
Vargas, 124
Zlotkey, .2
(*)
Kochhar, 100
Vargas, 124
Zlotkey, .2
(*)
♦Statement will fail
♦King, -1
Kochhar, 100
Vargas, 124
Zlotkey, 100
Kochhar, 100
Vargas, 124
Zlotkey, 100
15. You need to replace null values in the DEPT_ID column with the text N/A. Which functions should you use?
♦TO_NUMBER and NULLIF
♦TO_CHAR and NULLIF
♦TO_CHAR and NULL
♦TO_CHAR and NVL (*)
♦TO_CHAR and NULLIF
♦TO_CHAR and NULL
♦TO_CHAR and NVL (*)
16. All Human Resources data is stored in a table named EMPLOYEES. You have been asked to create a report that displays each employee’s name and salary. Each employee’s salary must be displayed in the following format: $000,000.00. Which function should you include in a SELECT statement to achieve the desired result?
♦TO_DATE
♦TO_CHAR (*)
♦TO_NUMBER
♦CHARTOROWID
♦TO_CHAR (*)
♦TO_NUMBER
♦CHARTOROWID
17. The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2 (25)
FIRST_NAME VARCHAR2 (25)
HIRE_DATE DATE
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2 (25)
FIRST_NAME VARCHAR2 (25)
HIRE_DATE DATE
You need to display HIRE_DATE values in this format:
January 28, 2000
Which SQL statement could you use?
♦SELECT TO_CHAR(hire_date, ‘Month DD’, ‘ YYYY’)
FROM employees;
FROM employees;
♦SELECT TO_CHAR(hire_date, ‘Month DD, YYYY’)
FROM employees;
(*)
FROM employees;
(*)
♦SELECT hire_date(TO_CHAR ‘Month DD’, ‘ YYYY’)
FROM employees;
FROM employees;
♦SELECT TO_CHAR(hire_date, Month DD, YYYY)
FROM employees;
FROM employees;
18. A table has the following definition: EMPLOYEES(
EMPLOYEE_ID NUMBER(6) NOT NULL,
NAME VARCHAR2(20) NOT NULL,
MANAGER_ID VARCHAR2(6))
EMPLOYEE_ID NUMBER(6) NOT NULL,
NAME VARCHAR2(20) NOT NULL,
MANAGER_ID VARCHAR2(6))
and contains the following rows:
(1001, ‘Bob Bevan’, ‘200’)
(200, ‘Natacha Hansen’, null)
(200, ‘Natacha Hansen’, null)
Will the folloiwng query work?
SELECT *
FROM employees
WHERE employee_id = manager_id;
FROM employees
WHERE employee_id = manager_id;
♦Yes, Oracle will perform implicit dataype conversion, and the query will return one row of data.
♦No, because the datatypes of EMPLOYEE_ID and MANAGER_ID are different.
♦Yes, Oracle will perform implicit datatype conversion, but the WHERE clause will not find any matching data. (*)
♦No.? You will have to re-wirte the statement and perform explicit datatype conversion.
19. Which statement is true about SQL functions?
♦Functions can convert values or text to another data type.
♦Functions can round a number to a specified decimal place.
♦Functions can convert upper case characters to lower case characters.
♦a, b and c are true. (*)
♦None of the above statements are true.
♦Functions can round a number to a specified decimal place.
♦Functions can convert upper case characters to lower case characters.
♦a, b and c are true. (*)
♦None of the above statements are true.
20. The following script will run successfully. True or False?
SELECT TO_CHAR(TO_DATE(’25-Dec-2004′,’dd-Mon-yyyy’))
FROM dual
SELECT TO_CHAR(TO_DATE(’25-Dec-2004′,’dd-Mon-yyyy’))
FROM dual
♦True (*)
♦False
♦False
21. Which three statements concerning explicit data type conversions are true? (Choose three.)
(Choose all correct answers)
(Choose all correct answers)
♦Use the TO_NUMBER function to convert a character string of digits to a number. (*)
♦Use the TO_CHAR function to convert a number or date value to a character string. (*)
♦Use the TO_DATE function to convert a character string to a date value. (*)
♦Use the TO_DATE function to convert a date value to a character string or number.
♦Use the TO_NUMBER function to convert a number to a character string.
♦Use the TO_CHAR function to convert a number or date value to a character string. (*)
♦Use the TO_DATE function to convert a character string to a date value. (*)
♦Use the TO_DATE function to convert a date value to a character string or number.
♦Use the TO_NUMBER function to convert a number to a character string.
22. Which function compares two expressions?
♦NULL
♦NVL2
♦NVL
♦NULLIF (*)
♦NVL2
♦NVL
♦NULLIF (*)
23. The following statement returns 0 (zero). True or False?
SELECT 121/NULL
FROM dual;
SELECT 121/NULL
FROM dual;
♦True
♦False (*)
♦False (*)
24. When executed, which statement displays a zero if the TUITION_BALANCE value is zero and the HOUSING_BALANCE value is null?
♦SELECT tuition_balance + housing_balance
FROM student_accounts;
FROM student_accounts;
♦SELECT NVL(tuition_balance, 0), NVL (housing_balance), tuition_balance + housing_balance “Balance Due”
FROM student_accounts;
FROM student_accounts;
♦SELECT TO_NUMBER(tuition_balance, 0), TO_NUMBER (housing_balance, 0), tutition_balance + housing_balance “Balance Due”
FROM student_accounts;
FROM student_accounts;
♦SELECT NVL (tuition_balance + housing_balance, 0) “Balance Due”
FROM student_accounts;
(*)
FROM student_accounts;
(*)
25. You need to replace null values in the DEPT_ID column with the text N/A. Which functions should you use?
♦TO_NUMBER and NULLIF
♦TO_CHAR and NULLIF
♦TO_CHAR and NULL
♦TO_CHAR and NVL (*)
♦TO_CHAR and NULLIF
♦TO_CHAR and NULL
♦TO_CHAR and NVL (*)
26. Which statement about group functions is true?
♦NVL and COALESCE, but not NVL2, can be used with group functions to replace null values.
♦NVL and NVL2, but not COALESCE, can be used with group functions to replace null values.
♦NVL, NVL2, and COALESCE can be used with group functions to replace null values. (*)
♦COALESCE, but not NVL and NVL2, can be used with group functions to replace null values.
♦NVL and COALESCE, but not NVL2, can be used with group functions to replace null values.
♦NVL and NVL2, but not COALESCE, can be used with group functions to replace null values.
♦NVL, NVL2, and COALESCE can be used with group functions to replace null values. (*)
♦COALESCE, but not NVL and NVL2, can be used with group functions to replace null values.
27. With the following data in Employees (last_name, commission_pct, manager_id) what is the result of the following statement?
DATA:
King, null, null
Kochhar, null, 100
Vargas, null, 124
Zlotkey, .2, 100
SELECT last_name, NVL2(commission_pct, manager_id, -1) comm
FROM employees ;
DATA:
King, null, null
Kochhar, null, 100
Vargas, null, 124
Zlotkey, .2, 100
SELECT last_name, NVL2(commission_pct, manager_id, -1) comm
FROM employees ;
♦King, -1
Kochhar, -1
Vargas, -1
Zlotkey, 100
(*)
Kochhar, -1
Vargas, -1
Zlotkey, 100
(*)
♦Statement will fail.
♦King, -1
Kochhar, -1
Vargas, -1
Zlotkey, .2
Kochhar, -1
Vargas, -1
Zlotkey, .2
♦King, -1
Kochhar, 100
Vargas, 124
Zlotkey, .2
Kochhar, 100
Vargas, 124
Zlotkey, .2
28. Which statement will return a listing of last names, salaries, and a rating of ‘Low’, ‘Medium’, ‘Good’ or ‘Excellent’ depending on the salary value?
♦SELECT last_name,salary,
(CASE WHEN salary<5000 o:p=”” ow=”” then=””>
WHEN salary<10000 edium=”” o:p=”” then=””>
WHEN salary<20000 o:p=”” ood=”” then=””>
ELSE ‘Excellent’
END) qualified_salary
FROM employees;
(*)
(CASE WHEN salary<5000 o:p=”” ow=”” then=””>
WHEN salary<10000 edium=”” o:p=”” then=””>
WHEN salary<20000 o:p=”” ood=”” then=””>
ELSE ‘Excellent’
END) qualified_salary
FROM employees;
(*)
♦SELECT last_name,sal,
(CASE WHEN sal<5000 o:p=”” ow=”” then=””>
WHEN sal<10000 edium=”” o:p=”” then=””>
WHEN sal<20000 o:p=”” ood=”” then=””>
ELSE ‘Excellent’
END) qualified_salary
FROM employees;
(CASE WHEN sal<5000 o:p=”” ow=”” then=””>
WHEN sal<10000 edium=”” o:p=”” then=””>
WHEN sal<20000 o:p=”” ood=”” then=””>
ELSE ‘Excellent’
END) qualified_salary
FROM employees;
♦SELECT last_name,salary,
(RATING WHEN salary<5000 o:p=”” ow=”” then=””>
WHEN salary<10000 edium=”” o:p=”” then=””>
WHEN salary<20000 o:p=”” ood=”” then=””>
ELSE ‘Excellent’
END) qualified_salary
FROM employees;
(RATING WHEN salary<5000 o:p=”” ow=”” then=””>
WHEN salary<10000 edium=”” o:p=”” then=””>
WHEN salary<20000 o:p=”” ood=”” then=””>
ELSE ‘Excellent’
END) qualified_salary
FROM employees;
♦SELECT last_name,salary,
(CASE WHEN salary<5000 o:p=”” ow=”” then=””>
WHEN sal <10000 edium=”” o:p=”” then=””>
WHEN sal <20000 o:p=”” ood=”” then=””>
ELSE ‘Excellent’
END) qualified_salary
FROM employees;
(CASE WHEN salary<5000 o:p=”” ow=”” then=””>
WHEN sal <10000 edium=”” o:p=”” then=””>
WHEN sal <20000 o:p=”” ood=”” then=””>
ELSE ‘Excellent’
END) qualified_salary
FROM employees;
29. Which of the following is a conditional expression used in SQL?
♦CASE (*)
♦NULLIF
♦DESCRIBE
♦WHERE
♦NULLIF
♦DESCRIBE
♦WHERE
30. For the given data from Employees (last_name, manager_id) what is the result of the following statement:
DATA:( King, null
Kochhar, 100
De Haan, 100
Hunold, 102
Ernst, 103)
SELECT last_name,
DECODE(manager_id, 100, ‘King’, ‘A N Other’) “Works For?”
FROM employees
DATA:( King, null
Kochhar, 100
De Haan, 100
Hunold, 102
Ernst, 103)
SELECT last_name,
DECODE(manager_id, 100, ‘King’, ‘A N Other’) “Works For?”
FROM employees
♦King, A N Other
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other
(*)
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other
(*)
♦Invalid statement.
♦King, Null
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other
♦King, A N Other
Kochhar, King
De Haan, King
Hunold, Kochhar
Ernst, De Haan
Kochhar, King
De Haan, King
Hunold, Kochhar
Ernst, De Haan
31. Sysdate is 12-May-2004.
You need to store the following date: 7-Dec-89
Which statement about the date format for this value is true?
You need to store the following date: 7-Dec-89
Which statement about the date format for this value is true?
♦The RR date format will interpret the year as 1989, and the YY date format will interpret the year as 2089 (*)
♦Both the YY and RR date formats will interpret the year as 2089
♦The RR date format will interpret the year as 2089, and the YY date format will interpret the year as 1989
♦Both the YY and RR date formats will interpret the year as 1989
32. Which functions allow you to perform explicit data type conversions?
♦TO_CHAR, TO_DATE, TO_NUMBER (*)
♦LENGTH, SUBSTR, LPAD, TRIM
♦NVL, NVL2, NULLIF
♦ROUND, TRUNC, ADD_MONTHS
♦LENGTH, SUBSTR, LPAD, TRIM
♦NVL, NVL2, NULLIF
♦ROUND, TRUNC, ADD_MONTHS
33. Which statement will return the salary (for example, the salary of 6000) from the Employees table in the following format? $6000.00
♦SELECT TO_CHAR(sal, ‘$99999.00’) SALARY
FROM employees
FROM employees
♦SELECT TO_CHAR(salary, ‘$99999’) SALARY
FROM employees
FROM employees
♦SELECT TO_CHAR(salary, ‘99999.00’) SALARY
FROM employees
FROM employees
♦SELECT TO_CHAR(salary, ‘$99999.00’) SALARY
FROM employees
(*)
FROM employees
(*)
34. The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2 (25)
FIRST_NAME VARCHAR2 (25)
HIRE_DATE DATE
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2 (25)
FIRST_NAME VARCHAR2 (25)
HIRE_DATE DATE
You need to display HIRE_DATE values in this format:
January 28, 2000
Which SQL statement could you use?
♦SELECT TO_CHAR(hire_date, Month DD, YYYY)
FROM employees;
FROM employees;
♦SELECT TO_CHAR(hire_date, ‘Month DD’, ‘ YYYY’)
FROM employees;
FROM employees;
♦SELECT TO_CHAR(hire_date, ‘Month DD, YYYY’)
FROM employees;
(*)
FROM employees;
(*)
♦SELECT hire_date(TO_CHAR ‘Month DD’, ‘ YYYY’)
FROM employees;
FROM employees;
35. If you use the RR format when writing a query using the date 27-Oct-17 and the year is 2001, what year would be the result?
♦2017 (*)
♦1901
♦1917
♦2001
♦1901
♦1917
♦2001
36. Which three statements concerning explicit data type conversions are true? (Choose three.)
(Choose all correct answers)
(Choose all correct answers)
♦Use the TO_DATE function to convert a character string to a date value. (*)
♦Use the TO_CHAR function to convert a number or date value to a character string. (*)
♦Use the TO_DATE function to convert a date value to a character string or number.
♦Use the TO_NUMBER function to convert a character string of digits to a number. (*)
♦Use the TO_NUMBER function to convert a number to a character string.
♦Use the TO_CHAR function to convert a number or date value to a character string. (*)
♦Use the TO_DATE function to convert a date value to a character string or number.
♦Use the TO_NUMBER function to convert a character string of digits to a number. (*)
♦Use the TO_NUMBER function to convert a number to a character string.
37. Which statement will return a listing of last names, salaries, and a rating of ‘Low’, ‘Medium’, ‘Good’ or ‘Excellent’ depending on the salary value?
♦SELECT last_name,salary,
(CASE WHEN salary<5000 o:p=”” ow=”” then=””>
WHEN salary<10000 edium=”” o:p=”” then=””>
WHEN salary<20000 o:p=”” ood=”” then=””>
ELSE ‘Excellent’
END) qualified_salary
FROM employees;
(*)
(CASE WHEN salary<5000 o:p=”” ow=”” then=””>
WHEN salary<10000 edium=”” o:p=”” then=””>
WHEN salary<20000 o:p=”” ood=”” then=””>
ELSE ‘Excellent’
END) qualified_salary
FROM employees;
(*)
♦SELECT last_name,salary,
(RATING WHEN salary<5000 o:p=”” ow=”” then=””>
WHEN salary<10000 edium=”” o:p=”” then=””>
WHEN salary<20000 o:p=”” ood=”” then=””>
ELSE ‘Excellent’
END) qualified_salary
FROM employees;
(RATING WHEN salary<5000 o:p=”” ow=”” then=””>
WHEN salary<10000 edium=”” o:p=”” then=””>
WHEN salary<20000 o:p=”” ood=”” then=””>
ELSE ‘Excellent’
END) qualified_salary
FROM employees;
♦SELECT last_name,salary,
(CASE WHEN salary<5000 o:p=”” ow=”” then=””>
WHEN sal <10000 edium=”” o:p=”” then=””>
WHEN sal <20000 o:p=”” ood=”” then=””>
ELSE ‘Excellent’
END) qualified_salary
FROM employees;
(CASE WHEN salary<5000 o:p=”” ow=”” then=””>
WHEN sal <10000 edium=”” o:p=”” then=””>
WHEN sal <20000 o:p=”” ood=”” then=””>
ELSE ‘Excellent’
END) qualified_salary
FROM employees;
♦SELECT last_name,sal,
(CASE WHEN sal<5000 o:p=”” ow=”” then=””>
WHEN sal<10000 edium=”” o:p=”” then=””>
WHEN sal<20000 o:p=”” ood=”” then=””>
ELSE ‘Excellent’
END) qualified_salary
FROM employees;
(CASE WHEN sal<5000 o:p=”” ow=”” then=””>
WHEN sal<10000 edium=”” o:p=”” then=””>
WHEN sal<20000 o:p=”” ood=”” then=””>
ELSE ‘Excellent’
END) qualified_salary
FROM employees;
38. For the given data from Employees (last_name, manager_id) what is the result of the following statement:
DATA:( King, null
Kochhar, 100
De Haan, 100
Hunold, 102
Ernst, 103)
SELECT last_name,
DECODE(manager_id, 100, ‘King’, ‘A N Other’) “Works For?”
FROM employees
DATA:( King, null
Kochhar, 100
De Haan, 100
Hunold, 102
Ernst, 103)
SELECT last_name,
DECODE(manager_id, 100, ‘King’, ‘A N Other’) “Works For?”
FROM employees
♦King, A N Other
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other
(*)
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other
(*)
♦King, Null
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other
♦Invalid statement.
♦King, A N Other
Kochhar, King
De Haan, King
Hunold, Kochhar
Ernst, De Haan
Kochhar, King
De Haan, King
Hunold, Kochhar
Ernst, De Haan
39. Which of the following is a conditional expression used in SQL?
♦DESCRIBE
♦CASE (*)
♦NULLIF
♦WHERE
♦CASE (*)
♦NULLIF
♦WHERE
40. Which function compares two expressions?
♦NVL
♦NULL
♦NULLIF (*)
♦NVL2
♦NULL
♦NULLIF (*)
♦NVL2
41. Which statement about group functions is true?
♦NVL and NVL2, but not COALESCE, can be used with group functions to replace null values.
♦COALESCE, but not NVL and NVL2, can be used with group functions to replace null values.
♦NVL and COALESCE, but not NVL2, can be used with group functions to replace null values.
♦NVL, NVL2, and COALESCE can be used with group functions to replace null values. (*)
♦COALESCE, but not NVL and NVL2, can be used with group functions to replace null values.
♦NVL and COALESCE, but not NVL2, can be used with group functions to replace null values.
♦NVL, NVL2, and COALESCE can be used with group functions to replace null values. (*)
42. When executed, which statement displays a zero if the TUITION_BALANCE value is zero and the HOUSING_BALANCE value is null?
♦SELECT tuition_balance + housing_balance
FROM student_accounts;
FROM student_accounts;
♦SELECT NVL (tuition_balance + housing_balance, 0) “Balance Due”
FROM student_accounts;
(*)
FROM student_accounts;
(*)
♦SELECT NVL(tuition_balance, 0), NVL (housing_balance), tuition_balance + housing_balance “Balance Due”
FROM student_accounts;
FROM student_accounts;
♦SELECT TO_NUMBER(tuition_balance, 0), TO_NUMBER (housing_balance, 0), tutition_balance + housing_balance “Balance Due”
FROM student_accounts;
FROM student_accounts;
43. You need to replace null values in the DEPT_ID column with the text N/A. Which functions should you use?
♦TO_NUMBER and NULLIF
♦TO_CHAR and NULL
♦TO_CHAR and NULLIF
♦TO_CHAR and NVL (*)
♦TO_CHAR and NULL
♦TO_CHAR and NULLIF
♦TO_CHAR and NVL (*)
44. Consider the following data in the Employees table: (last_name, commission_pct, manager_id)
DATA:
King, null, null
Kochhar, null, 100
Vargas, null, 124
Zlotkey, .2, 100
What is the result of the following statement:
SELECT last_name, COALESCE(commission_pct, manager_id, -1) comm
FROM employees ;
DATA:
King, null, null
Kochhar, null, 100
Vargas, null, 124
Zlotkey, .2, 100
What is the result of the following statement:
SELECT last_name, COALESCE(commission_pct, manager_id, -1) comm
FROM employees ;
♦King, -1
Kochhar, 100
Vargas, 124
Zlotkey, 100
Kochhar, 100
Vargas, 124
Zlotkey, 100
♦Statement will fail
♦King, null
Kochhar, 100
Vargas, 124
Zlotkey, .2
Kochhar, 100
Vargas, 124
Zlotkey, .2
♦King, -1
Kochhar, 100
Vargas, 124
Zlotkey, .2
(*)
Kochhar, 100
Vargas, 124
Zlotkey, .2
(*)
45. The STYLES table contains this data:
STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85940 12.00
968950 SANDAL 85909 10.00
869506 SANDAL 89690 15.00
809090 LOAFER 89098 10.00
890890 LOAFER 89789 14.00
857689 HEEL 85940 11.00
758960 SANDAL 86979
Evaluate this SELECT statement:
STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85940 12.00
968950 SANDAL 85909 10.00
869506 SANDAL 89690 15.00
809090 LOAFER 89098 10.00
890890 LOAFER 89789 14.00
857689 HEEL 85940 11.00
758960 SANDAL 86979
Evaluate this SELECT statement:
SELECT style_id, style_name, category, cost
FROM styles
WHERE style_name LIKE ‘SANDAL’ AND NVL(cost, 0) < 15.00
ORDER BY category, cost;
FROM styles
WHERE style_name LIKE ‘SANDAL’ AND NVL(cost, 0) < 15.00
ORDER BY category, cost;
Which result will the query provide?
♦STYLE_ID STYLE_NAME CATEGORY COST
968950 SANDAL 85909 10.00
895840 SANDAL 85940 12.00
758960 SANDAL 86979
(*)
968950 SANDAL 85909 10.00
895840 SANDAL 85940 12.00
758960 SANDAL 86979
(*)
♦STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85909 12.00
968950 SANDAL 85909 10.00
758960 SANDAL 86979
869506 SANDAL 89690 15.00
895840 SANDAL 85909 12.00
968950 SANDAL 85909 10.00
758960 SANDAL 86979
869506 SANDAL 89690 15.00
♦STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85940 12.00
968950 SANDAL 85909 10.00
758960 SANDAL 86979
895840 SANDAL 85940 12.00
968950 SANDAL 85909 10.00
758960 SANDAL 86979
♦STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85909 12.00
968950 SANDAL 85909 10.00
869506 SANDAL 89690 15.00
895840 SANDAL 85909 12.00
968950 SANDAL 85909 10.00
869506 SANDAL 89690 15.00
758960 SANDAL 86979
InfoCad Blog 