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

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

-- ------------------- Lesson 2.

-- Cau I: (Session -> New Session: SYSTEM)

-- Tạo user và grant quyền

create user may12tunghuynh IDENTIFIED by 12345;

grant CONNECT to may12tunghuynh;

grant RESOURCE to may12tunghuynh;

grant create view to may12tunghuynh;

-- Câu II.

-- Tạo bảng tb_sanpham

create table tb_sanpham(

    id number not null,

    ten_spham VARCHAR2(500),

    so_luong NUMBER,

    ngay_nhap_kho date,

    id_noi_sxuat number,

    id_nvien_nhapkho NUMBER,

    gianhap_1spham float(10),

    giabantoithieu float(10) )

-- Tạo bảng tb_noisxuat

create table tb_noisxuat(

    id number not null,

    noi_sxuat varchar2(500) )

-- Tạo bảng tb_nhanvien

create table tb_nhanvien(

    id number not null,

    ten_nvien varchar2(300),

    ngaysinh date,

    noisinh varchar2(1000),

    gioi varchar2(10),

    ngayvaolam date,

    ngyanghiviec date)

-- Tạo bảng tb_spham_ban   

create table tb_spham_ban(

    id number not null,

    id_spham_ban number,

    id_nvien_ban number,

    soluong number,

    ngayban date,

    giaban float(10) )

-- II.1.   

-- Tạo khóa chính cho 4 bảng trên

alter table tb_spham_ban

add CONSTRAINT pk_sp_ban PRIMARY key (id)

alter table tb_sanpham

add CONSTRAINTS pk_sp primary key (id)

alter table tb_nhanvien

add constraint pk_nvien PRIMARY key (id)

alter table tb_noisxuat

add constraint pk_noisx PRIMARY key (id)

-- alter table tb_noisxuat

-- drop constraint pk_noisx

alter table tb_sanpham

add CONSTRAINT fk_sp_noisx FOREIGN key (id_noi_sxuat)

    REFERENCES tb_noisxuat (id)

alter table tb_sanpham

add CONSTRAINT fk_sp_nv FOREIGN key (id_nvien_nhapkho)

    REFERENCES tb_nhanvien (id)

alter table tb_spham_ban

add constraint fk_spban_sp FOREIGN key (id_spham_ban)

    REFERENCES tb_sanpham (id)

alter table tb_spham_ban

add CONSTRAINT fk_spban_nv FOREIGN key (id_nvien_ban)

    REFERENCES tb_nhanvien (id)

-- II.2.   

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

create SEQUENCE seq_tb_sp

    start with 1

    INCREMENT by 1

    NOCYCLE

    NOORDER

    CACHE 20

    MINVALUE 1

    MAXVALUE 200

create SEQUENCE seq_tb_nv

    start with 1

    INCREMENT by 1

    NOCYCLE

    NOORDER

    CACHE 20

    MINVALUE 1

    MAXVALUE 200

create SEQUENCE seq_tb_sp_ban

    start with 1

    INCREMENT by 1

    NOCYCLE

    NOORDER

    CACHE 20

    MINVALUE 1

    MAXVALUE 200

create SEQUENCE seq_tb_noisx

    start with 1

    INCREMENT by 1

    NOCYCLE

    NOORDER

    CACHE 20

    MINVALUE 1

    MAXVALUE 200

-- II.3.   

-- Insert dữ liệu cho các bảng

insert into tb_noixuat values (seq_tb_noixuat.nextval,'Nhat');

insert into tb_noixuat values (seq_tb_noixuat.nextval,'My');

insert into tb_noixuat values (seq_tb_noixuat.nextval,'Han Quoc');

insert into tb_noixuat values (seq_tb_noixuat.nextval,'Trung Quoc');

--SELECT * from tb_noisxuat

--delete tb_noisxuat

insert into tb_nhanvien VALUES (seq_tb_nv.nextval, 'Nguyen Hai Anh',to_date('12/04/1970','dd//mm/yyyy'),'Nghe An','Nam',to_date('01/10/2000','dd/mm/yyyy'),null);

insert into tb_nhanvien VALUES (seq_tb_nv.nextval, 'Pham Thu Hong',TO_DATE('05/05/1982','dd//mm/yyyy'),'Nam Dinh','Nu',to_date('25/01/2006','dd/mm/yyyy'),to_date('30/12/2010','dd/mm/yyyy'));

insert into tb_nhanvien VALUES (seq_tb_nv.nextval, 'Le Thi Quyen',to_date('23/07/1990','dd//mm/yyyy'),'Phu Tho','Nu',to_date('24/09/2009','dd/mm/yyyy'),null);

insert into tb_nhanvien VALUES (seq_tb_nv.nextval, 'Tran Ba Dung',to_date('19/04/1975','dd//mm/yyyy'),'Thai Nguyen','Nam',to_date('28/06/1997','dd/mm/yyyy'),to_date('23/05/2007','dd/mm/yyyy'));

insert into tb_nhanvien VALUES (seq_tb_nv.nextval, 'Hong Duyen',to_date('22/05/1985','dd//mm/yyyy'),'Ha No','Nu',null,null);

--SELECT * from tb_nhanvien

insert into tb_sanpham values(seq_tb_sp.nextval,'tivi','20',to_date('10/12/2010','dd/mm/yyyy'),1,1,'500','550');

insert into tb_sanpham values(seq_tb_sp.nextval,'tu lanh','20',to_date('10/12/2010','dd/mm/yyyy'),3,2,'600','670');

insert into tb_sanpham values(seq_tb_sp.nextval,'dieu hoa','40',to_date('12/12/2010','dd/mm/yyyy'),'4',3,'200','300');

insert into tb_sanpham values(seq_tb_sp.nextval,'may giat ','50',to_date('08/12/2010','dd/mm/yyyy'),2,4,'600','550');

insert into tb_sanpham values(seq_tb_sp.nextval,'may tinh','60',to_date('08/12/2010','dd/mm/yyyy'),5,5,'800','1200');

--select * from tb_sanpham

insert into tb_spham_ban values(seq_tb_sp_ban.nextval,1,1,'20',to_date('12/12/2010','dd/mm/yyyy'),'550');

insert into tb_spham_ban values(seq_tb_sp_ban.nextval,2,2,'20',to_date('12/12/2010','dd/mm/yyyy'),'600');

insert into tb_spham_ban values(seq_tb_sp_ban.nextval,3,3,'20',to_date('12/12/2010','dd/mm/yyyy'),'300');

insert into tb_spham_ban values(seq_tb_sp_ban.nextval,4,4,'20',to_date('12/12/2010','dd/mm/yyyy'),'550');

insert into tb_spham_ban values(seq_tb_sp_ban.nextval,5,5,'20',to_date('12/12/2010','dd/mm/yyyy'),'1200');

-- II.8.   

-- Tạo một view chứa thông tin sản phẩm, nơi sản xuất, nhân viên nhập kho

create VIEW v_infor_sp

as

select tb_sanpham.*,tb_noisxuat.noi_sxuat,tb_nhanvien.ten_nvien

from tb_sanpham,tb_nhanvien,tb_noisxuat

where tb_noisxuat.id=tb_sanpham.id_noi_sxuat

    and tb_sanpham.id_nvien_nhapkho=tb_nhanvien.id

-- II.9.   

-- Liệt kê thông tin của những nhân viên có từ  Quyen trong bảng nhân vien

select * from tb_nhanvien

where instr(ten_nvien,'Quyen',0)<>0

-- instr(string1,string2,0): trả về vị trí đầu tiên mà tìm thấy string2 trong string1

-- Câu III. Export và Import user trên

-- Import/Export:

-- Start/Run/cmd (Window+R)

-- cd\

-- c:\>exp username/password file=<'path/filename.dmp'> full=yes;

    -- c:\>exp hr/123456 file='d:\tunghuynh.dmp' log='d:\tunghuynh.log' full=yes;

    -- c:\>exp hr/123456 file-'d:\tunghuynhtable.dmp' tables=(employees,jobs);

    -- c:\>imp may12tunghuynh/12345 file='d:\tunghuynh.dmp' log='d:\tunghuynh.log' full=yes;

    -- c:\>imp may12tunghuynh/12345 file-'d:\tunghuynhtable.dmp' tables=(employees);

-- II.10.   

-- Tính tổng số tiền đã nhập hàng

-- II.11.   

-- Tính số tiền trung bình phải nhập mỗi sản phẩm

-- II.12.   

-- Tính tổng số tiền đã bán được hàng

-- II.13.   

-- Hiển thị mặt hàng trong kho, số lượng còn lại

-- II.14.   

-- Tính số tiền lãi

-- II.15.   

-- Tìm nhân viên bán được nhiều hàng nhất

-- II.16.   

-- Danh sách nhân viên bán vi phạm qui định (giá bán dưới mức cho phép)

-- II.17.   

-- Mặt hàng bán chạy nhất, số lượng đã bán

-- II.18.   

-- Mặt hàng có lãi nhất, số tiền lãi

-- III.1.   

-- Export user trên

c:\>exp may12tunghuynh/12345 file='d:\tunghuynh.dmp' log='d:\tunghuynh_export.log' full=yes;

-- III.2.   

-- Drop user trên

drop user may12tunghuynh;

-- III.3.   

-- Create lại user trên

create user may12tunghuynh IDENTIFIED by 12345;

-- III.4.   

-- Import dữ liệu vào

c:\>imp may12tunghuynh/12345 file='d:\tunghuynh.dmp' log='d:\tunghuynh_import.log' full=yes;