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;