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