--1: tao khoa chinh cho 4 bang

alter table tb_noisxuat

add constraint pk_tb_noisxuat primary key(id);

alter table tb_nhanvien

add constraint pk_tb_nhanvien primary key(id);

alter table tb_sanpham

add constraint pk_tb_sanpham primary key(id);

alter table tb_spham_ban

add constraint pk_tb_spham_ban primary key(id);

--xx

--2:tao 4 sequence

create sequence seq_tb_noisxuat

increment by 1

start with 1

minvalue 1

create sequence seq_tb_nhanvien

increment by 1

start with 1

minvalue 1

create sequence seq_tb_sanpham

increment by 1

start with 1

minvalue 1

create sequence seq_tb_spham_ban

increment by 1

start with 1

minvalue 1

--xx

--insert du lieu

--xx

--7: tao 4 view

create view v_tb_noisxuat

as

select * from tb_noisxuat;

--...

--xx

--8: Tao view chua thong tin sp,noi sx, nhan vien nhap

create view v_tt_spham

as

select s.ten_spham, n.noi_sxuat, nv.ten_nvien

from tb_sanpham s, tb_noisxuat n, tb_nhanvien nv

where s.id_noi_sxuat = n.id

and s.id_nvien_nhapkho = nv.id;

--xx

--9: Liet ke nhan vien co ten quyen

select * from tb_nhanvien

where upper(tb_nhanvien.ten_nvien) like upper('%quyen');

--10: tinh so luong tien da nhap

select sum(tb_sanpham.so_luong*tb_sanpham.gianhap) as tiennhap from tb_sanpham;

--11: So tien trung binh phai nhap moi mat hang

select sum(tb_sanpham.so_luong*tb_sanpham.gianhap)/sum(tb_sanpham.so_luong) as trungbinh from tb_sanpham;

--12: tong so tien ban duoc

select sum(tb_spham_ban.soluong*tb_spham_ban.giaban) as ban from tb_spham_ban;

--13: hien thi mat hang trong kho, so luong con

select tb_sanpham.so_luong - a.tongban as soluongcon, tb_sanpham.ten_spham

from tb_sanpham,

    (   select sum(tb_spham_ban.soluong) as tongban, tb_spham_ban.id_spham_ban as id_spham

        from tb_spham_ban

        group by tb_spham_ban.id_spham_ban

    ) a

where tb_sanpham.id=a.id_spham;

--hien thi mat hang con trong kho

select sum((tb_sanpham.so_luong - tb_spham_ban.soluong)*tb_sanpham.gianhap) as conthua

from tb_sanpham,tb_spham_ban

where tb_sanpham.id=tb_spham_ban.id_spham_ban;

--14: Tinh lai = ban - bo von

select sum( tb_spham_ban.soluong*tb_spham_ban.giaban) - ( sum(tb_sanpham.so_luong*tb_sanpham.gianhap) -sum((tb_sanpham.so_luong - tb_spham_ban.soluong)*tb_sanpham.gianhap))

from tb_sanpham,tb_spham_ban

where tb_sanpham.id=tb_spham_ban.id_spham_ban;

--15: nhan vien ban duoc nhieu hang nhat

select * from(

    select sum(tb_spham_ban.soluong) as tongso,

            (

                select tb_nhanvien.ten_nvien 

                from tb_nhanvien 

                where tb_nhanvien.id=tb_spham_ban.id_nvien_ban

            ) as nhanvien

    from tb_spham_ban

    group by tb_spham_ban.id_nvien_ban

    order by tongso desc

)

where rownum =1;

--xx

--16: Danh sach nhan vien vi pham

select  tb_nhanvien.ten_nvien,tb_sanpham.ten_spham,tb_spham_ban.giaban,

        to_char(tb_spham_ban.ngayban,'dd/mm/yyyy') as ngayban

from    tb_nhanvien,tb_sanpham,tb_spham_ban

where   tb_nhanvien.id=tb_spham_ban.id_nvien_ban

and     tb_spham_ban.id_spham_ban=tb_sanpham.id

and     tb_spham_ban.giaban < tb_sanpham.giabantoithieu;

--xx

--17: Mat hang ban chay nhat, so luong da ban

select * from(

    select a.soluongban, tb_sanpham.ten_spham

    from tb_sanpham,

        (

        select sum(soluong) as soluongban, tb_spham_ban.id_spham_ban as id_spham

        from tb_spham_ban

        group by tb_spham_ban.id_spham_ban

        order by sum(soluong) desc

        )   a

    where tb_sanpham.id=a.id_spham

)

where rownum =1;

--18: liet ke mat hang lai nhat

select * from(

        select sum (tong) as sotienlai, tenspham from(

                select tb_spham_ban.soluong*(tb_spham_ban.giaban-tb_sanpham.gianhap) as tong, 

                tb_sanpham.ten_spham as tenspham

                from tb_sanpham,tb_spham_ban

                where tb_sanpham.id=tb_spham_ban.id_spham_ban

    )

    group by tenspham

    order by sum (tong) desc

)

where rownum =1;

--xx