×

Welcome to TagMyCode

Please login or create account to add a snippet.
 

QLTV_PRO104.sql

0
0
 
0
Language: SQL
Posted by: Thái Dương
Added: Aug 10, 2019 3:46 PM
Views: 3996
Tags: no tags
  1. USE master
  2. GO
  3. DROP DATABASE QLTV_PRO104
  4. GO
  5. CREATE DATABASE QLTV_PRO104
  6. GO
  7. USE QLTV_PRO104
  8. GO
  9.  
  10. CREATE TABLE ChuDe(
  11. MaCD INT IDENTITY(1,1) PRIMARY KEY,
  12. TenCD nvarchar(80) NOT NULL,
  13. )
  14. GO
  15.  
  16. CREATE TABLE NhaXuatBan(
  17.     MaNXB INT IDENTITY(1,1) PRIMARY KEY,
  18.     TenNXB nvarchar(80) UNIQUE NOT NULL,
  19.     SDT VARCHAR(15) NOT NULL,
  20.     DiaChi NVARCHAR(100) NOT NULL
  21. )
  22. GO
  23.  
  24. CREATE TABLE TacGia(
  25. MaTG INT IDENTITY(1,1) PRIMARY KEY,
  26. TenTG NVARCHAR(80) NOT NULL,
  27. GioiTinh BIT DEFAULT 0,
  28. NgaySinh DATE NOT NULL,
  29. )
  30. GO
  31.  
  32. CREATE TABLE Sach(
  33. MaSach INT IDENTITY(1,1) PRIMARY KEY,
  34. TieuDe nvarchar(80) NOT NULL,
  35. TacGia VARCHAR(80) NOT NULL ,
  36. MaNXB INT NOT NULL FOREIGN KEY REFERENCES NhaXuatBan(MaNXB),
  37. NamXB INT NOT NULL,
  38. MaCD INT NOT NULL FOREIGN KEY REFERENCES ChuDe(MaCD),
  39. NgonNgu nvarchar(40) NOT NULL,
  40. SoTrang INT NOT NULL,
  41. SoLuong INT NOT NULL,
  42. GiaTien INT NOT NULL,
  43. )
  44. GO
  45.  
  46.  
  47. CREATE TABLE NhanVien(
  48. MaNV INT IDENTITY(1,1) PRIMARY KEY,
  49. TenNV nvarchar(80) NOT NULL,
  50. ChucVu bit DEFAULT 0,
  51. TaiKhoan VARCHAR(40) NOT NULL,
  52. MatKhau VARCHAR(30) NOT NULL,
  53. )
  54. GO  
  55.  
  56. ----------------------- Thêm Dữ Liệu ------------------------------
  57.  
  58. SET IDENTITY_INSERT [dbo].[TacGia] OFF
  59. GO
  60.  
  61. INSERT INTO TacGia  VALUES (N'Benjamin Zander',1,'12/09/1998')
  62. INSERT INTO TacGia  VALUES (N'Bennie Bough',1,'12/09/1998')
  63. INSERT INTO TacGia  VALUES (N'Carol Kline',1,'12/09/1998')
  64. INSERT INTO TacGia  VALUES (N'Daniel Shpiro',1,'12/09/1998')
  65. INSERT INTO TacGia  VALUES (N'David Heinemeier Hansson',1,'12/09/1998')
  66. INSERT INTO TacGia  VALUES (N'Diane V. Cirinclone',1,'12/09/1998')
  67. INSERT INTO TacGia  VALUES (N'Fernando Trias De Bes',1,'12/09/1998')
  68. INSERT INTO TacGia  VALUES (N'Gale Muller',1,'12/09/1998')
  69. INSERT INTO TacGia  VALUES (N'Greg Hicks',1,'12/09/1998')
  70. INSERT INTO TacGia  VALUES (N'Hạ Dịch Ân',1,'12/09/1998')
  71. INSERT INTO TacGia  VALUES (N'Harvey McKinnon',1,'12/09/1998')
  72. INSERT INTO TacGia  VALUES (N'Hoàng Minh dịch',1,'12/09/1998')
  73. INSERT INTO TacGia  VALUES (N'Jasminka Petrovic',1,'12/09/1998')
  74. INSERT INTO TacGia  VALUES (N'Jim Harter',1,'12/09/1998')
  75. INSERT INTO TacGia  VALUES (N'Kent Healy',1,'12/09/1998')
  76. INSERT INTO TacGia  VALUES (N'Linda Francis',1,'12/09/1998')
  77. INSERT INTO TacGia  VALUES (N'Lothar J. Seiwert',1,'12/09/1998')
  78. INSERT INTO TacGia  VALUES (N'Mark Victor Hansen',1,'12/09/1998')
  79. INSERT INTO TacGia  VALUES (N'Nguyá»…n Duy Cần',1,'12/09/1998')
  80. INSERT INTO TacGia  VALUES (N'Paustovsky',1,'12/09/1998')
  81. INSERT INTO TacGia  VALUES (N'Phùng Chấn Dá»±c',1,'12/09/1998')
  82. INSERT INTO TacGia  VALUES (N'S. Truett Cathy',1,'12/09/1998')
  83. INSERT INTO TacGia  VALUES (N'Steve Rivkin',1,'12/09/1998')
  84. INSERT INTO TacGia  VALUES (N'TrÆ°Æ¡ng Thị Lan Anh',1,'12/09/1998')
  85. INSERT INTO TacGia  VALUES (N'Ubee Hoàng',1,'12/09/1998')
  86. INSERT INTO TacGia  VALUES (N'Werner Tiki Kustenmacher',1,'12/09/1998')
  87. INSERT INTO TacGia  VALUES (N'Wilhelm Grimm',1,'12/09/1998')
  88. INSERT INTO TacGia  VALUES (N'Ajahn Brahm',1,'12/09/1998')
  89. INSERT INTO TacGia  VALUES (N'Alex Rovira',1,'12/09/1998')
  90. INSERT INTO TacGia  VALUES (N'Alexander Grin',1,'12/09/1998')
  91. INSERT INTO TacGia  VALUES (N'Alexandre Dumas',1,'12/09/1998')
  92. INSERT INTO TacGia  VALUES (N'Anh Ä?ức',1,'12/09/1998')
  93. INSERT INTO TacGia  VALUES (N'Azim Jamal',1,'12/09/1998')
  94. INSERT INTO TacGia  VALUES (N'Ben Renshaw',1,'12/09/1998')
  95. INSERT INTO TacGia  VALUES (N'Boris Pasternak',1,'12/09/1998')
  96. INSERT INTO TacGia  VALUES (N'Carol Kinsey Goman',1,'12/09/1998')
  97. INSERT INTO TacGia  VALUES (N'Cecelia Ahern',1,'12/09/1998')
  98. INSERT INTO TacGia  VALUES (N'Charles Dickens',1,'12/09/1998')
  99. INSERT INTO TacGia  VALUES (N'Dale Carnegie',1,'12/09/1998')
  100. INSERT INTO TacGia  VALUES (N'Ä?ặng Huỳnh Mai Anh',1,'12/09/1998')
  101. INSERT INTO TacGia  VALUES (N'Daniel Defoe',1,'12/09/1998')
  102. INSERT INTO TacGia  VALUES (N'Daniel Gottlieb',1,'12/09/1998')
  103. INSERT INTO TacGia  VALUES (N'David Niven',1,'12/09/1998')
  104. INSERT INTO TacGia  VALUES (N'Dick Lyles',1,'12/09/1998')
  105. INSERT INTO TacGia  VALUES (N'Ä?oàn Giá»?i',1,'12/09/1998')
  106.  
  107. GO
  108.  
  109. SET IDENTITY_INSERT [dbo].[ChuDe] OFF
  110. GO
  111.  
  112. INSERT INTO ChuDe  VALUES (N'Phổ Thông')
  113. INSERT INTO ChuDe  VALUES (N'Giáo Trình')
  114. INSERT INTO ChuDe  VALUES (N'Há»?c Tập')
  115. INSERT INTO ChuDe  VALUES (N'Tham Khảo')
  116. INSERT INTO ChuDe  VALUES (N'Bổ Trợ')
  117. INSERT INTO ChuDe  VALUES (N'Giải Trí')
  118. INSERT INTO ChuDe  VALUES (N'Công Nghệ Thông Tin')
  119. INSERT INTO ChuDe  VALUES (N'Ä?ồ Há»?a')
  120. INSERT INTO ChuDe  VALUES (N'Lập Trình')
  121. INSERT INTO ChuDe  VALUES (N'Khách Sạn Du Lịch')
  122. INSERT INTO ChuDe  VALUES (N'kinh Tế')
  123. INSERT INTO ChuDe  VALUES (N'Ngoại Ngữ')
  124. GO
  125.  
  126. SET IDENTITY_INSERT [dbo].[NhaXuatBan] OFF
  127. GO
  128.  
  129. INSERT INTO NhaXuatBan VALUES (N'Văn Hóa','0912345678',N'Hà Nội')
  130. INSERT INTO NhaXuatBan VALUES (N'Văn NGhệ','0912345679',N'Hà Nội')
  131. INSERT INTO NhaXuatBan VALUES (N'Thông tin truy�n thông','0912345677',N'Thái Bình')
  132. INSERT INTO NhaXuatBan VALUES (N'Lao Ä‘á»™ng','0912345676',N'SÆ¡n La')
  133. INSERT INTO NhaXuatBan VALUES (N'Công thÆ°Æ¡ng','0912345675',N'Ä?iện Biên')
  134. INSERT INTO NhaXuatBan VALUES (N'Thanh niên','0912345674',N'Hà Nội')
  135. INSERT INTO NhaXuatBan VALUES (N'Tổng hợp TP.HCM','0912345673',N'Hà Nội')
  136. INSERT INTO NhaXuatBan VALUES (N'Tuổi trẻ','0912345672',N'Hà Nội')
  137. INSERT INTO NhaXuatBan VALUES (N'Phụ nữ','0912345671',N'Hà Nội')
  138. INSERT INTO NhaXuatBan VALUES (N'Tri thức','0912345670',N'Hà Nội')
  139. INSERT INTO NhaXuatBan VALUES (N'Thế giới','0912345661',N'Hà Nội')
  140. INSERT INTO NhaXuatBan VALUES (N'Kim Ä?ồng','0912345662',N'Hà Ná»™i')
  141. INSERT INTO NhaXuatBan VALUES (N'Hồng Ä?ức','0912345663',N'Hà Ná»™i')
  142. GO
  143.  
  144. SET IDENTITY_INSERT [dbo].[NhanVien] OFF
  145. GO
  146.  
  147. INSERT INTO NhanVien VALUES (N'Nguyễn Tiến Thành',1,'ThanhNT','123456')
  148. INSERT INTO NhanVien VALUES (N'Vũ Tiến Trung',0,'NV00002','123456')
  149. INSERT INTO NhanVien VALUES (N'Dương Hồng Thái',0,'NV00003','123456')
  150. INSERT INTO NhanVien VALUES (N'Kiá»?u Ä?ức Quyết',0,'NV00004','123456')
  151. GO
  152.  
  153. SET IDENTITY_INSERT [dbo].[Sach] OFF
  154. GO
  155.  
  156. INSERT INTO Sach  VALUES (N'AQ Chỉ số vượt khó','1','1','2000','2',N'Tiếng Anh',600,250,300000000)
  157. 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)
  158. 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)
  159. 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)
  160. INSERT INTO Sach  VALUES (N'Bí mật ngôn ngữ cÆ¡ thể','5','5','2000','2',N'Tiếng Anh',600,250,300000)
  161. 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)
  162. GO
  163.  
  164. INSERT INTO sach VALUES
  165. (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),
  166. (N'Bảy bước đến mùa hè','3, 2','1','2000','3',N'Tiếng Việt',600,250,300000000),
  167. (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),
  168. (N'Mắt biếc','2, 7, 5','7','2000','2',N'Tiếng Việt',600,250,300000000),
  169. (N'Hạt giống tâm hồn','3, 2, 11','8','2000','1',N'Tiếng Việt',600,250,300000000),
  170. (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),
  171. (N'Tấm vải đ�','11, 12, 15','5','2000','3',N'Tiếng Việt',600,250,300000000)
  172. GO
  173.  
  174. CREATE FUNCTION fn_loang_ngoang_vl(@tacgia nvarchar(80))
  175. RETURNS nvarchar(300)
  176. AS BEGIN
  177.         DECLARE @x nvarchar(MAX)
  178.         SET @x = ''
  179.  
  180.         DECLARE @ma INT
  181.         DECLARE @tentg nvarchar(MAX)
  182.  
  183.         while charindex(',', @tacgia) != 0
  184.                 BEGIN
  185.                         SET @ma = SUBSTRING(@tacgia, 0, charindex(',', @tacgia))
  186.        
  187.                         SET @tacgia = ltrim(RTRIM(SUBSTRING(@tacgia, charindex(',', @tacgia) + 1, len(@tacgia))))
  188.  
  189.                         SET @tentg = (SELECT TenTG FROM tacgia WHERE matg = @ma)
  190.  
  191.                         SET @x += @tentg + ', '
  192.                 END
  193.  
  194.         SET @tentg = (SELECT TenTG FROM tacgia WHERE matg = @tacgia)
  195.         SET @x += @tentg
  196.        
  197.         RETURN (SELECT @x)
  198. END
  199. GO
  200.  
  201. CREATE proc sp_thong_ke_sach_theo_chu_de
  202. AS BEGIN
  203.         SELECT TenCD,
  204.                 COUNT(sach.macd) 'so luong sach'
  205.         FROM sach
  206.         INNER JOIN chude ON ChuDe.MaCD = sach.MaCD
  207.         GROUP BY sach.macd, TenCD
  208. END
  209. GO
  210.  
  211. CREATE proc sp_thong_ke_sach_theo_nxb
  212. AS BEGIN
  213.         SELECT Tennxb,
  214.                 COUNT(sach.manxb) 'so luong sach'
  215.         FROM sach
  216.         INNER JOIN NhaXuatBan ON NhaXuatBan.MaNXB = sach.Manxb
  217.         GROUP BY sach.manxb, Tennxb
  218. END
  219. GO
  220.  
  221. CREATE proc sp_thong_ke_sach_theo_tac_gia
  222. AS BEGIN
  223.         SELECT tentg,
  224.                 (
  225.                         SELECT COUNT(masach)
  226.                         FROM sach
  227.                         WHERE masach IN (
  228.                                 SELECT dbo.fn_thong_ke_tac_gia(tentg, tacgia, masach)
  229.                                 FROM sach
  230.                         )
  231.                 )
  232.         FROM TacGia
  233. END
  234. GO
  235.  
  236. CREATE proc sp_thong_ke_sach_theo_ngon_ngu
  237. AS BEGIN
  238.         SELECT ngonngu, COUNT(masach) FROM sach
  239.         WHERE ngonngu IN (SELECT ngonngu FROM sach)
  240.         GROUP BY ngonngu
  241. END
  242. GO
  243.  
  244. CREATE FUNCTION fn_thong_ke_tac_gia (
  245.         @tentg nvarchar(80),
  246.         @a VARCHAR(MAX),
  247.         @masach INT
  248. )
  249. RETURNS INT
  250. AS BEGIN
  251.         DECLARE @matg INT
  252.         SET @matg = (SELECT matg FROM tacgia WHERE tentg = @tentg)
  253.  
  254.         DECLARE @b INT
  255.  
  256.         while charindex(',', @a) != 0
  257.             BEGIN
  258.                 SET @b = ltrim(rtrim(SUBSTRING(@a, 0, charindex(',', @a))))
  259.                 SET @a = ltrim(rtrim(SUBSTRING(@a, charindex(',', @a) + 1, len(@a))))
  260.                 IF @b = @matg
  261.                     BEGIN
  262.                             RETURN (SELECT @masach)
  263.                             break
  264.                     END
  265.             END
  266.  
  267.         IF ltrim(rtrim(@a)) = @matg
  268.                 BEGIN
  269.                         RETURN (SELECT @masach)
  270.                 END
  271.  
  272.         RETURN NULL
  273. END
  274. GO
  275.  
  276.  
  277.  
  278.  
  279.  
  280. SELECT * FROM nhanvien
  281.  

Write a comment

To post comments, please log in or register.