(Answer
all questions in this section)
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? Mark for Review
(1)
Points
True (*)
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
Mark
for Review
(1)
Points
King, 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
Invalid statement.
King, Null
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? Mark for Review
(1)
Points
NULLIF
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))
and
contains the following rows:
(1001,
'Bob Bevan', '200')
(200,
'Natacha Hansen', null)
Will the
folloiwng query work?
SELECT *
FROM
employees
WHERE
employee_id = manager_id; Mark for Review
(1)
Points
Yes, Oracle will perform implicit dataype 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-wirte
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? Mark for Review
(1)
Points
1901
2017 (*)
2001
1917
6.
Which arithmetic
operation will return a numeric
value?
Mark for Review
(1)
Points
SYSDATE + 30 / 24
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"? Mark for
Review
(1)
Points
SELECT TO_CHAR(price, '$99,900.99')
FROM
product;
(*)
SELECT TO_NUMBER(price, '$99,900.99')
FROM
product;
SELECT TO_CHAR(price, '$99,990.99')
FROM
product;
SELECT TO_CHAR(price, '$99,999.99')
FROM
product;
8.
Which two
statements concerning SQL functions are true? (Choose
two.) Mark
for Review
(1)
Points
(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?
Mark for Review
(1)
Points
SELECT TO_CHAR(hire_date, 'DDspth 'of' Month RRRR')
FROM
employees;
SELECT TO_CHAR(hire_date, 'DDTH "of" Month YYYY')
FROM
employees;
SELECT TO_CHAR(hire_date, 'ddth "of" Month YYYY')
FROM
employees;
(*)
SELECT enroll_date(hire_date, 'DDspth "of" Month YYYY')
FROM
employees;
10.
Which function compares two
expressions? Mark for Review
(1)
Points
NVL
NVL2
NULL
NULLIF (*)
11.
Which of the following General
Functions will return the first non-null expression in the expression
list?
Mark for Review
(1)
Points
NULLIF
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? Mark
for Review
(1)
Points
SELECT NVL (tuition_balance + housing_balance, 0) "Balance Due"
FROM
student_accounts;
(*)
SELECT tuition_balance + housing_balance
FROM
student_accounts;
SELECT TO_NUMBER(tuition_balance, 0), TO_NUMBER (housing_balance, 0),
tutition_balance + housing_balance "Balance Due"
FROM
student_accounts;
SELECT NVL(tuition_balance, 0), NVL (housing_balance), tuition_balance +
housing_balance "Balance Due"
FROM
student_accounts;
13.
Which statement about group
functions is true?
Mark for Review
(1)
Points
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. (*)
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 ;
Mark
for Review
(1)
Points
King, null
Kochhar,
100
Vargas,
124
Zlotkey,
.2
King, -1
Kochhar,
100
Vargas,
124
Zlotkey,
.2
(*)
Statement will fail
King, -1
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?
Mark for Review
(1)
Points
TO_NUMBER and NULLIF
TO_CHAR and NULLIF
TO_CHAR and NULL
TO_CHAR and NVL (*)
1.
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? Mark for Review
(1)
Points
TO_DATE
TO_CHAR (*)
TO_NUMBER
CHARTOROWID
2.
The EMPLOYEES
table contains these columns:
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?
Mark
for Review
(1)
Points
SELECT TO_CHAR(hire_date, 'Month DD', ' YYYY')
FROM
employees;
SELECT TO_CHAR(hire_date, 'Month DD, YYYY')
FROM
employees;
(*)
SELECT hire_date(TO_CHAR 'Month DD', ' YYYY')
FROM
employees;
SELECT TO_CHAR(hire_date, Month DD, YYYY)
FROM
employees;
3.
A table has the
following definition: EMPLOYEES(
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)
Will the
folloiwng query work?
SELECT *
FROM
employees
WHERE
employee_id = manager_id; Mark for Review
(1)
Points
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.
4.
Which statement is
true about SQL functions? Mark for Review
(1)
Points
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.
5.
The following
script will run successfully. True or False?
SELECT
TO_CHAR(TO_DATE('25-Dec-2004','dd-Mon-yyyy'))
FROM
dual Mark for Review
(1)
Points
True (*)
False
6.
Which three
statements concerning explicit data type conversions are true? (Choose
three.) Mark for Review
(1)
Points
(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.
7.
Which function
compares two expressions? Mark for
Review
(1)
Points
NULL
NVL2
NVL
NULLIF (*)
8.
The following
statement returns 0 (zero). True or False?
SELECT
121/NULL
FROM
dual; Mark for Review
(1)
Points
True
False (*)
9.
When executed,
which statement displays a zero if the TUITION_BALANCE value is zero and the
HOUSING_BALANCE value is
null? Mark
for Review
(1)
Points
SELECT tuition_balance + housing_balance
FROM
student_accounts;
SELECT NVL(tuition_balance, 0), NVL (housing_balance), tuition_balance +
housing_balance "Balance Due"
FROM
student_accounts;
SELECT TO_NUMBER(tuition_balance, 0), TO_NUMBER (housing_balance, 0),
tutition_balance + housing_balance "Balance Due"
FROM
student_accounts;
SELECT NVL (tuition_balance + housing_balance, 0) "Balance Due"
FROM
student_accounts;
(*)
10.
You need to replace null values in
the DEPT_ID column with the text N/A. Which functions should you
use?
Mark for Review
(1)
Points
TO_NUMBER and NULLIF
TO_CHAR and NULLIF
TO_CHAR and NULL
TO_CHAR and NVL (*)
11.
Which statement about group
functions is true?
Mark for Review
(1)
Points
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.
12.
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 ;
Mark
for Review
(1)
Points
King, -1
Kochhar,
-1
Vargas,
-1
Zlotkey,
100
(*)
Statement will fail.
King, -1
Kochhar,
-1
Vargas,
-1
Zlotkey,
.2
King, -1
Kochhar,
100
Vargas,
124
Zlotkey,
.2
13.
Which statement will return a
listing of last names, salaries, and a rating of 'Low', 'Medium', 'Good' or
'Excellent' depending on the salary
value? Mark for
Review
(1)
Points
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;
(*)
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;
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;
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;
14.
Which of the following is a
conditional expression used in SQL? Mark for Review
(1)
Points
CASE (*)
NULLIF
DESCRIBE
WHERE
15.
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
Mark
for Review
(1)
Points
King, 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
King, A N Other
Kochhar,
King
De Haan,
King
Hunold,
Kochhar
Ernst,
De Haan
1.
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? Mark for
Review
(1)
Points
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
2.
Which functions
allow you to perform explicit data type conversions? Mark for
Review
(1)
Points
TO_CHAR, TO_DATE, TO_NUMBER (*)
LENGTH, SUBSTR, LPAD, TRIM
NVL, NVL2, NULLIF
ROUND, TRUNC, ADD_MONTHS
3.
Which statement
will return the salary (for example, the salary of 6000) from the Employees
table in the following format?
$6000.00 Mark for Review
(1)
Points
SELECT TO_CHAR(sal, '$99999.00') SALARY
FROM
employees
SELECT TO_CHAR(salary, '$99999') SALARY
FROM
employees
SELECT TO_CHAR(salary, '99999.00') SALARY
FROM
employees
SELECT TO_CHAR(salary, '$99999.00') SALARY
FROM
employees
(*)
4.
The EMPLOYEES
table contains these columns:
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?
Mark
for Review
(1)
Points
SELECT TO_CHAR(hire_date, Month DD, YYYY)
FROM
employees;
SELECT TO_CHAR(hire_date, 'Month DD', ' YYYY')
FROM
employees;
SELECT TO_CHAR(hire_date, 'Month DD, YYYY')
FROM
employees;
(*)
SELECT hire_date(TO_CHAR 'Month DD', ' YYYY')
FROM
employees;
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? Mark for Review
(1)
Points
2017 (*)
1901
1917
2001
6.
Which three
statements concerning explicit data type conversions are true? (Choose
three.) Mark for Review
(1)
Points
(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.
7.
Which statement
will return a listing of last names, salaries, and a rating of 'Low', 'Medium',
'Good' or 'Excellent' depending on the salary value?
Mark for Review
(1)
Points
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;
(*)
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;
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;
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;
8.
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
Mark
for Review
(1)
Points
King, 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
Invalid statement.
King, A N Other
Kochhar,
King
De Haan,
King
Hunold,
Kochhar
Ernst,
De Haan
9.
Which of the
following is a conditional expression used in SQL? Mark for Review
(1)
Points
DESCRIBE
CASE (*)
NULLIF
WHERE
10.
Which function compares two
expressions? Mark for Review
(1)
Points
NVL
NULL
NULLIF (*)
NVL2
11.
Which statement about group
functions is
true?
Mark for Review
(1)
Points
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. (*)
12.
When executed, which statement
displays a zero if the TUITION_BALANCE value is zero and the HOUSING_BALANCE
value is
null? Mark
for Review
(1)
Points
SELECT tuition_balance + housing_balance
FROM
student_accounts;
SELECT NVL (tuition_balance + housing_balance, 0) "Balance Due"
FROM
student_accounts;
(*)
SELECT NVL(tuition_balance, 0), NVL (housing_balance), tuition_balance +
housing_balance "Balance Due"
FROM
student_accounts;
SELECT TO_NUMBER(tuition_balance, 0), TO_NUMBER (housing_balance, 0),
tutition_balance + housing_balance "Balance Due"
FROM
student_accounts;
13.
You need to replace null values in
the DEPT_ID column with the text N/A. Which functions should you
use?
Mark for Review
(1)
Points
TO_NUMBER and NULLIF
TO_CHAR and NULL
TO_CHAR and NULLIF
TO_CHAR and NVL (*)
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 ;
Mark
for Review
(1)
Points
King, -1
Kochhar,
100
Vargas,
124
Zlotkey,
100
Statement will fail
King, null
Kochhar,
100
Vargas,
124
Zlotkey,
.2
King, -1
Kochhar,
100
Vargas,
124
Zlotkey,
.2
(*)
15.
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:
SELECT
style_id, style_name, 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?
Mark
for Review
(1)
Points
STYLE_ID
STYLE_NAME
CATEGORY COST
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
STYLE_ID
STYLE_NAME
CATEGORY COST
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
758960
SANDAL
86979
No comments:
Post a Comment