Test: Mid-Term Exam Semester 2 Part Two

Test: Mid-Term Exam Semester 2 Part Two



Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Semester 2 Mid Term Exam Part II covers Sections 5-7 of Database Programming with SQL.


Section 5
(Answer all questions in this section)

1. Group functions can be nested to a depth of? Mark for Review
(1) Points

Three

Four

olor=darkred]arkred] Two (*)

Group functions cannot be nested.


Incorrect. Refer to Section 5 Lesson 1.

red]or=red]
2. The PLAYERS and TEAMS tables contain these columns:
PLAYERS
PLAYER_ID NUMBER NOT NULL, PRIMARY KEY
LAST_NAME VARCHAR2 (30) NOT NULL
FIRST_NAME VARCHAR2 (25) NOT NULL
TEAM_ID NUMBER
POSITION VARCHAR2 (25)

TEAMS
TEAM_ID NUMBER NOT NULL, PRIMARY KEY
TEAM_NAME VARCHAR2 (25) [/color]

You need to create a report that lists the names of each team with more than three goal keepers.
Which SELECT statement will produce the desired result?
Mark for Review
(1) Points

SELECT t.team_name, COUNT(p.player_id)
FROM players p, teams t
ON (p.team_id = t.team_id)
WHERE UPPER(p.position) = 'GOAL KEEPER'
GROUP BY t.team_name;


SELECT t.team_name, COUNT(p.player_id)
FROM players
JOIN teams t ON (p.team_id = t.team_id)
WHERE UPPER(p.position) = 'GOAL KEEPER'
HAVING COUNT(p.player_id) > 3;


SELECT t.team_name, COUNT(p.player_id)
FROM players p, teams t
ON (p.team_id = t.team_id)
WHERE UPPER(p.position) = 'GOAL KEEPER'
GROUP BY t.team_name
HAVING COUNT(p.player_id) > 3;


SELECT t.team_name, COUNT(p.player_id)
FROM players p
JOIN teams t ON (p.team_id = t.team_id)
WHERE UPPER(p.position) = 'GOAL KEEPER'
GROUP BY t.team_name
HAVING COUNT(p.player_id) > 3;
(*)



Incorrect. Refer to Section 5 Lesson 1.


3. Evaluate this statement:
SELECT department_id, AVG(salary)
FROM employees
WHERE job_id <> 69879
GROUP BY job_id, department_id
HAVING AVG(salary) > 35000
ORDER BY department_id;

Which clauses restricts the result? Choose two.
Mark for Review
(1) Points

(Choose all correct answers)

SELECT department_id, AVG(salary)

WHERE job_id <> 69879 (*)

GROUP BY job_id, department_id

HAVING AVG(salary) > 35000 (*)


Incorrect. Refer to Section 5 Lesson 1.


4. What will the following SQL Statement do?
SELECT job_id, COUNT(*)
FROM employees
GROUP BY job_id;
Mark for Review
(1) Points

Displays all the employees and groups them by job.

Displays each job id and the number of people assigned to that job id. (*)

Displays only the number of job_ids.

Displays all the jobs with as many people as there are jobs.


Correct


5. What is the correct order of clauses in a SELECT statement? Mark for Review
(1) Points

SELECT
FROM
WHERE
ORDER BY
GROUP BY
HAVING


SELECT
FROM
HAVING
GROUP BY
WHERE
ORDER BY


SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
(*)


SELECT
FROM
WHERE
HAVING
ORDER BY
GROUP BY



Correct


6. The PAYMENT table contains these columns:
PAYMENT_ID NUMBER(9) PK
PAYMENT_DATE DATE
CUSTOMER_ID NUMBER(9)

Which SELECT statement could you use to display the number of times each customer payment was made between January 1, 2003 and June 30, 2003 ?
Mark for Review
(1) Points

SELECT customer_id, COUNT(payment_id)
FROM payment
WHERE payment_date BETWEEN '01-JAN-2003' AND '30-JUN-2003'
GROUP BY customer_id;
(*)


SELECT COUNT(payment_id)
FROM payment
WHERE payment_date BETWEEN '01-JAN-2003' AND '30-JUN-2003';


SELECT customer_id, COUNT(payment_id)
FROM payment
WHERE payment_date BETWEEN '01-JAN-2003' AND '30-JUN-2003';


SELECT COUNT(payment_id)
FROM payment
WHERE payment_date BETWEEN '01-JAN-2003' AND '30-JUN-2003'
GROUP BY customer_id;



Incorrect. Refer to Section 5 Lesson 1.


7. The PLAYERS table contains these columns:
PLAYER_ID NUMBER PK
PLAYER_NAME VARCHAR2 (30)
TEAM_ID NUMBER
HIRE_DATE DATE
SALARY NUMBER (8,2)

Which clauses represent valid uses of aggregate functions? (Choose three.)
Mark for Review
(1) Points

(Choose all correct answers)

ORDER BY AVG(salary) (*)

GROUP BY MAX(salary)

SELECT AVG(NVL(salary, 0)) (*)

HAVING MAX(salary) > 10000 (*)

WHERE hire_date > AVG(hire_date)


Incorrect. Refer to Section 5 Lesson 1.


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



Correct


9. The PRODUCTS table contains these columns:
PRODUCT_ID NUMBER(9) PK
CATEGORY_ID VARCHAR2(10)
LOCATION_ID NUMBER(9)
DESCRIPTION VARCHAR2(30)
COST NUMBER(7,2)
PRICE NUMBER(7,2)
QUANTITY NUMBER

You display the total of the extended costs for each product category by location.
You need to include only the products that have a price less than $25.00.
The extended cost of each item equals the quantity value multiplied by the cost value.
Which SQL statement will display the desired result?
Mark for Review
(1) Points

SELECT category_id, SUM(cost * quantity) TOTAL,location_id
FROM products
WHERE price > 25.00
GROUP BY category_id, location_id;


SELECT SUM(cost * quantity) TOTAL, location_id
FROM products
WHERE price < 25.00 <br /> GROUP BY location_id;


SELECT category_id, SUM(cost * quantity) TOTAL, location_id
FROM products
WHERE price < 25.00 <br /> GROUP BY category_id, location_id;
(*)


SELECT SUM(cost * quantity) TOTAL
FROM products
WHERE price < 25.00;



Incorrect. Refer to Section 5 Lesson 1.


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



Page 1 of 5







Test: Mid-Term Exam Semester 2 Part Two



Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Semester 2 Mid Term Exam Part II covers Sections 5-7 of Database Programming with SQL.


Section 5
(Answer all questions in this section)

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


12. Evaluate this SELECT statement:
SELECT MIN(hire_date), deptartment_id
FROM employees
GROUP BY department_id;
Which values are displayed?
Mark for Review
(1) Points

The earliest hire date in each department. (*)

The the earliest hire date in the EMPLOYEES table.

The latest hire date in the EMPLOYEES table.

The hire dates in the EMPLOYEES table that contain NULL values.


Correct


13. Evaluate this SELECT statement:
SELECT MAX(salary), department_id
FROM employees
GROUP BY department_id;
Which values are displayed?
Mark for Review
(1) Points

The highest salary for all employees.

The highest salary in each department. (*)

The employees with the highest salaries.

The employee with the highest salary for each department.


Correct


14. You use GROUPING function to: Mark for Review
(1) Points

Produce subtotal and cross-tabulated values

Identify the extra row values created by either a ROLLUP or CUBE operation (*)

Aggregate rows using SUM, MIN, MAX, and COUNT


Incorrect. Refer to Section 5 Lesson 2.


15. CUBE can be applied to all aggregate functions including AVG, SUM, MIN, MAX, and COUNT. True or False? Mark for Review
(1) Points

True (*)

False


Incorrect. Refer to Section 5 Lesson 2.


16. You use ROLLUP to: Mark for Review
(1) Points

produce subtotal values (*)

cross-tabulate values

produce a single result set


Incorrect. Refer to Section 5 Lesson 2.


17. You use GROUPING function to ______ database rows from tabulated rows. Mark for Review
(1) Points

CREATE

DISTINGUISH (*)

COMPUTE

COUNT


Correct


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


19. To control the order of rows returned using SET operators, the ORDER BY clause is used ______ and is placed in the _____ SELECT statement of the query. Mark for Review
(1) Points

ONCE; FIRST

TWICE; FIRST

ONCE; LAST (*)

IN ALL; LAST


Correct




Section 7
(Answer all questions in this section)

20. The EMPLOYEES table contains the following columns:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPTARTMENT_ID VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(9,2)
BONUS NUMBER(9,2)

You need to increase the salary for all employees in department 10 by 10 percent. You also need to increase the bonus for all employees in department 10 by 15 percent. Which statement should you use?
Mark for Review
(1) Points

UPDATE employees
SET salary = salary * 1.10, bonus = bonus * 1.15
WHERE department_id = 10;
(*)


UPDATE employees
SET salary = salary * 1.10 AND bonus = bonus * 1.15
WHERE department_id = 10;


UPDATE employees
SET (salary = salary * 1.10) SET (bonus = bonus * 1.15)
WHERE department_id = 10;


UPDATE employees
SET salary = salary * .10, bonus = bonus * .15
WHERE department_id = 10;



Correct



Page 2 of 5








Test: Mid-Term Exam Semester 2 Part Two



Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Semester 2 Mid Term Exam Part II covers Sections 5-7 of Database Programming with SQL.


Section 7
(Answer all questions in this section)

21. What would happen if you issued a DELETE statement without a WHERE clause? Mark for Review
(1) Points

All the rows in the table would be deleted. (*)

An error message would be returned.

No rows would be deleted.

Only one row would be deleted.


Incorrect. Refer to Section 7 Lesson 2.


22. Which of the following represents the correct syntax for an INSERT statement? Mark for Review
(1) Points

INSERT VALUES INTO customers (3178 J. Smith 123 Main Street Nashville TN 37777;

INSERT INTO customers VALUES '3178' 'J.' 'Smith' '123 Main Street' 'Nashville' 'TN' '37777';

INSERT INTO customers VALUES ('3178', 'J.', 'Smith', '123 Main Street', 'Nashville', 'TN', '37777'); (*)

INSERT customers VALUES 3178, J., Smith, 123 Main Street, Nashville, TN, 37777;


Incorrect. Refer to Section 7 Lesson 2.


23. You need to update both the DEPARTMENT_ID and LOCATION_ID columns in the EMPLOYEES table using one UPDATE statement. Which clause should you include in the UPDATE statement to update multiple columns? Mark for Review
(1) Points

The USING clause

The ON clause

The WHERE clause

The SET clause (*)


Incorrect. Refer to Section 7 Lesson 2.


24. Examine the structures of the PLAYERS, MANAGERS, and TEAMS tables:
PLAYERS
PLAYER_ID NUMBER Primary Key
LAST_NAME VARCHAR2 (30)
FIRST_NAME VARCHAR2 (25)
TEAM_ID NUMBER
MGR_ID NUMBER
SIGNING_BONUS NUMBER(9,2)
SALARY NUMBER(9,2)

MANAGERS
MANAGER_ID NUMBER Primary Key
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
TEAM_ID NUMBER

TEAMS
TEAM_ID NUMBER Primary Key
TEAM_NAME VARCHAR2 (20)
OWNER_LAST_NAME VARCHAR2 (20)
OWNER_FIRST_NAME VARCHAR2 (20)

Which situation would require a subquery to return the desired result?
Mark for Review
(1) Points

To display the names each player on the Lions team

To display the maximum and minimum player salary for each team

To display the names of the managers for all the teams owned by a given owner (*)

To display each player, their manager, and their team name for all teams with a id value greater than 5000


Incorrect. Refer to Section 7 Lesson 2.


25. The EMPLOYEES table contains the following columns:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FAST_NAME VARCHAR2(20)
DEPARTMENT_ID VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(9,2)
BONUS NUMBER(9,2)

You want to execute one DML statement to change the salary of all employees in department 10 to equal the new salary of employee number 89898. Currently, all employees in department 10 have the same salary value. Which statement should you execute?
Mark for Review
(1) Points

UPDATE employees
SET salary = SELECT salary FROM employees WHERE employee_id = 89898;


UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id = 89898);


UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id = 89898)
WHERE department_id = 10;
(*)


UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id = 89898 AND department_id = 10);



Incorrect. Refer to Section 7 Lesson 2.


26. The TEACHERS and CLASS_ASSIGNMENTS tables contain these columns:
TEACHERS
TEACHER_ID NUMBER(5)
NAME VARCHAR2(25)
SUBJECT_ID NUMBER(5)
HIRE_DATE DATE
SALARY NUMBER(9,2)

CLASS_ASSIGNMENTS
CLASS_ID NUMBER(5)
TEACHER_ID NUMBER(5)
START_DATE DATE
MAX_CAPACITY NUMBER(3)

Which scenario would require a subquery to return the desired results?
Mark for Review
(1) Points

You need to display the start date for each class taught by a given teacher.

You need to create a report to display the teachers who were hired more than five years ago.

You need to display the names of the teachers who teach classes that start within the next week.

You need to create a report to display the teachers who teach more classes than the average number of classes taught by each teacher. (*)


Correct


27. You need to delete a record in the EMPLOYEES table for Tim Jones, whose unique employee identification number is 348. The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(5) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
ADDRESS VARCHAR2(30)
PHONE NUMBER(10)

Which DELETE statement will delete the appropriate record without deleting any additional records?
Mark for Review
(1) Points

DELETE FROM employees
WHERE employee_id = 348;
(*)


DELETE FROM employees
WHERE last_name = jones;


DELETE *
FROM employees
WHERE employee_id = 348;


DELETE 'jones'
FROM employees;



Incorrect. Refer to Section 7 Lesson 2.


28. The PRODUCTS table contains these columns:
PROD_ID NUMBER(4)
PROD_NAME VARCHAR2(25)
PROD_PRICE NUMBER(3)

You want to add the following row data to the PRODUCTS table:

(1) a NULL value in the PROD_ID column
(2) "6-foot nylon leash" in the PROD_NAME column
(3) "10" in the PROD_PRICE column

You issue this statement:

INSERT INTO products
VALUES (null,'6-foot nylon leash', 10);

What row data did you add to the table?
Mark for Review
(1) Points

The row was created with the correct data in all three columns. (*)

The row was created with the correct data in two of three columns.

The row was created with the correct data in one of the three columns.

The row was created completely wrong. No data ended up in the correct columns.


Incorrect. Refer to Section 7 Lesson 1.


29. You need to copy rows from the EMPLOYEE table to the EMPLOYEE_HIST table. What could you use in the INSERT statement to accomplish this task? Mark for Review
(1) Points

An ON clause

A SET clause

A subquery (*)

A function


Incorrect. Refer to Section 7 Lesson 1.


30. The PRODUCTS table contains these columns:
PRODUCT_ID NUMBER NOT NULL
PRODUCT_NAME VARCHAR2 (25)
SUPPLIER_ID NUMBER NOT NULL
LIST_PRICE NUMBER (7,2)
COST NUMBER (5,2)
QTY_IN_STOCK NUMBER(4)
LAST_ORDER_DT DATE NOT NULL DEFAULT SYSDATE

Which INSERT statement will execute successfully?
Mark for Review
(1) Points

INSERT INTO products
VALUES (2958, 'Cable', 8690, 7.09, 4.04, 700);
(*)


INSERT INTO products
VALUES (2958, 'Cable', 8690, 7.09, 4.04, SYSDATE);


INSERT INTO products(product_id, product_name)
VALUES (2958, 'Cable');


INSERT INTO products(product_id, product_name, supplier_id
VALUES (2958, 'Cable', 8690, SYSDATE);



Incorrect. Refer to Section 7 Lesson 1.



Page 3 of 5






Test: Mid-Term Exam Semester 2 Part Two



Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Semester 2 Mid Term Exam Part II covers Sections 5-7 of Database Programming with SQL.


Section 7
(Answer all questions in this section)

31. Assume all the column names are correct. The following SQL statement will execute which of the following?
INSERT INTO departments (department_id, department_name, manager_id, location_id)
VALUES (70, 'Public Relations', 100, 1700);
Mark for Review
(1) Points

100 will be inserted into the department_id column

1700 will be inserted into the manager_id column

70 will be inserted into the department_id column (*)

?Public Relations? will be inserted into the manager_name column


Correct


32. Aliases can be used with MERGE statements. True or False? Mark for Review
(1) Points

True (*)

False


Incorrect. Refer to Section 7 Lesson 3.


33. Multi-table inserts are used when the same source data should be inserted into _____________ target table. Mark for Review
(1) Points

Ten

A very large

More than one (*)

A data warehouse


Correct




Section 6
(Answer all questions in this section)

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

EVERY TIME

ONCE (*)

COMPLETELY


Correct


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

CREATE; EXECUTE; USE

DELETE; UPDATE; INSERT

GET; EXECUTE; USE (*)

ROLLUP; GRANT; DROP


Incorrect. Refer to Section 6 Lesson 4.


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


Incorrect. Refer to Section 6 Lesson 4.


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


Incorrect. Refer to Section 6 Lesson 4.


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

ANY

ALL

<> (*)

IN


Correct


39. 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 supervisors
WHERE supervisor_id =
(SELECT employee_id
FROM supervisors
WHERE last_name = 'Carter');


SELECT *
FROM employees
WHERE supervisor_id =
(SELECT employee_id
FROM employees
WHERE last_name = 'Carter');
(*)



Incorrect. Refer to Section 6 Lesson 2.


40. What would happen if you attempted to use a single-row operator with a multiple-row subquery? Mark for Review
(1) Points

An error would be returned. (*)

No rows will be selected.

All the rows will be selected.

The data returned may or may not be correct.


Correct



Page 4 of 5






Test: Mid-Term Exam Semester 2 Part Two



Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Semester 2 Mid Term Exam Part II covers Sections 5-7 of Database Programming with SQL.


Section 6
(Answer all questions in this section)

41. 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, department name for every Executive in the employees table.

The department ID, last name, job ID from departments for Executive employees.

The department ID, last name, job ID for every employee in the Executive department. (*)


Incorrect. Refer to Section 6 Lesson 3.


42. A multiple-row operator expects how many values? Mark for Review
(1) Points

One or more (*)

Only one

Two or more

None


Correct


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


Correct


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


Incorrect. Refer to Section 6 Lesson 1.


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



Incorrect. Refer to Section 6 Lesson 1.


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

IN (*)

<>

=

LIKE


Correct


47. The TEACHERS and CLASS_ASSIGNMENTS tables contain these columns:
TEACHERS
TEACHER_ID NUMBER(5) Primary Key
NAME VARCHAR2 (25)
SUBJECT_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
WHERE max_capacity = (SELECT AVG(max_capacity) FROM class_assignments);
(*)


SELECT *
FROM teachers
WHERE teacher_id = (SELECT teacher_id FROM class_assignments WHERE class_id = 45963);
(*)


SELECT *
FROM teachers
WHERE teacher_id = (SELECT teacher_id, class_assignments WHERE max_capacity > 0);


SELECT *
FROM teachers
WHERE teacher_id LIKE (SELECT teacher_id FROM class_assignments WHERE max_capacity > 1000);


SELECT *
FROM class_assignments
max_capacity = (SELECT AVG(max_capacity) FROM class_assignments GROUP BY teacher_id);



Incorrect. Refer to Section 6 Lesson 1.


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


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

=

>

<=

>= (*)


Incorrect. Refer to Section 6 Lesson 1.


50. Which operator can be used with subqueries that return only one row? Mark for Review
(1) Points

LIKE (*)

ANY

ALL

IN


Incorrect. Refer to Section 6 Lesson 1.



Page 5 of 5