太阳与月亮同存的星球

SECTION 8

2006-05-01T10:05:00.000-07:00

lesson 2
1.1 create o_employees
as (select* from employees)
1.2 create o_departments
as (select * from departments)
1.2 create o_jobs
as (select * from jobs)

2.insert into o_jobs(id,job_title,job_id,max_salary,min_salary)
values(210,'Human Resources Mamager','HR_MAN',5500,4500)

3.insert into o_employees
(emoloyee_id,first_name,last_name,email_address,hire_date)
values(210,'Ramon','Sanchez','RSANCHEZ',sysdate)

4.insert into o_employees
select (emoloyee_id,first_name,last_name,email_address,hire_date)
values(210,'Ramon','Sanchez','RSANCHEZ',sysdate)

lesson 4

2.create table grad_candidates
(student_id number(6),last_name varchar2(10), first_name varchar2(15),credits number (3), graduation_date date (8), constraint Graduate_Candidates_primary_key PRIMARY KEY(student_id));

3.despribe grad_candidates

4.update smith_table
as(select *
from grad_candidates);

5.select*
from USER_TABLES
select *
from USER_OBJECTS
select *
from USER_CATALOG or USER_CAT

6.insert into smith_table
values(..................................)

lesson 5

SECTION 7

2006-04-21T11:41:00.000-07:00

lesson 1

1.select year
from d_cds
where title='Carpe Diem')

2.select id,title,duration,artist
from d_songs
where type_code in (select code
from d_types
where description in('Jazz','Pop'))

3.select cost
from d_events
where cost<=(select cost
from d_events
where package_code=200)

4.select last_name,salary
from employees
where salary = any (select min(salary)
from employees)

5.select employee_id,last_name,salary
from employees
where salary=(select min(salary)
from employees)

6.a. Which CDs in our d_cds collection were produced before "Carpe Diem" was produced?
WHERE year _<_________ (SELECT year ...
b. Which employees have salaries lower than any one of the programmers in the IT department?
WHERE salary ___< any______(SELECT salary ...
c. What CD titles were produced in the same year as "Party Music for All Occasions" or "Carpe Diem"?
WHERE year _____=____(SELECT year ...
d. What song title has a duration longer than every type code 77 title?
WHERE duration _____>___(SELECT duration ...

7._F a. WHERE size > ANY If the inner query returns sizes ranging from 8 to 12, the value 9 could be returned in the outer query.
__F__b. WHERE book_number IN If the inner query returns books numbered
102, 105, 437, and 225 then 325 could be returned in the outer query.
_F___c. WHERE score <= ALL If the inner query returns the scores 89, 98, 65, and 72, then 82 could be returned in the outer query.
__T__d. WHERE color NOT IN If the inner query returns red, green, blue, black, and then the outer query could return white.
__ F_e. WHERE game_date = ANY If the inner query returns 05-JUN-97, 10-DEC-02, and 2-JAN-04, then the outer query could return 10- SEP-02.

8.select min(salary)
from employees
where salary<(select min(salary)
from employees
where department_id=50)

9. Which statements are True about the subquery below?
SELECT employee_id, last_name
FROM   employees
WHERE salary =
               (SELECT   MIN(salary)
                FROM     employees
                GROUP BY department_id);
__F____a. The inner query could be eliminated simply by changing the WHERE clause to WHERE MIN(salary).
_T_____b. The query wants the names of employees who make the same salary as the smallest salary in any department.
_F_____c. The query firsts selects the employee ID and last name, and then compares that to the salaries in every department.
__T____d. This query will not execute.

10.0. Place an X next to those values that could be returned by the outer query if the inner query returned 185.98, 315.75, 382.24, and 877.31 with the following WHERE clause:
WHERE total_sales < ANY
___X__a. 155.22
_____b. 325.88
_____c. 877.30
_____d. 382.24

lesson 2

1.select last_name, job_id,salary
from employees
where department_id in (10,30)

select last_name, job_id,salary
from employees
where department_id=10 or department_id=30

2.select department_name,location_id
from departments
order by department_id

3.select first_name,last_name,job_id,location_id,to_char(salary,'$999,999.99')
from employees,departments
order by job_id,salary desc

4.select first_name,last_name,job_id,salary
from employees
where salary in(select max(salary)
from employees)

5.

lesson 4
1.insert into copy_d_cds(cd_number,title,producer,year)
values('97','celebrate the day','R&B Inc.','2003')
values('98','holiday tunes for all ages','Tunes Are Us','2004')
values('99','party music','old town records','2004')
values('100','best of rock and roll','old town records','2004)
select *
from copy_d_cds

2.insert into copy_d_cds(id,title,duration,type code)
values('52','surfing summer','not known','12')
values('53','voctory victory','5 min','12)

3.insert into copy_d_clients
values(6655,'Ayako','Dahish',3608859030,'dahisha@harbor.net')
values(6689,'Nick','Neuville',9048953049,'nnicky@charter.net')

4.insert into copy_d_clients(id,name,event_date,
description,cost,venue_id,
package_code,theme_code,client_number)
values('110','Ayako Anniversary','07-jul-04',
'party for 50,sixties dress,cecorations',null,
'245','79','240','6655')

lesson 5

1.update copy_f_food_items
set price='3.75'
where food_item='strawberry shake'
and set price='1.20'
where food_item='fries'

2.update copy_f_staffs
set overtime_pay=overtime_pay+0.75
where last_name='Miller' and first_name='Bob'
set overtime_pay=nul(overtime_pay+0.85,0)
where last_name='Doe'and first_name='Sue'

5.update copy_f_staffs
set salary=(select salary
from copy_f_staffs
where last_name='Miler'and first_name='Bob')
WHERE LAST_NAME='Doe' and first_name='Sue'

SECTION 6

2006-04-20T21:35:00.000-07:00

lesson 1

1. a.T b. F c. T d.T e.F

2.a.SELECT manager_id,avg(salary)
from employees
having avg(salary)<16000
group by manager_id

b. SELECT cd_number, COUNT(title)
   FROM d_cds
   WHERE cd_number < 93
group by cd_number

c.SELECT ID, MAX(ID), artist AS "Artist"
   FROM d_songs
   WHERE duration IN('3 min', '6 min', '10 min')
   HAVING ID < 50
   GROUP by ID;

d.SELECT loc_type, rental_fee AS "Fee"
   FROM d_venues
   WHERE id <100
   ORDER BY 2;

3.SELECT MAX(DISTINCT(song_id))
FROM d_track_listings
where track IN ( 1, 2, 3);

4.?

5.a.F b.F c.F

lesson 2
F_____1. The CONCAT character function combines two or more character values into one character string.

T_____2. The SYSDATE function returns the Oracle Server date and time.

T_____3. The RPAD character function returns a right-justified value with a string of characters replicated to fill a specified number of character positions.

T_____4. The ROUND number function will round 469.89 to 470 if the decimal place value is omitted.

F_____5. The SUBSTR character function returns a portion of a string beginning at the start of a string to a specified number of characters.

T_____6. DESC will order alphabetical data from Z to A.

T_____7. The column name in a SELECT statement is an example of a SELECTION.

F_____8. You cannot use a column alias in the WHERE clause.

F_____9. To specify an alias in the SELECT clause for retired employee, use 'Retired Employee.'

T_____10. Aliases always appear in UPPERCASE, unless enclosed within proper quote marks.

F_____11. If any column value in an arithmetic expression is NULL, the result defaults to 0.

T_____12. Null values are treated as zero when using SUM functions.

F_____13. In arithmetic expressions, multiply and divide are evaluated after addition and substraction.

T_____14. By default, column headings are returned in UPPERCASE.

T_____15. SQL statements are not case-sensitive, unless indicated.

T_____16. SQL statements can be entered on one or more lines.

T_____17. SQL keywords can be abbreviated using single quotes.

T_____18. Columns are displayed in the order in which they appear in the WHERE clause.

T_____19. Column aliases that are more than one word require the AS prefix.

T_____20. In the SELECT clause, if an expression is not a column name or a column alias, it is a literal value.

T_____21. Number and date values must be enclosed in single quotes.

T_____22. The keyword DISTINCT will affect all of the columns in the statement: SELECT DISTINCT(employee_id, department_id,department_name)

T_____23. HTML DB is a type of application.

T_____24. VARCHAR2 describes variable-length character values.

F_____25. To restrict the rows returned from a query, use the SELECT clause.

F_____26. The comparison operator IN is used to match any of a list of items.

F_____27. Using WHERE employee_id = NULL returns all employees where do not have an employee ID.

F_____28. BETWEEN 250 AND 350 will return 101 values.

T_____29. LIKE '_ _ e%' could return 'Fred.'

T_____30. To return SA_MAN, use LIKE ' SA /_%' ESCAPE ' \ '

T_____31. Logical operators are evaluated in the AND, OR, NOT order.

T_____32. WHERE e.last_name LIKE 'Sm%' OR d.department_id = 60 AND d.department_name = 'IT' could return anyone from the IT department whose department ID is 60.

T_____33. The rules of precedence would evaluate an expression that contained each of the following in the order: arithmetic, concatenation, NOT, OR

T_____34. The ORDER BY clause comes last in the SELECT statement.

T_____35. In SELECT last_name, first_name, the ORDER BY clause could include department_id.

T_____36. The order of execution of a SELECT statement is: FROM, WHERE, SELECT, ORDER BY

F_____37. ADD_MONTHS(hire_date, 6) will return a number.

F_____38. MONTHS BETWEEN ('05-DEC-89' , '10-JUN-93') will return 4.

T_____39. If April 21st is a MONDAY, then NEXT_DAY('21-APR'-03', 'FRIDAY') will return 25-APR-03.

T_____40. ROUND(SYSDATE, 'YEAR') will return 01-JAN-05 if today is August 23, 2004.

F_____41. All group functions ignore NULL values.

F_____42. SELECT COUNT( last_name, first_name) will return the number of last and first names in the database.

F_____43. If salary values in a column are 5000, null, 4000, 7000, using AVG(salary) will return 4000.

T_____44. AVG(DISTINCT salary) will average only the salaries that are different from each other.

F_____45. NULLIF(salary, 4000) will return 4000 if the employee's salary is 5000.

T____ 46. COALESCE(hire_date, salary, commission_pct) will return salary if the hire date is NULL.

T____ 47. The HAVING clause is used to restrict groups.

T____48. If a GROUP BY clause is used, any column that is in the SELECT clause that is not a group function must be listed in the GROUP BY clause.

F_____49. If a query lists clauses WHERE, GROUP BY, and HAVING, no ORDER BY clause can be used.

T_____50. If a join is needed and you want to use a WHERE clause, choose a JOIN ON or JOIN USING join.

T_____51. COUNT(DISTINCT department_id) will return the number of different department IDs.

F_____52. If a column had 4 NULL employee IDs and 250 defined IDs, COUNT will return 254.

lesson 5

1.select title, artist
from d_songs
where type_code=(select type_code
from d_songs
where title='Im Going to Miss My Teacher')

2.select employee_id,last_name,salary
from employees
where salary<(select max(salary)
from employees
where job_id='ST_CLERK')

3.select event_id,song_id
from d_play_list_items
where event_id=(select event_id
from d_play_list_items
where song_id=45)

4.select id, name
from d_events
where cost>(select cost
from d_events
where id=100)

5.select cd_number,title
from d_cds
where cd_number=(select cd_number
from d_cds
where title='Party Music for All Occasions')

6.select code,description
from d_themes
where code=(select code
from d_themes
where description='Tropical')

7.select id,last_name,salary
from f_staffs
where salary>(select salary
from f_staffs
where id=12)

8.select id,last_name,staff_type
from f_staffs
where staff_type<>(select staff_type
from f_staffs
where first_name='Bob' and last_name='Miller')

9.select id,last_name,staff_type,salary
from f_staffs
where salary<(select salary
from f_staffs
where staff_type='Cook')

10.select id,theme_code
from d_events
where theme_code=(select theme_code
from d_events
where id=100)

11.select department_id,department_name
from departments
where department_id=(select department_id
from departments
where department_name='IT')

12.select department_id,department_name
from departments
where location_id=(select location_id
from locations
where city='Seattle')

13.select department_id,min(salary)
from employees
group by department_id
having min(salary)>(select min(salary)
from employees
where department_id<>50)

14.B

SECTION 5

2006-04-20T20:30:00.000-07:00

lesson 1.
1.null
2.depend on which side is null or the question need
3.condition
4.values,data type
5.colunm name, data type

lesson 4

1.select round(avg(cost),2)
from d_events
2.select avg(salary)
from f_staffs
where manager_id=19

3.select sum(salary)
from f_staffs
where id in (12,9)

4.select min(salary),max(hire_date),min(last_name),max(last_name)
from employees
where department_id in (50,60)

5.only one

6.??
7.march 30,1969

8.select avg(order_total)
from f_orders
order by order_date

9.select max(hire_date)
from employees

(MAX(HIRE_DATE)
29-1月 -00 )

10.sum

lesson 5

1.select count(title)
from d_songs
(6)
2.select count(distinct(loc_type))
from d_venues
(4)

3.select count(song_id),count(distinct(cd_number))
from d_track_listings

COUNT(SONG_ID) COUNT(DISTINCT(CD_NUMBER))
5 4

4.select count(email)
from d_clients
(3)

5.select count(auth_expense_amt)
from d_partners
(1)

6.(4)

7.select avg(auth_expense_amt)
from d_partners

8.a. from b.sum c.sum d.avg e.min f.max g.min

9. a.T b. T c. T d.F

10.a. T b.F c. F d.T

SECTOION 4

2006-04-20T20:25:00.000-07:00

Lesson5

1. A/An _Natural join__ is when the rows of the tables
are combined with each other and produce new rows .
The number of rows is equivalent to the product of the
number of rows in each table.

2. A/An ___self join___ is used when you need to query
a table that has a relationship to itself.

3. A/An _cross join_____ preserves unmatched rows from
one or both tables, returning the rows that are matched
and unmatched from one or both tables.

4. In an outer join, a plus sign (+) is placed on the
side of the join that is _____null___information.

5. A ____nonequijoin_____is used when a column in one
table does not correspond directly to a column in another table.

6. The join condition is always placed in the __using___clause
of the SELECT statement.

7. A/An shortcut of the colunm name____ is used to preface
the column name in order to clarify which table and column
are participating in the join.

8. Table aliases are created in the as__clause of
the SELECT statement.

9. In a full outer join, a row that does not contain
data will/will not appear in the results set if the
row satisfies the join condition.

10. Table aliases cannot exceed ___more______ characters
in length.

11. Identify the Oracle syntax to signify an outer join___________.

12. If a join condition is written:
WHERE e.client_number = c.client_number,
what kind of join would it be if we wanted
all the information in the e table even if
the c table has missing data? _ritght outer join_____

13. Joins that are based on hierarchical relationships
such as manager and employee are called __________.

14. How many join conditions does it take to join three tables? _4__

15.?
16.nonequijion
17.natural join and equijoin
18.Cartesian product
20. T___ cross-join
T___ equijoin
T___ natural join
21.nonequijoin
USING clause
22.Nonequijoin
23.????

Lesson 4

1.select j.job_id,j.job_title,e.first_name,e.last_name,e.department_id
from jobs j full outer join employees e
on(j.job_id=e.job_id)
where j.job_id='IT_PROG'

2.select e.last_name "Employee",e.employee_id "Emp#",
e.last_name "Manager",e.manager_id "Mgr#"
from employees e full outer join employees m
on (e.employee_id=m.manager_id)

3.select e.last_name "Employee",e.employee_id "Emp#",
e.last_name "Manager",e.manager_id "Mgr#"
from employees e full outer join employees m
on (e.employee_id=m.manager_id(+))

4.select c.first_name,c.last_name,e.event_date,
e.description
from d_clients c full outer join d_events e
on (c.client_number=e.client_number(+))

5.select f.description, s.shift_assgn_date
from f_shifts f full outer join f_shift_assignments s
on(f.code=s.code)

lesson 3

1.select l.location_id,d.department_name
from locations l join departments d
on (l.location_id=d.location_id)
where l.location_id=1400

2.??????????

3.select d.department_name,d.department_id,
l.location_id,l.city
from locations l join departments d
on(l.location_id=d.location_id)
where d.department_id in(10,20,30)

4.select region_id,country_name,region_name
from regions join countries
using (region_id)

5.select first_name,last_name,hire_date,job_id,job_title,max_salary
from employees join jobs
using (job_id)
where salary>=12000

6.select job_title,first_name,last_name,email
from employees join jobs
using (job_id)
where job_id ='ST_CLERK'

7.select e.employee_id,e.first_name,
e.last_name,m.manager_id,m.first_name,m.last_name
from employees e join employees m
on (e.employee_id=m.manager_id)

8.select l.location_id,l.city,d.department_name
from locations l join departments d
on (l.location_id=d.location_id)

9.select e.manager_id,d.department_id,
d.department_name,e.first_name,e.last_name
from employees e join departments d
on (e.department_id=d.department_id)
where e.department_id in(80,90,110,190)

10.select e.employee_id,e.last_name,
d.department_id,d.department_name,e.hire_date
from employees e join departments d
on (e.department_id=d.department_id)
where e.hire_date='07-6月 -94'

lesson 2

1.select last_name,department_name
from employees cross join departments

2.select department_id,department_name,location_id,city
from departments natural join locations

3.select d.department_id,d.department_name,l.location_id,l.city
from departments d,locations l
where d.location_id=l.location_id

4.select department_id,department_name,location_id,city
from departments natural join locations
where department_id in (20,50)

5.select d_songs.title,d_types.code,d_types.description
from d_songs,d_types
where d_types.code between 70 and 80

6. a. where b.from

7. a.aliase 8.join clause

SECTOION 3

2006-04-20T20:06:00.000-07:00

lesson 5

1.select e.last_name "Employee",e.employee_id "Emp#",m.last_name "Manager",m.manager_id "Mgr#"
from employees e,employees m

2.select e.last_name "Employee",e.employee_id "Emp#",m.last_name "Manager",m.manager_id "Mgr#"
from employees e,employees m
where e.employee_id(+)=m.manager_id

3.select e.last_name "Employee",e.hire_date "Emp Hired",m.last_name "Manager", m.hire_date "Mgr Hired"
from employees e,employees m
where e.hire_date<=m.hire_date

4.select e.last_name,e.department_id,d.department_name
from employees e,departments d
where e.department_id=d.department_id

5.select l.location_id,l.city,d.department_name
from locations l,departments d
where l.location_id=d.location_id

6.select e.last_name,d.department_name
from employees e, departments d
where e.first_name='Diana' and e.last_name='Lorentz'

7.when the side does not have the information that matching with the other side, you need to add (+).

Lesson 4

1.Select f_customers.*, f_orders.*
from f_customers, f_orders
where f_customers.id= f_orders.cust_id(+)

2.Select employees.last_name, departments.department_id, departments.department_name from employees, departments where employees.department_id= departments.department_id(+);

3.Select employees.last_name, departments.department_id, departments.department_name from employees, departments where employees.department_id(+)= departments.department_id

4.a. where e.department_id(+)=d.department_id OR where
e.department_id=d.department_id(+)
b.select e.first_name,e.last_name,d.department_name,d.department_id
from employees e, departmetns d
where e.department_id=d.department_id;
c.where e.position>=p.highest and where p.highest<=p.lowest;
d.select e.employee_id,e.last_name,d.location_id
from employees e, departments d
where e.department_id=e.department_id;
5.select d_cds.title,d_songs.id
from d_cds,d_songs
where d_cds.cd_number(+)=d_songs.type_code

lesson 3

1.select d_events.name,d_packages.code
from d_events,d_packages
2.Select employees.last_name, employees.salary, job_grades.grade_level from employees, job_grades where employees.salary between job_grades.lowest_sal and highest_sal
3.between.....and ...
4.ranking >= g.lowest_rank and <= g.highest_rank
5.denpent on what kind of join you are going to use
6.limite the searching
7.equijoins
8.nonequijoins

Lesson 2

1.select *
from d_play_list_items,d_track_listings
2.select d_play_list_items.event_id,d_play_list_items.song_id,
d_play_list_items.comments,d_track_listings.cd_number,
d_track_listings.track
from d_play_list_items,d_track_listings
where d_play_list_items.song_id=d_track_listings.song_id
3.select d_songs.title, d_songs.artist, d_types.description
from d_songs,d_types
4.select d_songs.title, d_songs.artist, d_types.description
from d_songs,d_types
where d_songs.id in(47,48)
5.select *
from d_clients,d_events,d_job_assignments
6.select d_track_listings.song_id,d_cds.title
from d_track_listings,d_cds
7. a.F b.T c.T d.F e.T f.F g.T

Lesson 1
plan for my future
i do not know what will happen in my future,so the first thing is do the thing i want to do.i will go to college first and then during my collgeg time i will look for job that connect with my major.
i think when i am still studying in college, i will live with my parent because i want to save money and may be my college is not far away from my house. the major i will choose is manager. the first thing i need to do is finished my four-years college and then think about next because there are a lot of changing in the future and i dont know what will happen.
my ten-years goal: in the first year, i will studying in college and go on look for my interst and stuggle for my english. in the second year, i will study and work together. in the fourth year, i think my major is decided, so i will stuggle in my major and start to look for a better job. after my four-years college, maybe i will work first and then go to the higher education.

LESSON 5

2006-03-19T20:20:00.000-08:00

2.1.Select cd_number||title
from d_cds
where cd_number =94;
2.Select concat(cd_number,title)
from d_cds
where cd_number =94;
3.a.F b.F c.F d.T e.F f.T g.T

4.Select to_char(cost, '$999999.99') from d_events;

5.Select lpad(id,2,'*') from f_staffs where id<10

6.Select '15-DEC-95' from dual

7.Select to_char('19-Jun-04','ddth "of" fmmonth year' from dual

8.Select substr('Oracle Academy',14,14) from dual

9.SELECT loc_type
FROM d_venues
WHERE loc_type LIKE 'National Park'
AND ID = 200;
CHANGE TO

SELECT loc_type
FROM d_venues
WHERE loc_type LIKE 'National Park'
OR ID = 200;

10.a.N b.N C.D d.N e.G f.CE g.G h.C i.D j.C k.C l.D

lesson4

2006-03-14T11:24:00.000-08:00

1.select first_name,last_name,zip
from F_CUSTOMERS
where length(zip)<10

2.

3.select id,first_name,last_name,nvl2(auth_expense_amt,'auth_expense_amt','Not Approved')from d_partners

4.i think it cant use nvl,so i change it to nvl2
SELECT first_name,last_name,NVL2(overtime_rate,'no overtime','overtime_rate') As "Payrate"
FROM f_staffs

5.select first_name,last_name,birthdate,to_char(birthdate,'Month,RRRR')as "SEND CARD"
from f_staffs

6.a.F b.F c.T d.T e.F f.F g.T

7.select first_name,last_name,NVL2('HIRE_DATE','ONE WEEK VACATION')AS "WORK YEAR"
FROM EMPLOYEES
WHERE MONTHS_BETWEEN(SYSDATE,HIRE_DATE)>120

lesson2

2006-03-13T09:48:00.000-08:00

1.select name,start_date,end_date,nvl('end in two weeks','end_date')
from f_promotional_menus

2.select last_name,NVL(overtime_rate,0) as
"Overtime Status" from f_staffs

3.select last_name,overtime_rate,nvl2(overtime_rate,overtime_rate,5)from f_staffs

4.select last_name
from f_staffs
where manager_id=19

5."d"

6.NAME
Doe
Miller
Tuttle

7.part1. select first_name,last_name,to_char(hire_date)
from employees
part2.select first_name,last_name,nullif(to_char(hire_date),to_char(hire_date))
from employees
where hire_date='21-sep-89';

8.select first_name,specialty,nvl(specialty,'No Specialty')from d_partners

9.select last_name,substr(phone,3,6)from d_clients

lesson3

2006-03-07T10:06:00.000-08:00

1.select name,round(months_between(sysdate,end_date))as"Past Promos" from F_PROMOTIONAL_MENUS

2.select first_name||' '||last_name||' '||concat('earns','$10000.00')||' '||concat('monthly','but')||' '||concat('wants','$14000.00')
from employees
where employee_id=174

3.select id,title,duration,decode(duration,'2 min','shortest','10 min','longest',duration)as "Play Times"
from d_songs

4.select department_id,last_name,salary,decode(department_id,50,1.25*salary,80,1.5*salary,1.75*salary)as "New Salary"
from employees

5.select first_name,last_name,manager_id,commission_pct
from employees
where department_id in(80, 90);

select manager_id,coalesce(manager_id,commission_pct,9999)as "Review"
from employees

section2 lesson1

2006-03-06T19:31:00.000-08:00

1.select last_name,to_char(birthdate,'fxmonth DD,YYYY')
from f_staffs

2.select to_date('January 3,2004','mon dd,RRRR')
from dual

3.select to_date(start_date)as"Promotion on 10th-Feb-2004"
from f_promotional_menus
where code=110

4.select 'Today is the'||' '|| to_char(sysdate,'Ddspth "of" month')||' '||to_char(sysdate,'Year')from dual

5.select id,first_name,last_name,salary,to_char(salary,'$9999.99')
from f_staffs

6.select first_name,last_name,to_number('salary','$9999.99'),to_number('salary','$9999.99')+2000 as "New Salary"
from employees
where last_name='abel'

7.select start_date
from f_promotional_menus
where code=110

8.select to_char(to_date('25-dec-04','Month ddth,YYYY'))from dual
select to_char(to_date('25-dec-04','MONTH ddTH,YYYY'))from dual
select to_char(to_date('25-dec-04','month ddth,YYYY'))from dual

9.select code,to_char(low_range,'$999999.99'),to_char(high_range,'$99999.99')from d_packages

10.select to_date('JUNE 19,2004','fxMONTH DD,RRRR') FROM DUAL

lesson4

2006-02-28T09:33:00.000-08:00

1. round(86.678,2)from dual

2.select title,upper(cd_number)as "DJ on Demand Collections"
from d_cds

3.select lower(last_name)||''||substr(upper(first_name),1,1)as "User Passwords"
from d_partners

4.select replace('HELLO WORLD','It is a small world')
from dual

5.select substr('fiddledeedum',1,9)||substr ('fiddledeedee',7,6)as "Nonsense"
from dual

6.select replace('mississpippi','i','$')
from dual

7.select round(5332.342,-2)from dual

8.select round(3.14159,2)from dual

9.select trunc(73.892,1)from dual

10.select add_months(sysdate,6)as "Future" from dual

11.select add_months(sysdate,120)as "Future" from dual

12.select add_months('29-feb-04',48)as "Future" from dual

13.select code,description from d_themes
where description between 'ie%'and '%ie'

14.select title,year from d_cds
where year between 2000 and 2003

15.select employee_id,hire_date from employees
where hire_date between '01-jan-97'and sysdate

LESSON 3

2006-02-24T20:54:00.000-08:00

1.select id,name,round(months_between(sysdate,event_date),0)
from d_events

2.select round(months_between('31-dec-06','01-jan-06')*30.5,0)
from dual

3.select round(months_between('31-dec-06','01-jan-06')*30.5,0)
from dual

4.select trunc(round(sysdate,'month'),'month')as alias,trunc(round(sysdate,'year'),'year')as alias
from dual;

5.select last_day('01-JUN-05')AS "JUNE"
FROM DUAL

6.select round(months_between(sysdate,birthday)/12,'year')
from dual
where name='Bob Miller';

7.SELECT ADD_MONTHS(SYSDATE,6)AS"Appointment"
FROM DUAL

8.select LAST_DAY('01-FEB-06')AS "Deadline"
from dual

9.select round(months_between('01-jan-07','27-aug-06'),0)as "BIRTHDAY DAY"
from dual

10.select next_day('27-aug-06','FRIDAY')AS "First Friday"
from dual

LESSON 2

2006-02-24T18:58:00.000-08:00

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

2.select last_name,trunc(salary*1.0533,2) as "salary" from employees where DEPARTMENT_ID=80

3.select mod(38873,2) from dual
(the answer is =1,so this is a odd number)

4.1.select round(845.553,1)from dual
(The answer is 845.6)
2.select round(30695.348,2)from dual
(The answer is 30695.35)
3.select round(30695.348,-2)from dual
(The answer is 30700)
4.select trunc(2.3453,1)from dual
(The answer is 2.3)

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

6.select mod(34,8)as"EXMAPLE" from dual
(the answer is 2)

7.select (0.004*1000)from dual
(the answer is 4)
select (0.004*100000)from dual
(the answer is 400)
select (0.004*10000000)from dual
(the answer is 40000)

lesson1

2006-02-24T15:56:00.000-08:00

Lesson1

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')||'Academy' 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('Oracle',10,'*')||lpad('Inernet',12,'*')||lpad('Academy',11,'*')||rpad('*',4,'*')
From DUAL

6. Starting with the string "Oracle Internet Academy", pad the string to produce
Oracle$$$Internet$$$Academy
Select rpad('Oracle',9,'&')||rpad('Inernet',11,'&')||'Academy'
From DUAL

7. Using the string 'Oracle Internet Academy', produce the output shown using the REPLACE function.
The Best Class
Oracle 2004-2005 Academy

Select replace('Oracle 2004-2005 Academy','Oracle Inernet Academy')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',7,'$')"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||' '||'.'
from F_CUSTOMERS
where id=456

2nd draft

2005-09-25T14:08:00.000-07:00

There is a big cultural difference between china and America. I lived in china for more than 17 years. The Chinese culture is a part of my life. A big change took place after I moved to America. Now I am starting a new life in America.

When I was still on the plane before arriving in America, I felt very excited and happy.
After I arrived in America, I felt that it is very hard for my new beginning.
At first, since I am still at school age, I need to look for a school. This is the most important thing in America. I found several schools and they all said that I am overaged. they cannot accept me. I feel scared in this new world. I thought "Is there no more chance for me to go on my study? My life will become very boring." I do not want that to happen. Luckily, I find a school that I can go to. This is a school for immigrants--Lower East Side Prep. High School. There are a lot of students in the same situation like me in the school.

Time flys day by day. I learn more and more culture and customs in America. I know it more that I see the more week of America. New York is a place where many immigrants live in. Though, people in America said "no prejudice and racial discrimination", but I can see it happening in many many places.
I remember when I came here always one year ago and my second auntie comes here less them half year ago. One time, my auntie and my old and worn down grandmother went into the subway station. My auntie forgot my grandmother who could meet use her metro card by herself and she could not help her. Those were not the bad things. The bad things is that they don not know any English word not even a world like help. They used body language to ask for help, but still nobody looked at them. Suddenly, a young teenage guy rubbed their metro card and ran away quickly. My auntie and grandmother shout “help” loudly in Chinese. Unfortunately nobody paid attention to them. My auntie felt very upset. Her metro card was a monthly pass that she just bought and when I heard that I felt raged and helpless. How come nobody could help them, I know language is very important that is what we need if we live in here.
Another story was in a winter morning. Everyone was in hurry to catch buses or subways to go to work or school. I was lucky; I caught the bus on time. The bus moved, I saw a Spanish woman running to the bus and signaled to stop. The driver did not stop the bus. In the next station, a slimily situation, the difference was a white man running to the bus. The driver stopped and smiled at him. I felt unfair. What kind of this society? How could the driver do that?
Whatever, I know one person can not change the society. But I only understand, I must study hard, speak the language and may be I won’t be the next victim. Sometimes this society is ruthless.

来美的经历

2005-09-18T15:55:00.000-07:00

中国的文化与美国的文化有着很大的区别.我在中国已经生活了十多年,中国的文化对于我来说早已经是我生活的一部分.一个大转变,由中国移民到了美国使我感受到中国对于我来说已经是一个终点,来到美国是我的一个新的开始.
当我仍然坐在飞机上还没有真正踏入美国的领土的时候,我的心情仍然是对美国充满了希望.但当我真正踏入美国,接触美国的时候,我就觉得这是一个很难的开始.我是在学习的阶段来到美国,所以我一来到了美国,最重要的事情就是找学校.在中国读书是自己付钱,所以是没有年龄的限制.但我在这边找了几间学校,他们都说我的年龄已经不太适合读高中,他们不接受我.我对这个新来的世界开始充满了恐惧,我想,难道我就这样开始要找工作,就这样过我的一生吗?我不希望是这样,好在后来我找到了我现在的这所学校.这是一所新移民的学校,在这校里面的人都跟我差不多,年龄大,英文不是很好,所以我在一种家庭的感觉.
时间一天一天地过去了,我对美国的文化,习俗也慢慢地开始了解了.我了解它越深我就看到它越多的缺点.纽约是一个集中了各种各样的移民,虽然美国声称不能有歧视和偏见这些事情存在,但对些事情却发生于不同种族之间.
就拿我们中国人来说,我读书的那所学校虽然大多数是中国人,老师也有很多都是中国人,但也有一些是外国人.我第一个学期来到这所学校就有一堂体育课,在这堂课里,在一部分是中国人,一部分是外国人.我在这堂课里认识了几位跟我背景差不多的女同学.这堂课的老师是一位白人,而且我觉得他对我们中国人很人偏见,因为我们不会讲英文.我记得有很多的时候,他明知我不太会英文,却偏要我做示范给同学看,这样经常使我在同学面前出羞.而且他不太相信我们的话,我们有事情要请假,就算是拿出了请假条,他都不太相信.但外国的同学只要跟他说一声,他就说行啦.而且我们要做的事情永远都比外国的同学多.我真的觉得这位老师很不公平,这不但是我的感觉,连我认识的一些同学都有这样的感觉.
人的体育课已经完满结束了,我觉得我好像脱离了那种无形的压力.但我知道我仍然还有很多类似这样的问题要面对,但我同样知道,我是一定可以面对和解决到这些问题.

(还没有经过修改)