Database programming section 2

1. The EMPLOYEES table contains these columns:
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
EMAIL VARCHAR2(50)
You are writing a SELECT statement to retrieve the names of employees that have an email address.
SELECT last_name||', '||first_name "Employee Name"
FROM employees;
Which WHERE clause should you use to complete this statement?
Mark for Review (1) Point
WHERE email IS NULL; WHERE email IS NOT NULL;(*)
WHERE email != NULL; WHERE email = NULL;

2. If you write queries using the BETWEEN operator, it does not matter in what order you enter the values, i.e. BETWEEN low value AND high value will give the same result as BETWEEN high value and low value. True or False?
Mark for Review (1) Point
True
False(*)

3. When using the "LIKE" operator, the % and _ symbols can be used to do a pattern-matching, wild card search. True or False?

Mark for Review (1) Point
True(*)
False


4. Which of the following are examples of comparison operators used in the WHERE clause?
Mark for Review (1) Point
=, >, <, <=, >=, <> in (..,..,.. )
between ___ and ___ like
is null All of the above(*)

5. The EMPLOYEES table includes these columns:
EMPLOYEE_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(15) NOT NULL
FIRST_NAME VARCHAR2(10) NOT NULL
HIRE_DATE DATE NOT NULL
You want to produce a report that provides the last names, first names, and hire dates of those employees who were hired between March 1, 2000, and August 30, 2000. Which statements can you issue to accomplish this task?
 Mark for Review (1) Point

SELECT last_name, first_name, hire_date
FROM employees
WHERE hire_date BETWEEN '01-Mar-2000' AND '30-Aug-2000';
(*)

6. Which comparison operator searches for a specified character pattern?
Mark for Review (1) Point
BETWEEN… AND… LIKE(*)
IS NULL IN

7. Which of the following is NOT BEING DONE in this SQL statement?
SELECT first_name || ' ' || last_name "Name"
FROM employees;
Mark for Review (1) Point
Concatenating first name, middle name and last name (*)
Putting a space between first name and last name
Selecting columns from the employees table
Using a column alias

8. You need to display all the rows in the EMPLOYEES table that contain a null value in the DEPARTMENT_ID column. Which comparison operator should you use?
Mark for Review (1) Point
“=NULL”
IS NULL
ISNULL
NULL!

9. The Concatenation Operator does which of the following?

Mark for Review (1) Point
Links rows of data together inside the database.
Separates columns.
Links two or more columns or literals to form a single output column (*)
Is represented by the asterisk (*) symbol

10. You need to display only unique combinations of the LAST_NAME and MANAGER_ID columns in the EMPLOYEES table. Which keyword should you include in the SELECT clause?
Mark for Review (1) Point
UNIQUEONE
DISTINCTROW
DISTINCT
ONLY

11. You need to display all the employees whose last names (of any length) start with the letters 'Sm' . Which WHERE clause should you use?

Mark for Review (1) Point

WHERE last_name LIKE '_Sm'
WHERE last_name LIKE ‘Sm_'
WHERE last_name LIKE 'Sm%'(*)

WHERE last_name LIKE '%Sm'

12. How can you write "not equal to" in the WHERE-clause?
Mark for Review (1) Point
!=
^=
<>
All of the above(*)

13. Which of the following are true? (Choose Two)

(Choose all correct answers)
Mark for Review (1) Point

Date values are enclosed in single quotation marks (*)
Date values are format-sensitive (*)
Character values are not case-sensitive
Character strings are enclosed in double quotation marks

14. Which of the following is true?
Mark for Review (1) Point
Character strings must be enclosed in double quotation marks
Date values are enclosed in single quotation marks (*)
Date values are not format-sensitive
Character values are not case-sensitive

15. You want to retrieve a list of customers whose last names begin with the letters 'Fr' . Which keyword should you include in the WHERE clause of your SELECT statement to achieve the desired result?
Mark for Review (1) Point
LIKE(*)
AND
BETWEEN
IN