--Cau 1: Tao database
CREATE DATABASE BANHANG
ON PRIMARY
(
NAME=BANHANG_DATA,
FILENAME='D:\BANHANG_DATA.MDF',
SIZE=10MB,
MAXSIZE=20MB,
FILEGROWTH=10%
)
LOG ON
(
NAME=BANHANG_LOG,
FILENAME='D:\BANHANG_LOG.LDF',
SIZE=5MB,
MAXSIZE=10MB,
FILEGROWTH=1MB
)
USE BANHANG
GO
--TAO BANG dm_NHACUNGCAP
CREATE TABLE DM_NHACUNGCAP
(
MAKH CHAR(4) NOT NULL,
TENKH NVARCHAR(150) NOT NULL,
DIACHI NVARCHAR(200) NOT NULL,
MASOTHUE NVARCHAR(50) NOT NULL,
TAIKHOAN NVARCHAR(100) NOT NULL,
TENNGANHANG NVARCHAR(200) NOT NULL,
GHICHU NVARCHAR(200),
CONSTRAINT PK_DM_NHACUNGCAP PRIMARY KEY (MAKH)
)
--TAO BANG DM_HANGHOA
CREATE TABLE DM_HANGHOA
(
MAHANG CHAR(10) NOT NULL,
TENHANG NVARCHAR(50) NOT NULL,
DONVITINH NVARCHAR(50) NOT NULL,
DACTINH NVARCHAR(200),
CONSTRAINT PK_DM_HANGHOA PRIMARY KEY (MAHANG)
)
--TAO BANG HOADON
CREATE TABLE DM_HOADON
(
MAHD CHAR(10) NOT NULL,
NGAYLAPHD SMALLDATETIME NOT NULL,
MAKH CHAR(4) NOT NULL,
MANV CHAR(4) NOT NULL,
MALOAI CHAR(4) NOT NULL,
DIENGIAI NVARCHAR(200),
CONSTRAINT PK_DM_HOADON PRIMARY KEY (MAHD),
CONSTRAINT FK_DN_HOADON FOREIGN KEY (MAKH) REFERENCES DM_NHACUNGCAP(MAKH)
)
--TAO BANG CHITIETHOADON
CREATE TABLE CHITIETHOADON
(
MAHD CHAR(10) NOT NULL,
MAHANG CHAR(10) NOT NULL,
SOLUONG FLOAT NOT NULL,
DONGIA FLOAT NOT NULL,
CHIETKHAU FLOAT NOT NULL,
CONSTRAINT FK_CTHD_DM_HOADON FOREIGN KEY (MAHD) REFERENCES DM_HOADON(MAHD),
CONSTRAINT FK_CTHD_DM_HANGHOA FOREIGN KEY (MAHANG) REFERENCES DM_HANGHOA(MAHANG),
CONSTRAINT PK_CHITIETHOADON PRIMARY KEY (MAHD,MAHANG)
)
--Cau 2: Tao thu tuc noi tai co ten Proc_DoanhSo de tinh tong doanh so cua 1 hoa don bat ky
CREATE PROC Proc_DoanhSo
@Ma CHAR(10)
AS
BEGIN
SELECT MaHD,sum((SoLuong * DonGia)- ChietKhau) as ThanhTien
FROM ChiTietHoaDon
WHERE MaHD=@Ma
GROUP BY MaHD
END
--thuc thi
exec Proc_DoanhSo 'HD01'
--Cau 3: Viet ham noi tuyen co ten F_ThanhTien() de tinh tien cua 1 san pham trong bang chitiethoadon. thanhtien=SoLuong*DonGia-ChietKhau
CREATE FUNCTION F_ThanhTien(@MaHang CHAR(10))
RETURNS TABLE
AS
RETURN(SELECT MaHD,MaHang,SoLuong,DonGia,ChietKhau,((SoLuong * DonGia)- ChietKhau) as ThanhTien
FROM ChiTietHoaDon
WHERE MaHang=@MaHang
GROUP BY MaHD,MaHang,SoLuong,DonGia,ChietKhau)
--thuc thi
select * from F_ThanhTien('HH01')
--cau 4: viet ham noi tuyen tra ve hoa don co doanh so cao nhat
CREATE FUNCTION MAX_DOANHSO()
RETURNS @DSSV TABLE
(
MAHD CHAR(10) NOT NULL,
MAHANG CHAR(10),
SOLUONG FLOAT,
DONGIA FLOAT,
CHIECKHAU FLOAT,
THANHTIEN FLOAT
)
AS
BEGIN
INSERT INTO @DSSV
SELECT TOP 1 MAHD,MAHANG,SOLUONG,DONGIA,CHIETKHAU,SUM(SOLUONG*DONGIA-CHIETKHAU)AS THANHTIEN
FROM CHITIETHOADON
GROUP BY MAHD,MAHANG,SOLUONG,DONGIA,CHIETKHAU
ORDER BY THANHTIEN DESC
RETURN
END
--thuc thi
SELECT * FROM MAX_DOANHSO()
--cau 5: viet ham noi tuyen co ten F_ChietKhau() tra ve 1 danh sach hoa don da dc chiet khau. biet rang hoa don co chiet khau >=100 thi chiet khau 5% thanh tien
CREATE FUNCTION F_CHIETKHAU()
RETURNS @DSSV TABLE
(
MAHD CHAR(10) NOT NULL,
MAHANG CHAR(10),
SOLUONG FLOAT,
DONGIA FLOAT,
CHIECKHAU FLOAT,
THANHTIEN FLOAT
)
AS
BEGIN
INSERT INTO @DSSV
SELECT MAHD,MAHANG,SOLUONG,DONGIA,CHIETKHAU=CHIETKHAU+(CHIETKHAU*0.05),THANHTIEN=(SOLUONG*DONGIA)
FROM CHITIETHOADON
WHERE (CHIETKHAU>=100)
RETURN
END
--thuc thi
select * from f_chietkhau()
--cau 6: viet trigger ten Trigger_ThayDoiMaHang trong bang DM_HangHoa. de thay doi ma hang 1 cach tu dong trong bang ChiTietHoaDon neu ta thay doi ma hang trong DM_HangHoa
CREATE TRIGGER Trigger_ThayDoiMaHang
ON DM_HANGHOA
FOR UPDATE
AS
DECLARE @MAHANG CHAR(10)
SELECT @MAHANG = INSERTED.MAHANG FROM INSERTED
UPDATE CHITIETHOADON SET MAHANG=@MAHANG