-- ------------------- STUDENT: Tùng Huynh

-- ------------------- CLASS:    K7A

-- ------------------- Lesson 1.

-- Đăng nhập và SYS hoặc SYSTEM (Session -> New session)

-- 1.

-- Tạo một user với tên là: Tên máy + Tên Sinh viên. VD: may01Nga

create user may12tunghuynh IDENTIFIED by 123456;

-- 2.   

-- Grant quyền connect cho user trên

grant CONNECT to may12tunghuynh;

-- 3.   

-- Grant quyền create table cho user trên

grant create table to may12tunghuynh;

-- 4.   

-- Grant quyền tạo view cho user tren

grant create view to may12tunghuynh;

-- 5.   

-- Grant quyền tạo sequence

grant create SEQUENCE to may12tunghuynh;

-- 6.   

-- Thay đổi mật khẩu của user trên

alter user may12tunghuynh IDENTIFIED by tunghuynh;

-- 7.   

-- Lock user trên.

alter user may12tunghuynh account lock;

-- Đăng nhập vào user này để kiểm tra user đã bị lock hay chưa

--  Session -> New session

--  Dang nhap theo ten

-- 8.   

-- Unlock user trên

alter user may12tunghuynh account unlock;

-- Grant quyền resource:

grant RESOURCE to may12tunghuynh;

-- 9.

-- Đăng nhập vào user trên

-- Session -> New Session

-- Đăng nhập

-- 10. (Sau khi đã đăng nhập vào user mình tạo)

-- Tạo các bảng có cấu trúc

-- 10.1 Tạo bảng tb_lop:

create table tb_lop(

    id NUMBER,

    tenlop VARCHAR2(100),

    id_gvien_cn NUMBER)

-- 10.2 Tạo bảng tb_sinhvien:

create table tb_sinhvien(

    id NUMBER,

    id_lop NUMBER,

    tensinhvien VARCHAR2(200),

    ngaysinh date,

    noisinh VARCHAR2(500),

    diemthi float)

-- 10.3 Tạo bảng tb_gvien_cn:

create table tb_gvien_cn(

    id NUMBER,

    tengvien VARCHAR2(200) )

-- 11.   

-- Tạo khóa chính trên trường ID cho mỗi bảng trên.

-- Qui tắc đặt tên PK_[Tên bảng]. VD: PK_ TB_LOP

-- Tạo khóa chính cho bảng tb_lop

alter table tb_lop

add constraint pk_tb_lop PRIMARY key (id)

-- Tạo khóa chính cho bảng tb_sinhvien:

alter table tb_sinhvien

add constraint pk_tb_sinhvien primary key (id)

-- Tạo khóa chính cho bảng tb_gvien_cn:

alter table tb_gvien_cn

add constraint pk_tb_gvien_cn PRIMARY key (id)

-- 12.   

-- Tạo khóa ngoại cho bảng TB_LOP trên trường ID_GVIEN_CN tham chiếu đến trường ID của bảng TB_GVIEN_CN.

-- Qui tắc đặt tên FK_[Tên bảng].

alter table tb_lop

add constraint fk_lop_gvien FOREIGN key (id_gvien_cn) references tb_gvien_cn (id)

-- 13.   

-- Tạo ba sequence cho ba bảng trên.

-- Mỗi sequence bắt đầu là 1, mỗi lần tăng 1, giá trị nhỏ nhất là 1.

-- Qui tắc đặt tên: SEQ_[Tên bảng]

-- SEQUENCE: biến tự động tăng giá trị cho trường nào đó

-- (không cần nhập tay), thường là cho trường khóa chính - vd: các trường ID

-- Phải tạo 3 sequence riêng cho từng bảng để giá trị tăng riêng biệt

-- Tạo Sequence cho bảng tb_lop

create SEQUENCE seq_tb_lop

    START with 1            -- Bắt đầu từ 1

    INCREMENT by 1            -- Mỗi lần tăng 1 đơn vị

    MINVALUE 1                -- Giá trị nhỏ nhất

    MAXVALUE 1000            -- Giá trị lớn nhất

    NOCYCLE                    -- Không quay vòng (khi đạt maxvalue)

    NOORDER                    --

    CACHE 20                -- Tạo sẵn 20 vị trí hỗ trợ insert nhanh hơn

-- VD: Đã nhập được 18 dòng thì hệ thống tự động tạo sẵn 20 vị trí tiếp theo -> 38

-- Tạo Sequence cho bảng tb_sinhvien

create SEQUENCE seq_tb_sinhvien

    START with 1

    INCREMENT by 1

    MINVALUE 1

    MAXVALUE 9999999

    NOCYCLE

    NOORDER

    CACHE 20

-- Tạo Sequence cho bảng gvien_cn

create SEQUENCE seq_gvien_cn

    START with 1

    INCREMENT by 1

    MINVALUE 1

    MAXVALUE 1000

    NOCYCLE

    NOORDER

    CACHE 20

-- 14.   

-- Insert dữ liệu vào bảng TB_GVIEN_CN:

INSERT into tb_gvien_cn VALUES (seq_gvien_cn.nextval,'Nguyen Van An');

INSERT into tb_gvien_cn VALUES (seq_gvien_cn.nextval,'Bui Ngoc Nam');

INSERT into tb_gvien_cn VALUES (seq_gvien_cn.nextval,'Le Bich Hong');

INSERT into tb_gvien_cn VALUES (seq_gvien_cn.nextval,'Pham Van Quan');

INSERT into tb_gvien_cn VALUES (seq_gvien_cn.nextval,'Nguoi new');

select * from tb_gvien_cn;

-- 15.   

-- Insert dữ liệu vào bảng TB_LOP

-- Lưu ý: Cột ID sẽ lấy dữ liêu từ seq tương ứng.

-- Cột ID_GVIEN_CN sẽ lấy dữ liệu sao cho

    -- Nguyen Van An là giáo viên chủ nhiệm của lớp CTO706L,

    -- Bui Ngoc Nam là giáo viên chủ nhiệm của lớp CTO707L,

    -- Bui Ngoc Nam là giáo viên chủ nhiệm của lớp CTO708L,

    -- Pham Van Quan là giáo viên chủ nhiệm của lớp CTO709L.

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','3');

INSERT into tb_lop VALUES (seq_tb_lop.nextval,'CTO709L','4');

select * from tb_lop;

-- 16.   

-- Insert dữ liệu vào bảng TB_SINHVIEN

-- Lưu ý: Cốt ID sẽ lấy dữ liệu từ seq tương ứng.

-- Cột ID_LOP sẽ lấy dữ liệu sao cho tất cả sinh viên trong bảng đều thuộc lớp CTO707L.

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','Dao Duy Anh',to_date('25/01/1989','dd/mm/yyyy'),'Nghe An',5);

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

INSERT into tb_sinhvien VALUES (seq_tb_sinhvien.nextval,'2','Le Uyen',to_date('22/06/1988','dd/mm/yyyy'),'Bac Ninh',null);

delete tb_sinhvien;

select * from tb_sinhvien;

-- 17.   

-- In ra danh sách sinh viên có điểm thi đạt, không đạt, chưa thi (>=5 đạt)

print 'Sinh vien chua thi:'

select * from tb_sinhvien

where diemthi is null

print 'Sinh vien khong dat:'

select * from tb_sinhvien

where diemthi<5

print 'Sinh vien dat:'

select * from tb_sinhvien

where diemthi>=5

-- 18.   

-- In ra danh sách giáo viên không chủ nhiệm lớp nào

SELECT * from tb_gvien_cn

where  id not in (select id_gvien_cn from tb_lop)

-- 19.   

-- Tạo một view tên là v_sinhvien có chứa các trường TENSINHVIEN, TENLOP, TENGVIEN_CN

create view v_sinhvien

as

    select tensinhvien,tenlop,tengvien

    from tb_lop, tb_gvien_cn, tb_sinhvien

    where tb_lop.id_gvien_cn=tb_gvien_cn.id

        and tb_sinhvien.id_lop=tb_lop.id

-- 20.

-- Đăng nhập vào user HR

-- Session -> New Session

-- Username: HR

-- Pass: 123456 (default)

    -- Nếu pass sai hoặc không vào được thì đăng nhập vào SYSTEM/123456

    -- Đổi pass HR:

    -- alter user HR IDENTIFIED by 123456;

        -- Nếu HR đang bị khóa (account locked) thì phải mở khóa HR

        -- alter user may11tunghuynh account lock;