Qlbenhvien.sql

CREATE DATABASE QLBENHVIENN
USE QLBENHVIENN

CREATE TABLE BENHVIEN
(
MABV CHAR(10) NOT NULL PRIMARY KEY,
TENBV NVARCHAR(20) NOT NULL
)

CREATE TABLE KHOAKHAM 
(
MAKHOA CHAR(10) NOT NULL PRIMARY KEY,
TENKHOA NVARCHAR(20) NOT NULL,
SOBENHNHAN INT NOT NULL,
MABV CHAR(10) NOT NULL,
CONSTRAINT FK1 FOREIGN KEY(MABV) REFERENCES BENHVIEN(MABV)
)

CREATE TABLE BENHNHAN
(
MABN CHAR(10) NOT NULL PRIMARY KEY,
HOTEN NVARCHAR(20) NOT NULL,
NGAYSINH DATETIME NOT NULL,
GIOITINH BIT NOT NULL,
SONGAYNV INT NOT NULL,
MAKHOA CHAR(10) NOT NULL,
CONSTRAINT FK2 FOREIGN KEY(MAKHOA) REFERENCES KHOAKHAM(MAKHOA)
)

INSERT INTO BENHVIEN VALUES
('BV1', N'BẠCH MAI'),
('BV2', N'VIỆT ĐỨC')

INSERT INTO KHOAKHAM VALUES
('K1', N'NGOẠI KHOA', 20, 'BV1'),
('K2', N'THẦN KINH', 10, 'BV2')

INSERT INTO BENHNHAN VALUES
('7', N'ĐỖ ĐẠT', '3/10/2001', 0, 10, 'K1'),
('6', N'PHẠM THẾ HÙNG', '2/22/2001', 0, 21, 'K1'),
('1', N'ĐỖ NGỌC ĐỨC', '3/22/2001', 1, 10, 'K1'),
('2', N'ĐỖ NGỌC', '3/23/2001', 1, 11, 'K1'),
('3', N'ĐỖ NGỌC HOÀI', '3/24/2001', 0, 7, 'K2'),
('4', N'ĐỖ ĐỨC', '3/27/2001', 0, 13, 'K2'),
('5', N'ĐỖ', '3/28/2001', 1, 20, 'K1')

SELECT * FROM BENHVIEN
SELECT * FROM KHOAKHAM
SELECT * FROM BENHNHAN

SELECT MABN, HOTEN, YEAR(GETDATE()) - YEAR(NGAYSINH)
FROM BENHNHAN
WHERE YEAR(NGAYSINH) = (SELECT MIN(YEAR(NGAYSINH)) FROM BENHNHAN)


ALTER FUNCTION CAU3(@MABN CHAR(10))
RETURNS @THONGKE TABLE(MABN CHAR(10), HOTEN NVARCHAR(20), NGAYSINH DATETIME, GIOITINH NVARCHAR(10), TENKHOA NVARCHAR(20), TUOI INT)
AS
BEGIN
	INSERT INTO @THONGKE
	SELECT MABN, HOTEN, NGAYSINH, CASE GIOITINH 
	WHEN 1 THEN N'NAM'
	WHEN 0 THEN N'NỮ' END AS 'GIỚI TÍNH', TENKHOA, YEAR(GETDATE()) - YEAR(NGAYSINH)
	FROM BENHVIEN INNER JOIN KHOAKHAM ON BENHVIEN.MABV = KHOAKHAM.MABV
		          INNER JOIN BENHNHAN ON KHOAKHAM.MAKHOA = BENHNHAN.MAKHOA
	WHERE MABN = @MABN
	RETURN
END

SELECT * FROM DBO.CAU3('3')