SECTION 1 LESSON 4
1. Using DUAL, create a function that will convert 86.678 to 86.68.

----select round(86.678,2)
from dual;

2. Create a function that will display the DJ on Demand CD titles for cd_numbers 90 and 91 in uppercase in a column headed "DJ on Demand Collections."

----select upper(title) as"DJ on Demand Collections"
from d_cds
where cd_number in (90,91);

3. Create a function that will create computer usernames for the DJ on Demand partners. The usernames will be the lowercase letters of the last name + the uppercase first letter in the first name. Title the column "User Passwords." For example, Mary Smythers would be smythersM.

----select lower(last_name)// ' '//upper(instr(first_name,1))as"User Passwords"
from employees ;

4. Create a function that will convert " It's a small world" to "HELLO WORLD."

----select upper(replace('it is a small world','it is a small','hello'))
from dual;

5. Create a function that will remove the "fiddle" from "fiddledeedee" and the "dum" from "fiddledeedum." Display the result "fiddledeedeedee" in a column with the heading "Nonsense."

----select substr('fiddledeedum',1,9)''substr('fiddledeedee',7,5)as"Nonsense"
from dual ;

6. Replace every "i" in Mississippi with "$."

----select replace('Mississippi','i','$')
from dual;

7. Using DUAL, convert 5332.342 to 5300.

----select trunk(5332.342,-2)
from dual;

8. Using DUAL, convert 3.14159 to 3.14.

----select trunc(3.14159,2)
from dual;

9. Using DUAL, convert 73.892 to 73.8.

----select trunc(73.892,1)
from dual;

10. What is the next Friday six months from now? Label the column "Future."

----select next_day(add_months(sysdate,6),'FRIDAY')as"future"
from dual

11. What is the date 10 years from now? Label the column "Future."

----select add_months(sysdate,10*12)as"future"
from dual;

12. Leap years occur every four years. Remember, 2004 was a leap year. Now create a function that will show the date of the next leap year as 29-FEB-08. Label the column "Future."

----select add_months('29-FEB-04',4*12)as"future"
from dual;
13. Create a query that will find any of the DJ on Demand CD themes that have an "ie" in their names.

----select name
from d_themes
where name='%ie%';

14. Create a query that will return only the DJ on Demand CDs with years greater than 2000 but less than 2003. Display both the title and year.

----select title,year
from d_cds
where year>=2000 and year<=2003;

15. Create a query that will return the Oracle database employee's employee ID and their starting hire dates between January 1, 1997 and today. Display the result ordered from most recently hired to the oldest.

----select employee_id,hire_date
from employees
where hire_date between sysdate and '01-JAN-97'
order by hire_date;