Bài thực hành Oracle (4 tuần) Bài 1 - Tùng Huynh
--create user
create user may01Nga identified by 123456;
--grant quyen connect cho user may01Nga
grant connect to may01Nga;
--grant quyen resource
grant resource to may01Nga;
--grant quyen create table cho user tren
grant create table to may01Nga;
--grant quyen create view cho user
grant create view to may01Nga;
--grant quyen tao sequence
grant create sequence to may01nga;
--thay doi mat khau cua user
alter user may01Nga identified by 654321;
--lock user
alter user may01Nga account lock;
--un lock user
alter user may01Nga account unlock;
--tao bang tb_lop
create table tb_lop(
id number not null,
tenlop varchar2(100 byte),
id_gvien_cn number
--tao bang tb_sinhvien
create table tb_sinhvien(
id number not null,
id_lop number,
tensinhvien varchar2(200 byte),
ngaysinh date,
noisinh varchar2(500 byte),
diemthi float(126)
--tao bang tb_gvien_cn
create table tb_gvien_cn(
id number not null,
tengvien varchar2(200 byte)
--tao khoa chinh cho bang tb_lop
alter table tb_lop
add constraint pk_tb_lop primary key (id)
--tao khoa chinh cho bang tb_gvien_cn
alter table tb_gvien_cn
add constraint pk_tb_gvien_cn primary key (id)
--tao khoa chinh cho bang tb_sinhvien
alter table tb_sinhvien
add constraint pk_tb_sinhvien primary key (id)
--tao khoa ngoai cho bang tb_lop
alter table tb_lop
add constraint fk_tb_lop foreign key (id_gvien_cn)
references tb_gvien_cn (id)
--Tao sequence seq_tb_lop
create sequence seq_tb_lop
increment by 1
start with 1
minvalue 1
maxvalue 999999999999999999999999999
nocycle
noorder
cache 20
--Tao sequence seq_tb_gvien_cn
create sequence seq_tb_gvien_cn
increment by 1
start with 1
minvalue 1
maxvalue 999999999999999999999999999
nocycle
noorder
cache 20
--Tao sequence seq_tb_sinhvien
create sequence seq_tb_sinhvien
increment by 1
start with 1
minvalue 1
maxvalue 999999999999999999999999999
nocycle
noorder
cache 20
--insert du lieu vao bang tb_gvien_cn
insert into tb_gvien_cn values(seq_tb_gvien_cn.nextval,'Nguyen Van An');
insert into tb_gvien_cn values(seq_tb_gvien_cn.nextval,'Bui Ngoc Nam');
insert into tb_gvien_cn values(seq_tb_gvien_cn.nextval,'Le Bich Hong');
insert into tb_gvien_cn values(seq_tb_gvien_cn.nextval,'Pham Van Quan');
--select * from tb_gvien_cn de lay du lieu
--insert du lieu vao bang tb_gvien_cn
insert into TB_LOP values(seq_TB_LOP.nextval,'CTO706L',1);
insert into TB_LOP values(seq_TB_LOP.nextval,'CTO707L',2);
insert into TB_LOP values(seq_TB_LOP.nextval,'CTO708L',2);
insert into TB_LOP values(seq_TB_LOP.nextval,'CTO709L',4);
--insert du lieu vao bang
--select bang TB_LOP thay CTO707L co ID = 2
insert into TB_SINHVIEN values(seq_TB_SINHVIEN.nextval,2,'Le Hong Anh',to_date('11/03/1988','dd/mm/yyyy'),'Thai Nguyen',7.5);
insert into TB_SINHVIEN values(seq_TB_SINHVIEN.nextval,2,'Nguyen Manh Hai',to_date('03/04/1988','dd/mm/yyyy'),'Phu Tho',9);
insert into TB_SINHVIEN values(seq_TB_SINHVIEN.nextval,2,'Hoang Hai',to_date('17/09/1988','dd/mm/yyyy'),'Ha noi',null);
--liet ke sinh vien co diem dat
select * from tb_sinhvien where DIEMTHI >=5;
--khong dat
select * from tb_sinhvien where DIEMTHI <5;
--chua thi
select * from tb_sinhvien where DIEMTHI is null;
--giao vien khong chu nhiem lop nao
select * from tb_gvien_cn where id not in (select ID_GVIEN_CN from tb_lop);
--tao view
create view v_sinhvien
as
select s.tensinhvien,m.tenlop,n.tengvien
from tb_sinhvien s, tb_lop m, tb_gvien_cn n
where s.id_lop=m.id
and m.id_gvien_cn=n.id;
--dem so ban ghi co duoc tu view tren
select count(*) from v_sinhvien
--grant quyen tao role cho hr
grant create role to hr;
--dang nhap vao user hr
--tao role
create role role_emp;
--grant quyen cho role
grant select,insert,update on employees to role_emp;
--grant role cho user may01Nga
grant role_emp to may01Nga;
--dang nhap lai user may01Nga
--hien thi du lieu cua bang employees trong hr
select * from hr.employees;
--tao bang employees2
create table employees2
as select * from hr.employees;
Bạn đang đọc truyện trên: AzTruyen.Top