Section 10
The EMPLOYEES and ORDERS tables contain these columns:
EMPLOYEES
EMPLOYEE_ID NUMBER(10) NOT NULL PRIMARY KEY
FIRST_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';
(*)
SELECT
order_id, total
FROM ORDERS (SELECT employee_id
FROM employees
WHERE last_name = 'Franklin')
WHERE order_date BETWEEN '01-Jan-2001' AND '31-Dec-2001';
SELECT
order_id, employee_id, total
FROM ORDERS
WHERE order_date BETWEEN '01-Jan-2001' AND '31-Dec-2001' AND
emp_id = 'Franklin';
SELECT
(SELECT employee_id FROM employees WHERE last_name = 'Franklin') AND order_id,
total
FROM ORDERS
WHERE order_date BETWEEN '01-Jan-2001' AND '31-Dec-2001';
Which of the following
statements is a true guideline for using subqueries?
The
outer and inner queries can reference more than one table. They can get data
from different tables. (*)
Only
one WHERE clause can be used for a SELECT statement, and if specified, it must
be the outer query.
Place
the subquery on the left side of the comparison condition.
Do not
enclose the subquery in parentheses.
Using a subquery in which
clause will return a syntax error?
You can
use subqueries in all of the above clauses. (*)
HAVING
FROM
WHERE
Which comparison operator would
you use to compare a value to every value returned by a subquery?
IN
ANY
SOME
ALL (*)
A multiple-row operator expects
how many values?
One or
more (*)
Only
one
Two or
more
None
Which statement about single-row and multiple-row subqueries
is true?
Single-row
operators can be used with both single-row and multiple-row subqueries.
Multiple-row
subqueries can only be used in SELECT statements.
Multiple-row
subqueries can be used with both single-row and multiple-row operators.
Multiple-row
subqueries cannot be used with the LIKE operator. (*)
What would happen if you
attempted to use a single-row operator with a multiple-row subquery?
All the
rows will be selected.
An
error would be returned. (*)
No rows
will be selected.
The
data returned may or may not be correct.
Which operator or keyword
cannot be used with a multiple-row subquery?
= (*)
ANY
>
ALL
The SQL multiple-row subquery
extends the capability of the single-row syntax through the use of which three
comparison operators?
IN,
ANY, and EQUAL
IN,
ALL, and EVERY
IN,
ANY, and ALL (*)
IN,
ANY, and EVERY
You
need to produce a report that contains all employee-related information for
those employees who have Brad Carter as a supervisor. However, you are not sure
which supervisor ID belongs to Brad Carter. Which query should you issue to
accomplish this task?
SELECT
*
FROM employees
WHERE supervisor_id =
(SELECT supervisor_id
FROM employees
WHERE last_name =
'Carter');
SELECT
*
FROM supervisors
WHERE supervisor_id =
(SELECT employee_id
FROM supervisors
WHERE last_name =
'Carter');
SELECT
*
FROM supervisors
WHERE supervisor_id =
(SELECT
supervisor_id
FROM employees
WHERE last_name =
'Carter');
SELECT
*
FROM employees
WHERE supervisor_id =
(SELECT
employee_id
FROM employees
WHERE last_name =
'Carter');
(*)
Subqueries are limited to four per SQL transaction. True or
False?
True
False
(*)
Which
statement about the <> operator is true?
The
<> operator is NOT a valid SQL operator.
The
<> operator can be used when a single-row subquery returns only one row.
(*)
The
<> operator returns the same result as the ANY operator in a subquery.
The
<> operator CANNOT be used in a single-row subquery.
The
Oracle server performs a correlated subquery when the subquery references a
column from a table referred to in the parent. True or False?
True
(*)
False
Table
aliases must be used when you are writing correlated subqueries. (True or
false?)
True
False
(*)
The
WITH clause is a way of creating extra tables in the database. (True or False?)
True
False
(*)
No comments:
Post a Comment