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;

SECTION 1 LESSON 3
1. For DJ on Demand, display the number of months between the event_date of the Vigil wedding and today's date. Round to the nearest month.

----select round(month_between(event_date,sysdate),0)
from d_events
where name=Vigil wedding;

2. Display the days between the start of last summer's school vacation break and the day school started this year. Assume 30.5 days per month. Name the output "Days."

----select round(months_between('13-Sep-05','25-Jun-05')*30.5,0) "Day"
from dual
3. Display the days between January 1 and December 31.

----select (months_between('01-JAN-06','31-DEC-06')/12)*365
from dual;

4. Using one statement, round today's date to the nearest month and nearest year and truncate it to the nearest month and nearest year. Use an alias for each column.

----select round(sysdate,'month') "nearest month", round(sysdate,'year') "nearest year", trunc(sysdate,'month') "Nearest Month", trunc(sysdate,'year') "Nearest Year"
from dual;
5. What is the last day of the month for June 2005? Use an alias for the output.

----select last_day('01-JUN-05')
from dual;

6. Display the number of years between the Global Fast Foods employee Bob Miller's birthday and today. Round to the nearest year.

----select round(months_between(sysdate,birthday)/12,'year')
from f_staffs
where first_name='Bob';

7. Your next appointment with the dentist is six months from today. On what day will you go to the dentist? Name the output, "Appointment."

----select add_months(sysdate,6) as"Appointment"
from dual;
(Appointment: 24-8月 -06)

8. The teacher said you have until the last day of this month to turn in your research paper. What day will this be? Name the output, "Deadline."

----select last_day(sysdate)as "Deadline"
from dual;
(Deadline: 28-2月 -06)

9. How many months between your birthday this year and January 1 next year?

----select months_between('27-MAY-06','01-JAN-07')
from dual;

10. What's the date of the next Friday after your birthday this year? Name the output, "First Friday."

----select next_day('27-MAY-06','friday')as"First Friday"
from dual;

SECTION 1 LESSON 2
1. Display Oracle database employee last_name and salary for employee_ids between 100 and 102. Include a third column that divides each salary by 1.55 and rounds the result to two decimal places.

----select last_name,round(salary/1.55,2)
from employees
where employee_id between 100 and 102;

2. Display employee last_name and salary for those employees who work in department 80. Give each of them a raise of 5.33% and truncate the result to two decimal places.

----select last_name,salary,trunc(salary+salary*0.0533,2)
from employees
where department_id=80;

3. Use a MOD number function to determine whether 38873 is an even number or an odd number.

----select mod(38873,2)
from dual;
(the remainder is 1, so 38873 is an odd number.)

4. Use the DUAL table to process the following numbers:
845.553 - round to one decimal place
30695.348 - round to two decimal places
30695.348 - round to -2 decimal places
2.3454 - truncate the 454 from the decimal place

----select round(845.553,1)
from dual;

select round(30695.348,2)
from dual;

select round(30695.348,-2)
from dual;

select trunc(2.3454,1)
from dual;

5. Divide each employee's salary by 3. Display only those employees?last names and salaries who earn a salary that is a multiple of 3.

----select last_name,salary ,mod(salary,3)
from employees
where mod(salary,3)=0

6. Divide 34 by 8. Show only the remainder of the division. Name the output as EXAMPLE.

----select mod(34,8)as"EXAMPLE"
from dual;

7. How would you like your paycheck -- rounded or truncated? What if your paycheck was calculated to be $565.784 for the week, but you noticed that it was issued for $565.78. The loss of .004 cent would probably make very little difference to you. However, what if this was done to a thousand people, a 100,000 people, or a million people! Would it make a difference then? How much difference?

----I would like my paycheck to be rounded,haha.

SQL SECTION 1 LESSON 1
1. Using the three separate words "Oracle," "Internet," and "Academy," use one command to produce the following output: The Best Class Oracle Internet Academy

---- SELECT CONCAT('Oracle',' Internet')//' Acameny' AS"The Best Class"
From dual;

2. Use the string "Oracle Internet Academy" to produce the following output: The Net net

---- SELECT SUBSTR('Oracle Internet Academy',13,3)AS"The Net"
FROM dual;

3. What is the length of the string "Oracle Internet Academy"?

---- SELECT LENGTH('Oracle Internet Academy')
FROM dual;

4. What's the position of " I " in "Oracle Internet Academy"?

---- SELECT INSTR('Oracle Internet Academy','I')
FROM dual;

5. Starting with the string "Oracle Internet Academy", pad the string to create ?***Oracle****Internet****Academy****

---- SELECT LPAD(RPAD('Oracle',10,'*')RPAD('Internet',12,'*')RPAD('Academy',11,'*'),37,'*')
FROM dual;
6. Starting with the string "Oracle Internet Academy", pad the string to produce Oracle$$$Internet$$$Academy

---- SELECT RPAD(SUBSTR('Oracle Internet Academy',1,6),9,'$')RPAD(SUBSTR('Oracle Internet Academy',8,8),11,'$')SUBSTR('Oracle Internet Academy',17,7)
FROM dual;

7. Using the string 'Oracle Internet Academy', produce the output shown using the REPLACE function.

---- SELECT REPLACE('Oracle Internet Academy','Internet','2004-2005')AS"The Best Class"
FROM dual;

8. List the order date and the order total from the Global Fast Foods F_ORDERS table. Name the order total as TOTAL, and fill in the empty spaces to the left of the order total with $.

---- SELECT order_date,LPAD('order total',16,'$') AS"TOTAL"
FROM F_ORDERS;

9. Write a query that will output a column called ADDRESS?which has the following information: ZOE TWEE 1009 OLIVER AVENUE BOSTON, MA 12889. Use the Global Fast Foods F_CUSTOMERS table.

----select first_name' 'last_name' 'Address' 'city', 'state' 'zip "ADDRESS"
from f_customerswhere id=456