SQL Homework Exercises:

Please choose the correct answer/s letter.

1. Examine the structures of the PLAYER and TEAM tables:

PLAYER

-------------

PLAYER_ID NUMBER PK

LAST_NAME VARCHAR2(30)

FIRST_NAME VARCHAR2(25)

TEAM_ID NUMBER

MGR_ID NUMBER

SIGNING_BONUS NUMBER(9,2)

TEAM

---------

TEAM_ID NUMBER

TEAM_NAME VARCHAR2(30)

Which situation would require a subquery to return the desired result?

A)a list of all players who are also managers

B)a list of all teams that have more than 11 players

C)a list of all players, including their signing bonus amounts and their manager names

D)a list of all players who have a larger signing bonus than their manager

E)a list of all players who received a signing bonus that was lower than the average bonus

********************************************************************************************************************************************************************

2. Evaluate this SELECT statement:

SELECT s.student_name, s.grade_point_avg, s.major_id, m.gpa_avg

FROM student s, (SELECT major_id, AVG(grade_point_avg) gpa_avg

FROM student

GROUP BY major_id) m

WHERE s.major_id = m.major_id AND s.grade_point_avg > m.gpa_avg;

What will be the result of executing this SELECT statement?

A)The names of all students with a grade point average that is higher than the average grade point average in their major will be displayed.

B)The names of all students with a grade point average that is higher than the average grade point average of all students will be displayed.

C)The names of all students, grouped by each major, with a grade point average that is higher than the average grade point average of all students in each major will be displayed.

D)A syntax error will occur because of ambiguous table aliases.

E)A syntax error will be returned because the FROM clause cannot contain a subquery.

********************************************************************************************************************************************************************

3. Which two statements regarding the valid use of single-row and multiple-row subqueries are true? (CHOOSE TWO.)

A)Single-row subqueries can only be used in a WHERE clause.

B)Multiple-row subqueries can be used with the LIKE operator.

C)Single-row operators can only be used with single-row subqueries.

D)Single- and multiple-row subqueries can be used with the BETWEEN operator.

E)Multiple-row subqueries can be used with both single-row and multiple-row operators.

F)Multiple-row subqueries can be used in a WHERE clause and the INTO portion of an INSERT statement.

*******************************************************************************************************************************************************************************

4. You need to create a report to display the names of customers with a credit limit greater than the average credit limit of all customers.

Which SELECT statement should you use?

A)

SELECT last_name, first_name

FROM customer

WHERE credit_limit > AVG(credit_limit);

B)

SELECT last_name, first_name, AVG(credit_limit)

FROM customer

GROUP BY AVG(credit_limit);

C)

SELECT last_name, first_name, AVG(credit_limit)

FROM customer

GROUP BY AVG(credit_limit)

HAVING credit_limit > AVG(credit_limit);

D)

SELECT last_name, first_name

FROM customer

WHERE credit_limit > (SELECT AVG(credit_limit)

FROM customer);

E)

SELECT last_name, first_name

FROM customer

WHERE credit_limit = (SELECT AVG(credit_limit)

FROM customer);

*******************************************************************************************************************************************************************************

5. Evaluate this SQL statement:

SELECT product_id, product_name, price

FROM product

WHERE supplier_id IN (SELECT supplier_id

FROM product

WHERE price > 120 OR qty_in_stock > 100);

Which values will be displayed?

A)the PRODUCT_ID, PRODUCT_NAME, and PRICE of products that are priced greater than $120.00 and have a QTY_IN_STOCK value greater than 100

B)the PRODUCT_ID, PRODUCT_NAME, and PRICE of products that are priced greater than $120.00 or that have a QTY_IN_STOCK value greater than 100

C)the PRODUCT_ID, PRODUCT_NAME, and PRICE of products that are priced greater than $120.00 or that have a QTY_IN_STOCK value greater than 100, and that have a supplier

D)the PRODUCT_ID, PRODUCT_NAME, and PRICE of products supplied by a supplier with products that are priced greater than $120.00 or with products that have a QTY_IN_STOCK value greater than 100

******************************************************************************************************************************************************************************************************

6. Examine the data from the DONATION table.

This statement fails when executed:

SELECT amount_pledged, amount_paid

FROM donation

WHERE donor_id =

(SELECT donor_id

FROM donation

WHERE amount_pledged = 1000.00

OR pledge_dt = '05-JAN-2002');

Which two changes could correct the problem? (CHOOSE TWO. Each correct answer is a separate solution.)

A)Remove the subquery WHERE clause.

B)Change the outer query WHERE clause to WHERE donor_id IN.

C)Change the outer query WHERE clause to WHERE donor_id LIKE.

D)Include the DONOR_ID column in the select list of the outer query.

E)Remove the single quotes around the date value in the inner query WHERE clause.

F)Change the subquery WHERE clause to WHERE amount_pledged = 1000.00 AND pledge_dt = '05-JAN-2002'.

***********************************************************************************************************************************************************************

7. Which statement regarding subqueries is true?

A)Subqueries can return multiple columns.

B)Subqueries can be nested up to five levels.

C)A subquery must be placed on the right side of the comparison operator.

D)A subquery cannot reference a table that is not included in the outer query's FROM clause.

*******************************************************************************************************************************************************************************

8. Evaluate this SELECT statement:

SELECT first_name, last_name

FROM physician

WHERE physician_id NOT IN (SELECT physician_id

FROM physician

WHERE license_no = 17852);

Which one of the following SELECT statements would achieve the same result?

A)

SELECT first_name, last_name

FROM physician

WHERE physician_id = 17852;

B)

SELECT first_name, last_name

FROM physician_id

WHERE license_no 17852

AND license_no IS NOT NULL;

C)

SELECT first_name, last_name

FROM physician

WHERE physician_id IN (SELECT physician_id

FROM physician

WHERE license_no = 17852);

D)

SELECT first_name, last_name

FROM physician

WHERE physician_id != ALL (SELECT physician_id

FROM physician

WHERE license_no = 17852);

**************************************************************************************************************************************************************************

9. Examine the structures of the CUSTOMER and CURR_ORDER tables:

CUSTOMER

--------------------

CUSTOMER_ID NUMBER(5)

NAME VARCHAR2(25)

CREDIT_LIMIT NUMBER(8,2)

ACCT_OPEN_DATE DATE

CURR_ORDER

-------------------------

ORDER_ID NUMBER(5)

CUSTOMER_ID NUMBER(5)

ORDER_DATE DATE

TOTAL NUMBER(8,2)

Which scenario would require a subquery to return the desired results?

A)You need to display the names of all the customers who placed an order today.

B)You need to determine the number of orders placed this year by the customer with CUSTOMER_ID value 30450.

C)You need to determine the average credit limit of all the customers who opened an account this year.

D)You need to determine which customers have placed orders with amount totals larger than the average order amount.

*********************************************************************************************************************************************************************

10 The EMPLOYEE table contains these columns:

EMPLOYEE_ID NUMBER NOT NULL

EMP_LNAME VARCHAR2(20) NOT NULL

EMP_FNAME VARCHAR2(10) NOT NULL

DEPT_ID NUMBER

SALARY NUMBER(9,2)

A user needs to retrieve information on employees who have the same department ID and salary as an employee ID that the user will enter. You want the query results to include employees who do not have a salary, but not the employee that the user entered.

Which statement will return the desired result?

A)

SELECT *

FROM employee

WHERE (department, salary) NOT IN

(SELECT department, salary)

FROM employee

WHERE employee_id = &1);

B)

SELECT *

FROM employee

WHERE (dept_id, salary) IN

(SELECT dept_id, NVL(salary, 0)

FROM employee

WHERE employee_id = &1);

C)

SELECT *

FROM employee

WHERE (dept_id, NVL(salary, 0)) IN

(SELECT dept_id, NVL(salary, 0)

FROM employee

WHERE employee_id = &&1)

AND employee_id &&1;

D)

SELECT *

FROM employee

WHERE (dept_id, salary) IN

(SELECT dept_id, salary)

FROM employee

WHERE employee_id = &1

AND salary IS NULL);

******************************************************************************************************************************************************************************

11.Examine the structure of the EMPLOYEE table.

You want to generate a list of employees are in department 30, have been promoted from clerk to associate by querying the EMPLOYEE and EMPLOYEE_HIST tables. The EMPLOYEE_HIST table has the same structure as the EMPLOYEE table. The JOB_ID value for clerks is 1 and the JOB_ID value for associates is 6.

Which query should you use?

A)

SELECT employee_id, emp_lname, emp_fname, dept_id

FROM employee

WHERE (employee_id, dept_id) IN

(SELECT employee_id, dept_id

FROM employee_hist

WHERE dept_id = 30 AND job_id = 1)

AND job_id = 6;

B)

SELECT employee_id, emp_lname, emp_fname, dept_id

FROM employee

WHERE (employee_id) IN

(SELECT employee_id

FROM employee_hist

WHERE dept_id = 30 AND job_id = 1);

C)

SELECT employee_id, emp_lname, emp_fname, dept_id

FROM employee

WHERE (employee_id, dept_id) =

(SELECT employee_id, dept_id

FROM employee_hist

WHERE dept_id = 30 AND job_id = 6);

D)

SELECT employee_id, emp_lname, emp_fname, dept_id

FROM employee

WHERE (employee_id, dept_id) IN

(SELECT employee_id, dept_id

FROM employee

WHERE dept_id = 30)

AND job_id = 6;

E)

SELECT employee_id, emp_lname, emp_fname, dept_id

FROM employee_hist

WHERE (employee_id, dept_id) =

(SELECT employee_id, dept_id

FROM employee_hist WHERE dept_id = 30

AND job_id = 1)

AND job_id = 6;

DONATION (PLEDGE ID IS the primary key.) PLEDGE ID DONOR ID AMOUNT PLEDGEDAMOUNT PAID PAYHENT DT PLEDGE DT 1 10-SEP-2001 22-FEB-2002 1000 1000 10 50 1000 02-OCT-2001 2 08-OCT-2001 10 28-OCT-2001 10-DEC-200 02-NOW-2001 O5-JAN-2002 9-NoV-2001 09-DEC-2001 1000 2100 110 9000 28-DEC-2001 1000 31-JAN-2002 1001 15-DEC-2001 110 29-DEC-2001