Giải bài tập CSDL Bài toán quản lý thư viện (Đề CH ĐHQGHCM)

Giải bài tập CSDL Bài toán quản lý thư viện

USE MASTER

GO

DROP DATABASE CH_HT_QUAN_LY_SACH

GO

CREATE DATABASE CH_HT_QUAN_LY_SACH

GO

USE CH_HT_QUAN_LY_SACH

GO

CREATE TABLE SACH

(

       MASACH NVARCHAR(10) PRIMARY KEY,

       TENSACH NVARCHAR(200),

       TACGIA NVARCHAR(200),

       NGONNGU NVARCHAR(200),

       CHUYENNGANH NVARCHAR(200),

       NAMXUATBAN INT,

       NHAXUATBAN NVARCHAR(200)

)

GO

CREATE TABLE QUYENSACH

(

       MAQUYENSACH NVARCHAR(100) PRIMARY KEY,

       TINHTRANG   NVARCHAR(200),

       MASACH NVARCHAR(10)

)

GO

ALTER TABLE QUYENSACH

ADD CONSTRAINT FK_LOAISANPHAM_SANPHAM FOREIGN KEY (MASACH) REFERENCES SACH(MASACH)

GO

CREATE TABLE BANDOC

(

       MABANDOC NVARCHAR(100) PRIMARY KEY,

       HODEM NVARCHAR(100),

       TEN NVARCHAR(100),

       NGAYCAPTHE DATE,

       NGAYHETHAN DATE

)

GO

CREATE TABLE MUONSACH

(

       MAQUYENSACH NVARCHAR(100) ,

       MABANDOC NVARCHAR(100),

       NGAYMUON DATE,

       NGAYTRA DATE

       CONSTRAINT  FP_CHITIETPHIEU PRIMARY KEY (MAQUYENSACH,MABANDOC)

)

GO

ALTER TABLE MUONSACH

ADD CONSTRAINT FK_KHOAHOC_SUDUNGTHIETBI FOREIGN KEY (MAQUYENSACH) REFERENCES QUYENSACH(MAQUYENSACH)

GO

ALTER TABLE MUONSACH

ADD CONSTRAINT FK_THIETBI_SUDUNGTHIETBI FOREIGN KEY (MABANDOC) REFERENCES BANDOC(MABANDOC)

GO

INSERT INTO SACH

VALUES('S01',N'Sách tâm lý 1',N'Lưu Ánh Linh5',N'Tiếng Anh',N'Văn Học',2015,N'Giáo Dục');

INSERT INTO SACH

VALUES('S02',N'Sách  2',N'Lưu Ánh Linh4',N'Tiếng Nga',N'CNTT',2015,N'Giáo Dục');

INSERT INTO SACH

VALUES('S03',N'Sách  3',N'Lưu Ánh Linh3',N'Tiếng Pháp',N'Khoa học',2015,N'Giáo Dục');

INSERT INTO SACH

VALUES('S04',N'Sách  4',N'Lưu Ánh Linh2',N'Tiếng Nhật',N'Điện tử',2015,N'Giáo Dục');

INSERT INTO SACH

VALUES('S05',N'Sách  5',N'Lưu Ánh Linh1',N'Tiếng Việt',N'Khoa học TN',2015,N'Kim Đồng');

INSERT INTO QUYENSACH VALUES('Q01','NEW','S01');

INSERT INTO QUYENSACH VALUES('Q02','OLD','S04');

INSERT INTO QUYENSACH VALUES('Q03','OLD','S02');

INSERT INTO QUYENSACH VALUES('Q04','NEW','S03');

INSERT INTO QUYENSACH VALUES('Q05','OLD','S05');

INSERT INTO BANDOC VALUES('B01',N'Nguyễn văn',N'Nguyễn','1/1/2016','9/2/2016');

INSERT INTO BANDOC VALUES('B02',N'Châu văn',N'Thông','1/1/2016','6/2/2016');

INSERT INTO BANDOC VALUES('B03',N'Lê văn',N'Tuấn','1/1/2016','8/2/2016');

INSERT INTO BANDOC VALUES('B04',N'Hà văn',N'Hải','1/1/2016','6/2/2016');

INSERT INTO BANDOC VALUES('B05',N'Đinh văn',N'Trâm','1/1/2016','5/2/2016');

INSERT INTO BANDOC VALUES('B06',N'Nguyễn văn',N'Sang','1/1/2016','9/2/2016');

INSERT INTO MUONSACH VALUES('Q01','B01','1/1/2016','1/15/2016');

INSERT INTO MUONSACH VALUES('Q05','B01','1/4/2016','3/18/2016');

INSERT INTO MUONSACH VALUES('Q02','B05','1/3/2016','1/19/2016');

INSERT INTO MUONSACH VALUES('Q03','B04','1/6/2016','6/20/2016');

INSERT INTO MUONSACH VALUES('Q04','B02','1/9/2016','2/22/2016');

INSERT INTO MUONSACH VALUES('Q05','B06','1/20/2016','2/23/2016');

--A/ LIỆT KỆ MASACH,TENSACH XUẤT BẢN SAU NĂM 2010

SELECT MASACH,TENSACH FROM SACH

WHERE CHUYENNGANH ='CNTT'

AND NAMXUATBAN>=2010

---B/ HOTEN VÀ SỐ LƯỢNG MƯỢN TRONG NĂM 2011(VÍ DỤ 2016)

SELECT HODEM+' '+TEN AS BANDOC,COUNT(BD.MABANDOC) AS'LƯỢT MƯỢN'

FROM BANDOC BD INNER JOIN MUONSACH MS ON BD.MABANDOC=MS.MABANDOC

WHERE YEAR(NGAYMUON)=2016 AND YEAR(NGAYTRA)=2016

GROUP BY HODEM+' '+TEN