· 7 years ago · Dec 25, 2018, 07:38 PM
1CREATE DATABASE QuanLiDuocPham
2GO
3
4USE QuanLiDuocPham
5GO
6
7CREATE TABLE NHACUNGCAP
8(
9 MANCC VARCHAR(5),
10 TENNCC VARCHAR(30),
11 QUOCGIA VARCHAR(30),
12 LOAINCC VARCHAR(30)
13 CONSTRAINT PK_NCC PRIMARY KEY (MANCC)
14)
15
16CREATE TABLE DUOCPHAM
17(
18 MADP VARCHAR(4),
19 TENDP VARCHAR(30),
20 LOAIDP VARCHAR(30),
21 GIA MONEY
22 CONSTRAINT PK_DP PRIMARY KEY (MADP)
23)
24
25CREATE TABLE PHIEUNHAP
26(
27 SOPN VARCHAR(5),
28 NGNHAP SMALLDATETIME,
29 MANCC VARCHAR(5),
30 LOAINHAP VARCHAR(30)
31 CONSTRAINT PK_PN PRIMARY KEY (SOPN)
32)
33
34CREATE TABLE CTPN
35(
36 SOPN VARCHAR(5),
37 MADP VARCHAR(4),
38 SOLUONG INT
39 CONSTRAINT PK_CT PRIMARY KEY (SOPN, MADP)
40)
41
42INSERT INTO NHACUNGCAP VALUES('NCC01', 'Phuc Hung', 'Viet Nam', 'Thuong xuyen')
43INSERT INTO NHACUNGCAP VALUES('NCC02', 'J. B. Pharmaceuticals', 'India', 'Vang lai')
44INSERT INTO NHACUNGCAP VALUES('NCC03', 'Sapharco', 'Singapore', 'Vang lai')
45
46INSERT INTO DUOCPHAM VALUES('DP01', 'Thuoc ho PH', 'Siro', 120.000)
47INSERT INTO DUOCPHAM VALUES('DP02', 'Zecuf Herbal CouchRemedy', 'Vien nen', 200.000)
48INSERT INTO DUOCPHAM VALUES('DP03', 'Cotrim', 'Vien sui', 80.000)
49
50SET DATEFORMAT DMY
51INSERT INTO PHIEUNHAP VALUES('00001', '22/11/2017', 'NCC01', 'Noi dia')
52INSERT INTO PHIEUNHAP VALUES('00002', '04/12/2017', 'NCC03', 'Nhap khau')
53INSERT INTO PHIEUNHAP VALUES('00003', '10/12/2017', 'NCC02', 'Nhap khau')
54
55INSERT INTO CTPN VALUES('00001', 'DP01', 100)
56INSERT INTO CTPN VALUES('00001', 'DP02', 200)
57INSERT INTO CTPN VALUES('00003', 'DP03', 543)
58
59ALTER TABLE PHIEUNHAP ADD CONSTRAINT FK_NCC FOREIGN KEY (MANCC) REFERENCES NHACUNGCAP(MANCC)
60ALTER TABLE CTPN ADD CONSTRAINT FK_SOPN FOREIGN KEY (SOPN) REFERENCES PHIEUNHAP(SOPN)
61ALTER TABLE CTPN ADD CONSTRAINT FK_SODP FOREIGN KEY (MADP) REFERENCES DUOCPHAM(MADP)
62
63-- Câu 3: Hiện thá»±c rà ng buá»™c toà n vẹn sau: Tất cả các dược phẩm có loại là Siro Ä‘á»u có giá lá»›n hÆ¡n 100.000Ä‘
64CREATE TRIGGER trg_chk_GiaSiro ON DUOCPHAM
65FOR INSERT, UPDATE
66AS
67BEGIN
68 DECLARE @LOAIDP VARCHAR(30), @GIA MONEY
69 SELECT @LOAIDP = LOAIDP, @GIA = GIA FROM INSERTED
70 IF (@LOAIDP = 'Siro' AND @GIA <= 100000)
71 BEGIN
72 PRINT('Tat ca duoc pham Siro co gia > 100.000d')
73 ROLLBACK TRAN
74 END
75END
76
77-- Câu 4: Hiện thực rà ng buộc toà n vẹn sau:
78-- Phiếu nháºp của những nhà cung cấp ở những quốc gia khác Việt Nam Ä‘á»u có loại nháºp là Nháºp khẩu
79CREATE TRIGGER trg_chk_NhaCungCap ON PHIEUNHAP
80FOR INSERT, UPDATE
81AS
82BEGIN
83 IF EXISTS (SELECT * FROM NHACUNGCAP NCC, INSERTED I
84 WHERE NCC.MANCC = I.MANCC AND QUOCGIA <> 'Viet Nam' AND LOAINHAP <> 'Nhap khau')
85 BEGIN
86 PRINT('Phieu nhap cua nha cung cap o quoc gia khac VN co loai nhap la Nhap khau')
87 ROLLBACK TRAN
88 END
89END
90
91CREATE TRIGGER trg_chk_upd_NhaCungCap ON NHACUNGCAP
92FOR UPDATE
93AS
94BEGIN
95 IF EXISTS (SELECT * FROM INSERTED I, PHIEUNHAP PN
96 WHERE I.MANCC = PN.MANCC AND QUOCGIA <> 'Viet Nam' AND LOAINHAP <> 'Nhap khau')
97 BEGIN
98 PRINT('Phieu nhap cua nha cung cap o quoc gia khac VN co loai nhap la Nhap khau')
99 ROLLBACK TRAN
100 END
101END
102
103-- Câu 5: Tìm tất cả các phiếu nháºp có ngà y nháºp trong tháng 12 năm 2017, sắp xếp kết quả tăng dần theo ngà y nháºp
104SELECT *
105FROM PHIEUNHAP
106WHERE MONTH(NGNHAP) = 12 AND YEAR(NGNHAP) = 2017
107ORDER BY DAY(NGNHAP) ASC
108
109-- Câu 6: Tìm dược phẩm được nháºp số lượng nhiá»u nhất trong năm 2017
110SELECT *
111FROM DUOCPHAM DP
112WHERE MADP IN
113(
114 SELECT TOP 1 WITH TIES MADP
115 FROM CTPN
116 GROUP BY MADP
117 ORDER BY SUM(SOLUONG) DESC
118)
119
120-- Câu 7: Tìm dược phẩm chỉ có nhà cung cấp thÆ°á»ng xuyên (LOAINCC là Thuong xuyen)
121-- cung cấp nhà cung cấp vãng lai (LOAINCC là Vang lai) không cung cấp.
122SELECT *
123FROM DUOCPHAM DP
124WHERE EXISTS
125(
126 SELECT *
127 FROM PHIEUNHAP PN, CTPN CT, NHACUNGCAP NCC
128 WHERE DP.MADP = CT.MADP AND PN.SOPN = CT.SOPN AND NCC.MANCC = PN.MANCC
129 AND LOAINCC = 'Thuong xuyen'
130)
131AND NOT EXISTS
132(
133 SELECT *
134 FROM PHIEUNHAP PN, CTPN CT, NHACUNGCAP NCC
135 WHERE DP.MADP = CT.MADP AND PN.SOPN = CT.SOPN AND NCC.MANCC = PN.MANCC
136 AND LOAINCC = 'Vang lai'
137)
138
139-- Câu 8: Tìm nhà cung cấp đã từng cung cấp tất cả những dược phẩm có giá trên 100.000đ trong năm 2017
140SELECT *
141FROM NHACUNGCAP NCC
142WHERE NOT EXISTS
143(
144 SELECT *
145 FROM DUOCPHAM DP
146 WHERE GIA > 100.000
147 AND NOT EXISTS
148 (
149 SELECT *
150 FROM PHIEUNHAP PN, CTPN CT
151 WHERE YEAR(NGNHAP) = 2017
152 AND PN.MANCC = NCC.MANCC AND PN.SOPN = CT.SOPN
153 AND CT.MADP = DP.MADP
154 )
155)