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;