Section 5
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
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?
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
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. (*)
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 ;
Statement
will fail
King,
null
Kochhar, 100
Vargas, 124
Zlotkey, .2
King,
-1
Kochhar, 100
Vargas, 124
Zlotkey, .2
(*)
King,
-1
Kochhar, 100
Vargas, 124
Zlotkey, 100
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;
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;
(*)
The PRODUCT table contains this
column: PRICE NUMBER(7,2)
Evaluate this statement:
SELECT NVL(10 / price, '0')
FROM PRODUCT;
What would happen if the PRICE column contains null values?
A value
of 0 would be displayed. (*)
The
statement would fail because values cannot be divided by 0.
The
statement would fail because values cannot be divided by null.
A value
of 10 would be displayed.
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 ;
King,
-1
Kochhar, 100
Vargas, 124
Zlotkey, .2
King,
-1
Kochhar, -1
Vargas, -1
Zlotkey, .2
King,
-1
Kochhar, -1
Vargas, -1
Zlotkey, 100
(*)
Statement
will fail.
Which best describes the
TO_CHAR function?
The
TO_CHAR function can only be used on Date columns.
The
TO_CHAR function can be used to display dates and numbers according to
formatting conventions that are supported by Oracle. (*)
The
TO_CHAR function can be used to remove text from column data that will be
returned by the database.
The
TO_CHAR function can be used to specify meaningful column names in an SQL
statement's result set.
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, 'ddth "of" Month YYYY')
FROM employees;
(*)
SELECT
TO_CHAR(hire_date, 'DDspth 'of' Month RRRR')
FROM employees;
SELECT
TO_CHAR(hire_date, 'DDTH "of" Month YYYY')
FROM employees;
SELECT
enroll_date(hire_date, 'DDspth "of" Month YYYY')
FROM employees;
You have been asked to create a
report that lists all customers who have placed orders of at least $2,500. The
report's date should be displayed using this format:
Day, Date Month, Year (For example, Tuesday, 13 April, 2004
).
Which statement should you issue?
SELECT
companyname, TO_CHAR (sysdate, 'fmdd, dy month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
SELECT
companyname, TO_CHAR (sysdate, 'fmDay, dd Month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
(*)
SELECT
companyname, TO_DATE (date, 'day, dd month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
SELECT
companyname, TO_DATE (sysdate, 'dd, dy month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
Which three statements concerning
explicit data type conversions are true? (Choose three.)
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_DATE function to convert a date value to a character string or number.
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. (*)
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(salary, '$99999.00') SALARY
FROM employees
(*)
SELECT
TO_CHAR(salary, '99999.00') SALARY
FROM employees
SELECT
TO_CHAR(salary, '$99999') SALARY
FROM employees
SELECT
TO_CHAR(sal, '$99999.00') SALARY
FROM employees
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?
1917
2001
1901
2017
(*)
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
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
King, A
N Other
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other
(*)
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
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,sal,
(CASE WHEN sal<5000 THEN 'Low'
WHEN sal<10000
THEN 'Medium'
WHEN sal<20000
THEN 'Good'
ELSE 'Excellent'
END) qualified_salary
FROM employees;
SELECT
last_name,salary,
(RATING WHEN salary<5000 THEN 'Low'
WHEN
salary<10000 THEN 'Medium'
WHEN
salary<20000 THEN 'Good'
ELSE 'Excellent'
END) qualified_salary
FROM employees;
SELECT
last_name,salary,
(CASE WHEN salary<5000 THEN 'Low'
WHEN
salary<10000 THEN 'Medium'
WHEN
salary<20000 THEN 'Good'
ELSE 'Excellent'
END) qualified_salary
FROM employees;
(*)
SELECT
last_name,salary,
(CASE WHEN salary<5000 THEN 'Low'
WHEN sal
<10000 THEN 'Medium'
WHEN sal
<20000 THEN 'Good'
ELSE 'Excellent'
END) qualified_salary
FROM employees;
No comments:
Post a Comment