programming database quiz 4

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;