Untitled

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