create user may01Nga identified by 123456;

grant connect to may01Nga;

grant resource to may01Nga;

grant create table to may01Nga;

grant create view to may01Nga;

grant create sequence to may01nga;

alter user may01Nga identified by 654321;

--lock user

alter user may01Nga account lock;

--un lock user

alter user may01Nga account unlock;

--tao bang tb_gvien_cn

create table tb_gvien_cn(

    id                             number not null,

    tengvien                       varchar2(200 byte)

)

Cau 3: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_lop

create table tb_lop(

    id                             number not null,

    tenlop                         varchar2(100 byte),

    id_gvien_cn                    number

)

--Cau 4: tao khoa chinh cho bang tb_sinhvien

alter table tb_sinhvien

add constraint pk_tb_sinhvien primary key (id)

--tao khoa chinh cho bang tb_lop

alter table tb_lop

add constraint pk_tb_lop primary key (id)

--Cau5: T?o khóa ngo?i trên trý?ng ID_LOP c?a b?ng TB_SINHVIEN tham chi?u ð?n trý?ng ID c?a b?ng TB_LOP

alter table tb_sinhvien

add constraint fk_tb_sinhvien foreign key (id_lop)

references tb_lop (id)

--Cau 6: Tao sequence seq_tb_sinhvien

create sequence seq_tb_sinhvien

  increment by 1

  start with 1

  minvalue 1

  maxvalue 999999999999999999999999999

  nocycle

  noorder

  cache 20

--Cau 6:Tao sequence seq_tb_lop

create sequence seq_tb_lop

  increment by 1

  start with 1

  minvalue 1

  maxvalue 999999999999999999999999999

  nocycle

  noorder

  cache 20

--Cau 7:insert du lieu vao bang tb_lop

insert into tb_lop values (seq_tb_lop.nextval,'CTO706L',1); 

insert into tb_lop values (seq_tb_lop.nextval,'CTO707L',2);

--Cau 8:insert du lieu vao bang tb_sinhvien

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,'Vuong Viet An',to_date('06/08/1987','dd/mm/yyyy'),'Tuyen Quang',4);

insert into TB_SINHVIEN values(seq_TB_SINHVIEN.nextval,2,'Hoang Hai',to_date('17/09/1988','dd/mm/yyyy'),'Ha noi',null);

--cau 9: Hien thi danh sach sinh vien dat du lieu sap xep theo chieu giam dan

select * from tb_sinhvien where diemthi >=5

order by diemthi desc

--Câu 10. Hien thi sinh vien co diem cao nhat, thuoc lop nao

select * from (select tb_sinhvien.*, tb_lop.tenlop from tb_sinhvien, tb_lop

where diemthi >=5

order by diemthi desc)

where rownum =1

--Câu 11. Hien thi ten lop ko co sinh vien

select * from tb_lop where id not in (select id_lop from tb_sinhvien);

--Câu 12. Tính diem trung binh cua sinh vien

select trunc ( avg(diemthi),3) as "Diem Trung Binh"

from tb_sinhvien

--Câu 13. Export user

  exp userid='"sys/123456 as sysdba"' file=exp. dmp grants=y log=exp.log owner=(thainguyen2011)

--giao vien khong chu nhiem lop nao

select * from tb_gvien_cn where id not in (select ID_GVIEN_CN from tb_lop);

--dem so ban ghi co duoc tu view tren

select * from v_sinhvien

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;