Untitled
unknown
plain_text
2 months ago
3.2 kB
2
Indexable
USE master GO CREATE DATABASE qlsach GO USE qlsach GO -- Tạo bảng tác giả CREATE TABLE tacgia ( matg NCHAR(10) NOT NULL PRIMARY KEY, tentg NVARCHAR(50) NOT NULL, soluong INT ) -- Tạo bảng nhà xuất bản CREATE TABLE nhaxb ( manxb NCHAR(10) NOT NULL PRIMARY KEY, tennxb NVARCHAR(50) NOT NULL, soluong INT ) -- Tạo bảng sách CREATE TABLE sach ( masach NCHAR(10) NOT NULL PRIMARY KEY, tensach NVARCHAR(50) NOT NULL, manxb NCHAR(10), matg NCHAR(10), namxb INT, soluong INT, dongia MONEY, CONSTRAINT fk_sach_tg FOREIGN KEY(matg) REFERENCES tacgia(matg), CONSTRAINT fk_sach_nxb FOREIGN KEY(manxb) REFERENCES nhaxb(manxb) ) GO -- Thêm dữ liệu INSERT INTO tacgia VALUES ('TG01', 'Nguyen Van A', 20) INSERT INTO tacgia VALUES ('TG02', 'Tran Thi B', 30) INSERT INTO tacgia VALUES ('TG03', 'Le Van C', 40) INSERT INTO nhaxb VALUES ('NXB01', 'Kim Dong', 50) INSERT INTO nhaxb VALUES ('NXB02', 'Tre', 60) INSERT INTO nhaxb VALUES ('NXB03', 'Giao Duc', 70) INSERT INTO sach VALUES ('S01', 'Sach A', 'NXB01', 'TG01', 2018, 100, 50000) INSERT INTO sach VALUES ('S02', 'Sach B', 'NXB02', 'TG02', 2019, 80, 60000) INSERT INTO sach VALUES ('S03', 'Sach C', 'NXB03', 'TG03', 2020, 120, 70000) INSERT INTO sach VALUES ('S04', 'Sach D', 'NXB01', 'TG01', 2021, 90, 55000) GO -- Thủ tục thống kê tiền bán theo tên nhà xuất bản CREATE PROCEDURE sp_ThongKeNXB (@tennxb NVARCHAR(50)) AS BEGIN IF NOT EXISTS (SELECT * FROM nhaxb WHERE tennxb = @tennxb) PRINT 'Khong ton tai ten nha xuat ban' ELSE BEGIN SELECT nhaxb.manxb, tennxb, SUM(soluong * dongia) AS tien_ban FROM nhaxb INNER JOIN sach ON nhaxb.manxb = sach.manxb WHERE tennxb = @tennxb GROUP BY nhaxb.manxb, tennxb END END GO -- Hàm thống kê tiền bán theo mã tác giả CREATE FUNCTION fn_ThongKeTG (@tentg NVARCHAR(50)) RETURNS @bang TABLE ( matg NCHAR(10), tien_ban MONEY ) AS BEGIN INSERT INTO @bang SELECT tacgia.matg, SUM(soluong * dongia) AS tien_ban FROM tacgia INNER JOIN sach ON tacgia.matg = sach.matg WHERE tentg = @tentg GROUP BY tacgia.matg RETURN END GO -- Trigger kiểm tra mã NXB khi nhập sách CREATE TRIGGER trg_InsertSach ON sach FOR INSERT AS BEGIN DECLARE @manxb NCHAR(10) DECLARE @soluong INT SET @manxb = (SELECT manxb FROM inserted) SET @soluong = (SELECT soluong FROM inserted) IF NOT EXISTS (SELECT * FROM nhaxb WHERE manxb = @manxb) BEGIN RAISERROR(N'Ma NXB chua co mat trong bang NhaXB', 16, 1) ROLLBACK TRANSACTION END ELSE BEGIN UPDATE nhaxb SET soluong = soluong + @soluong WHERE manxb = @manxb END END GO -- Kiểm tra dữ liệu SELECT * FROM tacgia SELECT * FROM nhaxb SELECT * FROM sach -- Gọi thủ tục EXEC sp_ThongKeNXB 'Kim Dong' EXEC sp_ThongKeNXB 'Sai Gon' -- Gọi hàm SELECT * FROM fn_ThongKeTG('Nguyen Van A') SELECT * FROM fn_ThongKeTG('Tran Thi B') SELECT * FROM fn_ThongKeTG('Le Van C')
Editor is loading...
Leave a Comment