create database Quan_Ly_Thu_Vien go use Quan_Ly_Thu_Vien go create table LINHVUC (
maLV char(10) constraint p_LV primary key, tenLV Nvarchar(50) not null ) go select* from LINHVUC create table NXB
(maNXB char(10) constraint p_NXB primary key , tenNXB Nvarchar(50) not null, diaChi Nvarchar(50)not null, dienThoai char(10) not null ) go select* from NXB create table TacGia (
maTG char(10) constraint p_TG primary key, tenTG Nvarchar(25) not null, diaChi Nvarchar(50) not null, dienThoai char(50) not null ) go select* from TacGia create table DAUSACH ( maDS char(10), tenSach Nvarchar(50) not null, namXB int not null, ngayNhap Datetime not null, soLuong int not null, maLV char(10) not null, maNXB char(10) not null, maTG char(10) not null,
Constraint p_DS Primary Key(maDS),
Constraint FK_maLV_LINHVUC FOREIGN KEY (maLV) REFERENCES LINHVUC(maLV),
Constraint FK_maNXB_NXB FOREIGN KEY (maNXB) REFERENCES NXB(maNXB), Constraint FOREIGN FK_maTG_TACGIA
KEY (maTG) REFERENCES TACGIA(maTG) ) go select* from DAUSACH create table SACH ( maSach char(10), maDS char(10) not null,
tinhTrang Nvarchar(50) not null,
Constraint p_Sach Primary key(maSach),
Constraint FK_maDS_DAUSACH Foreign key(maDS) references DAUSACH(maDS) ) go select* from SACH create table DOCGIA ( maDG char(10), hoTen Nvarchar(50) not null, ngaySinh Datetime not null, tenLop Nvarchar(15) not null, Gt Nvarchar(3) not null,
Constraint p_DOCGIA Primary Key(maDG), ) go select* from DOCGIA create table PhieuMuon ( Sophieu char(10), maDG char(10), maSach char(10), ngayMuon Datetime not null, ngayTra Datetime not null,
Constraint p_PhieuMuon Primary key(Sophieu),
Constraint Fk_soThe_DOCGIA Foreign key(maDG) references DOCGIA(maDG), Constraint Foreign FK_maSach_SACH
key(maSach) references SACH(maSach) ) go select * from PhieuMuon
--1) Hiển thị tất các nhà xuất b n có sách đ ả c m ượ n vào ngày ‘3/9/2008’. ượ select * from NXB where maNXB in (select maNXB from DAUSACH where maDS in (select maDS from SACH where maSach in (select maSach from PhieuMuon where ngayMuon='05/11/2021')))
--2) Hiển thị tất cả các sinh viên m n các cu ượ n sách c ố a tác gi ủ ‘Quách Tu ả n Ng ấ c’. ọ select maDG from DOCGIA where maDG in ( select maDG from PhieuMuon where maSach in (select maSach from DAUSACH where maTG in (select maDG from TacGia where tenTG =N'Tô Ph ng Lan' ươ )))
--3) Hiển thị tất cả các cu n sách c ố a NXB Lao Đ ủ ng có tình tr ộ ng sách h ạ ng ỏ . select * from DAUSACH except select ds.*
from DAUSACH as ds inner join SACH as sach on ds.maDS = sach.maDS inner join NXB as nxb on ds.maNXB = nxb.maNXB
where tenNXB = N'NXB Lao động' and tinhTrang = N'Hỏng'
--4) Hiển thị thông tin c a các đ ủ u sách đ ầ c xu ượ t b ấ n t ả i Hà N ạ i. ộ select ds.*
from DAUSACH as ds inner join NXB as nxb on ds.maNXB = nxb.maNXB where diaChi = N'Hà nội'
--5) Hiển thị tất cả sinh viên m n sách thu ượ c lĩnh v ộ c ‘Tin H ự c’ ọ select * from DOCGIA where maDG in (select maDG from PhieuMuon where maSach in (select maSach from DAUSACH where maLV in (select maLV from LINHVUC where tenLV=N'Công nghệ thông tin')))
--6) Hiển thị tất cả các cu n sách thu ố ộc lĩnh v c ‘Ô tô’ ho ự c lĩnh v ặ c ‘Tin h ự c’ ọ