Section
10 Quiz
(Answer all questions in this
section)
1. A
multiple-row operator expects how many values?
Mark for Review
(1)
Points
One or more (*)
Only one
Two or more
None
2. 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?
Mark for Review
(1) Points
SELECT last_name, first_name
FROM
f_staffs
WHERE
salary IN
(SELECT
last_name, first_name FROM f_staffs WHERE salary <5000 o:p="">
SELECT last_name, first_name
FROM
f_staffs
WHERE
salary = (SELECT salary FROM f_staffs WHERE salary < 5000);
SELECT last_name, first_name
FROM
f_staffs
WHERE
salary IN (SELECT salary FROM f_staffs WHERE salary > 5000);
(*)
SELECT last_name, first_name
FROM
f_staffs
WHERE
salary = (SELECT salary FROM f_staffs WHERE salary > 5000);
3. Examine
the data in the PAYMENT table:
PAYMENT_ID CUSTOMER_ID PAYMENT_DATE PAYMENT_TYPE PAYMENT_AMOUNT
86590586 8908090 10-Jun-2003 BASIC 859.00
89453485 8549038 15-Feb-2003 INTEREST 596.00
85490345 5489304 20-Mar-2003 BASIC 568.00
This
statement fails when executed:
SELECT
customer_id, payment_type
FROM
payment
WHERE
payment_id =
(SELECT payment_id
FROM payment
WHERE payment_amount = 596.00 OR
payment_date = '20-Mar-2003');
Which
change could correct the problem?
Mark for Review
(1)
Points
Change the outer query WHERE clause
to 'WHERE payment_id IN'. (*)
Remove the parentheses surrounding
the nested SELECT statement.
Change the comparison operator to a
single-row operator.
Remove the quotes surrounding the
date value in the OR clause.
4. 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? Mark for Review
(1)
Points
SELECT description
FROM
d_types
WHERE
code
IN
(SELECT type_code FROM d_songs);
SELECT description
FROM
d_types
WHERE
code = ANY (SELECT type_code FROM d_songs);
SELECT description
FROM
d_types
WHERE
code <> ALL (SELECT type_code FROM d_songs);
All of the above. (*)
5. Group
functions can be used in multiple-row subqueries in the HAVING and GROUP BY
clauses. True or False? Mark for
Review
(1)
Points
True (*)
False
6. The SQL multiple-row subquery extends
the capability of the single-row syntax through the use of which three
comparison operators? Mark for Review
(1)
Points
IN, ANY, and EVERY
IN, ALL, and EVERY
IN, ANY, and EQUAL
IN, ANY, and ALL (*)
7. Which statement about the <>
operator is true? Mark for
Review
(1)
Points
The <> operator can be used
when a single-row subquery returns only one row. (*)
The <> operator is NOT a valid
SQL operator.
The <> operator CANNOT be used
in a single-row subquery.
The <> operator returns the
same result as the ANY operator in a subquery.
8. Single row subqueries may not include
this operator: Mark for Review
(1)
Points
>
<>
ALL (*)
=
9. Examine the structure of the EMPLOYEE,
DEPARTMENT, and ORDERS tables.
EMPLOYEE:
EMPLOYEE_ID
NUMBER(9)
LAST_NAME
VARCHAR2(25)
FIRST_NAME
VARCHAR2(25)
DEPARTMENT_ID
NUMBER(9)
DEPARTMENT:
DEPARTMENT_ID
NUMBER(9)
DEPARTMENT_NAME
VARCHAR2(25)
CREATION_DATE
DATE
ORDERS:
ORDER_ID
NUMBER(9)
EMPLOYEE_ID
NUMBER(9)
DATE
DATE
CUSTOMER_ID
NUMBER(9)
You
want to display all employees who had an order after the Sales department was
established. Which of the following constructs would you use?
Mark for Review
(1)
Points
The HAVING clause
A group function
A single-row subquery (*)
A MERGE statement
10. If
you use the equality operator (=) with a subquery, how many values can the
subquery return? Mark for Review
(1)
Points
Only 1 (*)
Up to 2
Up to 5
Unlimited
11. The
TEACHERS and CLASS_ASSIGNMENTS tables contain these columns:
TEACHERS
TEACHER_ID
NUMBER(5) Primary Key
NAME
VARCHAR2 (25)
SUBJECT_ID
NUMBER(5)
CLASS_ID
NUMBER(5)
CLASS_ASSIGNMENTS
CLASS_ID
NUMBER (5) Primary Key
TEACHER_ID
NUMBER (5)
DATE
MAX_CAPACITY
NUMBER (3)
All
MAX_CAPACITY values are greater than 10. Which two SQL statements correctly use
subqueries? (Choose two.)
Mark for Review
(1)
Points
(Choose all
correct answers)
SELECT *
FROM
teachers
WHERE
teacher_id LIKE (SELECT teacher_id FROM class_assignments WHERE max_capacity
> 1000);
SELECT *
FROM
teachers
WHERE
teacher_id = (SELECT teacher_id FROM class_assignments WHERE class_id = 45963);
(*)
SELECT *
FROM
class_assignments
max_capacity
= (SELECT AVG(max_capacity) FROM class_assignments GROUP BY teacher_id);
SELECT *
FROM
teachers
WHERE
teacher_id = (SELECT teacher_id, class_assignments WHERE max_capacity > 0);
SELECT *
FROM
class_assignments
WHERE
max_capacity = (SELECT AVG(max_capacity) FROM class_assignments);
(*)
12. Which
operator can be used with a multiple-row subquery? Mark for Review
(1)
Points
LIKE
IN (*)
=
<>
13. Which
answer is INCORRECT? The parent statement of a correlated subquery can be: Mark for Review
(1)
Points
A SELECT statement
A DELETE statement
An UPDATE statement
An INSERT statement (*)
14. Oracle
allows you to write named subqueries in one single statement, as long as you
start your statement with the keyword WITH. True or False? Mark for Review
(1)
Points
True (*)
False
15. The
Oracle server performs a correlated subquery when the subquery references a
column from a table referred to in the parent. True or False? Mark for Review
(1)
Points
True (*)
False
1. In a non-correlated subquery, the outer
query always executes prior to the inner query's execution. True or False? Mark for Review
(1)
Points
True
False (*)
2. Subqueries are limited to four per SQL
transaction. True or False? Mark for
Review
(1)
Points
True
False (*)
3. Which statement about the <>
operator is true? Mark for
Review
(1)
Points
The <> operator can be used
when a single-row subquery returns only one row. (*)
The <> operator is NOT a valid
SQL operator.
The <> operator CANNOT be used
in a single-row subquery.
The <> operator returns the
same result as the ANY operator in a subquery.
4. Which statement about subqueries is
true? Mark for Review
(1)
Points
Subqueries should be enclosed in
double quotation marks.
Subqueries generally execute last,
after the main or outer query executes.
Subqueries cannot contain group
functions.
Subqueries are often used in a WHERE
clause to return values for an unknown conditional value. (*)
5. You need to create a report to display
the names of products with a cost value greater than the average cost of all
products. Which SELECT statement should you use? Mark for Review
(1)
Points
SELECT product_name
FROM
products
WHERE
cost > AVG(cost);
SELECT product_name
FROM
products
WHERE
cost > (SELECT AVG(cost)
FROM
products);
(*)
SELECT AVG(cost), product_name
FROM
products
WHERE
cost > AVG(cost)
GROUP
by product_name;
SELECT product_name
FROM
(SELECT AVG(cost) FROM product)
WHERE
cost > AVG(cost);
6. The TEACHERS and CLASS_ASSIGNMENTS
tables contain these columns:
TEACHERS
TEACHER_ID
NUMBER(5) Primary Key
NAME
VARCHAR2 (25)
SUBJECT_ID
NUMBER(5)
CLASS_ID
NUMBER(5)
CLASS_ASSIGNMENTS
CLASS_ID
NUMBER (5) Primary Key
TEACHER_ID
NUMBER (5)
DATE
MAX_CAPACITY
NUMBER (3)
All
MAX_CAPACITY values are greater than 10. Which two SQL statements correctly use
subqueries? (Choose two.)
Mark for Review
(1)
Points
(Choose all
correct answers)
SELECT *
FROM
class_assignments
max_capacity
= (SELECT AVG(max_capacity) FROM class_assignments GROUP BY teacher_id);
SELECT *
FROM
teachers
WHERE
teacher_id = (SELECT teacher_id FROM class_assignments WHERE class_id = 45963);
(*)
SELECT *
FROM
teachers
WHERE
teacher_id LIKE (SELECT teacher_id FROM class_assignments WHERE max_capacity
> 1000);
SELECT *
FROM
class_assignments
WHERE
max_capacity = (SELECT AVG(max_capacity) FROM class_assignments);
(*)
SELECT *
FROM
teachers
WHERE
teacher_id = (SELECT teacher_id, class_assignments WHERE max_capacity > 0);
7. 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. Mark for Review
(1)
Points
WITH CLAUSE
GROUP BY
CORRELATED SUBQUERY (*)
8. Which statement is false? Mark for Review
(1)
Points
The WITH clause retrieves the
results of one or more query blocks.
The WITH clause stores the results
for the user who runs the query.
The WITH clause decreases
performance. (*)
The WITH clause makes the query
simple to read.
9. Correlated Subqueries must reference the
same tables in both the inner and outer queries. (True or False?) Mark for Review
(1)
Points
True
False (*)
10. When
a multiple-row subquery uses the NOT IN operator (equivalent to <>ALL),
if one of the values returned by the inner query is a null value, the entire
query returns: Mark for Review
(1)
Points
All rows that were selected by the
inner query including the null values
No rows returned (*)
All rows that were selected by the
inner query minus the null values
A list of Nulls
11. Examine
the structures of the PARTS and MANUFACTURERS tables:
PARTS:
PARTS_ID
VARCHAR2(25) PK
PARTS_NAME
VARCHAR2(50)
MANUFACTURERS_ID
NUMBER
COST
NUMBER(5,2)
PRICE
NUMBER(5,2)
MANUFACTURERS:
ID
NUMBER PK
NAME
VARCHAR2(30)
LOCATION
VARCHAR2(20)
Assume
that the tables have been populated with data including 100 rows in the PARTS
table, and 20 rows in the MANUFACTURERS table. Which SQL statement correctly
uses a subquery?
Mark for Review
(1)
Points
SELECT parts_name, price, cost
FROM
parts
WHERE
manufacturers_id !=
(SELECT id
FROM
manufacturers
WHERE LOWER(name) = 'cost plus');
SELECT parts_name, price, cost
FROM
parts
WHERE
manufacturers_id IN
(SELECT id
FROM manufacturers m
JOIN parts p
ON (m.id = p.manufacturers_id));
(*)
UPDATE parts SET price = price *
1.15
WHERE
manufacturers_id =
(SELECT id
FROM manufacturers
WHERE UPPER(location)
IN("ATLANTA", "BOSTON", "DALLAS"));
SELECT parts_name
FROM
(SELECT AVG(cost) FROM manufacturers)
WHERE
cost > AVG(cost);
12. You
need to create a SELECT statement that contains a multiple-row subquery. Which
comparison operator(s) can you use?
Mark for Review
(1)
Points
=, <, and >
IN, ANY, and ALL (*)
LIKE
BETWEENï¾…ANDï¾…
13. You
are looking for Executive information using a subquery.
What
will the following SQL statement display?
SELECT
department_id, last_name, job_id
FROM
employees
WHERE
department_id IN
(SELECT department_id FROM departments
WHERE department_name = 'Executive');
Mark for Review
(1)
Points
The department ID, department name,
and last name for every employee in the Executive department
The department ID, last name, and
department name for every Executive in the employees table
The department ID, last name, and
job ID from departments for Executive employees
The department ID, last name, and
job ID for every employee in the Executive department (*)
14. Evaluate
the structure of the EMPLOYEES and DEPART_HIST tables:
EMPLOYEES
EMPLOYEE_ID
NUMBER(9)
LAST_NAME
VARCHAR2(25)
FIRST_NAME
VARCHAR2(25)
DEPARTMENT_ID
NUMBER(9)
MANAGER_ID
NUMBER(9)
SALARY
NUMBER(7,2)
DEPART_HIST:
EMPLOYEE_ID
NUMBER(9)
OLD_DEPT_ID
NUMBER(9)
NEW_DEPT_ID
NUMBER(9)
CHANGE_DATE
DATE
You
want to generate a list of employees who are in department 10, but used to be
in department 15. Which query should you use?
Mark for Review
(1)
Points
SELECT employee_id, last_name,
first_name, department_id
FROM
employees
WHERE
(employee_id, department_id) =
(SELECT employee_id, new_dept_id
FROM depart_hist
WHERE new_dept_id = 15);
SELECT employee_id, last_name,
first_name, department_id
FROM
employees
WHERE
(employee_id, department_id) IN
(SELECT employee_id, dept_id
FROM employees
WHERE old_dept_id = 15);
SELECT employee_id, last_name,
first_name, department_id
FROM
employees
WHERE
(employee_id) IN
(SELECT employee_id
FROM employee_hist
WHERE old_dept_id = 15);
SELECT employee_id, last_name,
first_name, department_id
FROM
employees
WHERE
(employee_id, department_id) IN
(SELECT employee_id, new_dept_id
FROM depart_hist
WHERE old_dept_id = 15) AND new_dept_id =
10;
(*)
15. Which
of the following best describes the meaning of the ANY operator? Mark for Review
(1)
Points
Equal to any member in the list
Equal to each value in the list
Compare value to the first value
returned by the subquery
Compare value to each value returned
by the subquery (*)
1. Table aliases must be used when you are
writing correlated subqueries. (True or false?)
Mark for Review
(1) Points
True
False (*)
2. Oracle allows you to write named
subqueries in one single statement, as long as you start your statement with
the keyword WITH. True or False?
Mark for Review
(1)
Points
True (*)
False
3. Which statement is false? Mark for Review
(1)
Points
The WITH clause decreases
performance. (*)
The WITH clause makes the query simple
to read.
The WITH clause stores the results
for the user who runs the query.
The WITH clause retrieves the
results of one or more query blocks.
4. Subqueries can only be placed in the
WHERE clause. True or False? Mark for
Review
(1)
Points
True
False (*)
5. Which operator can be used with a
multiple-row subquery? Mark for Review
(1)
Points
IN (*)
LIKE
<>
=
6. Using a subquery in which clause will
return a syntax error? Mark for Review
(1)
Points
WHERE
FROM
HAVING
You can use subqueries in all of the
above clauses. (*)
7. Which comparison operator can only be
used with a single-row subquery? Mark
for Review
(1)
Points
IN
<> (*)
ANY
ALL
8. Subqueries are limited to four per SQL
transaction. True or False? Mark for
Review
(1)
Points
True
False (*)
9. 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? Mark for Review
(1)
Points
SELECT *
FROM
employees
WHERE
supervisor_id = (SELECT supervisor_id
FROM employees
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');
(*)
SELECT *
FROM
supervisors
WHERE
supervisor_id =
(SELECT employee_id
FROM
supervisors
WHERE last_name = 'Carter');
10. Which
operator or keyword cannot be used with a multiple-row subquery? Mark for Review
(1)
Points
>
= (*)
ANY
ALL
11. When
a multiple-row subquery uses the NOT IN operator (equivalent to <>ALL),
if one of the values returned by the inner query is a null value, the entire
query returns: Mark for Review
(1)
Points
A list of Nulls
No rows returned (*)
All rows that were selected by the
inner query including the null values
All rows that were selected by the
inner query minus the null values
12. Evaluate
this SQL statement:
SELECT
employee_id, last_name, salary
FROM
employees
WHERE
department_id IN
(SELECT department_id
FROM employees
WHERE salary > 30000 AND salary <
50000);
Which
values will be displayed?
Mark for Review
(1)
Points
Only employees who earn more than
$30,000.
All employees who work in a
department with employees who earn more than $30,000 and more than $50,000.
All employees who work in a
department with employees who earn more than $30,000, but less than $50,000.
(*)
Only employees who earn less than
$50,000.
13. Group
functions can be used in multiple-row subqueries in the HAVING and GROUP BY
clauses. True or False? Mark for
Review
(1)
Points
True (*)
False
14. The
SQL multiple-row subquery extends the capability of the single-row syntax
through the use of which three comparison operators? Mark for Review
(1)
Points
IN, ANY, and ALL (*)
IN, ANY, and EVERY
IN, ALL, and EVERY
IN, ANY, and EQUAL
15. Evaluate
the structure of the EMPLOYEES and DEPART_HIST tables:
EMPLOYEES
EMPLOYEE_ID
NUMBER(9)
LAST_NAME
VARCHAR2(25)
FIRST_NAME
VARCHAR2(25)
DEPARTMENT_ID
NUMBER(9)
MANAGER_ID
NUMBER(9)
SALARY
NUMBER(7,2)
DEPART_HIST:
EMPLOYEE_ID
NUMBER(9)
OLD_DEPT_ID
NUMBER(9)
NEW_DEPT_ID
NUMBER(9)
CHANGE_DATE
DATE
You
want to generate a list of employees who are in department 10, but used to be
in department 15. Which query should you use?
Mark for Review
(1)
Points
SELECT employee_id, last_name,
first_name, department_id
FROM
employees
WHERE
(employee_id, department_id) =
(SELECT employee_id, new_dept_id
FROM depart_hist
WHERE new_dept_id = 15);
SELECT employee_id, last_name,
first_name, department_id
FROM
employees
WHERE
(employee_id, department_id) IN
(SELECT employee_id, dept_id
FROM
employees
WHERE old_dept_id = 15);
SELECT employee_id, last_name,
first_name, department_id
FROM
employees
WHERE
(employee_id, department_id) IN
(SELECT employee_id, new_dept_id
FROM depart_hist
WHERE old_dept_id = 15) AND new_dept_id =
10;
(*)
SELECT employee_id, last_name,
first_name, department_id
FROM
employees
WHERE
(employee_id) IN
(SELECT employee_id
FROM employee_hist
WHERE old_dept_id = 15);
1. The Oracle server performs a correlated
subquery when the subquery references a column from a table referred to in the
parent. True or False? Mark for
Review
(1)
Points
True (*)
False
2. The WITH clause is a way of creating
extra tables in the database. (True or False?)
Mark for Review
(1)
Points
True
False (*)
3. In a correlated subquery, the outer and
inner queries are joined on one or more columns. (True or False?) Mark for Review
(1)
Points
True (*)
False
4. If a single-row subquery returns a null
value and uses the equality comparison operator, what will the outer query
return? Mark for Review
(1)
Points
All the rows in the table
No rows (*)
A null value
An error
5. Examine the structure of the EMPLOYEE,
DEPARTMENT, and ORDERS tables.
EMPLOYEE:
EMPLOYEE_ID
NUMBER(9)
LAST_NAME
VARCHAR2(25)
FIRST_NAME
VARCHAR2(25)
DEPARTMENT_ID
NUMBER(9)
DEPARTMENT:
DEPARTMENT_ID
NUMBER(9)
DEPARTMENT_NAME
VARCHAR2(25)
CREATION_DATE
DATE
ORDERS:
ORDER_ID
NUMBER(9)
EMPLOYEE_ID
NUMBER(9)
DATE
DATE
CUSTOMER_ID
NUMBER(9)
You
want to display all employees who had an order after the Sales department was
established. Which of the following constructs would you use?
Mark for Review
(1)
Points
A MERGE statement
A group function
The HAVING clause
A single-row subquery (*)
6. Single row subqueries may not include
this operator: Mark for Review
(1)
Points
=
>
<>
ALL (*)
7. The SQL multiple-row subquery extends
the capability of the single-row syntax through the use of which three
comparison operators? Mark for Review
(1)
Points
IN, ANY, and EQUAL
IN, ANY, and ALL (*)
IN, ALL, and EVERY
IN, ANY, and EVERY
8. Which of the following best describes
the meaning of the ANY operator?
Mark for Review
(1)
Points
Equal to each value in the list
Equal to any member in the list
Compare value to the first value
returned by the subquery
Compare value to each value returned
by the subquery (*)
9. Which comparison operator would you use
to compare a value to every value returned by a subquery? Mark for Review
(1)
Points
IN
ANY
SOME
ALL (*)
10. A
multiple-row operator expects how many values?
Mark for Review
(1) Points
One or more (*)
Only one
Two or more
None
11. Evaluate
this SELECT statement:
SELECT
player_id, name
FROM
players
WHERE
team_id IN
(SELECT team_id
FROM teams
WHERE team_id > 300 AND salary_cap >
400000);
What
would happen if the inner query returned a NULL value?
Mark for Review
(1)
Points
A syntax error in the outer query
would be returned.
No rows would be returned by the
outer query. (*)
A syntax error in the inner query
would be returned.
All the rows in the PLAYER table
would be returned by the outer query.
12. You
need to create a SELECT statement that contains a multiple-row subquery. Which
comparison operator(s) can you use?
Mark for Review
(1)
Points
LIKE
BETWEENï¾…ANDï¾…
IN, ANY, and ALL (*)
=, <, and >
13. You
need to display all the players whose salaries are greater than or equal to
John Brown's salary. Which comparison operator should you use? Mark for Review
(1)
Points
=
<=
>= (*)
>
14. What
will the following statement return:
SELECT
last_name, salary
FROM
employees
WHERE
salary < (SELECT salary
FROM employees
WHERE employee_id = 103);
Mark for Review
(1)
Points
A list of last_names and salaries of
employees who make more than employee 103
A list of last_names and salaries of
employees who make less than employee 103 (*)
A list of first_names and salaries
of employees making less than employee 103
Nothing. It is an invalid statement.
15. Which
of the following is TRUE regarding the order of subquery execution? Mark for Review
(1)
Points
The subquery executes once after the
main query.
The result of the main query is used
with the subquery.
The subquery executes once before
the main query. (*)
The outer query is executed first.
1. Examine the structure of the EMPLOYEE,
DEPARTMENT, and ORDERS tables.
EMPLOYEE:
EMPLOYEE_ID
NUMBER(9)
LAST_NAME
VARCHAR2(25)
FIRST_NAME
VARCHAR2(25)
DEPARTMENT_ID
NUMBER(9)
DEPARTMENT:
DEPARTMENT_ID
NUMBER(9)
DEPARTMENT_NAME
VARCHAR2(25)
CREATION_DATE
DATE
ORDERS:
ORDER_ID
NUMBER(9)
EMPLOYEE_ID
NUMBER(9)
DATE
DATE
CUSTOMER_ID
NUMBER(9)
You
want to display all employees who had an order after the Sales department was
established. Which of the following constructs would you use?
Mark for Review
(1)
Points
A group function
The HAVING clause
A single-row subquery (*)
A MERGE statement
2. If a single-row subquery returns a null
value and uses the equality comparison operator, what will the outer query
return? Mark for Review
(1)
Points
No rows (*)
A null value
All the rows in the table
An error
3. 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);
Mark for Review
(1)
Points
Only the employees whose job id
matches employee 141 and who work in location 1500 (*)
All employees from Location 1500
will be displayed
All employees with the department id
of 141
An error since you canï¾’t get data from two tables in the
same subquery
4. Which answer is INCORRECT? The parent
statement of a correlated subquery can be:
Mark for Review
(1)
Points
A SELECT statement
An UPDATE statement
A DELETE statement
An INSERT statement (*)
5. Table aliases must be used when you are
writing correlated subqueries. (True or false?)
Mark for Review
(1)
Points
True
False (*)
6. The WITH clause enables a SELECT
statement to define the subquery block at the start of the query, process the
block just once, label the results, and then refer to the results multiple
times. True or False? Mark for
Review
(1)
Points
True (*)
False
7. Which of the following statements is a
true guideline for using subqueries?
Mark for Review
(1)
Points
The outer and inner queries can
reference more than one table. They can get data from different tables. (*)
Do not enclose the subquery in
parentheses.
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.
8. You need to create a report to display
the names of products with a cost value greater than the average cost of all
products. Which SELECT statement should you use? Mark for Review
(1)
Points
SELECT product_name
FROM
(SELECT AVG(cost) FROM product)
WHERE
cost > AVG(cost);
SELECT AVG(cost), product_name
FROM
products
WHERE
cost > AVG(cost)
GROUP
by product_name;
SELECT product_name
FROM
products
WHERE
cost > (SELECT AVG(cost)
FROM
products);
(*)
SELECT product_name
FROM
products
WHERE
cost > AVG(cost);
9. You need to display all the players
whose salaries are greater than or equal to John Brown's salary. Which
comparison operator should you use? Mark
for Review
(1)
Points
>= (*)
=
<=
>
10. Evaluate
this SELECT statement:
SELECT
customer_id, name
FROM
customer
WHERE
customer_id IN
(SELECT customer_id
FROM customer
WHERE state_id = 'GA' AND credit_limit
> 500.00);
What
would happen if the inner query returned null?
Mark for Review
(1)
Points
Only the rows with CUSTOMER_ID
values equal to null would be selected.
No rows would be returned by the
outer query. (*)
An error would be returned.
All the rows in the table would be
selected.
11. Which
statement about the ANY operator, when used with a multiple-row subquery, is
true? Mark for Review
(1)
Points
The ANY operator is a synonym for
the ALL operator.
The ANY operator can be used with
the LIKE and IN operators.
The ANY operator compares every
value returned by the subquery. (*)
The ANY operator can be used with
the DISTINCT keyword.
12. Multiple-row
subqueries must have NOT, IN, or ANY in the WHERE clause of the inner query.
True or False? Mark for Review
(1)
Points
True
False (*)
13. Group
functions can be used in multiple-row subqueries in the HAVING and GROUP BY
clauses. True or False? Mark for
Review
(1)
Points
True (*)
False
14. 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?
Mark for Review
(1)
Points
SELECT last_name, first_name
FROM
f_staffs
WHERE
salary = (SELECT salary FROM f_staffs WHERE salary < 5000);
SELECT last_name, first_name
FROM
f_staffs
WHERE
salary = (SELECT salary FROM f_staffs WHERE salary > 5000);
SELECT last_name, first_name
FROM
f_staffs
WHERE
salary IN (SELECT salary FROM f_staffs WHERE salary > 5000);
(*)
SELECT last_name, first_name
FROM
f_staffs
WHERE
salary IN
(SELECT
last_name, first_name FROM f_staffs WHERE salary <5000 o:p="">
Correct Correct
15. You
need to create a SELECT statement that contains a multiple-row subquery. Which
comparison operator(s) can you use? Mark
for Review
(1)
Points
LIKE
IN, ANY, and ALL (*)
=, <, and >
BETWEENï¾…ANDï¾…
Correct Correct
A correlated subquery is evaluated _____ for each row processed by the parent statement. Mark for Review
BalasHapus(1) Points
ONCE*
EVERY TIME
COMPLETELY
panduan bermain permainan ayam sabung s128
BalasHapusBosan Menang tidak dibayar ? judi sabung ayam
BalasHapusLukisan Sabung Ayam
BalasHapusvery interesting keep posting.
BalasHapusWebdesign
I’ve read some good stuff here. Definitely worth bookmarking for revisiting. I surprise how much effort you put to create such a great informative website. Online Quran Academy
BalasHapus
BalasHapusIt's a Very informative blog and useful article thank you for sharing with us, keep posting learn more about BI Tools Thanks for sharing valuable information.
Power BI Online Training
Artikel terkait cryptocurrency :
BalasHapus1. Panduan Sukses Trading Cryptocurrency
2. Review Cloud Mining Terbaik
3. Review Faucet Crypto Terpercaya
4. Review PTC Bitcoin Terbaik
5. Review Cloud Mining Gratis
6. Review Situs Afiliasi Bitcoin
7. Info Bounty Crypto Terbaru
8. Info Airdrop Token Terbaru
9. Review Tempat Trading Crypto Terbaik
10. Review Wallet Crypto Terbaik
1- A correlated subquery will _______ a candidate row from an outer query, _______ the inner query using candidate row value, and _______ values from the inner query to qualify or disqualify the candidate row.
BalasHapusROLLUP; GRANT; DROP
DELETE; UPDATE; INSERT
GET; EXECUTE; USE (*)
CREATE; EXECUTE; USE
2- Which operator can be used with subqueries that return only one row?
ANY
ALL
IN
LIKE (*)
3- Subqueries can only be placed in the WHERE clause. True or False?
True
False (*)
What is wrong with the following query?
BalasHapusSELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary) FROM employees GROUP BY department_id);
(1/1) Points
Subquery returns more than one row and single row comparison operator is used. (*)
Single rows contain multiple values and a logical operator is used.
Nothing, it will run without problems.
Subquery references the wrong table in the WHERE clause.
Evaluate this SELECT statement that includes a subquery:
SELECT last_name, first_name
FROM customer
WHERE area_code IN
(SELECT area_code
FROM sales
WHERE salesperson_id = 20);
Which statement is true about the given subquery?
(1/1) Points
Both the inner and outer queries must return a value, or an error occurs.
The outer query executes before the nested subquery.
The results of the inner query are returned to the outer query. (*)
An error occurs if either the inner or outer queries do not return a value.
Examine the structures of the CUSTOMER and ORDER_HISTORY tables:
CUSTOMER
CUSTOMER_ID NUMBER(5)
NAME VARCHAR2(25)
CREDIT_LIMIT NUMBER(8,2)
OPEN_DATE DATE
ORDER_HISTORY
ORDER_ID NUMBER(5)
CUSTOMER_ID NUMBER(5)
ORDER_DATE DATE
TOTAL NUMBER(8,2)
Which of the following scenarios would require a subquery to return the desired results?
(1/1) Points
You need to display all the orders that were placed on a certain date.
You need to display the date each customer account was opened.
You need to display all the orders that were placed on the same day as order number 25950. (*)
You need to display each date that a customer placed an order.
Deni Ace: Section 10 Quiz Database Programming With Sql >>>>> Download Now
BalasHapus>>>>> Download Full
Deni Ace: Section 10 Quiz Database Programming With Sql >>>>> Download LINK
>>>>> Download Now
Deni Ace: Section 10 Quiz Database Programming With Sql >>>>> Download Full
>>>>> Download LINK
Deni Ace: Section 10 Quiz Database Programming With Sql >>>>> Download Now
BalasHapus>>>>> Download Full
Deni Ace: Section 10 Quiz Database Programming With Sql >>>>> Download LINK
>>>>> Download Now
Deni Ace: Section 10 Quiz Database Programming With Sql >>>>> Download Full
>>>>> Download LINK 7B
Which best describes a multiple-row subquery?
BalasHapusA query that returns one or more rows from the inner SELECT statement (*)
A query that returns one or more column values from the inner SELECT statement
A query that returns only one column value from the inner SELECT statement
A query that returns only one row from the inner SELECT statement
14. The EMPLOYEES and ORDERS tables contain these columns:
BalasHapusEMPLOYEES
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'; (*)
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';
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';
Fine such a nice content thanks for sharing this Blog, worthy words I have gained more information
BalasHapusB tech CSE in Full stack Development
Cyber Security Course
It's a huge help to have this information. I appreciate you sharing this informative and entertaining information with us.
BalasHapusCoinbase wallet extension
Pro coinbase com
Thanks for sharing these blog :) Elf Bar
BalasHapusLost Mary
FLUM PEBBLE
FUNKY LANDS
Which best describes a single-row subquery?
BalasHapusA query that returns one or more rows from the inner SELECT statement
A query that returns only one row from the inner SELECT statement (*)
A query that returns only one column value from the inner SELECT statement
A query that returns one or more column values from the inner SELECT statement
Examine the data in the PAYMENT table:
BalasHapus>
>
>
>
PAYMENT_ID CUSTOMER_ID PAYMENT_DATE PAYMENT_TYPE PAYMENT_AMOUNT
86590586 8908090 10-Jun-2003 BASIC 859.00
89453485 8549038 15-Feb-2003 INTEREST 596.00
85490345 5489304 20-Mar-2003 BASIC 568.00
This statement fails when executed:
SELECT payment_date, customer_id, payment_amount
FROM payment
WHERE payment_id =
(SELECT payment_id
FROM payment
WHERE payment_date >= '05-Jan-2002' OR payment_amount > 500.00);
Which change could correct the problem?
Remove the subquery WHERE clause.
Remove the single quotes around the date value in the inner query WHERE clause.
Include the PAYMENT_ID column in the select list of the outer query.
Change the outer query WHERE clause to 'WHERE payment_id IN'. (*)
There can be more than one subquery returning information to the outer query. True or False?
BalasHapusTrue (*)
False
What will the following statement return:
BalasHapusSELECT last_name, salary
FROM employees
WHERE (department_id, job_id) = (SELECT department_id, job_id
FROM employees
WHERE employee_id = 103)
A list of last_names and salaries of employees that works in the same department and has the same job_id as that of employee 103. (*)
A list of last_names or salaries of employees that works in the same department and has the same job_id as that of employee 103.
A list of last_names and salaries of employees that works in the same department or has the same job_id as that of employee 103.
Nothing. It is an invalid statement.
Evaluate this SELECT statement:
BalasHapusSELECT student_id, last_name, first_name
FROM student
WHERE major_id NOT IN
(SELECT major_id
FROM majors
WHERE department_head_id = 30 AND title = 'ADJUNCT');
What would happen if the inner query returned a NULL value row?
All the rows in the STUDENT table would be displayed.
No rows would be returned from the STUDENT table. (*)
A syntax error would be returned.
Only the rows with STUDENT_ID values equal to NULL would be displayed.
In a subquery, the ALL operator compares a value to every value returned by the inner query. True or False?
BalasHapusTrue (*)
False
If the subquery returns no rows, will the outer query return any values?
BalasHapusYes, Oracle will find the nearest value and rewrite your statement implicitly when you run it.
No, because the subquery will be treated like a null value. (*)
Yes. It will just run and ignore the subquery.
No, because you are not allowed to return empty values from a subquery.
Which of the following is a valid reason why the query below will not execute successfully?
BalasHapusSELECT 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. (*)
Second subquery found on the right instead of the left side of the operator.
First subquery not enclosed in parentheses.
The greater than operator is not valid.
What would happen if you attempted to use a single-row operator with a multiple-row subquery?
BalasHapusNo rows will be selected.
The data returned may or may not be correct.
An error would be returned. (*)
All the rows will be selected.
Group functions can be used in subqueries even though they may return many rows. True or False?
BalasHapusTrue (*)
False
What will the following statement return:
BalasHapusSELECT last_name, salary
FROM employees
WHERE salary < (SELECT salary
FROM employees
WHERE employee_id = 103);
Mark for Review
(1) Points
A list of last_names and salaries of employees who make more than employee 103
A list of last_names and salaries of employees who make less than employee 103 (*)
A list of first_names and salaries of employees making less than employee 103
Nothing. It is an invalid statement.
10. Evaluate the structure of the EMPLOYEES and DEPART_HIST tables:
BalasHapusEMPLOYEES
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER(9)
MANAGER_ID NUMBER(9)
SALARY NUMBER(7,2)
DEPART_HIST:
EMPLOYEE_ID NUMBER(9)
OLD_DEPT_ID NUMBER(9)
NEW_DEPT_ID NUMBER(9)
CHANGE_DATE DATE
You want to generate a list of employees who are in department 10, but used to be in department 15. Which query should you use?
SELECT employee_id, last_name, first_name, department_id
FROM employees
WHERE (employee_id, department_id) IN
(SELECT employee_id, new_dept_id
FROM depart_hist
WHERE old_dept_id = 15 AND new_dept_id = 10); (*)
SELECT employee_id, last_name, first_name, department_id
FROM employees
WHERE (employee_id, department_id) IN
(SELECT employee_id, dept_id
FROM employees
WHERE old_dept_id = 15);
SELECT employee_id, last_name, first_name, department_id
FROM employees
WHERE (employee_id, department_id) =
(SELECT employee_id, new_dept_id
FROM depart_hist
WHERE new_dept_id = 15);
SELECT employee_id, last_name, first_name, department_id
FROM employees
WHERE (employee_id) IN
(SELECT employee_id
FROM employee_hist
WHERE old_dept_id = 15);
Which statement about single-row and multiple-row subqueries is true?
BalasHapusMultiple-row subqueries can be used with both single-row and multiple-row operators.
Multiple-row subqueries can only be used in SELECT statements.
Single-row operators can be used with both single-row and multiple-row subqueries.
Multiple-row subqueries cannot be used with the LIKE operator. (*)