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

SECTION 6
LESSON 1
1. In the SQL query shown below, which of the following is True about this query?
____T___a. Kimberly Grant would not appear in the results set.
___F____b. The GROUP BY clause has an error because the manager_id is not listed in the SELECT clause.
___T____c. Only salaries greater than 16001 will be in the result set.
___T____d. Names beginning with Ki will appear after names beginning with Ko.
___F____e. Last names such as King and Kochhar will be returned even if they don't have salaries > 16000.
SELECT last_name, MAX(salary)
FROM employees
WHERE last_name LIKE 'K%'GROUP BY manager_id, last_name
HAVING MAX(salary) >16000
ORDER BY last_name DESC

2. Each of the following SQL queries has an error. Find the error and correct it. Use HTML DB to verify that your corrections produce the desired results.
a. SELECT manager_id
FROM employees
Having AVG(salary) <1600
GROUP BY manager_id;
b. SELECT cd_number, COUNT(title)
FROM d_cds
WHERE cd_number <>
GROUP BY CD_NUMBER;
c. SELECT ID, MAX(ID), artist AS Artist
FROM d_songs
WHERE duration IN('3 min', '6 min', '10 min')
AND ID <>
GROUP by ID;
d. SELECT loc_type, rental_fee AS Fee
FROM d_venues
WHERE id <100>
GROUP BY rental_fee,loc_type;

3. Rewrite the following query to accomplish the same result:
SELECT DISTINCT MAX(song_id) FROM d_track_listingsWHERE track IN ( 1, 2, 3);

----select max(distinct song_id)
from d_track_listings
where track in (1,2,3);

4. Your teacher needs an alphabetized list of all students?first and last names and the class average for Test 1. She would like the results grouped by gender (male and female), but only show male students whose grade level is 10. Write pseudocode for the SQL statement needed to accomplish the task.

----select first_name,last_name,avg(grade)
from class
where grade=10 and gender='male'
group by gender;
5. Indicate True or False
__T___a. If you include a group function and any other individual columns in a SELECT clause, then each individual column must also appear in the GROUP BY clause.
__F___b. You can use a column alias in the GROUP BY clause.
__T___c. The GROUP BY clause always includes a group function.
LESSON 2
Complete the practice exercises.Mark T or F for each of the following questions:
__F___1. The CONCAT character function combines two or more character values into one character string.
__T___2. The SYSDATE function returns the Oracle Server date and time.
__T___3. The RPAD character function returns a right-justified value with a string of characters replicated to fill a specified number of character positions.
__T___4. The ROUND number function will round 469.89 to 470 if the decimal place value is omitted.
__T___5. The SUBSTR character function returns a portion of a string beginning at the start of a string to a specified number of characters.
__T___6. DESC will order alphabetical data from Z to A.
__F___7. The column name in a SELECT statement is an example of a SELECTION.
__T___8. You cannot use a column alias in the WHERE clause.
__F___9. To specify an alias in the SELECT clause for retired employee, use 'Retired Employee.'
__F___10. Aliases always appear in UPPERCASE, unless enclosed within proper quote marks.
__F___11. If any column value in an arithmetic expression is NULL, the result defaults to 0.
__F___12. Null values are treated as zero when using SUM functions.
__F__13. In arithmetic expressions, multiply and divide are evaluated after addition and substraction.
__F__14. By default, column headings are returned in UPPERCASE.
__T__15. SQL statements are not case-sensitive, unless indicated.
__T__16. SQL statements can be entered on one or more lines.
__T__17. SQL keywords can be abbreviated using single quotes.
__F__18. Columns are displayed in the order in which they appear in the WHERE clause.
__F__19. Column aliases that are more than one word require the AS prefix.
__F_20. In the SELECT clause, if an expression is not a column name or a column alias, it is a literal value.
__F__21. Number and date values must be enclosed in single quotes.
__F__22. The keyword DISTINCT will affect all of the columns in the statement: SELECT DISTINCT(employee_id, department_id,department_name)
__T__23. HTML DB is a type of application.
__F__24. VARCHAR2 describes variable-length character values.
__F__25. To restrict the rows returned from a query, use the SELECT clause.
__T__26. The comparison operator IN is used to match any of a list of items.
__T__27. Using WHERE employee_id = NULL returns all employees where do not have an employee ID.
__T__28. BETWEEN 250 AND 350 will return 101 values.
__F__29. LIKE '_ _ e%' could return 'Fred.'
__T_30. To return SA_MAN, use LIKE ' SA /_%' ESCAPE ' \ '
__T_31. Logical operators are evaluated in the AND, OR, NOT order.
__F__32. WHERE e.last_name LIKE 'Sm%' OR d.department_id = 60 AND d.department_name = 'IT' could return anyone from the IT department whose department ID is 60.
__F_33. The rules of precedence would evaluate an expression that contained each of the following in the order: arithmetic, concatenation, NOT, OR
__F__34. The ORDER BY clause comes last in the SELECT statement.
__F__35. In SELECT last_name, first_name, the ORDER BY clause could include department_id.
__F__36. The order of execution of a SELECT statement is: FROM, WHERE, SELECT, ORDER BY
__F__37. ADD_MONTHS(hire_date, 6) will return a number.
__F__38. MONTHS BETWEEN ('05-DEC-89' , '10-JUN-93') will return 4.
__T__39. If April 21st is a MONDAY, then NEXT_DAY('21-APR'-03', 'FRIDAY') will return 25-APR-03.
__F__40. ROUND(SYSDATE, 'YEAR') will return 01-JAN-05 if today is August 23, 2004.
__T__41. All group functions ignore NULL values.
__F__42. SELECT COUNT( last_name, first_name) will return the number of last and first names in the database.
__F__43. If salary values in a column are 5000, null, 4000, 7000, using AVG(salary) will return 4000.
__T__44. AVG(DISTINCT salary) will average only the salaries that are different from each other.
__T__45. NULLIF(salary, 4000) will return 4000 if the employee's salary is 5000.
__T_ 46. COALESCE(hire_date, salary, commission_pct) will return salary if the hire date is NULL.
__T_ 47. The HAVING clause is used to restrict groups.
__F_48. a GROUP BY clause is used, any column that is in the SELECT clause that is not a group function must be listed in the GROUP BY clause.
__F__49. If a query lists clauses WHERE, GROUP BY, and HAVING, no ORDER BY clause can be used.
__T__50. If a join is needed and you want to use a WHERE clause, choose a JOIN ON or JOIN USING join.
__T__51. COUNT(DISTINCT department_id) will return the number of different department IDs.
__F__52. If a column had 4 NULL employee IDs and 250 defined IDs, COUNT will return 254.
LESSON 5
1. You need to know the title and artist of all DJs on Demand songs that have the same type code as the song, "I'm Going to Miss My Teacher."

----select title,artist
from d_songs
where type_code=(select type_code from d_songs where title='I'm Going to Miss My Teacher');

2. What Oracle employees have a salary greater than the maximum salary of any ST_CLERK?

----select job_title
from jobs
where salary>(select max_sslary from jobs where job_title='ST_CLERK');

3. What DJs on Demand d_play_list_items song_id's have the same event_id as song_id 45?

----select event_id
from d_play_list_items
where event_id=(select event_id from d_play_list_items where song_id=45);

4.Which events in the DJs on Demand database cost more than event_id = 100?

----select name
from d_events
where cost>(select cost from d_events where event_id=100);

5. Find the track number of the song that has the same CD number as "Party Music for All Occasions."

----select cd_number
from d_track_listings
where song_id=(select id from d_songs where title='Party Music for All Occasions');

6. List the DJs on Demand events whose theme code is the same as the code for "Tropical."

----select name
from d_event
where theme_code=(select code from d_themes where description='Tropical');

7. What are the names of the Global Fast Foods staff members whose salary is greater than the staff member whose ID is 12?

----select last_name,first_name
from f_staffs
where salary>(select salary from f_staffs where id=12);

8. What are the names of the Global Fast Foods staff members whose staff type is not the same as Bob Miller's?

----select last_name,first_name
from f_staffs
where staff_type!=(select staff_type from f_staffs where last_name='Miller' and first_name='Bob');

9. What is the staff type for those Global Fast Foods jobs that have a salary less than those of any Cook staff-type jobs?

----select staff_type
from f_staffs
where salary<(select salary from f_staffs where staff_type='Cook');

10. What DJs on Demand events have the same theme code event ID = 100?

---- select name
from d_events
where theme_code=(select theme_code from d_evnets where id=100);

11. Which Oracle employees have the same department ID as the IT department?

----select last_name,first_name
from employees
where department_id=(select department_id from departments where department_name='IT');

12. What are the department names of the Oracle departments that have the same location ID as Seattle?

----select department_id
from departments
where location_id=(select location_id from locations where city='Seattle');

13. Return the department ID and minimum salary of all employees, grouped by department ID, having a minimum salary greater than the minimum salary of those employees whose department ID is not equal to 50.

----select department_id, min(salary)
from employees where min(salary)>(select min(salary) from employees
where department_id!=50)
group by department_id;

14. Which statement(s) regarding subqueries is/are true?
a. It is good programming practice to place a subquery on the right side of the comparison operator.
b. A subquery can reference a table that is not included in the outer query's FROM clause.
c. Single-row subqueries can return multiple values to the outer query.

----a

SECTION 5
LESSON 1
Refer to the review examples shown below to answer the following questions about joins:
1. How do you know where to place the (+) in an outer join?
----the other side has null information
2. How do you know whether to indicate a join as a right, left, or full outer join?
----what side or both sides have null information
3. What is the function of the WHERE clause in an equijoin?
----provide the conditions to join tables
4. When using a nonequijoin, what conditions must be true about the data in each table?
----the column name
5. What data will not be returned in a natural join between two tables?
----not match
LESSON 4
1. Create a query that will show the average cost of the DJ on Demand events. Round to two decimal places.

----select round(avg(cost),2)
from d_events;

2. Find the average salary for Global Fast Foods staff members whose manager ID is 19.

----select avg(salary)
from f_staffs
where manager_id=19;

3. Find the sum of the salaries for Global Fast Foods staff members whose IDs are 12 and 9.

---select sum(salary)
from f_staffs
where id in(12,19);

4. Using the Oracle database, select the lowest salary, the most recent hire date, the last name of the person who is at the top of an alphabetical list of employees, and the last name of the person who is at the bottom of an alphabetical list of employees. Select only employees who are in departments 50 or 60.

----select min(salary),max(hire_date),min(last_name),max(last_name)
from employees
where department_id in(50,60);

5. Your new Internet business has had a good year financially. You have had 1,289 orders this year. Your customer order table has a column named total_sales. If you submit the following query, how many rows will be returned?
SELECT sum(total_sales)
FROM orders;

----none

6. You were asked to create a report of the average salaries for all employees in each division of the company. Some employees in your company are paid hourly instead of by salary. When you ran the report, it seemed as though the averages were not what you expected. They were much higher than you thought! What could have been the cause?

----someone paid hourly, someone paid yearly

7. Employees of Global Fast Foods have birth dates of July 1, 1980, March 19, 1979, and March 30, 1969. If you select MIN(birthdate), which date will be returned?

----March 10,1969

8. Create a query that will return the average order total for all Global Fast Foods orders from January 1, 2002, to December 21, 2002.

----select avg(order_total)
from d_orders
where order_date is between 'January 1, 2002' and 'December 21, 2002';

9. What was the hire date of the last Oracle employee hired?

----select max(hire_date)
from employees;

10. In the following SELECT clause, which value returned by the SELECT statement will be larger?SELECT SUM(operating_cost), AVG(operating_cost)

----SUM(operating_cost)
LESSON 5
1. How many songs are listed in the DJs on Demand D_SONGS table?

----select count(id)
from d_songs;

2. In how many different location types has DJs on Demand had venues?

----select count(distinct loc_type)
from d_venues;

3. The d_track_listings table in the DJs on Demand database has a song_id column and a cd_number column. How many song IDs are in the table and how many different CD numbers are in the table?

----select count(song_id),count(distinct cd_number)
from D_TRACK_LISTINGs;

4. How many of the DJs on Demand customers have email addresses?

----select count(email)
from d_clients;

5. Some of the partners in DJs on Demand do not have authorized expense amounts (auth_expense_amt). How many partners do have this privilege?

----select count(auth_expense_amt)
from partners;

6. Using the table shown, what values will be returned when the following statement is issued?
SELECT COUNT(shoe_color), COUNT(DISTINCT shoe_color)FROM shoes;

----the total number of shoes, the number of shoes for different types

7. Create a query that will convert any null values in the auth_expense_amt column on the DJs on Demand D_PARTNERS table to 100000 and find the average of the values in this column. Round the result to two decimal places.

----select round(avg(nvl(auth_expense_amt,100000)),2)
from d_partners;