Section 4 Quiz Oracle Database Programming with SQL

 Section 4 Quiz Oracle Database Programming with SQL

Section 4 Quiz

(SOLUTION)

(Answer all questions in this section)

Which of the following are types of SQL functions? (Choose two correct answers.) Mark for Review
(1) Points
(Choose all correct answers)

Column-Row Functions
Multi-Row Functions (*)
Many-to-Many Functions
Single-Row Functions (*)

Evaluate this SELECT statement:
SELECT LENGTH(email)
FROM employee;
What will this SELECT statement display?
Mark for Review
(1) Points

The longest e-mail address in the EMPLOYEE table
The number of characters for each value in the EMAIL column in the employees table (*)
The maximum number of characters allowed in the EMAIL column
The email address of each employee in the EMPLOYEE table

Which SQL function is used to return the position where a specific character string begins within a larger character string? Mark for Review
(1) Points

CONCAT
LENGTH
INSTR (*)
SUBSTR

What does the following SQL SELECT statement return?
SELECT UPPER( SUBSTR('Database Programming', INSTR('Database Programming','P'),20))
FROM dual;
Mark for Review
(1) Points

Programming
PROGRAMMING (*)
DATABASE
Database

You need to display each employee's name in all uppercase letters. Which function should you use? Mark for Review
(1) Points

TOUPPER
UPPER (*)
UCASE
CASE

You issue this SQL statement:
SELECT TRUNC(751.367,-1) FROM dual;
Which value does this statement display?
Mark for Review
(1) Points

751
750 (*)
700
751.3

The answer to the following script is 456. True or False?
SELECT TRUNC(ROUND(456.98))
FROM dual;
Mark for Review
(1) Points

True
False (*)

Which two functions can be used to manipulate number or date column values, but NOT character column values? (Choose two.) Mark for Review
(1) Points
(Choose all correct answers)

TRUNC (*)
CONCAT
RPAD
ROUND (*)
INSTR

Which number function may be used to determine if a value is odd or even? Mark for Review
(1) Points

MOD (*)
TRUNC
ROUND
BINARY

Which comparison operator retrieves a list of values? Mark for Review
(1) Points

BETWEEN IN
LIKE
IS NULL
IN (*)

What is the result of the following query?
SELECT ADD_YEARS ('11-Jan-1994',6)
FROM dual; Mark for Review
(1) Points

This in not a valid SQL statement. (*)
11-Jul-2000
11-Jul-1995
11-Jan-2000

What is the result of the following query?
SELECT ADD_MONTHS ('11-Jan-1994',6)
FROM dual; Mark for Review
(1) Points

11-Jan-1995
17-Jan-1994
17-Jul-1994
11-Jul-1994 (*)

Evaluate this SELECT statement:
SELECT SYSDATE + 30
FROM dual;
Which value is returned by the query?
Mark for Review
(1) Points

No value is returned because the SELECT statement generates an error.
The current date plus 30 hours.
The current date plus 30 days. (*)
The current date plus 30 months.

Round and Trunc cannot be used on Date datatypes. True or False? Mark for Review
(1) Points

True
False (*)

Which of the following Date Functions will add calendar months to a date? Mark for Review
(1) Points

ADD_MONTHS (*)
MONTHS + Date
NEXT_MONTH
Months + Calendar (Month)

Which number function may be used to determine if a value is odd or even? Mark for Review
(1) Points

ROUND
BINARY
MOD (*)
TRUNC

Which script displays '01-May-2004' when the HIRE_DATE value is '20-May-2004'? Mark for Review
(1) Points

SELECT ROUND(hire_date, 'MONTH')
FROM employees;
SELECT ROUND(hire_date, 'MON')
FROM employees;
SELECT TRUNC(hire_date, 'MI')
FROM employees;
SELECT TRUNC(hire_date, 'MONTH')
FROM employees;
(*)

Evaluate this function: MOD (25, 2) Which value is returned? Mark for Review
(1) Points

2
0
25
1 (*)

Which two functions can be used to manipulate number or date column values, but NOT character column values? (Choose two.) Mark for Review
(1) Points
(Choose all correct answers)

TRUNC (*)
CONCAT
RPAD
INSTR
ROUND (*)

ROUND and TRUNC functions can be used with which of the following Datatypes? Mark for Review
(1) Points

Dates and numbers (*)
Dates and characters
Numbers and characters
None of the above

The EMPLOYEES table contains these columns:
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;
The values returned by this SELECT statement will be of which data type?
Mark for Review
(1) Points

DATETIME
NUMBER
INTEGER
DATE (*)

Which SELECT statement will NOT return a date value? Mark for Review
(1) Points

SELECT (30 + hire_date) + 1440/24
FROM employees;
SELECT SYSDATE - TO_DATE('25-Jun-2002') + hire_date
FROM employees;
SELECT (SYSDATE - hire_date) + 10*8
FROM employees;
(*)

SELECT (hire_date - SYSDATE) + TO_DATE('25-Jun-2002')
FROM employees;
What is the result of the following query?
SELECT ADD_MONTHS ('11-Jan-1994',6)
FROM dual; Mark for Review
(1) Points

11-Jul-1994 (*)
11-Jan-1995
17-Jan-1994
17-Jul-1994

Which SELECT statement will return a numeric value? Mark for Review
(1) Points

SELECT ROUND(hire_date, DAY)
FROM employees;
SELECT (SYSDATE - hire_date) / 7
FROM employees;
(*)

SELECT SYSDATE - 7

FROM employees;
SELECT SYSDATE + 600 / 24
FROM employees;

Which of the following Date Functions will add calendar months to a date? Mark for Review
(1) Points

MONTHS + Date
ADD_MONTHS (*)
NEXT_MONTH
Months + Calendar (Month)

Identify the output from the following SQL statement:
SELECT RPAD('SQL',6, '*')
FROM DUAL;
Mark for Review
(1) Points

SQL******
SQL*** (*)
***SQL
******SQL

Evaluate this SELECT statement:
SELECT LENGTH(email)
FROM employee;
What will this SELECT statement display?
Mark for Review
(1) Points

The maximum number of characters allowed in the EMAIL column
The longest e-mail address in the EMPLOYEE table
The number of characters for each value in the EMAIL column in the employees table (*)
The email address of each employee in the EMPLOYEE table

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?
Mark for Review
(1) Points

SELECT INSTR(category, 2,2)
FROM styles
WHERE style_id = 895840;
SELECT INSTR(category, -2,2)
FROM styles
WHERE style_id = 895840;
SELECT SUBSTR(category, -2,2)
FROM styles
WHERE style_id = 758960;
(*)
SELECT SUBSTR(category, 2,2)
FROM styles
WHERE style_id = 895840;

Which character manipulation function always returns a numerical value? Mark for Review
(1) Points

SUBSTR
LPAD
TRIM
LENGTH (*)

You query the database with this SQL statement:
SELECT LOWER(SUBSTR(CONCAT(last_name, first_name)), 1, 5) "ID"
FROM employee;
In which order are the functions evaluated?
Mark for Review
(1) Points

CONCAT, SUBSTR, LOWER (*)
SUBSTR, CONCAT, LOWER
LOWER, SUBSTR, CONCAT
LOWER, CONCAT, SUBSTR

Evaluate this SELECT statement:
SELECT SYSDATE + 30
FROM dual;
Which value is returned by the query?
Mark for Review
(1) Points

The current date plus 30 months.
No value is returned because the SELECT statement generates an error.
The current date plus 30 days. (*)
The current date plus 30 hours.

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; Mark for Review
(1) Points

01-Aug-2003
01-Jan-2004 (*)
01-Jul-2003
01-Jan-2003

What is the result of the following query?
SELECT ADD_YEARS ('11-Jan-1994',6)
FROM dual; Mark for Review
(1) Points

11-Jul-2000
This in not a valid SQL statement. (*)
11-Jan-2000
11-Jul-1995

Which function would you use to return the current database server date and time? Mark for Review
(1) Points

DATE
CURRENTDATE
DATETIME
SYSDATE (*)

You need to subtract three months from the current date. Which function should you use? Mark for Review
(1) Points

MONTHS_BETWEEN
ROUND
ADD_MONTHS (*)
TO_DATE

Which character manipulation function always returns a numerical value? Mark for Review
(1) Points
LPAD
SUBSTR
LENGTH (*)
TRIM

You query the database with this SQL statement:
SELECT LOWER(SUBSTR(CONCAT(last_name, first_name)), 1, 5) "ID"
FROM employee;

In which order are the functions evaluated?

Mark for Review
(1) Points

CONCAT, SUBSTR, LOWER (*)
SUBSTR, CONCAT, LOWER
LOWER, CONCAT, SUBSTR
LOWER, SUBSTR, CONCAT

Which SQL function can be used to remove heading or trailing characters (or both) from a character string? Mark for Review
(1) Points

NVL2
LPAD
TRIM (*)
CUT

Which of the following SQL statements would correctly return a song title identified in the database as "All These Years"? Mark for Review
(1) Points

WHERE title IN('All','These','Years');
WHERE title LIKE LOWER('all these years');
WHERE title CONTAINS 'Years';
WHERE title LIKE INITCAP('%all these years'); (*)

Which query would return a user password combining the ID of an employee and the first 4 digits of the last name? Mark for Review
(1) Points

SELECT CONCAT (employee_id, INSTR(last_name,1,4))
AS "User Passwords"
FROM employees
SELECT CONCAT (employee_id, SUBSTR(last_name,4,1))
AS "User Passwords"
FROM employees
SELECT CONCAT (employee_id, INSTR(last_name,4,1))
AS "User Passwords"
FROM employees
SELECT CONCAT (employee_id, SUBSTR(last_name,1,4))
AS "User Passwords"
FROM employees
(*)

You issue this SQL statement:
SELECT ROUND (1282.248, -2) FROM dual;
What value does this statement produce?
Mark for Review
(1) Points

1300 (*)
1200
1282
1282.25

You issue this SQL statement:
SELECT TRUNC(751.367,-1) FROM dual;
Which value does this statement display?
Mark for Review
(1) Points

750 (*)
700
751.3
751

Evaluate this function: MOD (25, 2) Which value is returned? Mark for Review
(1) Points

25

1 (*)

0
2

ROUND and TRUNC functions can be used with which of the following Datatypes? Mark for Review
(1) Points

Dates and numbers (*)
Dates and characters
Numbers and characters
None of the above

The answer to the following script is 456. True or False?
SELECT TRUNC(ROUND(456.98))
FROM dual;
Mark for Review
(1) Points

True
False (*)

You need to return a portion of each employeeﾒs last name, beginning with the first character up to the fifth character. Which character function should you use? Mark for Review
(1) Points

CONCAT
INSTR
SUBSTR(*)
TRUNC

Which three statements about functions are true? (Choose three.) Mark for Review
(1) Points
(Choose all correct answers)

The SUBSTR character function returns a portion of a string beginning at a defined character position to a specified length.(*)
The CONCAT function can only be used on character strings, not on numbers.
The SYSDATE function returns the Oracle Server date and time.(*)
The ROUND number function rounds a value to a specified decimal place or the nearest whole number.(*)

Character functions accept character arguments and only return character values. True or False? Mark for Review
(1) Points

True
False(*)

You want to create a report that displays all orders and their amounts that were placed during the month of January. You want the orders with the highest amounts to appear first. Which query should you issue? Mark for Review
(1) Points

SELECT orderid, total
FROM orders
WHERE order_date BETWEEN '01-Jan-2002' AND '31-Jan-2002'
ORDER BY total DESC;(*)
SELECT orderid, total
FROM orders
WHERE order_date BETWEEN '31-Jan-2002' AND '01-Jan-2002'
ORDER BY total DESC;
SELECT orderid, total
FROM orders
WHERE order_date IN ( 01-Jan-2002 , 31-Jan-2002 )
ORDER BY total;
SELECT orderid, total
FROM orders
WHERE order_date LIKE '01-Jan-2002' AND '31-Jan-2002'
ORDER BY total DESC;