Saturday, March 04, 2006

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;

3 Comments:

Blogger Henry Ruan said...

Look like I have to give you a good grade this time. But you still miss something, journal!!! Can you write a little about the class.

8:47 AM  
Blogger 大葱~~~~~~~ said...

ok,ok,no problem~!

4:41 PM  
Blogger Jeffy said...


Thank you for sharing such a nice and interesting blog with us. I have seen that all will say the same thing repeatedly. But in your blog, I had a chance to get some useful and unique information. I would like to suggest your blog in my dude circle. please keep on updates. hope it might be much useful for us. keep on updating...
seo company in chennai
Digital Marketing company in chennai

11:01 PM  

Post a Comment

<< Home