SECTION 7
LESSON 1
1. What DJs on Demand cd titles were produced in any year before "Carpe Diem" was produced?

----selelct title
from d_cds
where year>all(select year from d_cds where title='Carpe Diem');

2. Write a query that returns jazz and pop songs. Write a multi-row subquery and use the d_songs and d_types tables. Include the id, title, duration, and the artist name.

----select id,duration,artist
from d_songs
where type_code=any(select code from d_types where description in('jazz,'pop'));

3. Find the DJs on Demand events whose cost was less than or equal to the low-range cost of package code 200?

----select name
from d_events
where cost=
4. Find the last names of all employees whose salaries are the same as the minimum salary for any department.

----select last_name
from employees
where salary=any(select min(salary) from employees group by department_id);

5. Which Global Fast Foods employee earns the lowest salary? Hint: You can use either a single-row or a multiple-row subquery.

----select first_name,last_name
from f_staffs
where salary=(select min(salary) from f_staffs group by id);

6. Place the correct multiple-row comparison operators in the outer query WHERE clause of each of the following:
a. Which CDs in our d_cds collection were produced before "Carpe Diem" was produced? WHERE year __>all____ (SELECT year ...
b. Which employees have salaries lower than any one of the programmers in the IT department?WHERE salary___c. What CD titles were produced in the same year as "Party Music for All Occasions" or "Carpe Diem"?WHERE year ___in_____(SELECT year ...
d. What song title has a duration longer than every type code 77 title?WHERE duration __>any___(SELECT duration ...

7. Which of the following are True? Each WHERE clause is from the outer query.
_T__a. WHERE size > ANY If the inner query returns sizes ranging from 8 to 12, the value 9 could be returned in the outer query.
_T__b. WHERE book_number IN If the inner query returns books numbered 102, 105, 437, and 225 then 325 could be returned in the outer query.
_F__c. WHERE score <= ALL If the inner query returns the scores 89, 98, 65, and 72, then 82 could be returned in the outer query.
_F__d. WHERE color NOT IN If the inner query returns red, green, blue, black, and then the outer query could return white.
_F__e. WHERE game_date = ANY If the inner query returns 05-JUN-97, 10-DEC-02, and 2-JAN-04, then the outer query could return 10- SEP-02.

8. The goal of the following query is to display the minimum salary for each department whose minimum salary is less than the lowest salary of the employees in department 50. However, the subquery does not execute because it has five errors. Find them, correct them, and run the query.SELECT department_idFROM employeesWHERE MIN(salary)HAVING IN(salary) >GROUP BY department_idSELECT MIN(salary)WHERE Dpartment_id < 50;

----select min(salary)
from employees
where min(salary)<(select min(salary) from employees where department_id=50);

9. Which statements are True about the subquery below?
SELECT employee_id, last_name
FROM employees
WHEREsalary =(SELECT MIN(salary)FROM employeesGROUP BY department_id);
__F___a. The inner query could be eliminated simply by changing the WHERE clause to WHERE MIN(salary).
__T_b. The query wants the names of employees who make the same salary as the smallest salary in any department.
____T_c. The query firsts selects the employee ID and last name, ad then compares that to the salaries in every department.
____F__d. This query will not execute.

10. Place an X next to those values that could be returned by the outer query if the inner query returned 185.98, 315.75, 382.24, and 877.31 with the following WHERE clause:WHERE total_sales < ANY__*_a. 155.22_____b. 325.88_____c. 877.30_____d. 382.24