## Saturday, March 25, 2006

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);