SECTION 8

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

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

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

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

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

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

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

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

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

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