10.txt

Oracle database 10g基础教程（第二版） 源码

create table bookTitle(
title char(50)
);
insert into bookTitle values('Oracle Database 10g');
insert into bookTitle values('Microsoft SQL Server 2005');
select title, length(title)
from bookTitle;


 
create table company_events(
event_name varchar2(50),
event_date date)
/

insert into company_events(event_name, event_date)
values('学习Oracle数据库技术', sysdate);
insert into company_events(event_name, event_date)
values('学习SQL Server数据库技术', date '2008-12-28');




create table other_company_events(
event_name varchar2(50),
event_date date,
event_timestamp timestamp)
/
insert into other_company_events
values('创建other_company_events表', sysdate, sysdate);




column event_date format a30
column event_timestamp format a35
select event_date, event_timestamp 
from other_company_events;



create table employee_history(
employee_id number(6) not null,
salary number(8, 2),
hire_date date default sysdate,
termination_date date,
termination_desc varchar2(200),
constraint emphistory_pk primary key(employee_id, hire_date)
);

desc employee_history






create table another_dept
as
select * 
from dept;
select *
from another_dept;
insert into another_dept
values(40, 'OPERATIONS', 'BOSTON');
select *
from another_dept;







drop table another_dept;
create table another_dept
as
select * 
from dept;
alter table another_dept
add constraint another_dept_pk primary key(deptno);
insert into another_dept
values(40, 'OPERATIONS', 'BOSTON');



create table students(
student_id number(3),
student_name varchar2(20),
gender varchar2(6)
);
alter table students
add constraint ck_gender
check(gender in ('female', 'male'));


insert into students
values(100, 'Clinton', 'male');
insert into students
values(101, 'Hillary', 'female');
insert into students
values(102, 'Bush', 'George');




create table emp_copy
as
select *
from emp
where 1=2;
select *
from emp_copy;




create table subjects(
subject_id number not null,
subject_name varchar2(30) not null,
description varchar2(4000)
)
tablespace sysaux
/


alter table subjects
add constraint pk_subjects_subject_id
primary key(subject_id)
/


create table courses(
course_id number not null,
course_name varchar2(60) not null,
subject_id number not null,
duration number(2),
skill_lvl varchar2(12) not null)
tablespace sysaux;





alter table courses
add constraint pk_courses_course_id
primary key(course_id)
/




alter table courses
add constraint fk_courses_subjects_subject_id
foreign key(subject_id)
references subjects(subject_id)
/



alter table courses
add constraint ck_courses_skill_lvl
check ( skill_lvl in ('beginner', 'intermediate', 'advanced'))
/



Robert, Balfe, 010-1234-5678
Richard, Clauser, 010-1234-5677
Mark, Freeman, 010-1234-5676
Brian, Gilbert, 010-1234-5675
Shane, Hogeland, 010-1234-5674
Lance, Horner, 010-1234-5673
David, Jaffe, 010-1234-5672
Carl, Meyers, 010-1234-5671
Robert, Slagel, 010-1234-5670




create directory ext_data_files
as 
'D:\'
/



create table teachers_ext(
first_name varchar2(15),
last_name varchar2(15),
phone_number varchar2(15)
)
organization external(
type oracle_loader
default directory ext_data_files
access parameters(fields terminated by ',')
location('teachers.csv')
)
reject limit unlimited
/









select first_name || ' ' || last_name "Name", phone_number "Phone"
from teachers_ext
order by last_name;









create table states(
state_id varchar2(2),
state_name varchar2(20),
constraint pk_states_state_id
primary key(state_id)
)
organization index;
/











create global temporary table session_table
on commit preserve rows
as
select * 
from emp;
select count(*)
from session_table;









create global temporary table transaction_table
on commit delete rows
as
select *
from emp
where 1=0;
insert into transaction_table
select *
from emp;
select count(*)
from transaction_table;



commit;
select count(*)
from session_table;
select count(*)
from transaction_table;








connect scott/tiger
select default_tablespace
from user_users;




create table foo(
col1 int);
select table_name, tablespace_name
from user_tables
where table_name = 'FOO';








select username, default_tablespace, temporary_tablespace
from dba_users
where default_tablespace = 'SYSTEM'
   or temporary_tablespace = 'SYSTEM';




create table people(
employee_id number(3),
first_name varchar2(15),
last_name varchar2(15),
email varchar2(20),
constraint pk_people_employee_id primary key(employee_id));




insert into people
values(1, 'Clinton', 'Bill', '[e-mail]');
insert into people
values(2, 'Bush', 'Press', '[e-mail]');
insert into people
values(3, 'Putin', 'Miler', '[e-mail]');



select *
from people;



alter table people
add(
  phone_number varchar2(10)
);
select *
from people;



alter table people
add(
  ssn number(15)
);



update people
set ssn = 123456789012345
where employee_id = 1;
update people
set ssn = 234567890123456
where employee_id = 2;
update people
set ssn = 345678901234567
where employee_id = 3;


alter table people
modify(
  ssn number(15) not null
);



desc people
alter table people
drop column email;
desc people



alter table people
rename to employees;
select *
from people;



drop table employees;
select *
from employees;




create table gender_tab(
gender_id char(1),
gender_name varchar2(6),
constraint pk_gender_id primary key(gender_id),
constraint ck_gender_id check(gender_id in ('M', 'F'))
);
insert into gender_tab
values ('F', 'Female');
insert into gender_tab
values ('M', 'Male');








create table people(
name varchar2(15),
gender char(1)
);
alter table people
add constraint fk_people_gender
foreign key(gender) references gender_tab(gender_id);
insert into people
values('Clinton', 'M');
insert into people
values('Hillary', 'F');
insert into people
values('Bush', 'M');




drop table gender_tab;


drop table gender_tab
cascade constraints
/




select table_name, tablespace_name
from user_tables;