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
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 (*)
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
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
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
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
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="">
(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
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 (*)
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
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
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
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
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="">
Comments
Post a Comment