Database programming section 10

1. Which statement about the <> operator is true?
The <> operator can be used when a single-row subquery returns only one row. (*)

2. The result of this statement will be:
SELECT last_name, job_id, salary, department_id
FROM employees
WHERE job_id =
     (SELECT job_id
      FROM employees
      WHERE employee_id = 141) AND
    department_id =
     (SELECT department_id
      FROM departments
      WHERE location_id =1500);
Only the employees whose job id matches employee 141 and who work in location 1500 (*)

3. In a non-correlated subquery, the outer query always executes prior to the inner query's execution. True or False?
False (*)

4. When creating a report of all employees earning more than the average salary for their department, a __________ ____________ can be used to first calculate the average salary of each department, and then compare the salary for each employee to the average salary of that employeeﾒs department.
CORRELATED SUBQUERY (*)

5. In a correlated subquery, the outer and inner queries are joined on one or more columns. (True or False?)
True (*)

6. A correlated subquery is evaluated _____ for each row processed by the parent statement.
ONCE (*)

7. You need to create a SELECT statement that contains a multiple-row subquery. Which comparison operator(s) can you use?
IN, ANY, and ALL (*)

8. Which of the following is a valid reason why the query below will not execute successfully?
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id =
    (SELECT department_id FROM employees WHERE last_name like '%u%');
A single, rather than a multiple value operator was used. (*)

9. The salary column of the f_staffs table contains the following values:
4000
5050
6000
11000
23000
Which of the following statements will return the last_name and first_name of those employees who earn more than 5000?

SELECT last_name, first_name
FROM f_staffs
WHERE salary IN (SELECT salary FROM f_staffs WHERE salary > 5000); (*)

10. Group functions can be used in multiple-row subqueries in the HAVING and GROUP BY clauses. True or False?
True (*)

11. Which of the following statements contains a comparison operator that is used to restrict rows based on a list of values returned from an inner query?
All of the above. (*)

12. What would happen if you attempted to use a single-row operator with a multiple-row subquery?
An error would be returned. (*)

13. Which operator can be used with a multiple-row subquery?
IN (*)

14. Using a subquery in which clause will return a syntax error?
You can use subqueries in all of the above clauses. (*)

15. The EMPLOYEES and ORDERS tables contain these columns:
EMPLOYEES
EMPLOYEE_ID NUMBER(10) NOT NULL PRIMARY KEY
FIRST_NAME VARCHAR2(30)
LAST_NAME VARCHAR2(30)
ADDRESS VARCHAR2(25)
CITY VARCHAR2(20)
STATE VARCHAR2(2)
ZIP NUMBER(9)
TELEPHONE NUMBER(10)
ORDERS
ORDER_ID NUMBER(10) NOT NULL PRIMARY KEY
EMPLOYEE_ID NUMBER(10) NOT NULL FOREIGN KEY
ORDER_DATE DATE
TOTAL NUMBER(10)
Which SELECT statement will return all orders generated by a sales representative named Franklin during the year 2001?
SELECT order_id, total
FROM ORDERS
WHERE employee_id = (SELECT employee_id FROM employees WHERE last_name = 'Franklin')
AND order_date BETWEEN '01-Jan-2001' AND '31-Dec-2001';
(*)