OT2.sql

CREATE DATABASE QLTHUOC
USE QLTHUOC

CREATE TABLE NSX
(
MANSX CHAR(10) PRIMARY KEY NOT NULL,
TENNSX NVARCHAR(20) NOT NULL,
DC NVARCHAR(20) NOT NULL, 
DT NVARCHAR(20) NOT NULL
)

CREATE TABLE THUOC 
(
MATHUOC CHAR(10) NOT NULL PRIMARY KEY,
TENTHUOC NVARCHAR(20) NOT NULL,
SLCO INT NOT NULL,
SOLO INT NOT NULL,
NGAYSX DATETIME NOT NULL,
HANSUDUNG DATETIME NOT NULL,
MANSX CHAR(10),
CONSTRAINT FK1 FOREIGN KEY(MANSX) REFERENCES NSX(MANSX)
)

CREATE TABLE PN
(
SOPN CHAR(10) NOT NULL,
MATHUOC CHAR(10) NOT NULL,
NGAYNHAP DATETIME NOT NULL,
SOLUONG INT NOT NULL,
DONGIA FLOAT NOT NULL,
PRIMARY KEY(SOPN, MATHUOC),
CONSTRAINT FK2 FOREIGN KEY(MATHUOC) REFERENCES THUOC(MATHUOC)
)

INSERT INTO NSX VALUES
('NSX1', N'HỒNG HÀ', N'HÀ NỘI', 'ABC'),
('NSX2', N'HƯNG THỊNH', N'HÀ NỘI', 'BCD'),
('NSX3', N'HẢI TIẾN', N'TP HCM', 'CDE')

INSERT INTO THUOC VALUES
('T1', 'CÚM', 20, 1, '2/22/2019', '2/22/2021', 'NSX1'),
('T2', 'HO', 13, 23, '2/24/2020', '10/22/2020', 'NSX3'),
('T4', 'SỐT', 19, 10, '2/22/2018', '10/22/2021', 'NSX2'),
('T3', 'CẢM', 20, 1, '7/22/2020', '7/22/2021', 'NSX1')

INSERT INTO PN VALUES
('PN1', 'T1', '3/1/2019', 100, 10000),
('PN2', 'T3', '3/2/2019', 100, 30000),
('PN3', 'T2', '3/10/2018', 200, 40000),
('PN4', 'T4', '3/1/2018', 130, 15000),
('PN6', 'T1', '3/1/2019', 170, 19000),
('PN5', 'T3', '3/2/2019', 200, 30000)

-------------------------------------------------
-- CÂU 1
CREATE FUNCTION TONG(@MATHUOC CHAR(10))
RETURNS INT
AS
BEGIN
	DECLARE @TONGSLN INT 
	SELECT @TONGSLN = SUM(SOLUONG)
	FROM PN
	WHERE MATHUOC = @MATHUOC
	GROUP BY MATHUOC
END

--
CREATE FUNCTION THONGKE(@MAT)




-----------------------------------------------------
-- CÂU 3
CREATE PROC SP_THEM(@MANSX CHAR(10), @TENNSX NVARCHAR(20), @DC NVARCHAR(20), @DT CHAR(10))
AS
BEGIN
	IF(EXISTS(SELECT MANSX FROM NSX WHERE MANSX = @MANSX))
		PRINT('DA TON TAI')
	ELSE
		INSERT INTO NSX VALUES(@MANSX, @TENNSX, @DC, @DT)
END



----------------------------------------------
CREATE PROC SEARCH(@MATHUOC CHAR(10))
AS
BEGIN
	IF (NOT EXISTS(SELECT * FROM THUOC WHERE MATHUOC = @MATHUOC))
		PRINT('KHONG CO MA THUOC')
	ELSE
		PRINT('TIM THAY MA THUOC')
		SELECT * FROM THUOC WHERE MATHUOC = @MATHUOC
END