Database Programming with SQL Section 4

Section 4 Quiz
(Answer all questions in this section)

1. 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 IN ( 01-Jan-2002 , 31-Jan-2002 )
ORDER BY total;


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 LIKE '01-Jan-2002' AND '31-Jan-2002'
ORDER BY total DESC;


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







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


SELECT (SYSDATE - hire_date) + 10*8
FROM employees;
(*)



SELECT (hire_date - SYSDATE) + TO_DATE('25-Jun-2002')
FROM employees;


SELECT SYSDATE - TO_DATE('25-Jun-2002') + hire_date
FROM employees;


SELECT (30 + hire_date) + 1440/24
FROM employees;






3. You need to display the current year as a character value (for example: Two Thousand and One). Which element would you use? Mark for Review
(1) Points


RR


YYYY


YEAR (*)


YY






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


11-Jan-2000


This in not a valid SQL statement. (*)


11-Jul-2000


11-Jul-1995






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


SYSDATE (*)


DATETIME


DATE


CURRENTDATE
Section 4 Quiz
(Answer all questions in this section)

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


True


False (*)






7. 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, SUBSTR(last_name,4,1))
AS "User Passwords"
FROM employees


SELECT CONCAT (employee_id, INSTR(last_name,1,4))
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
(*)







8. Identify the output from the following SQL statement:
SELECT RPAD('SQL',6, '*')
FROM DUAL;

 Mark for Review
(1) Points


SQL*** (*)


******SQL


SQL******


***SQL






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


CUT


NVL2


LPAD


TRIM (*)






10. You issue this SQL statement:
SELECT INSTR ('organizational sales', 'al')
FROM dual;

Which value is returned by this command?

 Mark for Review
(1) Points


17


1


2


13 (*)


Section 4 Quiz
(Answer all questions in this section)

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


BETWEEN IN


LIKE


IS NULL


IN (*)






12. You issue this SQL statement:
SELECT ROUND (1282.248, -2) FROM dual;
What value does this statement produce?

 Mark for Review
(1) Points


1282.25


1282


1300 (*)


1200






13. 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 (*)






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


SELECT ROUND(hire_date, 'MON')
FROM employees;


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



SELECT TRUNC(hire_date, 'MI')
FROM employees;


SELECT ROUND(hire_date, 'MONTH')
FROM employees;






15. 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