USE master
GO
DROP DATABASE QLTV_PRO104
GO
CREATE DATABASE QLTV_PRO104
GO
USE QLTV_PRO104
GO
CREATE TABLE ChuDe(
MaCD INT IDENTITY(1,1) PRIMARY KEY,
TenCD nvarchar(80) NOT NULL,
)
GO
CREATE TABLE NhaXuatBan(
MaNXB INT IDENTITY(1,1) PRIMARY KEY,
TenNXB nvarchar(80) UNIQUE NOT NULL,
SDT VARCHAR(15) NOT NULL,
DiaChi NVARCHAR(100) NOT NULL
)
GO
CREATE TABLE TacGia(
MaTG INT IDENTITY(1,1) PRIMARY KEY,
TenTG NVARCHAR(80) NOT NULL,
GioiTinh BIT DEFAULT 0,
NgaySinh DATE NOT NULL,
)
GO
CREATE TABLE Sach(
MaSach INT IDENTITY(1,1) PRIMARY KEY,
TieuDe nvarchar(80) NOT NULL,
TacGia VARCHAR(80) NOT NULL ,
MaNXB INT NOT NULL FOREIGN KEY REFERENCES NhaXuatBan(MaNXB),
NamXB INT NOT NULL,
MaCD INT NOT NULL FOREIGN KEY REFERENCES ChuDe(MaCD),
NgonNgu nvarchar(40) NOT NULL,
SoTrang INT NOT NULL,
SoLuong INT NOT NULL,
GiaTien INT NOT NULL,
)
GO
CREATE TABLE NhanVien(
MaNV INT IDENTITY(1,1) PRIMARY KEY,
TenNV nvarchar(80) NOT NULL,
ChucVu bit DEFAULT 0,
TaiKhoan VARCHAR(40) NOT NULL,
MatKhau VARCHAR(30) NOT NULL,
)
GO
----------------------- Thêm Dữ Liệu ------------------------------
SET IDENTITY_INSERT [dbo].[TacGia] OFF
GO
INSERT INTO TacGia VALUES (N'Benjamin Zander',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Bennie Bough',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Carol Kline',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Daniel Shpiro',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'David Heinemeier Hansson',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Diane V. Cirinclone',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Fernando Trias De Bes',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Gale Muller',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Greg Hicks',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Hạ Dịch Ân',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Harvey McKinnon',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Hoà ng Minh dịch',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Jasminka Petrovic',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Jim Harter',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Kent Healy',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Linda Francis',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Lothar J. Seiwert',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Mark Victor Hansen',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Nguyễn Duy Cần',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Paustovsky',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Phùng Chấn Dực',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'S. Truett Cathy',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Steve Rivkin',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Trương Thị Lan Anh',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Ubee Hoà ng',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Werner Tiki Kustenmacher',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Wilhelm Grimm',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Ajahn Brahm',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Alex Rovira',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Alexander Grin',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Alexandre Dumas',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Anh Ä?ức',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Azim Jamal',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Ben Renshaw',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Boris Pasternak',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Carol Kinsey Goman',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Cecelia Ahern',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Charles Dickens',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Dale Carnegie',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Ä?ặng Huỳnh Mai Anh',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Daniel Defoe',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Daniel Gottlieb',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'David Niven',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Dick Lyles',1,'12/09/1998')
INSERT INTO TacGia VALUES (N'Ä?oà n Giá»?i',1,'12/09/1998')
GO
SET IDENTITY_INSERT [dbo].[ChuDe] OFF
GO
INSERT INTO ChuDe VALUES (N'Phổ Thông')
INSERT INTO ChuDe VALUES (N'Giáo Trình')
INSERT INTO ChuDe VALUES (N'Há»?c Táºp')
INSERT INTO ChuDe VALUES (N'Tham Khảo')
INSERT INTO ChuDe VALUES (N'Bổ Trợ')
INSERT INTO ChuDe VALUES (N'Giải TrÃ')
INSERT INTO ChuDe VALUES (N'Công Nghệ Thông Tin')
INSERT INTO ChuDe VALUES (N'Ä?ồ Há»?a')
INSERT INTO ChuDe VALUES (N'Láºp Trình')
INSERT INTO ChuDe VALUES (N'Khách Sạn Du Lịch')
INSERT INTO ChuDe VALUES (N'kinh Tế')
INSERT INTO ChuDe VALUES (N'Ngoại Ngữ')
GO
SET IDENTITY_INSERT [dbo].[NhaXuatBan] OFF
GO
INSERT INTO NhaXuatBan VALUES (N'Văn Hóa','0912345678',N'Hà Nội')
INSERT INTO NhaXuatBan VALUES (N'Văn NGhệ','0912345679',N'Hà Nội')
INSERT INTO NhaXuatBan VALUES (N'Thông tin truy�n thông','0912345677',N'Thái Bình')
INSERT INTO NhaXuatBan VALUES (N'Lao động','0912345676',N'Sơn La')
INSERT INTO NhaXuatBan VALUES (N'Công thương','0912345675',N'Ä?iện Biên')
INSERT INTO NhaXuatBan VALUES (N'Thanh niên','0912345674',N'Hà Nội')
INSERT INTO NhaXuatBan VALUES (N'Tổng hợp TP.HCM','0912345673',N'Hà Nội')
INSERT INTO NhaXuatBan VALUES (N'Tuổi trẻ','0912345672',N'Hà Nội')
INSERT INTO NhaXuatBan VALUES (N'Phụ nữ','0912345671',N'Hà Nội')
INSERT INTO NhaXuatBan VALUES (N'Tri thức','0912345670',N'Hà Nội')
INSERT INTO NhaXuatBan VALUES (N'Thế giới','0912345661',N'Hà Nội')
INSERT INTO NhaXuatBan VALUES (N'Kim Ä?ồng','0912345662',N'Hà Ná»™i')
INSERT INTO NhaXuatBan VALUES (N'Hồng Ä?ức','0912345663',N'Hà Ná»™i')
GO
SET IDENTITY_INSERT [dbo].[NhanVien] OFF
GO
INSERT INTO NhanVien VALUES (N'Nguyễn Tiến Thà nh',1,'ThanhNT','123456')
INSERT INTO NhanVien VALUES (N'Vũ Tiến Trung',0,'NV00002','123456')
INSERT INTO NhanVien VALUES (N'Dương Hồng Thái',0,'NV00003','123456')
INSERT INTO NhanVien VALUES (N'Kiá»?u Ä?ức Quyết',0,'NV00004','123456')
GO
SET IDENTITY_INSERT [dbo].[Sach] OFF
GO
INSERT INTO Sach VALUES (N'AQ Chỉ số vượt khó','1','1','2000','2',N'Tiếng Anh',600,250,300000000)
INSERT INTO Sach VALUES (N'Ai hiểu khách hà ng ngư�i đó bán được hà ng','2','2','2000','3',N'Tiếng Anh',600,250,300000)
INSERT INTO Sach VALUES (N'Bà i h�c vô giá từ những đi�u bình dị','3','3','2000','1',N'Tiếng Anh',600,250,300000)
INSERT INTO Sach VALUES (N'Bà máºt hà nh trình tình yêu','4','4','2000','5',N'Tiếng Anh',600,250,300000)
INSERT INTO Sach VALUES (N'Bà máºt ngôn ngữ cÆ¡ thể','5','5','2000','2',N'Tiếng Anh',600,250,300000)
INSERT INTO Sach VALUES (N'Bà máºt tình yêu Táºp 1','7','6','2000','1',N'Tiếng Anh',600,250,300000)
GO
INSERT INTO sach VALUES
(N'Ngồi khóc trên cây','1, 2, 5, 7, 10','1','2000','1',N'Tiếng Việt',600,250,300000000),
(N'Bảy bước đến mùa hè','3, 2','1','2000','3',N'Tiếng Việt',600,250,300000000),
(N'Ngà y xưa có 1 truyện tình','7, 2, 8, 9','4','2000','4',N'Tiếng Việt',600,250,300000000),
(N'Mắt biếc','2, 7, 5','7','2000','2',N'Tiếng Việt',600,250,300000000),
(N'Hạt giống tâm hồn','3, 2, 11','8','2000','1',N'Tiếng Việt',600,250,300000000),
(N'Cuộc sống rất giống cuộc đ�i','4, 2, 12','2','2000','1',N'Tiếng Việt',600,250,300000000),
(N'Tấm vải đ�','11, 12, 15','5','2000','3',N'Tiếng Việt',600,250,300000000)
GO
CREATE FUNCTION fn_loang_ngoang_vl(@tacgia nvarchar(80))
RETURNS nvarchar(300)
AS BEGIN
DECLARE @x nvarchar(MAX)
SET @x = ''
DECLARE @ma INT
DECLARE @tentg nvarchar(MAX)
while charindex(',', @tacgia) != 0
BEGIN
SET @ma = SUBSTRING(@tacgia, 0, charindex(',', @tacgia))
SET @tacgia = ltrim(RTRIM(SUBSTRING(@tacgia, charindex(',', @tacgia) + 1, len(@tacgia))))
SET @tentg = (SELECT TenTG FROM tacgia WHERE matg = @ma)
SET @x += @tentg + ', '
END
SET @tentg = (SELECT TenTG FROM tacgia WHERE matg = @tacgia)
SET @x += @tentg
RETURN (SELECT @x)
END
GO
CREATE proc sp_thong_ke_sach_theo_chu_de
AS BEGIN
SELECT TenCD,
COUNT(sach.macd) 'so luong sach'
FROM sach
INNER JOIN chude ON ChuDe.MaCD = sach.MaCD
GROUP BY sach.macd, TenCD
END
GO
CREATE proc sp_thong_ke_sach_theo_nxb
AS BEGIN
SELECT Tennxb,
COUNT(sach.manxb) 'so luong sach'
FROM sach
INNER JOIN NhaXuatBan ON NhaXuatBan.MaNXB = sach.Manxb
GROUP BY sach.manxb, Tennxb
END
GO
CREATE proc sp_thong_ke_sach_theo_tac_gia
AS BEGIN
SELECT tentg,
(
SELECT COUNT(masach)
FROM sach
WHERE masach IN (
SELECT dbo.fn_thong_ke_tac_gia(tentg, tacgia, masach)
FROM sach
)
)
FROM TacGia
END
GO
CREATE proc sp_thong_ke_sach_theo_ngon_ngu
AS BEGIN
SELECT ngonngu, COUNT(masach) FROM sach
WHERE ngonngu IN (SELECT ngonngu FROM sach)
GROUP BY ngonngu
END
GO
CREATE FUNCTION fn_thong_ke_tac_gia (
@tentg nvarchar(80),
@a VARCHAR(MAX),
@masach INT
)
RETURNS INT
AS BEGIN
DECLARE @matg INT
SET @matg = (SELECT matg FROM tacgia WHERE tentg = @tentg)
DECLARE @b INT
while charindex(',', @a) != 0
BEGIN
SET @b = ltrim(rtrim(SUBSTRING(@a, 0, charindex(',', @a))))
SET @a = ltrim(rtrim(SUBSTRING(@a, charindex(',', @a) + 1, len(@a))))
IF @b = @matg
BEGIN
RETURN (SELECT @masach)
break
END
END
IF ltrim(rtrim(@a)) = @matg
BEGIN
RETURN (SELECT @masach)
END
RETURN NULL
END
GO
SELECT * FROM nhanvien