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;

SECTION4 LESSON 2
Use the Oracle database for problems 1 - 4.
1. Create a cross-join that displays the last name and department name from the employees and departments tables.

----select employees.last_name,departments.department_name
from employees cross join departments;

2. Create a query that uses a natural join to join the departments table and the locations table by the location_id column. Display the department id and name, location id, and city.

----select departments.department_id,location_id,locations.city
from departments natural join locations;

3. Rewrite problem 2 using equijoin syntax.

----select d.department_id,d.location_id,l.city
from departments d,locations l
where d.location_id=l.location_id;

4. Create a query that uses a natural join to join the departments table by the location_id column. Restrict the output to only department IDs of 20 and 50. Display the department id and name, location id, and city.

----select departments.department_id,departments.department_name,location_id, locations.city
from departments natural join locations
where departments.department_id = 20
or departmnets.department_id=50;

5. Use an equijoin between the DJs on Demand database tables, d_songs and d_types. Display the type code, description and title. Limit the rows returned to those type codes between 70 and 80.

---- select d_songs.type_code,d_songs.title,d_types.description
from d_songs,d_types
where d_songs.type_code=d_types.code
and d_songs.type_code is between 70 and 80;

6. a. When using Oracle proprietary syntax, the join condition is always placed in the _____where__________ clause of the SELECT statement.
b. When using ANSI/ISO SQL: 1999 syntax, the join condition is always placed in the _____from__________ clause of the SELECT statement.

7. A/an ______equijoin_________ can be used to preface the column name in order to clarify which table and column are participating in the join.

8. Table aliases are created in the ________from________ clause of the SELECT statement.
LESSON 3
1. Join the Oracle database locations and departments table using the location_id column. Limit the results to location 1400 only.

----select *
from locations l join departments d
on(l.location_id=d.location_id);

2. Join DJ on Demand d_play_list_items, d_track_listings, and d_cds tables with the JOIN USING syntax. Include the song ID, CD number, title, and comments in the output.

----select dd.title,dd.cd_number,dd.song_id,dp.comments
from d_cd dd join d_track_listing dt join d_play_list_items dp
on(dd.cd_number=dt.cd_number, and dt.song_id=dp.song_id);

3. Display the city, department name, location ID, and department ID for departments 10, 20, and 30 for the city of Seattle.

----select city,department_name,location_id,department_id
from departments join locations
using (location_id)
where department_id in (10,20,30)
and city='Seattle';

4. Display country name, region ID and region name for Americas.

----select country_name,region_id,region_name from countries join regionsusing(region_id)where country_name='United States of America';

5. write a statement joining the employees and jobs tables. Display the first and last names, hire date, job id, job title and maximum salary. Limit the query to those employees who are in jobs that can earn more than $12,000.

----select first_name,last_name,hire_date,job_id,job_title,max_salary from employees join jobs using (job_id) where salary>=12000;

6. Display job title, employee first name, last name, and email for all employees that are stock clerks.

----select first_name,last_name,email,job_title from jobs join employees using (job_id) where job_title='Stock Clerk';

The following questions use the JOIN…ON syntax:
7. Write a statement that displays the employee ID, first name, last name, manager ID, manager first name, and manager last name for every employee in the employees table. Hint: this is a self-join.

----select e.employee_id,e.first_name,e.last_name,m.employee_id,m.first_name,m.last_name from employees e,employees m where e.employee_id=m.employee_id;

8. Use JOIN ON syntax to query and display the location ID, city and department name for all Canadian locations.

----select location_id,city,department_name from departments d join locations l on(d.location_id=l.location_id) where country_id='CA'

9. Query and display manager ID, department ID, department name, first name, and last name for all employees in departments 80, 90, 110, and 190.

----select manager_id,department_id,department_name,first_name,last_name from employees join departments using(department_id) where department_id in(80,90,110,190)

10. Display employee ID, last name, department ID, department name, and hire date for those employees whose hire date was June 7, 1994.

----select employee_id,last_name,department_id,department_name,hire_date from employees join departments using(department_id) where hire_date='June 7,1994';

LESSON 4
1. Return the job ID, job title, first name, last name, and department ID of all employees with a job ID of IT_PROG.

----select job_id, job_title,first_name,last_name,department_id
from employees join jobsusing (job_id)
where job_id='IT_PROG';
2. Display the employee's last name and employee number along with the manager's last name and manager number. Label the columns Employee, Emp#, Manager, and Mgr#, respectively.

----select e.last_name "Employee",e.employee_id "Emp#",m.last_name "Mnager",m.employee_id "Mgr#"
from employees e,employees m
where e.employee_id=m.employee_id;
3. Modify problem 2 to display all employees, including those who have no manager. Order the results by the employee number.

----select e.last_name "Employee",e.employee_id "Emp#",m.last_name "Mnager",m.employee_id "Mgr#" from employees e,employees m where e.employee_id=m.employee_id(+)
order by e.employee_id;

4. Create a query of the DJ on Demand database to return the first name, last name, event date, and description of the event the client held. Include all the clients even if they have not had an event scheduled.

----select first_name,last_name,event_date,description
from d_clients left outer join d_events
on(d_clients.client_id=d_events.client_id);
5. Using the Global Fast Foods database, show the shift description and shift assignment date even if there is no date assigned for each shift description.

----select s.description,sa.shift_assgn_date
from f_shifts s
left outer join f_shift_assignments sa
where s.code=sa.code;
LESSON 5
Practice exercises:1. A/An ______join query__________ is when the rows of the tables are combined with each other and produce new rows . The number of rows is equivalent to the product of the number of rows in each table.
2. A/An _____self join____________ is used when you need to query a table that has a relationship to itself.3. A/An _____cross join____________ preserves unmatched rows from one or both tables, returning the rows that are matched and unmatched from one or both tables.4. In an outer join, a plus sign (+) is placed on the side of the join that is _____null____________ information.5. A ______outer____________is used when a column in one table does not correspond directly to a column in another table.6. The join condition is always placed in the ____on___________ clause of the SELECT statement.7. A/An _____aliases__________ is used to preface the column name in order to clarify which table and column are participating in the join.8. Table aliases are created in the ____from____________ clause of the SELECT statement.9. In a full outer join, a row that does not contain data will appear in the results set if the row satisfies the join condition.10. Table aliases cannot exceed ___3______ characters in length.11. Identify the Oracle syntax to signify an outer join___________.12. If a join condition is written: WHERE e.client_number = c.client_number, what kind of join would it be if we wanted all the information in the e table even if the c table has missing data? _cross join_____13. Joins that are based on hierarchical relationships such as manager and employee are called _self join_________.14. How many join conditions does it take to join three tables? _2__15. What does the term "proprietary syntax" mean?16. What type of join condition is best when two columns with the same name but different data types exist in two different tables?
cross join17. What type of join(s) are based on all columns in two tables that have the same name?
self join18. Another name for a cross-join is a___nonequijoin________.19. When specifying a join, you need to identify the what, where, and how. Match up these terms to the SQL syntax words: FROM, WHERE, SELECT.20. For each join condition listed, mark T for those that can include an alias in the syntax:___ cross-join___ equijoin___ nonequijoin___ natural join___ full outer join___ left outer join___ USING clause21. Which two types of join conditions cannot use an alias?
natural join22. The query below is an example of what kind of join?SELECT last_name, first_nameFROM f_staffs, f_orders;
cross join23. Use the three tables shown to answer the following questions:a. What kind of join would you use to join all three tables? ______equrium____________ Write the syntax that would produce the desired result.b. Name two tables that could be used to retrieve data from a natural join.Write the syntax that would produce the desired result.c. What kind of join would you use to return only those students who have mentors?Write the syntax that would produce the desired result.d. What kind of join would you use to return all students whether they have a mentor or not.Write the syntax that would produce the desired result.

SECTION 3 LESSON 5
1. Display the employee's last name and employee number along with the manager's last name and manager number. Label the columns:Employee, Emp#, Manager, and Mgr#, respectively. -

---select e.last_name "Employee",e.employee_id "Emp #",m.manager_id "Mgr #",m.last_name "Manager"
from employees e,employees m
where e.employee_id=m.manager_id

2. Modify question 1 to display all employees and their managers, even if the employee does not have a manager. Order the list alphabetically by the last name of the employee.

----select e.last_name "Employee",e.employee_id "Emp #",m.manager_id "Mgr #",m.last_name "Manager"
from employees e,employees
where e.employee_id(+)=m.manager_id
order by e.last_name;

3. Display the names and hire dates for all employees who were hired before their managers, along with their managers' names and hire dates. Label the columns Employee, Emp Hired, Manager, and Mgr Hired, respectively
d
---- select e.first_name' 'e.last_name "Employee",e.hire_date "Emp Hire",m.first_name' 'm.last_name "Manager",m.hire_date "Mgr Hire"
from employees e,employees m
where e.manager_id=m.employee_id and e.hire_date

4. Finish this code to produce all possible combinations of rows from both tables:
SELECT e.last_name, e.department_id, d.department_name

----SELECT e.last_name, e.department_id, d.department_name
from employees e,departments d
where e.department_id=d.department_id;

5. Finish this code to produce all information in the departments table whether it has a match in the locations table or not:
SELECT l.location_id, l.city, d.department_name

----SELECT l.location_id, l.city, d.department_name
from locations l,departments d
where l.location_id=d.department_id;

6. Finish this code to produce the following information for Diana Lorentz:
SELECT e.last_name, d.department_name

----select e.last_name,d.department_name
from employees e,departments d
where e.last_name='Diana', e.first_name='Lorentz'
and e.department_id=d.department_id;

7. How do you know which side of a join gets the (+) sign?
----This side has the values that the other doesn't.

SECTION 3 LESSON 4
1. You need to produce a report for Global Fast Foods showing customers and orders. A customer must be included on the report even if the customer has had no orders.

----select *
from f_customers,f_orders
where f_customers.id=f_orders.cust_id(+);

2. Create a query of the Oracle database that shows employee last names, department IDs, and department names. Include all employees even if they are not assigned to a department.

----select d.department_name,e.last_name,e.department_id
from departments d,employees e
where e.department_id=d.department_id(+);

3. Modify the query in problem 2 to return all the department IDs even if no employees are assigned to them.

----select d.department_name,e.last_name,e.department_id
from departments d,employees e
where e.department_id(+)=d.department_id;

4. There are one or more errors in each of the following statements. Describe the errors and correct them.

a. WHERE e.department_id(+) = d.department_id (+);
b. SELECT e.first_name,e.last_name, d.department_name, e.department_id
FROM employees e, departments d
WHERE e.department_id = d.department_id;
c. WHERE e.position >= p.highest and<=p.lowest;
d. SELECT e.employee_id, e. last_name, d. location_id
FROM employees e, departments d
WHERE e.department_id = d.department_id;

5. Create a query that will show all CD titles and song IDs in the DJ on Demand database even if there is no CD number in the track-listings table.

----select dc.cd_number,dc.title,dt.cd_number
from d_cds dc,d_track_listings dt
where dc.cd_number(+)=dt.cd_number;

SECTION 3 LESSON 3
1. Create a join based on the cost of the event between the DJs on Demand tables D_EVENTS and D_PACKAGES. Show the name of the event and the code for each event.

---- select de.name,dp.code
from d_events de, d_packages dp
where de.cost between dp.low_range and dp.high_range;

2. Using the Oracle database, create a query that returns the employee last name, salary, and job-grade level based on the salary. Select the salary between the lowest and highest salaries.

----select e.last_name,e.salary, jg.grade_level
from employees e, job_grades jg
where salary between lowest_sal and highest_sal

3. What condition requires creation of a nonequijoin?

---- Since there is no exact match between the two columns in each table.

4. Rewrite the following nonequijoin statement using the logical condition operators (AND, OR, NOT):
WHERE a.ranking BETWEEN g.lowest_rank AND g.highest_rank

----ranking >= g.lowest_rank and <= g.highest_rank

5. How do you know when to use a table alias and when not to use a table alias?

----the column is so long, and the column has to be used many times

6. What is the purpose of the WHERE clause in a join?

----provide the condition

7. What kind of join would you use if you wanted to find data between a range of numbers?

----nonequijoin

8. What kind of join would you use if you wanted to join two tables on any rows that have the same name and the same data type.

----equijoin

SECTION 3 LESSON 2
1. Create a join based on the cost of the event between the DJs on Demand tables D_EVENTS and D_PACKAGES. Show the name of the event and the code for each event.

----select d_packages.code,d_events.name
from d_packages, d_events
where d_events.cost between d_packages.low_range and d_packages.high_range;

2. Using the Oracle database, create a query that returns the employee last name, salary, and job-grade level based on the salary. Select the salary between the lowest and highest salaries.

----select e.last_name,e.salary,j.grade_level
from employees e,job_grades j
where e.salary between j.lowest_sal and highest_sal;

3. What condition requires creation of a nonequijoin?

----There is no exact match between the two columns in each table.

4. Rewrite the following nonequijoin statement using the logical condition operators (AND, OR, NOT): WHERE a.ranking BETWEEN g.lowest_rank AND g.highest_rank

5. How do you know when to use a table alias and when not to use a table alias?

----Working with lengthy column and table names can be cumbersome. Fortunately, there is a way to shorten the syntax using aliases. To distinguish columns that have identical names but reside in different tables, use column aliases.

6. What is the purpose of the WHERE clause in a join?

----Don't need to list all information of the tables, only the information that will be used.

7. What kind of join would you use if you wanted to find data between a range of numbers?

----nonequijoin

8. What kind of join would you use if you wanted to join two tables on any rows that have the same name and the same data type.

----equijoin

SECTION 3 LESSON 1
1. Create a Cartesian product that displays the columns in the d_play_list_items and the d_track_listings in the DJs on Demand database.

----select *
from d_play_list_items,d_track_listings

2. Correct the Cartesian product produced in question 1 by creating an equijoin using a common column.

----select d_play_list_items.song_id,d_track_listings.song_id
from d_play_list_items,d_track_listings
where d_play_list_items.song_id=d_track_listings.song_id;

3. Write a query to display the title, type description, and artist from the DJs on Demand database.

----select d_songs.title,d_songs.artist,d_types.description
from d_songs,d_types;

4. Rewrite the query in question 3 to select only those titles with an ID of 47 or 48.

----select id,d_songs.title,d_songs.artist,d_types.description
from d_songs,d_types
where d_songs.id in(47,48);

5. Write a query that extracts information from three tables in the DJs on Demand database, the d_clients table, the d_events table, and the d_job_assignments table.

----select *
from d_clients table,d_events table,the d_job_assignments;

6. Create and execute an equijoin between DJs on Demand tables d_track_listings and d_cds. Return the song_id and the title only.

----select d_track_listings.song_id,d_cds.title
from d_track_listings,d_cds
where d_track_listings.cd_number=d_cds.cd_number;

7. Mark T for the statements that are True and F for the statements that are False.

__f__a. A join is a type of query that gets data from more than one table based on columns with the same name.
__t__b. To join tables using an equijoin, there must be a common column in both tables and that column is usually a primary key in one of the tables.
__t__c. A Cartesian product occurs because the query does not specify a WHERE clause.
__f__d. Table aliases are required to create a join condition.
__t__e. If a table alias is used for a table name in the FROM clause, it must be substituted for the table name throughout the SELECT statement.
__f__f. Table alias must be only one character in length.
__f__g. A simple join or inner join is the same as an equijoin.

8. What advantage does being able to combine data from multiple tables have for a business?

----That is convenient to get information from different tables.

SECTION 2 LESSON 5
2. Create two different queries that produce the cd number and title as "94CARPE DIEM." Use the d_cds table in the DJ on Demand database.

----select cd_number//''//upper(title)
from d_cds;
select concat(cd_number,upper(title))
from d_cds;

3. Mark the following statements as True or False.
__f__a. LOWER converts numbers to lowercase.
__f__b. Use RPAD to move numbers to the right to place an * on the left.
__f__c.. TRIM can be used to trim one or more characters from a string.
__t__d. LENGTH returns a number.
__f__e. SUBSTR is used to substitute one string for another.
__t__f. CONCAT is limited to using two parameters.
__t__g. TRUNC will return zero decimal places if a decimal value is omitted.

4. Create a query to show the cost of events for DJ on Demand in the format $0000.00

----select to_char(cost,'$99999.99')
from d_events;

5. For the f_staffs table in the Global Fast Foods database, change the ID of those staff members whose IDs are only single digits to two digits by adding an asterisk * to the front of the number. For example, change ID 9 to ID *9.

----select last_name,first_name,lpad(id,2,'*')
from f_staffswhere length(id)=1;

6. As the database administrator, you have been asked to store historical employee records in the current database. The records have termination dates from 1990 to 1999. Write a query using DUAL to show how you could store 15-DEC-95.

----insert into dual
value('15-dec-95');

7. Using DUAL, format 19-JUN-04 to appear as: 19th of june two thousand four

----select to_char('19-JUN-04','DD')' of 'to_char('19-JUN-04','month year')
from dual;

8. Create a query that will return only the last word from "Oracle Academy."

----select substr('Oracle Academy.',8,7)
from dual;

9. Lance and Arnie created the following SQL query but it did not return any results. What is the problem with this query?
----SELECT loc_type
FROM d_venues
WHERE ID = 200;

10. What type of function would you use in each case?D = Date function,N = Number function,C = Conversion/Character functions,G = General function,CE = Conditional expression
__n__a. To convert varchar2 to number data
__c__b. To format a date to other than the default format
__c__c. To convert a date such as June 19, 2000 to default format
__n__d. To format a number to appear as currency
__g__e. To substitute a value in a table for null
__ce_f. To do an IF-THEN-ELSE statement
__g__g. To find the first not null expression among a list of expressions
__c__h. To replace a section of a string with another string
__d__i. To format a 20th-century date
__c__j. To present output all in uppercase
_____k. To find the numeric position of a character in a string
_____l. To find the last day of the month

SECTION 2 LESSON 4
1. The business manager of Global Fast Foods needs to update the customer list. She wants to find any zip/postal code that has fewer than 10 digits in order to identify those codes without the new format postfix 87392-8723. Create a query to identify those customers.

----select last_name//' '//first_name,zip
from f_customers
where length(zip)<10;>
a. LPAD(c) b. ROUND (n,d)
c. TRUNC(n,d) d. LENGTH(c)
e. LAST_DAY (d) f. INSTR(c) g. CONCAT (c)

3. You need to display the auth_expense_amt for each DJ on Demand partner. For those partners who do not have an expense account, the output should display "Not Approved."

----select last_name//' '//first_name,nvl(to_char(auth_expense_amt),'Not approved.')
from d_partners;

4. Jason and Jamie tried to run a report displaying the Global Fast Foods staff members who do not have an overtime rate. They wrote the following SQL query and received a report error: ORA-01722: invalid number.

SELECT first_name, last_name, NVL(overtime_rate, 'no overtime') As "Payrate"
FROM f_staffs;
What is wrong with their query and how can it be fixed?

----SELECT first_name, last_name, NVL(to_char(overtime_rate), 'no overtime') As "Payrate"
FROM f_staffs;

5. The president of Global Fast Foods likes to send a birthday card to all employees. He has asked you to send a reminder to him on the month of the employee's birthday so the card can be sent with that month's paycheck. Prepare a query to produce the table shown.

----select last_name//' '//first_name,trunc(birthdate,'month')
from f_staffs;

6. For each statement, mark T if the statement is True or F if the statement is False.

a. TO_CHAR is required to convert the date '03-JUN-04' to June 3, 2004.(f)
b. TO_NUMBER will convert '23-NOV-02' to use with ADD_MONTHS.(f)
c. TO_DATE will convert SYSDATE to today's date.(f)
d. TO_NUMBER('101', '$99999') will convert 101 to a number.(f)
e. TO_CHAR(salary, '$9999.99') will convert number to character format.(t)
f. TO_NUM(varchar2 column) will convert character data to a number.(t)
g. TO_CHAR(SYSDATE, 'Month fmdd, yyyy') will format the date. (f)

7. Employees in the Oracle database who have worked more than 10 years will be given one extra week of vacation. Create a report showing first name, last name, and years worked. Round the result to one decimal place.

----select last_name,first_name,round(months_between(sysdate,hire_date)/12,0)
from employees
where months_between(sysdate,hire_date)/12 > 10;

8. The manager of DJ on Demand needs you to change the zip code for the ID 105, New York venues. She wants to show the old zip code in one column and the new zip code in another column. The new zip code is the same as the old zip code except -2345 needs to be added to the end. The output should appear as shown in the table.

----select id,zip,concat(to_char(zip),'-2345')"new zip"
from dualwhere id=105;

9. Create a query using one SELECT statement that returns today's date. Assign an alias to each column.
a. rounded to the nearest year
b. rounded to the nearest month
c. truncated to the nearest year
d. truncated to the nearest month

----select round(sysdate,'year') from dual;
select round(sysdate,'month') from dual;
select trunc(sysdate,'year') from dual;
select trunc(sysdate,'month') from dual;

10. You need to find out how many days it has been since the start of the Global Fast Foods promotional menus. Round the result to the nearest day.

----select round(months_between(sysdate,start_date)*30.5,0)"nearest day"
from f_promotional_menus;

11. The Human Resources department (Oracle database) has decided that, for their purposes, the job title for all employees will be the first five letters of the job title followed by an asterisk. For example, the accounting manager will be changed to accou* Create a query to accomplish this request.

----select rpad(substr(job_title,1,5),6,'*')
from jobs;

12. What is the order of operations in question 11?

----pregdigest

SECTION 2 LESSON 3
1. For each Global Fast Foods promotional menu, display the event name, and calculate the number of months between today and the ending date of the promotion. Round the months to a whole number. Label the column "Past Promos."

----select event_name,round(months_between(sysdate,end_date),0)"past promos"
from c_event;

2. Use the Oracle database to write a query that returns the salary for employee 174 as:Ellen Abel earns $11000.00 monthly but wants $14000.00

----select last_name' 'first_name' earns'to_char(salary,'$9999999999.99')' monthly but wants $14000.00'
from employees
where employee_id=174;

3. From the DJ on Demand d_songs table, create a query that replaces the 2-minute songs with "shortest" and the 10-minute songs with "longest." Label the output column "Play Times."

----select case duration when '2 min' then 'shortest' when '10 min' then 'longest' else duration end"new time"
from d_songs;

4. Use the Oracle database employees table and CASE expression to decode the department id. Display the department id, last name, salary and a column called "New Salary" whose value is based on the following conditions:
If the department id is 10 then 1.25 * salary
If the department id is 90 then 1.5 * salary
If the department id is 130 then 1.75 * salary
Otherwise, display the old salary.

----select department_id,salary,case department_id when 10 then 1.25*salary when 90 then 1.5*salary when 130 then 1.75*salary else salary end "new salary"
from employees;

5. Display the first name, last name, manager ID, and commission percentage of all employees in departments 80 and 90. Display the manager ID in an additional column called "Review." If they don't have a manager, display the commission percentage. If they don't have a commission, display 99999.

----select first_name,last_name,commission_pct,nvl2(manager_id,9999,commission_pct)"review"
from employees
where department_id in (80,90);

SECTION 2 LESSON 2
1. Create a report that shows the Global Fast Foods promotional name, start date, and end date from the f_promotional_menus table. If there is an end date, temporarily replace it with "end in two weeks." If there is no end date, replace it with today's date.

----select name,start_date,NVL2(end_date,'end in two weeks',sysdate)
from f_promotional_menus;

2. Not all Global Fast Foods staff members receive overtime pay. Instead of displaying a null value for these employees, replace null with zero. Include the employee's last name and overtime rate in the output. Label the overtime rate as "Overtime Status."

----select last_name,NVL(overtime_rate,0)"Overtime Status"
from f_staffs;

3. The manager of Global Fast Foods has decided to give all staff that currently do not earn overtime an overtime rate of $5.00. Construct a query that displays last names and overtime rate shown as $5.00.

----select last_name,to_char(NVL2(overtime_rate,'$5.00','$5.00')
from f_staffs;

4. Not all Global Fast Foods staff members have a manager. Create a query that displays the employee last name and 9999 in the manager ID column for these employees.

----select last_name,NVL(manager_id,'9999')
from f_staffs;

5. Which statement(s) below will return null if the value of v_sal is 50?
a. SELECT nvl(v_sal, 50) FROM emp;
b. SELECT nvl2(v_sal, 50) FROM emp;
c. SELECT nullif(v_sal, 50) FROM emp; ----------^.^
d. SELECT coalesce (v_sal, Null, 50) FROM emp;

6. What does this query on the Global Fast Foods table return?SELECT COALESCE(last_name, to_char(manager_id)) as NAME FROM f_staffs;

----If employees don't have last name, it will label manager id as NAME. And the last name of employees are not null,it will label employees' s last names as NAME. But manager id can never be null because it is the primary key of table.

7. Part 1: Create a report listing the first and last names and month of hire for all employees in the EMPLOYEES table (use TO_CHAR to convert hire_date to display the month).
Part 2: Modify the report to display null if the month of hire is September. Use the NULLIF function.

----select first_name,last_name,NULLIF(to_char(hire_date,'MM'),'09')
from EMPLOYEES;

8. For all null values in the specialty column in the DJs on Demand d_partners table, substitute "No Specialty." Show the first name and specialty columns only.

----select first_name,NVL(specialty,'No Specialty.')
from d_parents;

9. Remove the first three numbers of all the phone numbers in the DJs on Demand d_clients table. Show the last name and revised phone number only.

----select last_name,NVL2(phone,substr(phone,4,7),'')
from d_clients;

SECTION 2 LESSON 1
1. List the last names and birthdays of Global Fast Food Employees. Convert the birth dates to character data in the Month DD, YYYY format. Suppress any leading zeros.

----select last_name,to_char(birthdate,'fxMonth DD, YYYY')
from f_staffs;

2. Convert January 3, 2004, to the default date format 03-JAN-04.

----select to_date('January 3, 2004','DD MON,YYYY')
from dual;

3. Format a query from the Global Fast Foods f_promotional_menus table to print out the start_date of promotional code 110 as: The promotion began on the tenth of February 2004.

----select 'The promotion began on the'//To_char(start_date,'ddth')' of 'to_char(start_date,'Month YYYY')
from f_promotional_menus
where code=110;
4. Convert today's date to a format such as: "Today is the Twentieth of March, Two Thousand Four"

----select 'Today is the'//' '//to_char(sysdate,'Ddspth')//' of '//to_char(sysdate,'MONTH')//', '//to_char(sysdate,'YEAR')
from dual;

5. List the ID, name and salary for all Global Fast Foods employees. Display salary with a $ sign and two decimal places.

----select employee_id,first_name' 'last_name,to_char(salary,'$999999.99')
from f_staffs;

6. Ellen Abel is an employee who has received a $2,000 raise. Display her first name and last name, her current salary, and her new salary. Display both salaries with a $ and two decimal places. Label her new salary column AS New Salary.

----select first_name,last_name,salary,to_number(salary+2000,'$999999.99')+2000 as "New Salary"
from dual
where last_name=Abel;

7. On what day of the week and date did Global Fast Foods?promotional code 110 Valentine's Special begin?

----select start_date,to_char(start_date,'D')
from f_promotional_menus
where code=110;

8. Create one query that will convert 25-DEC-04 into each of the following (you will have to convert 25-DEC-04 to a date and then to character data):
December 25th,2004
DECEMBER 25TH,2004
december 25th, 2004

----select To_char('20-dec-04','Month ddth,YYYY')
from dual;
----select to_char('20-dec-04','MONTH ddTH,YYYY')
from dual;
----select to_char('20-dec-04','month ddth,YYYY')
from dual;

9. Create a query that will format the DJ on Demand d_packages columns, low-range and high-range package costs, in the format $2500.00.

----select to_char(low_range,'$99999999'),to_char(high_range,'$9999999999')
from d_packages;

10. Convert JUNE192004 to a date using the fx format model.

----select to_char('JUNE192004','fxMONDD,YYYY')
from dual;