1. Which comparison operator can only be used with a single-row subquery? Mark for Review
(1) Points

ANY

ALL

(*)

IN

Correct Correct

2. Which best describes a single-row subquery? Mark for Review
(1) Points

A query that returns only one row from the inner SELECT statement (*)

A query that returns one or more rows 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

Correct Correct

3. Which statement is false? Mark for Review
(1) Points

The WITH clause retrieves the results of one or more query blocks

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

Correct Correct

4. Which answer is incorrect? The parent statement can be: Mark for Review
(1) Points

A SELECT statement

An INSERT statement (*)

An UPDATE statement

A DELETE statement

Correct Correct

5. A correlated subquery is evaluated _____ for each row processed by the parent statement. Mark for Review
(1) Points

EVERY TIME

ONCE (*)

COMPLETELY

Correct Correct

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

Correct Correct

7. 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

=

>

= (*)

Correct Correct

8. 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. (*)

Correct Correct

9. 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 > (SELECT AVG(cost)
FROM product);

(*)

SELECT product_name
FROM products
WHERE cost > AVG(cost);

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);

Correct Correct

10. Which of the following is TRUE regarding the order of subquery execution? Mark for Review
(1) Points

The outer query is executed first

The subquery executes once after the main query

The subquery executes once before the main query (*)

The result of the main query is used with the subquery

Correct Correct

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?
Mark for Review
(1) Points

You need to display the date each customer account was opened.

You need to display each date that a customer placed an order.

You need to display all the orders that were placed on a certain date.

You need to display all the orders that were placed on the same day as order number 25950. (*)

Correct Correct

12. Which statement about subqueries is true? Mark for Review
(1) Points

Subqueries should be enclosed in double quotation marks.

Subqueries cannot contain group functions.

Subqueries are often used in a WHERE clause to return values for an unknown conditional value. (*)

Subqueries generally execute last, after the main or outer query executes.

Correct Correct

13. Which operator can be used with a multiple-row subquery? Mark for Review
(1) Points

IN (*)

=

LIKE

Correct Correct

14. The EMPLOYEES and ORDERS tables contain these columns:

EMPLOYEES
EMPLOYEE_ID NUMBER(10) NOT NULL PRIMARY KEY
FIRST_NAME VARCHAR2(30)
LAST_NAME VARCHAR2(30)
ADDRESS VARCHAR2(25)
CITY VARCHAR2(20)
STATE VARCHAR2(2)
ZIP NUMBER(9)
TELEPHONE NUMBER(10)

ORDERS

ORDER_ID NUMBER(10) NOT NULL PRIMARY KEY
EMPLOYEE_ID NUMBER(10) NOT NULL FOREIGN KEY
ORDER_DATE DATE
TOTAL NUMBER(10)

Which SELECT statement will return all orders generated by a sales representative named Franklin during the year 2001?
Mark for Review
(1) Points

SELECT order_id, total
FROM ORDERS (SELECT employee_id
FROM employees
WHERE last_name = ‘Franklin’)
WHERE order_date BETWEEN ‘01-jan-01′ AND ‘31-dec-01′;

SELECT (SELECT employee_id FROM employees WHERE last_name = ‘Franklin’) AND order_id, total
FROM ORDERS
WHERE order_date BETWEEN ‘01-jan-01′ AND ‘31-dec-01′;

SELECT order_id, employee_id, total
FROM ORDERS
WHERE order_date BETWEEN ‘01-jan-01′ AND ‘31-dec-01′ AND emp_id = ‘Franklin’;

SELECT order_id, total
FROM ORDERS
WHERE employee_id = (SELECT employee_id FROM employees WHERE last_name = ‘Franklin’)
AND order_date BETWEEN ‘01-jan-01′ AND ‘31-dec-01′;

(*)

Correct Correct

15. 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)

Which SQL statement correctly uses a subquery?
Mark for Review
(1) Points

UPDATE parts SET price = price * 1.15
WHERE manufacturers_id =
(SELECT id
FROM manufacturers
WHERE UPPER(location) IN(’ ATLANTA ‘, ム BOSTON ‘, ム DALLAS ‘));

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 part p
ON (m.id = p.manufacturers_id));

(*)

SELECT parts_name
FROM (SELECT AVG(cost) FROM manufacturers)
WHERE cost > AVG(cost);

Correct Correct

16. What is wrong with the following query?
SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary) FROM employees GROUP BY department_id); Mark for Review
(1) Points

Single rows contain multiple values and a logical operator is used.

Subquery returns more than one row and single row comparison operator is used. (*)

Subquery references the wrong table in the WHERE clause.

Nothing, it will run without problems.

Correct Correct

17. 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 10000 (*)

WHERE hire_date > AVG(hire_date)

Correct Correct

39. Evaluate this SELECT statement:

SELECT SUM(salary), department_id, manager_id
FROM employees
GROUP BY department_id, manager_id;

Which SELECT clause allows you to restrict the rows returned, based on a group function?
Mark for Review
(1) Points

HAVING SUM(salary) > 100000 (*)

WHERE SUM(salary) > 100000

WHERE salary > 100000

HAVING salary > 100000

Correct Correct

40. The MANUFACTURER table contains these columns:

MANUFACTURER_ID NUMBER
MANUFACTURER_NAME VARCHAR2(30)
TYPE VARCHAR2(25)
LOCATION_ID NUMBER

You need to display the number of unique types of manufacturers at each location. Which SELECT statement should you use?
Mark for Review
(1) Points

SELECT location_id, COUNT(DISTINCT type)
FROM manufacturer
GROUP BY location_id;

(*)

SELECT location_id, COUNT(DISTINCT type)
FROM manufacturer;

SELECT location_id, COUNT(type)
FROM manufacturer
GROUP BY location_id;

SELECT location_id, COUNT(DISTINCT type)
FROM manufacturer
GROUP BY type;

41. The PRODUCTS table contains these columns:

PROD_ID NUMBER(4)
PROD_NAME VARCHAR(20)
PROD_CAT VARCHAR2(15)
PROD_PRICE NUMBER(5)
PROD_QTY NUMBER(4)

You need to identify the minimum product price in each product category.
Which statement could you use to accomplish this task?
Mark for Review
(1) Points

SELECT prod_cat, MIN (prod_price)
FROM products
GROUP BY prod_price;

SELECT prod_cat, MIN (prod_price)
FROM products
GROUP BY prod_cat;

(*)

SELECT MIN (prod_price), prod_cat
FROM products
GROUP BY MIN (prod_price), prod_cat;

SELECT prod_price, MIN (prod_cat)
FROM products
GROUP BY prod_cat;

Incorrect Incorrect. Refer to Section 5 Lesson 1.

42. What is the best explanation as to why this SQL statement will NOT execute?

SELECT department_id “Department”, AVG (salary)”Average”
FROM employees
GROUP BY Department;
Mark for Review
(1) Points

Salaries cannot be averaged as not all the numbers will divide evenly.

You cannot use a column alias in the GROUP BY clause. (*)

The GROUP BY clause must have something to GROUP.

The department id is not listed in the departments table.

Correct Correct

43. Which statement about group functions is true? Mark for Review
(1) Points

Group functions ignore null values. (*)

Group functions can only be used in a SELECT list.

Group functions can be used in a WHERE clause.

A query that includes a group function in the SELECT list must include a GROUP BY clause.

Correct Correct

44. Evaluate this SELECT statement:

SELECT SUM(salary), department_id, department_name
FROM employees
WHERE department_id = 1
GROUP BY department;

Which clause of the SELECT statement contains a syntax error?
Mark for Review
(1) Points

SELECT

FROM

WHERE

GROUP BY (*)

Correct Correct

45. Evaluate this SELECT statement:

SELECT COUNT(emp_id), mgr_id, dept_id
FROM employees
WHERE status = ‘I’
GROUP BY dept_id
HAVING salary > 30000
ORDER BY 2;

Why does this statement return a syntax error?
Mark for Review
(1) Points

MGR_ID must be included in the GROUP BY clause. (*)

The HAVING clause must specify an aggregate function.

A single query cannot contain a WHERE clause and a HAVING clause.

The ORDER BY clause must specify a column name in the EMPLOYEE table.

Correct Correct

46. The EMPLOYEES table contains these columns:

ID_NUMBER NUMBER Primary Key
NAME VARCHAR2 (30)
DEPARTMENT_ID NUMBER
SALARY NUMBER (7,2)
HIRE_DATE DATE

Evaluate this SQL statement:

SELECT id_number, name, department_id, SUM(salary)
FROM employees
WHERE salary > 25000
GROUP BY department_id, id_number, name
ORDER BY hire_date;

Why will this statement cause an error?
Mark for Review
(1) Points

The HAVING clause is missing.

The WHERE clause contains a syntax error.

The SALARY column is NOT included in the GROUP BY clause.

The HIRE_DATE column is NOT included in the GROUP BY clause. (*)

Correct Correct

47. You want to write a report that returns the average salary of all employees in the company, sorted by departments.
The EMPLOYEES table contains the following columns:

EMPLOYEES:
EMP_ID NUMBER(10) PRIMARY KEY
LNAME VARCHAR2(20)
FNAME VARCHAR2(20)
DEPT VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(10)

Which SELECT statement will return the information that you require?
Mark for Review
(1) Points

SELECT salary (AVG)
FROM employees
GROUP BY dept;

SELECT AVG (salary)
FROM employees
GROUP BY dept;

(*)

SELECT AVG (salary)
FROM employees
BY dept;

SELECT AVG salary
FROM employees
BY dept;

Correct Correct

48. Evaluate this SELECT statement:

SELECT COUNT(employee_id), department_id
FROM employees
GROUP BY department_id;

You only want to include employees who earn more than 15000.
Which clause should you include in the SELECT statement?
Mark for Review
(1) Points

WHERE salary > 15000 (*)

HAVING salary > 15000

WHERE SUM(salary) > 15000

HAVING SUM(salary) > 15000

Correct Correct

49. When using SET operators the names of the columns must be identified in all of the SELECT statements used in the query. True or False? Mark for Review
(1) Points

True

False (*)

Correct Correct

50. The ___________ operator returns all rows from both tables, after eliminating duplicates. Mark for Review
(1) Points

UNION (*)

UNION ALL

INTERSECT

MINUS

Correct Correct

Advertisements