Oracle Database Programming with SQL Section 3
1. Which number function may be used to determine if a value is odd or even?
♦TRUNC
♦MOD (*)
♦ROUND
♦BINARY
♦MOD (*)
♦ROUND
♦BINARY
2. What is the result of the following SQL Statement:
SELECT ROUND(45.923,-1)
FROM DUAL;
SELECT ROUND(45.923,-1)
FROM DUAL;
♦46
♦45.9
♦50 (*)
♦None of the above
♦45.9
♦50 (*)
♦None of the above
3. Which two functions can be used to manipulate number or date column values, but NOT character column values? (Choose two.)
♦CONCAT
♦INSTR
♦ROUND (*)
♦RPAD
♦TRUNC (*)
♦INSTR
♦ROUND (*)
♦RPAD
♦TRUNC (*)
4. Evaluate this function: MOD (25, 2) Which value is returned?
♦0
♦25
♦1 (*)
♦2
♦25
♦1 (*)
♦2
5. Which script displays ’01-May-2004′ when the HIRE_DATE value is ’20-May-2004′?
♦SELECT ROUND(hire_date, ‘MON’)
FROM employees;
FROM employees;
♦SELECT ROUND(hire_date, ‘MONTH’)
FROM employees;
FROM employees;
♦SELECT TRUNC(hire_date, ‘MI’)
FROM employees;
FROM employees;
♦SELECT TRUNC(hire_date, ‘MONTH’)
FROM employees;
(*)
FROM employees;
(*)
6. You need to display the current year as a character value (for example: Two Thousand and One). Which element would you use?
♦YY
♦YYYY
♦RR
♦YEAR (*)
♦YYYY
♦RR
♦YEAR (*)
7. What is the result of the following query?
SELECT ADD_YEARS (’11-Jan-1994′,6)
FROM dual;
SELECT ADD_YEARS (’11-Jan-1994′,6)
FROM dual;
♦11-Jul-1995
♦11-Jan-2000
♦This in not a valid SQL statement. (*)
♦11-Jul-2000
♦11-Jan-2000
♦This in not a valid SQL statement. (*)
♦11-Jul-2000
8. You need to display the number of months between today’s date and each employee’s hiredate. Which function should you use?
♦ROUND
♦ADD_MONTHS
♦BETWEEN
♦MONTHS_BETWEEN (*)
♦ADD_MONTHS
♦BETWEEN
♦MONTHS_BETWEEN (*)
9. Which query would return a whole number if the sysdate is 26-May-2004?
♦SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,’19-Mar-1979′) /12)
AS YEARS
FROM DUAL;
(*)
AS YEARS
FROM DUAL;
(*)
♦SELECT TRUNC(YEARS_BETWEEN(SYSDATE,’19-Mar-1979′) /12)
AS YEARS
FROM DUAL;
AS YEARS
FROM DUAL;
♦SELECT MONTHS_BETWEEN(SYSDATE,’19-Mar-1979′) /12
AS YEARS
FROM DUAL;
AS YEARS
FROM DUAL;
♦None of the above
10. Which SELECT statement will NOT return a date value?
♦SELECT (hire_date – SYSDATE) + TO_DATE(’25-Jun-2002′)
FROM employees;
FROM employees;
♦SELECT (30 + hire_date) + 1440/24
FROM employees;
FROM employees;
♦SELECT SYSDATE – TO_DATE(’25-Jun-2002′) + hire_date
FROM employees;
FROM employees;
♦SELECT (SYSDATE – hire_date) + 10*8
FROM employees;
(*)
FROM employees;
(*)
11. You need to display each employee’s name in all uppercase letters. Which function should you use?
♦CASE
♦TOUPPER
♦UCASE
♦UPPER (*)
♦TOUPPER
♦UCASE
♦UPPER (*)
12. 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 12.00
You query the database and return the value 79. Which script did you use?
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 12.00
You query the database and return the value 79. Which script did you use?
♦SELECT SUBSTR(category, -2,2)
FROM styles
WHERE style_id = 758960;
(*)
FROM styles
WHERE style_id = 758960;
(*)
♦SELECT SUBSTR(category, 2,2)
FROM styles
WHERE style_id = 895840;
FROM styles
WHERE style_id = 895840;
♦SELECT INSTR(category, 2,2)
FROM styles
WHERE style_id = 895840;
FROM styles
WHERE style_id = 895840;
♦SELECT INSTR(category, -2,2)
FROM styles
WHERE style_id = 895840;
FROM styles
WHERE style_id = 895840;
13. What does the following SQL SELECT statement return?
SELECT UPPER( SUBSTR(‘Database Programming’, INSTR(‘Database Programming’,’P’),20))
FROM dual;
SELECT UPPER( SUBSTR(‘Database Programming’, INSTR(‘Database Programming’,’P’),20))
FROM dual;
♦Programming
♦Database
♦PROGRAMMING (*)
♦DATABASE
♦Database
♦PROGRAMMING (*)
♦DATABASE
14. You issue this SQL statement:
SELECT INSTR (‘organizational sales’, ‘al’)
FROM dual;
SELECT INSTR (‘organizational sales’, ‘al’)
FROM dual;
Which value is returned by this command?
♦1
♦2
♦17
♦13 (*)
♦2
♦17
♦13 (*)
15. Which of the following are types of SQL functions? (Choose two correct answers.)
♦Column-Row Functions
♦Multi-Row Functions (*)
♦Single-Row Functions (*)
♦Many-to-Many Functions
♦Multi-Row Functions (*)
♦Single-Row Functions (*)
♦Many-to-Many Functions
16. The answer to the following script is 456. True or False?
SELECT TRUNC(ROUND(456.98))
FROM dual;
SELECT TRUNC(ROUND(456.98))
FROM dual;
♦True
♦False (*)
♦False (*)
17. You issue this SQL statement:
SELECT ROUND (1282.248, -2) FROM dual;
What value does this statement produce?
SELECT ROUND (1282.248, -2) FROM dual;
What value does this statement produce?
♦1200
♦1300 (*)
♦1282.25
♦1282
♦1300 (*)
♦1282.25
♦1282
18. You issue this SQL statement:
SELECT TRUNC(751.367,-1) FROM dual;
Which value does this statement display?
SELECT TRUNC(751.367,-1) FROM dual;
Which value does this statement display?
♦700
♦750 (*)
♦751.3
♦751
♦750 (*)
♦751.3
♦751
19. What is the result of the following SQL Statement:
SELECT ROUND(45.923,-1)
FROM DUAL;
SELECT ROUND(45.923,-1)
FROM DUAL;
♦46
♦45.9
♦50 (*)
♦None of the above
♦45.9
♦50 (*)
♦None of the above
20. Which number function may be used to determine if a value is odd or even?
♦BINARY
♦MOD (*)
♦TRUNC
♦ROUND
♦MOD (*)
♦TRUNC
♦ROUND
21. If hire_date has a value of ’03-Jul-2003′, then what is the output from this code?
SELECT ROUND(hire_date, ‘Year’) FROM employees;
SELECT ROUND(hire_date, ‘Year’) FROM employees;
♦01-Jul-2003
♦01-Jan-2003
♦01-Jan-2004 (*)
♦01-Aug-2003
♦01-Jan-2003
♦01-Jan-2004 (*)
♦01-Aug-2003
22. Which SELECT statement will return a numeric value?
♦SELECT ROUND(hire_date, DAY)
FROM employees;
FROM employees;
♦SELECT (SYSDATE – hire_date) / 7
FROM employees;
(*)
FROM employees;
(*)
♦SELECT SYSDATE + 600 / 24
FROM employees;
FROM employees;
♦SELECT SYSDATE – 7
FROM employees;
FROM employees;
23. Which query would return a whole number if the sysdate is 26-May-2004?
♦SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,’19-Mar-1979′) /12)
AS YEARS
FROM DUAL;
(*)
AS YEARS
FROM DUAL;
(*)
♦SELECT TRUNC(YEARS_BETWEEN(SYSDATE,’19-Mar-1979′) /12)
AS YEARS
FROM DUAL;
♦SELECT MONTHS_BETWEEN(SYSDATE,’19-Mar-1979′) /12
AS YEARS
FROM DUAL;
AS YEARS
FROM DUAL;
♦SELECT MONTHS_BETWEEN(SYSDATE,’19-Mar-1979′) /12
AS YEARS
FROM DUAL;
♦None of the above
24. You need to subtract three months from the current date. Which function should you use?
♦ROUND
♦MONTHS_BETWEEN
♦ADD_MONTHS (*)
♦TO_DATE
♦MONTHS_BETWEEN
♦ADD_MONTHS (*)
♦TO_DATE
25. What is the result of the following query?
SELECT ADD_MONTHS (’11-Jan-1994′,6)
FROM dual;
SELECT ADD_MONTHS (’11-Jan-1994′,6)
FROM dual;
♦17-Jul-1994
♦11-Jul-1994 (*)
♦17-Jan-1994
♦11-Jan-1995
♦11-Jul-1994 (*)
♦17-Jan-1994
♦11-Jan-1995
26. You issue this SQL statement:
SELECT INSTR (‘organizational sales’, ‘al’)
FROM dual;
SELECT INSTR (‘organizational sales’, ‘al’)
FROM dual;
Which value is returned by this command?
♦17
♦1
♦2
♦13 (*)
♦1
♦2
♦13 (*)
27. The PRICE table contains this data:
PRODUCT_ID MANUFACTURER_ID
86950 59604
PRODUCT_ID MANUFACTURER_ID
86950 59604
You query the database and return the value 95. Which script did you use?
♦SELECT TRIM(product_id, -3, 2)
FROM price
WHERE manufacturer_id = 59604;
FROM price
WHERE manufacturer_id = 59604;
♦SELECT SUBSTR(product_id, 3, 2)
FROM price
WHERE manufacturer_id = 59604;
(*)
FROM price
WHERE manufacturer_id = 59604;
(*)
♦SELECT LENGTH(product_id, 3, 2)
FROM price
WHERE manufacturer_id = 59604;
FROM price
WHERE manufacturer_id = 59604;
♦SELECT SUBSTR(product_id, -1, 3)
FROM price
WHERE manufacturer_id = 59604;
FROM price
WHERE manufacturer_id = 59604;
28. You need to display the number of characters in each customer’s last name. Which function should you use?
♦SUBSTR
♦COUNT
♦LENGTH (*)
♦LPAD
♦COUNT
♦LENGTH (*)
♦LPAD
29. Identify the output from the following SQL statement:
SELECT RPAD(‘SQL’,6, ‘*’)
FROM DUAL;
SELECT RPAD(‘SQL’,6, ‘*’)
FROM DUAL;
♦SQL*** (*)
♦******SQL
♦***SQL
♦SQL******
♦******SQL
♦***SQL
♦SQL******
30. What does the following SQL SELECT statement return?
SELECT UPPER( SUBSTR(‘Database Programming’, INSTR(‘Database Programming’,’P’),20))
FROM dual;
SELECT UPPER( SUBSTR(‘Database Programming’, INSTR(‘Database Programming’,’P’),20))
FROM dual;
♦Database
♦Programming
♦DATABASE
♦PROGRAMMING (*)
♦Programming
♦DATABASE
♦PROGRAMMING (*)
31. The EMPLOYEES table contains these columns:
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
HIRE_DATE DATE
EVAL_MONTHS NUMBER(3)
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
HIRE_DATE DATE
EVAL_MONTHS NUMBER(3)
Evaluate this SELECT statement:
SELECT hire_date + eval_months
FROM employees;
FROM employees;
The values returned by this SELECT statement will be of which data type?
♦DATE (*)
♦INTEGER
♦NUMBER
♦DATETIME
♦INTEGER
♦NUMBER
♦DATETIME
32. If hire_date has a value of ’03-Jul-2003′, then what is the output from this code?
SELECT ROUND(hire_date, ‘Year’) FROM employees;
SELECT ROUND(hire_date, ‘Year’) FROM employees;
♦01-Jan-2004 (*)
♦01-Aug-2003
♦01-Jan-2003
♦01-Jul-2003
♦01-Aug-2003
♦01-Jan-2003
♦01-Jul-2003
33. Which query would return a whole number if the sysdate is 26-May-2004?
♦SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,’19-Mar-1979′) /12)
AS YEARS
FROM DUAL;
(*)
AS YEARS
FROM DUAL;
(*)
♦SELECT TRUNC(YEARS_BETWEEN(SYSDATE,’19-Mar-1979′) /12)
AS YEARS
FROM DUAL;
AS YEARS
FROM DUAL;
♦SELECT MONTHS_BETWEEN(SYSDATE,’19-Mar-1979′) /12
AS YEARS
FROM DUAL;
AS YEARS
FROM DUAL;
♦None of the above
34. What is the result of the following query?
SELECT ADD_YEARS (’11-Jan-1994′,6)
FROM dual;
SELECT ADD_YEARS (’11-Jan-1994′,6)
FROM dual;
♦11-Jul-2000
♦11-Jan-2000
♦This in not a valid SQL statement. (*)
♦11-Jul-1995
♦11-Jan-2000
♦This in not a valid SQL statement. (*)
♦11-Jul-1995
35. What is the result of the following query?
SELECT ADD_MONTHS (’11-Jan-1994′,6)
FROM dual;
SELECT ADD_MONTHS (’11-Jan-1994′,6)
FROM dual;
♦11-Jan-1995
♦11-Jul-1994 (*)
♦17-Jan-1994
♦17-Jul-1994
♦11-Jul-1994 (*)
♦17-Jan-1994
♦17-Jul-1994
36. Which script displays ’01-May-2004′ when the HIRE_DATE value is ’20-May-2004′?
♦SELECT ROUND(hire_date, ‘MONTH’)
FROM employees;
FROM employees;
♦SELECT ROUND(hire_date, ‘MON’)
FROM employees;
FROM employees;
♦SELECT TRUNC(hire_date, ‘MI’)
FROM employees;
FROM employees;
♦SELECT TRUNC(hire_date, ‘MONTH’)
FROM employees;
(*)
FROM employees;
(*)
37. What is the result of the following SQL Statement:
SELECT ROUND(45.923,-1)
FROM DUAL;
SELECT ROUND(45.923,-1)
FROM DUAL;
♦46
♦45.9
♦50 (*)
♦None of the above
♦45.9
♦50 (*)
♦None of the above
38. Which comparison operator retrieves a list of values?
♦IS NULL
♦BETWEEN IN
♦LIKE
♦IN (*)
♦BETWEEN IN
♦LIKE
♦IN (*)
39. Which number function may be used to determine if a value is odd or even?
♦TRUNC
♦ROUND
♦MOD (*)
♦BINARY
♦ROUND
♦MOD (*)
♦BINARY
40. Evaluate this function: MOD (25, 2) Which value is returned?
♦2
♦25
♦1 (*)
♦0
♦25
♦1 (*)
♦0
41. Which of the following are types of SQL functions? (Choose two correct answers.)
♦Multi-Row Functions (*)
♦Single-Row Functions (*)
♦Column-Row Functions
♦Many-to-Many Functions
♦Single-Row Functions (*)
♦Column-Row Functions
♦Many-to-Many Functions
42. Which SQL function is used to return the position where a specific character string begins within a larger character string?
♦INSTR (*)
♦SUBSTR
♦CONCAT
♦LENGTH
♦SUBSTR
♦CONCAT
♦LENGTH
43. You issue this SQL statement:
SELECT INSTR (‘organizational sales’, ‘al’)
FROM dual;
SELECT INSTR (‘organizational sales’, ‘al’)
FROM dual;
Which value is returned by this command?
♦13 (*)
♦1
♦2
♦17
♦1
♦2
♦17
44. You query the database with this SQL statement:
SELECT CONCAT(last_name, (SUBSTR(LOWER(first_name), 4))) “Default Password”
FROM employees;
SELECT CONCAT(last_name, (SUBSTR(LOWER(first_name), 4))) “Default Password”
FROM employees;
Which function will be evaluated first?
♦CONCAT
♦SUBSTR
♦LOWER (*)
♦All three will be evaluated simultaneously.
♦SUBSTR
♦LOWER (*)
♦All three will be evaluated simultaneously.
15. You need to display the number of characters in each customer’s last name. Which function should you use?
♦SUBSTR
♦LENGTH (*)
♦LPAD
♦LENGTH (*)
♦LPAD
♦COUNT
InfoCad Blog 