NS2A2

bug

Jun 10th, 2021
894
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE DATABASE QLNhapXuat
  2.  
  3. GO
  4. USE QLNhapXuat
  5. GO
  6.  
  7. --a, Creata table
  8. CREATE TABLE SANPHAM
  9. (
  10.     MaSP nvarchar(30) PRIMARY KEY,
  11.     TenSP nvarchar(30) NOT NULL,
  12.     mausac nvarchar(30) NOT NULL,
  13.     soluong INT NOT NULL,
  14.     giaban money NOT NULL
  15. )
  16.  
  17. CREATE TABLE Nhap
  18. (
  19.     SoHDN nvarchar(30) PRIMARY KEY,
  20.     MaSP nvarchar(30) NOT NULL,
  21.     SoLuongN INT NOT NULL,
  22.     NgayN DATE NOT NULL
  23.     FOREIGN KEY (MaSP) REFERENCES SANPHAM(MaSP)
  24. )
  25.  
  26. CREATE TABLE Xuat
  27. (
  28.     SoHDX nvarchar(30) PRIMARY KEY,
  29.     MaSP nvarchar(30) NOT NULL,
  30.     SoluongX INT NOT NULL,
  31.     NgayX DATE NOT NULL
  32.     FOREIGN KEY (MaSP) REFERENCES SANPHAM(MaSP)
  33. )
  34.  
  35. GO
  36. --b, Insert data
  37.  
  38. INSERT INTO SANPHAM VALUES
  39. ('sp01','sanpham1','xanh',1,1),
  40. ('sp02','sanpham2','vang',2,2),
  41. ('sp03','sanpham3','cam',3,3)
  42.  
  43. INSERT INTO Nhap VALUES
  44. ('Nhap1','sp01',1,'2021-1-6'),
  45. ('Nhap2','sp02',2,'2021-2-6'),
  46. ('Nhap3','sp03',3,'2021-3-6')
  47.  
  48. INSERT INTO Xuat VALUES
  49. ('Xuat1','sp01',1,'2021-4-6'),
  50. ('Xuat2','sp02',2,'2021-5-6')
  51.  
  52. GO
  53. --Display data
  54.  
  55. SELECT * FROM SANPHAM
  56. SELECT * FROM Nhap
  57. SELECT * FROM Xuat
  58.  
  59. GO
  60. --Câu 2:
  61. CREATE FUNCTION Fn_Cau2 (@TenSP nvarchar(30))
  62. RETURNS money
  63. AS
  64. BEGIN
  65.     DECLARE @tong money
  66.     SELECT @tong = SUM(SoLuongN*giaban)
  67.     FROM SANPHAM JOIN Nhap ON SANPHAM.MaSP = Nhap.MaSP
  68.     WHERE TenSP = @tenSP
  69.     RETURN @tong
  70. END
  71.  
  72. SELECT dbo.Fn_Cau2 ('sanpham1')
  73. SELECT dbo.Fn_Cau2 ('sanpham2')
  74.  
  75. GO
  76. --Câu 3:
  77. CREATE PROC tt_Cau3
  78. @masp CHAR(4), @TenSP nvarchar(50), @mausac nvarchar(50), @soluong INT, @giaban money,
  79. @kq INT output
  80. AS
  81. BEGIN
  82.     IF EXISTS (SELECT * FROM SANPHAM WHERE TenSP = @TenSP)
  83.         BEGIN
  84.             print N'Tên SP đã tồn tại'
  85.             SET @kq = 1
  86.             RETURN
  87.         END
  88.     ELSE
  89.         BEGIN
  90.             INSERT INTO SANPHAM VALUES (@maSP, @tenSP, @mausac,@soluong, @giaban)
  91.             SET @kq = 0
  92.         END
  93.     RETURN @kq
  94. END
  95.  
  96. DECLARE @kq INT
  97. EXEC tt_Cau3 'SP04', N'Quạt điện', N'đen', 50, 500000, @kq output
  98. print 'KQ = ' + CONVERT(CHAR(5), @kq)
  99.  
  100. DECLARE @kq INT
  101. EXEC tt_Cau3 'SP03', N'Bàn học', N'đen', 50, 500000, @kq output
  102. print 'KQ = ' + CONVERT(CHAR(5), @kq)
  103.  
  104. DECLARE @kq INT
  105. EXEC tt_Cau3 'SP04', N'Bàn học', N'đen', 50, 500000, @kq output
  106. print 'KQ = ' + CONVERT(CHAR(5), @kq)
  107.  
  108. GO
  109. --Câu 4:
  110. CREATE TRIGGER tg_Cau4
  111. ON Xuat
  112. FOR INSERT
  113. AS
  114. BEGIN
  115.     IF EXISTS (SELECT * FROM inserted JOIN SANPHAM ON inserted.MaSP = SANPHAM.MaSP
  116.                 WHERE SoLuongX > soluong)
  117.         BEGIN
  118.             print N'Số lượng không đủ'
  119.             ROLLBACK tran
  120.         END
  121.     UPDATE SANPHAM SET soluong = soluong - SoLuongX
  122.     FROM inserted JOIN SANPHAM ON inserted.MaSP = SANPHAM.MaSP
  123. END
  124.  
  125. SELECT * FROM SANPHAM
  126. SELECT * FROM Xuat
  127.  
  128. INSERT INTO Xuat VALUES ('X001', 'SP01', 10, '2021-5-5')
  129. INSERT INTO Xuat VALUES ('X003', 'SP01', 60, '2021-5-5')
  130. INSERT INTO Xuat VALUES ('X003', 'SP01', 20, '2021-5-5')
RAW Paste Data