--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;