Bấm để cập nhật phim mới !
- » Nhấn thích hoặc +1 nếu phim hay bạn nhé!
- »Có đang xem phim này.
- » Các bạn xem phim nên ấn nút tạm dừng khoảng 30s - 1 phút rồi tiếp tục xem phim để không bị giật.
- » Nếu bạn không xem được phim vui lòng nhấn Ctrl + F5 vài lần. Hoặc chuyển sang Server khác để xem.
- » Xem phim nhanh hơn với trình duyệt Firefox và Google Chrome , Cờ Rôm +
BÀI TẬP LỚN SQL NHÓM 7
Thành viên: Trần Thị Thùy Linh
Lê Thị Lan
Nguyễn Văn Thái
Nguyễn Hữu Hoàn
ĐỀ TÀI: Quản lý bán hàng_tạp hóa
HANG(MaH,TenH,DVT,NoiSX,SoLuong)
Khachhang(MaKH,TenKH,Diachi,SÐT)
Hoadon(SoHD,MaKH,NgayLapHD)
ChitietHD(SoHD,MaH,SoLuong,DonGia)
PhieuNhapKho(SoPN, SoHD, MaKH)
ChiTietPhieuNhap(SoPN, MaH, SoLuong,DonGia).
/* TaO CO So Du LIeU */
CREATE DATABASE QLBH_TAPHOA
USE QLBH_TAPHOA
/* TaO BaNG HÀNG */
CREATE TABLE HANG
(
MaH Char(10) PRIMARY KEY,
TenH Varchar(100) NOT NULL,
DVT Char(10) NOT NULL,
NoiSX Varchar(20) NULL,
SoLuong Int NULL,
)
/* TaO BaNG KHÁCH HÀNG */
CREATE TABLE KHACHHANG
(
MaKH Char(10) PRIMARY KEY,
TenKH Varchar(50) NOT NULL,
DiaChi Varchar(20) NULL,
SDT Char(15) NULL,
)
/* TAO BANG HÓA ÐON*/
CREATE TABLE HOADON
(
SoHD Char(10) PRIMARY kEY,
NgayLapHD DateTime NOT NULL,
MaKH Char(10),
CONSTRAINT MaKH_FK FOREIGN KEY (MaKH) REFERENCES KHACHHANG (MaKH),
)
/* TAO BANG CHI TIET HÓA ÐON */
CREATE TABLE CHITIETHOADON
(
SoHD Char(10),
MaH Char(10),
SoLuong int NOT NULL,
DonGia Money NOT NULL,
CONSTRAINT MaH_SoHD_PK PRIMARY KEY (MaH, SoHD),
CONSTRAINT MaH_FK FOREIGN KEY (MaH) REFERENCES HANG (MaH),
CONSTRAINT SoHD_FK FOREIGN KEY (SoHD) REFERENCES HOADON (SoHD)
)
/*TẠO BANG PHIEU NHAP KHO*/
CREATE TABLE PHIEUNHAPKHO
(
SoPN CHAR(10) PRIMARY KEY,
SoHD CHAR(10),
MaKH CHAR(10)
)
/*TAO BANG CHI TIET PHIEU NHAP*/
CREATE TABLE CHITIETPHIEUNHAP
(
SoPN CHAR(10) PRIMARY KEY
MaH CHAR(10),
SoLuong INT NOT NULL,
DonGia MONEY NOT NULL
)
/* NHAP DU LIEU CHO CÁC BANG */
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH01','But Bi','Cai','HA NOI','300')
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH02','But chi','Cai','Thai Nguyen','300')
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH03','Thuoc Ke','Cai','Hoa Binh','300')
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH04','Com Pa','Cai','Hai Phong','550')
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH05','Vo Viet','Quyen','Cao Bang','700')
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH06','BIA','CHAI','HA NOI','100')
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH07','RUOU','CHAI','HA NOI','100')
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH08','BIM BIN','GOI','HA NOI','500')
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH09','BANH','GOI','Ha NAM','500')
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH10','KEO','GOI','HA NOI','500')
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH11','KEM DANH RANG','HOP','BAC GIANG','100')
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH12','SUA TAM','CHAI','HA NOI','100')
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH13','DAU GOI DAU','CHAI','HA NOI','120')
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH14','NUOC NGOT','CHAI','HA NOI','200')
/*NHAP DU LIEU BANG KHACHHANG*/
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK01','Nguyen Bao An','HA NOI','0989009876')
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK02','Hoang Thu Thuy','Ha Tinh','0978409876')
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK03','Tran Thi Trang','Nghe An','0967679854')
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK04','Vu Hong Quan','Thai Nguyen','0989076454')
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK05','Ngo Van Tung','Hai Phong','0989346578')
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK06','Ngo Van AN','HA NOI','0989456578')
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK07','NgUYEN VAN MINH','Hai Phong','0999346578')
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK08','LE THI LAN','THANH HOA','0989896578')
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK09','NGHUYEN VAN THAI','HUNG YEN','098938578')
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK10','LE THI LAN','HA NOI','0912396578')
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK11','NGHUYEN HUU HOAN','THAI BINH','0989806578')
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK12','CAO BA QUAT','THANH HOA','0981236578')
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK13','ÐINH TRONG CAO','HA NOI','0989853578')
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK14','TR?N TH? THÙY','NINH BINH','098924578')
/*NHAP DU LIEU BANG HOA DON*/
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD01','09/23/2016','MaK01')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD02','01/19/2016','MaK01')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD03','05/01/2016','MaK03')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD04','04/01/2016','MaK04')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD05','04/27/2016','MaK04')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD06','04/22/2016','MaK04')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD07','05/03/2016','MaK06')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD07','06/22/2016','MaK10')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD08','09/12/2016','MaK14')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD09','10/22/2016','MaK09')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD10','10/12/2016','MaK08')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD11','04/06/2016','MaK02')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD12','10/03/2016','MaK02')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD13','09/06/2016','MaK10')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD14','10/09/2016','MaK02')
/*NHAP DU LIEU BANG CHI TIET HOA DON*/
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD01','MaH01','50','3000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD02','MaH02','20','2500')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD03','MaH03','15','5000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD04','MaH04','10','4000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD05','MaH06','20','6000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD06','MaH07','30','10000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD07','MaH08','10','20000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD08','MaH09','5','15000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD09','MaH10','20','30000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD10','MaH11','2','16000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD11','MaH12','7','60000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD12','MaH13','10','160000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD13','MaH05','20','60000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD14','MaH14','23','50000')
/*NHAP BANG PHIEU NHAP KHO*/
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN01','HD01','MaK01')
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN02','HD02','MaK02')
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN03','HD03','MaK03')
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN04','HD04','MaK04')
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN05','HD05','MaK05')
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN06','HD06','MaK06')
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN07','HD07','MaK07')
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN08','HD08','MaK08')
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN09','HD09','MaK09')
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN10','HD10','MaK10')
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN011','HD11','MaK11')
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN12','HD012','MaK12')
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN13','HD13','MaK13')
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN14','HD14','MaK14')
/*NHAP BANG CHI TIET PHIEU NHAP*/
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN01','MaH01','50','3000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN02','MaH02','20','2500')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN03','MaH03','15','5000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN04','MaH04','10','4000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN05','MaH06','20','6000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN06','MaH07','30','10000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN07','MaH08','10','20000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN08','MaH09','5','15000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN09','MaH10','20','30000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN10','MaH11','2','16000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN11','MaH12','7','60000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN12','MaH13','10','160000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN13','MaH05','20','60000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN14','MaH14','23','50000')
/* ToNG HoP THÔNG TIN Ve Các Khách Hàng Ðã Mua Hàng TaI CuA Hàng */
CREATE VIEW VWKHACHMUAHANG
AS
SELECT *
FROM KHACHHANG
WHERE MaKH IN ( SELECT MaKH
FROM HOADON
)
/* ToNG HoP Thông Tin Ve Khách Hàng Có ÐiA ChI HA NOI Và TuNG Mua Hàng TaI CuA Hàng */
CREATE VIEW VWDIACHIKHACHHANG
AS
SELECT *
FROM KHACHHANG
WHERE MaKH IN ( SELECT MaKH
FROM HOADON
)
AND DiaChi='HA NOI'
/* Thông Tin Ve Các Mat Hàng Ðuoc San Xuat Tai Thái Nguyên HOaC HA NOI */
CREATE VIEW VWNOISX
AS
SELECT *
FROM HANG
WHERE NoiSX='Thai Nguyen' OR NoiSX='HA NOI'
/* Thông Tin Ve Các Khách Hàng Ðã Tung Mua Các Mat Hàng ÐUoC San Xuat Tai HA NOI */
CREATE VIEW VWKHACHMUAHANG
AS
SELECT *
FROM KHACHHANG
WHERE MaK IN ( SELECT MaKH
FROM HANG, HOADON, CHITIETHOADON
WHERE HOADON.SoHD=CHITIETHOADON.SoHD
AND HANG.MaH=CHITIETHOADON.MaH
AND NoiSX='HA NOI'
)
/* Thông Tin Ve Các Mat Hàng Ðã ÐUoc Bán Trong Ngày Mùng 01/04/2016 */
CREATE VIEW VWHANGDUOCBAN
AS
SELECT *
FROM HANG
WHERE MaH IN ( SELECT MaH
FROM CHITIETHOADON
WHERE SoHD IN ( SELECT SoHD
FROM HOADON
WHERE DAY(NgayLapHD)='01'
AND MONTH(NgayLapHD)='04'
AND YEAR(NgayLapHD)='2016'
)
)
/* Thông Tin Ve Các Khách Hàng Ðã Mua Hàng Trong Ngày 03/10/2016 */
CREATE VIEW VWTTKHACHHANG
AS
SELECT *
FROM KHACHHANG
WHERE MaK IN ( SELECT MaKH
FROM HOADON
WHERE DAY(NgayLapHD)='03'
AND MONTH(NgayLapHD)='10'
AND YEAR(NgayLapHD)='2016'
)
/* Thông Tin Ve Các Mat Hàng Không Bán ÐUoc Trong Tháng 2 Nam 2016 */
CREATE VIEW VWHANGCHUABANDUOC
AS
SELECT *
FROM HANG
WHERE MaH NOT IN ( SELECT MaH
FROM CHITIETHOADON
WHERE SoHD IN (SELECT SoHD
FROM HOADON
WHERE MONTH(NgayLapHD)='02'
AND YEAR(NgayLapHD)='2016'
)
)
/*dua ra danh sach hoa don giua cac ngay trong thang*/
select * from Hoadon
where NgayLapHD between '05/03/2016' and '09/23/2016'
/* danh sach mat hang ban voi so luong >30*/
select * from Hang
where MaH in (select MaH
from ChiTietHD
where SoLuong > 30)
/*danh sach mat hang ban chay nhat*/
select * from Hang
where MaH in (select MaH
from ChiTietHD
where SoLuong in (select max SoLuong
from ChiTietHD) )
/*danh sach mat hang ban kem chay nhat*/
select * from Hang
where MaH in (select MaH
from ChiTietHD
where SoLuong in (select min SoLuong
from ChiTietHD) )
/*khach da mua bao nhiêu hoa don mat hang Ma01*/
select MaH,SoHD=count(MaH)
from Hoadon
where MaH='Ma01'
group by MaH
/*khach da mua bao nhiêu hoa don mat hang Ma02*/
select MaH,SoHD=count(MaH)
from Hoadon
where MaH='Ma02'
group by MaH
/*khach da mua bao nhiêu hoa don mat hang Ma03*/
select MaH,SoHD=count(MaH)
from Hoadon
where MaH='Ma03'
group by MaH
/*khach da mua bao nhiêu hoa don mat hang Ma04*/
select MaH,SoHD=count(MaH)
from Hoadon
where MaH='Ma04'
group by MaH
/*khach da mua bao nhiêu hoa don mat hang Ma11*/
select MaH,SoHD=count(MaH)
from Hoadon
where MaH='Ma11'
group by MaH
/*khach da mua bao nhiêu hoa don mat hang Ma08*/
select MaH,SoHD=count(MaH)
from Hoadon
where MaH='Ma08'
group by MaH
/*khach da mua bao nhiêu hoa don mat hang Ma14*/
select MaH,SoHD=count(MaH)
from Hoadon
where MaH='Ma14'
group by MaH
/*thay doi ten mat hang có MaH=02 thanh 'giay' cua b?ng hang dã có*/
update Hang
set TenH='giay'
where MaH='MaH02'
/*thong ke tong so luong theo ma hang*/
select MaH,tongsoluong=sum(SoLuong)
from Hang
group by MaH
/*dua ra nhung hoa don co tu ngay 1/4*/
select SoHD
from Hoadon
where NgayLapHD='04/01/2016'
/*dua ra danh sach mat hang co don vi tinh la 'chai'*/
select MaH,TenH,SoLuong
from Hang
where DVT='chai'
/*sua doi don gia cua tat ca mat hang lên 10% */
select SoHD,MaH,dongia=DonGia+ÐonGia/10
from ChiTietHD
/*dua ra khach hang co ngay thanh lap hoa don gan nhat*/
select Hoadon.SoHD,Hoadon.MaKH,Khachhang.TenKH
from Hoadon inner join Khachhang on Khachhang.MaKH=Hoadon.MaKH
where NgayLapHD in (select max(NgayLapHD)
from Hoadon
where getdate()>NgayLapHD)
/*DUA RA THONG TIN KHACH HANG VIP*/
select Hoadon.SoHD,Hoadon.MaKH,Khachhang.TenKH
from Hoadon inner join Khachhang on Khachhang.MaKH=Hoadon.MaKH
inner join ChiTietHD on Hoadon.SoHD=ChiTietHD.SoHD
where DonGia in (select max(DonGia)
from ChiTietHD
group by Hoadon.MaKH,Khachhang.TenKH)
/*DUA RA DANH SACH HANG CO DVT GIONG DVT CUA MAT HANG 04*/
select *
from Hang
where DVT in (select MaH,DVT
from Hang
where MaH='MaH04'
/*DUA RA DANH SACH HANG CO DVT GIONG DVT CUA MAT HANG 12*/
select *
from Hang
where DVT in (select MaH,DVT
from Hang
where MaH='MaH12'
/*Tao trigger cho phep nguoi dung nhap ten mat hang theo ma tu 1-15*/
creat trigger tr_kiemsoatNhap
on MaH
for insert,update
if(insert.MaH>1 or insert.MaH<15)
or(update.MaH>1 or update.MaH<15)
begin
RAISERORR("phai nhap du lieu trong khoang [1-15]")
roll back tran
end
/*thu tuc ham*/
creat procedured h_insertHang(@MaH as char(10),@TenH as char(10))
as
insert into Hang(MaH,TenH)
value(@MaH,@TenH)
end;
execute h_insertHang 'MaH15','quat'
Thành viên: Trần Thị Thùy Linh
Lê Thị Lan
Nguyễn Văn Thái
Nguyễn Hữu Hoàn
ĐỀ TÀI: Quản lý bán hàng_tạp hóa
HANG(MaH,TenH,DVT,NoiSX,SoLuong)
Khachhang(MaKH,TenKH,Diachi,SÐT)
Hoadon(SoHD,MaKH,NgayLapHD)
ChitietHD(SoHD,MaH,SoLuong,DonGia)
PhieuNhapKho(SoPN, SoHD, MaKH)
ChiTietPhieuNhap(SoPN, MaH, SoLuong,DonGia).
/* TaO CO So Du LIeU */
CREATE DATABASE QLBH_TAPHOA
USE QLBH_TAPHOA
/* TaO BaNG HÀNG */
CREATE TABLE HANG
(
MaH Char(10) PRIMARY KEY,
TenH Varchar(100) NOT NULL,
DVT Char(10) NOT NULL,
NoiSX Varchar(20) NULL,
SoLuong Int NULL,
)
/* TaO BaNG KHÁCH HÀNG */
CREATE TABLE KHACHHANG
(
MaKH Char(10) PRIMARY KEY,
TenKH Varchar(50) NOT NULL,
DiaChi Varchar(20) NULL,
SDT Char(15) NULL,
)
/* TAO BANG HÓA ÐON*/
CREATE TABLE HOADON
(
SoHD Char(10) PRIMARY kEY,
NgayLapHD DateTime NOT NULL,
MaKH Char(10),
CONSTRAINT MaKH_FK FOREIGN KEY (MaKH) REFERENCES KHACHHANG (MaKH),
)
/* TAO BANG CHI TIET HÓA ÐON */
CREATE TABLE CHITIETHOADON
(
SoHD Char(10),
MaH Char(10),
SoLuong int NOT NULL,
DonGia Money NOT NULL,
CONSTRAINT MaH_SoHD_PK PRIMARY KEY (MaH, SoHD),
CONSTRAINT MaH_FK FOREIGN KEY (MaH) REFERENCES HANG (MaH),
CONSTRAINT SoHD_FK FOREIGN KEY (SoHD) REFERENCES HOADON (SoHD)
)
/*TẠO BANG PHIEU NHAP KHO*/
CREATE TABLE PHIEUNHAPKHO
(
SoPN CHAR(10) PRIMARY KEY,
SoHD CHAR(10),
MaKH CHAR(10)
)
/*TAO BANG CHI TIET PHIEU NHAP*/
CREATE TABLE CHITIETPHIEUNHAP
(
SoPN CHAR(10) PRIMARY KEY
MaH CHAR(10),
SoLuong INT NOT NULL,
DonGia MONEY NOT NULL
)
/* NHAP DU LIEU CHO CÁC BANG */
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH01','But Bi','Cai','HA NOI','300')
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH02','But chi','Cai','Thai Nguyen','300')
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH03','Thuoc Ke','Cai','Hoa Binh','300')
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH04','Com Pa','Cai','Hai Phong','550')
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH05','Vo Viet','Quyen','Cao Bang','700')
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH06','BIA','CHAI','HA NOI','100')
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH07','RUOU','CHAI','HA NOI','100')
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH08','BIM BIN','GOI','HA NOI','500')
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH09','BANH','GOI','Ha NAM','500')
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH10','KEO','GOI','HA NOI','500')
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH11','KEM DANH RANG','HOP','BAC GIANG','100')
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH12','SUA TAM','CHAI','HA NOI','100')
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH13','DAU GOI DAU','CHAI','HA NOI','120')
INSERT INTO HANG(MaH,TenH,DVT,NoiSX,SoLuong)
VALUES('MaH14','NUOC NGOT','CHAI','HA NOI','200')
/*NHAP DU LIEU BANG KHACHHANG*/
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK01','Nguyen Bao An','HA NOI','0989009876')
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK02','Hoang Thu Thuy','Ha Tinh','0978409876')
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK03','Tran Thi Trang','Nghe An','0967679854')
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK04','Vu Hong Quan','Thai Nguyen','0989076454')
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK05','Ngo Van Tung','Hai Phong','0989346578')
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK06','Ngo Van AN','HA NOI','0989456578')
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK07','NgUYEN VAN MINH','Hai Phong','0999346578')
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK08','LE THI LAN','THANH HOA','0989896578')
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK09','NGHUYEN VAN THAI','HUNG YEN','098938578')
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK10','LE THI LAN','HA NOI','0912396578')
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK11','NGHUYEN HUU HOAN','THAI BINH','0989806578')
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK12','CAO BA QUAT','THANH HOA','0981236578')
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK13','ÐINH TRONG CAO','HA NOI','0989853578')
INSERT INTO KHACHHANG(MaKH,TenKH,Diachi,SÐT)
VALUES('MaK14','TR?N TH? THÙY','NINH BINH','098924578')
/*NHAP DU LIEU BANG HOA DON*/
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD01','09/23/2016','MaK01')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD02','01/19/2016','MaK01')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD03','05/01/2016','MaK03')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD04','04/01/2016','MaK04')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD05','04/27/2016','MaK04')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD06','04/22/2016','MaK04')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD07','05/03/2016','MaK06')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD07','06/22/2016','MaK10')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD08','09/12/2016','MaK14')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD09','10/22/2016','MaK09')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD10','10/12/2016','MaK08')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD11','04/06/2016','MaK02')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD12','10/03/2016','MaK02')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD13','09/06/2016','MaK10')
INSERT INTO HOADON(SoHD,NgayLapHD,MaKH)
VALUES('HD14','10/09/2016','MaK02')
/*NHAP DU LIEU BANG CHI TIET HOA DON*/
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD01','MaH01','50','3000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD02','MaH02','20','2500')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD03','MaH03','15','5000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD04','MaH04','10','4000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD05','MaH06','20','6000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD06','MaH07','30','10000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD07','MaH08','10','20000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD08','MaH09','5','15000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD09','MaH10','20','30000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD10','MaH11','2','16000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD11','MaH12','7','60000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD12','MaH13','10','160000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD13','MaH05','20','60000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('HD14','MaH14','23','50000')
/*NHAP BANG PHIEU NHAP KHO*/
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN01','HD01','MaK01')
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN02','HD02','MaK02')
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN03','HD03','MaK03')
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN04','HD04','MaK04')
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN05','HD05','MaK05')
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN06','HD06','MaK06')
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN07','HD07','MaK07')
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN08','HD08','MaK08')
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN09','HD09','MaK09')
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN10','HD10','MaK10')
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN011','HD11','MaK11')
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN12','HD012','MaK12')
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN13','HD13','MaK13')
INSERT INTO PHIEUNHAPKHO(SoPN,SoHD,MaKH)
VALUES('PN14','HD14','MaK14')
/*NHAP BANG CHI TIET PHIEU NHAP*/
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN01','MaH01','50','3000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN02','MaH02','20','2500')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN03','MaH03','15','5000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN04','MaH04','10','4000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN05','MaH06','20','6000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN06','MaH07','30','10000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN07','MaH08','10','20000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN08','MaH09','5','15000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN09','MaH10','20','30000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN10','MaH11','2','16000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN11','MaH12','7','60000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN12','MaH13','10','160000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN13','MaH05','20','60000')
INSERT INTO CHITIETHOADON(SoHD,MaH,SoLuong,DonGia)
VALUES('PN14','MaH14','23','50000')
/* ToNG HoP THÔNG TIN Ve Các Khách Hàng Ðã Mua Hàng TaI CuA Hàng */
CREATE VIEW VWKHACHMUAHANG
AS
SELECT *
FROM KHACHHANG
WHERE MaKH IN ( SELECT MaKH
FROM HOADON
)
/* ToNG HoP Thông Tin Ve Khách Hàng Có ÐiA ChI HA NOI Và TuNG Mua Hàng TaI CuA Hàng */
CREATE VIEW VWDIACHIKHACHHANG
AS
SELECT *
FROM KHACHHANG
WHERE MaKH IN ( SELECT MaKH
FROM HOADON
)
AND DiaChi='HA NOI'
/* Thông Tin Ve Các Mat Hàng Ðuoc San Xuat Tai Thái Nguyên HOaC HA NOI */
CREATE VIEW VWNOISX
AS
SELECT *
FROM HANG
WHERE NoiSX='Thai Nguyen' OR NoiSX='HA NOI'
/* Thông Tin Ve Các Khách Hàng Ðã Tung Mua Các Mat Hàng ÐUoC San Xuat Tai HA NOI */
CREATE VIEW VWKHACHMUAHANG
AS
SELECT *
FROM KHACHHANG
WHERE MaK IN ( SELECT MaKH
FROM HANG, HOADON, CHITIETHOADON
WHERE HOADON.SoHD=CHITIETHOADON.SoHD
AND HANG.MaH=CHITIETHOADON.MaH
AND NoiSX='HA NOI'
)
/* Thông Tin Ve Các Mat Hàng Ðã ÐUoc Bán Trong Ngày Mùng 01/04/2016 */
CREATE VIEW VWHANGDUOCBAN
AS
SELECT *
FROM HANG
WHERE MaH IN ( SELECT MaH
FROM CHITIETHOADON
WHERE SoHD IN ( SELECT SoHD
FROM HOADON
WHERE DAY(NgayLapHD)='01'
AND MONTH(NgayLapHD)='04'
AND YEAR(NgayLapHD)='2016'
)
)
/* Thông Tin Ve Các Khách Hàng Ðã Mua Hàng Trong Ngày 03/10/2016 */
CREATE VIEW VWTTKHACHHANG
AS
SELECT *
FROM KHACHHANG
WHERE MaK IN ( SELECT MaKH
FROM HOADON
WHERE DAY(NgayLapHD)='03'
AND MONTH(NgayLapHD)='10'
AND YEAR(NgayLapHD)='2016'
)
/* Thông Tin Ve Các Mat Hàng Không Bán ÐUoc Trong Tháng 2 Nam 2016 */
CREATE VIEW VWHANGCHUABANDUOC
AS
SELECT *
FROM HANG
WHERE MaH NOT IN ( SELECT MaH
FROM CHITIETHOADON
WHERE SoHD IN (SELECT SoHD
FROM HOADON
WHERE MONTH(NgayLapHD)='02'
AND YEAR(NgayLapHD)='2016'
)
)
/*dua ra danh sach hoa don giua cac ngay trong thang*/
select * from Hoadon
where NgayLapHD between '05/03/2016' and '09/23/2016'
/* danh sach mat hang ban voi so luong >30*/
select * from Hang
where MaH in (select MaH
from ChiTietHD
where SoLuong > 30)
/*danh sach mat hang ban chay nhat*/
select * from Hang
where MaH in (select MaH
from ChiTietHD
where SoLuong in (select max SoLuong
from ChiTietHD) )
/*danh sach mat hang ban kem chay nhat*/
select * from Hang
where MaH in (select MaH
from ChiTietHD
where SoLuong in (select min SoLuong
from ChiTietHD) )
/*khach da mua bao nhiêu hoa don mat hang Ma01*/
select MaH,SoHD=count(MaH)
from Hoadon
where MaH='Ma01'
group by MaH
/*khach da mua bao nhiêu hoa don mat hang Ma02*/
select MaH,SoHD=count(MaH)
from Hoadon
where MaH='Ma02'
group by MaH
/*khach da mua bao nhiêu hoa don mat hang Ma03*/
select MaH,SoHD=count(MaH)
from Hoadon
where MaH='Ma03'
group by MaH
/*khach da mua bao nhiêu hoa don mat hang Ma04*/
select MaH,SoHD=count(MaH)
from Hoadon
where MaH='Ma04'
group by MaH
/*khach da mua bao nhiêu hoa don mat hang Ma11*/
select MaH,SoHD=count(MaH)
from Hoadon
where MaH='Ma11'
group by MaH
/*khach da mua bao nhiêu hoa don mat hang Ma08*/
select MaH,SoHD=count(MaH)
from Hoadon
where MaH='Ma08'
group by MaH
/*khach da mua bao nhiêu hoa don mat hang Ma14*/
select MaH,SoHD=count(MaH)
from Hoadon
where MaH='Ma14'
group by MaH
/*thay doi ten mat hang có MaH=02 thanh 'giay' cua b?ng hang dã có*/
update Hang
set TenH='giay'
where MaH='MaH02'
/*thong ke tong so luong theo ma hang*/
select MaH,tongsoluong=sum(SoLuong)
from Hang
group by MaH
/*dua ra nhung hoa don co tu ngay 1/4*/
select SoHD
from Hoadon
where NgayLapHD='04/01/2016'
/*dua ra danh sach mat hang co don vi tinh la 'chai'*/
select MaH,TenH,SoLuong
from Hang
where DVT='chai'
/*sua doi don gia cua tat ca mat hang lên 10% */
select SoHD,MaH,dongia=DonGia+ÐonGia/10
from ChiTietHD
/*dua ra khach hang co ngay thanh lap hoa don gan nhat*/
select Hoadon.SoHD,Hoadon.MaKH,Khachhang.TenKH
from Hoadon inner join Khachhang on Khachhang.MaKH=Hoadon.MaKH
where NgayLapHD in (select max(NgayLapHD)
from Hoadon
where getdate()>NgayLapHD)
/*DUA RA THONG TIN KHACH HANG VIP*/
select Hoadon.SoHD,Hoadon.MaKH,Khachhang.TenKH
from Hoadon inner join Khachhang on Khachhang.MaKH=Hoadon.MaKH
inner join ChiTietHD on Hoadon.SoHD=ChiTietHD.SoHD
where DonGia in (select max(DonGia)
from ChiTietHD
group by Hoadon.MaKH,Khachhang.TenKH)
/*DUA RA DANH SACH HANG CO DVT GIONG DVT CUA MAT HANG 04*/
select *
from Hang
where DVT in (select MaH,DVT
from Hang
where MaH='MaH04'
/*DUA RA DANH SACH HANG CO DVT GIONG DVT CUA MAT HANG 12*/
select *
from Hang
where DVT in (select MaH,DVT
from Hang
where MaH='MaH12'
/*Tao trigger cho phep nguoi dung nhap ten mat hang theo ma tu 1-15*/
creat trigger tr_kiemsoatNhap
on MaH
for insert,update
if(insert.MaH>1 or insert.MaH<15)
or(update.MaH>1 or update.MaH<15)
begin
RAISERORR("phai nhap du lieu trong khoang [1-15]")
roll back tran
end
/*thu tuc ham*/
creat procedured h_insertHang(@MaH as char(10),@TenH as char(10))
as
insert into Hang(MaH,TenH)
value(@MaH,@TenH)
end;
execute h_insertHang 'MaH15','quat'