De1

CREATE DATABASE De01

DROP DATABASE De01

USE De01

SET DATEFORMAT DMY

CREATE TABLE TACGIA
(
    MATG CHAR(5) PRIMARY KEY,
    HOTEN VARCHAR(20),
    DIACHI VARCHAR(50),
    NGSINH SMALLDATETIME,
    SODT VARCHAR(15)
)
CREATE TABLE SACH
(
    MASACH CHAR(5) PRIMARY KEY,
    TENSACH VARCHAR(25),
    THELOAI VARCHAR(25)
)
CREATE TABLE TACGIA_SACH
(
    MATG CHAR(5),
    MASACH CHAR(5),
    CONSTRAINT PK_TGS PRIMARY KEY (MATG,MASACH),
    CONSTRAINT FK_TG FOREIGN KEY (MATG) REFERENCES TACGIA(MATG),
    CONSTRAINT FK_SACH FOREIGN KEY (MASACH) REFERENCES SACH(MASACH)
)
CREATE TABLE PHATHANH
(
    MAPH CHAR(5) PRIMARY KEY,
    MASACH CHAR(5) REFERENCES SACH(MASACH),
    NGAYPH SMALLDATETIME,
    SOLUONG INT,
    NHAXUATBAN VARCHAR(20)
)

/* Nhap du lieu */

INSERT TACGIA(MaTG, HoTen, DiaChi, NgSinh, SoDT) VALUES ('001','Tac Gia 1','So 1 Duong 1 Tp 1','1/1/2001','011111')
INSERT TACGIA(MaTG, HoTen, DiaChi, NgSinh, SoDT) VALUES ('002','Tac Gia 2','So 2 Duong 2 Tp 2','2/2/2002','022222')
INSERT TACGIA(MaTG, HoTen, DiaChi, NgSinh, SoDT) VALUES ('003','Tac Gia 3','So 3 Duong 3 Tp 3','3/3/2001','033333')

INSERT SACH(MaSach,TenSach, TheLoai) VALUES ('s01','Sach 1','Van Hoc')
INSERT SACH(MaSach,TenSach, TheLoai) VALUES ('s02','Sach 2','GIAOKHOA')
INSERT SACH(MaSach,TenSach, TheLoai) VALUES ('s03','Sach 3','Van Hoc')
INSERT SACH(MaSach,TenSach, TheLoai) VALUES ('s04','Sach 4','Van Hoc')

INSERT TACGIA_SACH(MaTG, MaSach) VALUES ('001','s01')
INSERT TACGIA_SACH(MaTG, MaSach) VALUES ('002','s02')
INSERT TACGIA_SACH(MaTG, MaSach) VALUES ('002','s03')
INSERT TACGIA_SACH(MaTG, MaSach) VALUES ('003','s04')

INSERT PHATHANH(MaPH, MaSach, NgayPH, SoLuong, NhaXuatBan) VALUES ('ph01','s01','01/01/2011',111,'TRE')
INSERT PHATHANH(MaPH, MaSach, NgayPH, SoLuong, NhaXuatBan) VALUES ('ph02','s02','02/02/2012',111,'GIAODUC')
INSERT PHATHANH(MaPH, MaSach, NgayPH, SoLuong, NhaXuatBan) VALUES ('ph03','s03','03/03/2013',111,'GIAODUC')
INSERT PHATHANH(MaPH, MaSach, NgayPH, SoLuong, NhaXuatBan) VALUES ('ph04','s04','03/03/2013',111,'GIAODUC')

--Ngay phat hanh sach phai lon hon ngay sinh cua tac gia
CREATE TRIGGER CK_PH ON PHATHANH
FOR UPDATE, INSERT
AS IF UPDATE(NGAYPH)
BEGIN
    DECLARE @NPH SMALLDATETIME, @NS SMALLDATETIME,@MATG CHAR(5)
    SET @NPH = (SELECT NGAYPH FROM INSERTED)
    SET @NS = (SELECT NGSINH FROM TACGIA,TACGIA_SACH,INSERTED WHERE TACGIA.MATG=TACGIA_SACH.MATG AND TACGIA_SACH.MASACH=INSERTED.MASACH)
    IF (@NPH<@NS)
        BEGIN
        PRINT 'NGAY PHAT HANH PHAI LON HON NGAY SINH CUA TAC GIA'
        ROLLBACK TRAN
        END
END

-- Kiem tra trigger
INSERT PHATHANH(MaPH, MaSach, NgayPH, SoLuong, NhaXuatBan) VALUES ('ph05','s01','03/03/2003',111,'GIAODUC')

UPDATE phathanh
SET ngayph = '03/03/2009'
WHERE maph = 'ph05' AND masach = 's01'

SELECT * FROM PHATHANH

-- Ghi chu: Sinh vien tu viet trigger update tren bang TACGIA

--THE LOAI GIÁO KHOA DO NSX GIÁO DỤC
CREATE TRIGGER CK_TL ON PHATHANH
FOR UPDATE, INSERT
AS IF UPDATE(NHAXUATBAN)
BEGIN
    DECLARE @TL VARCHAR(25), @NXB VARCHAR(20),@MASACH CHAR(5)
    SET @TL = (SELECT THELOAI FROM SACH,INSERTED WHERE SACH.MASACH=INSERTED.MASACH)
    SET @NXB = (SELECT NHAXUATBAN FROM INSERTED)
    IF (@TL='GIAOKHOA' AND @NXB<>'GIAODUC')
        BEGIN
        PRINT 'SACH GIAO KHOA CHI DO NXB GIAO DUC PHAT HANH'
        ROLLBACK TRAN
        END
END

-- Kiem tra trigger
INSERT PHATHANH(MaPH, MaSach, NgayPH, SoLuong, NhaXuatBan) VALUES ('ph06','s02','03/03/2003',111,'GIAODUC')

UPDATE PHATHANH
SET NHAXUATBAN = 'TRE'
WHERE MAPH = 'PH02' AND MASACH = 'S02'

--Tìm tác giả (MaTG,HoTen,SoDT) của những quyển sách thuộc thể loại “Văn học”
--do nhà xuất bản Trẻ phát hành.
SELECT TACGIA.MATG, HOTEN, SODT
FROM TACGIA, SACH, PHATHANH, TACGIA_SACH
WHERE TACGIA.MATG=TACGIA_SACH.MATG AND
        TACGIA_SACH.MASACH=SACH.MASACH AND
        SACH.MASACH = PHATHANH.MASACH AND
        SACH.THELOAI='VAN HOC' AND
        PHATHANH.NHAXUATBAN='TRE'

--Tìm nhà xuất bản phát hành nhiều thể loại sách nhất
SELECT NHAXUATBAN,COUNT(DISTINCT THELOAI)
FROM PHATHANH, SACH
WHERE PHATHANH.MASACH=SACH.MASACH
GROUP BY NHAXUATBAN
HAVING COUNT(DISTINCT THELOAI)>=ALL(SELECT COUNT(DISTINCT THELOAI)
                                    FROM SACH, PHATHANH
                                    WHERE PHATHANH.MASACH=SACH.MASACH
                                    GROUP BY NHAXUATBAN)

--Trong mỗi nhà xuất bản, tìm tác giả (MaTG,HoTen) có số lần phát hành nhiều sách nhất.
-- Tao view: trong moi nha xuat ban, thong ke so lan phat hanh cua tung tac gia


SELECT * FROM PHATHANH

SELECT * FROM TACGIA

SELECT * FROM SACH

SELECT * FROM TACGIA_SACH

CREATE VIEW NXB_TG_SLPH
AS
    SELECT a.NhaXuatBan, b.MaTG, COUNT(a.MaPH) [LanXB]
    FROM PHATHANH a INNER JOIN TACGIA_SACH b ON a.MaSach = b.MaSach
    GROUP BY a.NhaXuatBan, b.MaTG

-- Xem du lieu cua view
SELECT * FROM NXB_TG_SLPH

-- Tao view: trong moi nha xuat ban, thong ke so lan phat hanh lon nhat
CREATE VIEW NXB_TG_SLPHLonNhat
AS
    SELECT NhaXuatBan, MAX(LanXB) [LanXB]
    FROM NXB_TG_SLPH
    GROUP BY NhaXuatBan

-- Xem du lieu cua view
SELECT * FROM NXB_TG_SLPHLonNhat

SELECT a.NhaXuatBan, a.MaTG, c.HoTen, a.LanXB
FROM NXB_TG_SLPH a, NXB_TG_SLPHLonNhat b, TACGIA c
WHERE a.NhaXuatBan = b.NhaXuatBan AND
      a.LanXB = b.LanXB AND
      a.MaTG = c.MaTG