· 4 years ago · Jun 11, 2021, 03:58 AM
1create database QLNhapXuat
2
3go
4use QLNhapXuat
5go
6
7--a, Creata table
8create 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
17create 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
26create 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
35GO
36--b, Insert data
37
38insert into SANPHAM values
39('sp01','sanpham1','xanh',1,1),
40('sp02','sanpham2','vang',2,2),
41('sp03','sanpham3','cam',3,3)
42
43insert 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
48insert into Xuat values
49('Xuat1','sp01',1,'2021-4-6'),
50('Xuat2','sp02',2,'2021-5-6')
51
52GO
53--Display data
54
55select * from SANPHAM
56select * from Nhap
57select * from Xuat
58
59GO
60--Câu 2:
61CREATE FUNCTION Fn_Cau2 (@TenSP nvarchar(30))
62RETURNS money
63AS
64begin
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
70end
71
72select dbo.Fn_Cau2 ('sanpham1')
73select dbo.Fn_Cau2 ('sanpham2')
74
75GO
76--Câu 3:
77CREATE PROC tt_Cau3
78@masp char(4), @TenSP nvarchar(50), @mausac nvarchar(50), @soluong int, @giaban money,
79@kq int output
80AS
81begin
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
94end
95
96declare @kq int
97exec tt_Cau3 'SP04', N'Quạt điện', N'đen', 50, 500000, @kq output
98print 'KQ = ' + convert(char(5), @kq)
99
100declare @kq int
101exec tt_Cau3 'SP03', N'Bàn học', N'đen', 50, 500000, @kq output
102print 'KQ = ' + convert(char(5), @kq)
103
104declare @kq int
105exec tt_Cau3 'SP04', N'Bàn học', N'đen', 50, 500000, @kq output
106print 'KQ = ' + convert(char(5), @kq)
107
108GO
109--Câu 4:
110CREATE TRIGGER tg_Cau4
111ON Xuat
112FOR INSERT
113AS
114begin
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
123end
124
125SELECT * FROM SANPHAM
126SELECT * FROM Xuat
127
128INSERT INTO Xuat VALUES ('X001', 'SP01', 10, '2021-5-5')
129INSERT INTO Xuat VALUES ('X003', 'SP01', 60, '2021-5-5')
130INSERT INTO Xuat VALUES ('X003', 'SP01', 20, '2021-5-5')