CREATE DATABASE CAULACBO

ON

(NAME='CAULACBO',FILENAME='D:\TAILIEUHOC\TAI LIEU KI 7\CSDL NC\CAULACBO_DATA.MDF',

SIZE=2MB,MAXSIZE=100MB,FILEGROWTH=2MB)

LOG ON

(NAME='CAULACBO_LOG',FILENAME='D:\TAILIEUHOC\TAI LIEU KI 7\CSDL NC\CAULACBO_LOG.LDF',

SIZE=2MB, MAXSIZE=100MB,FILEGROWTH=2MB)

USE CAULACBO

GO

CREATE TABLE CAULACBO( MACLB CHAR(4) CONSTRAINT PK_CLB PRIMARY KEY,

TENCLB NVARCHAR(100),TENKHOA NVARCHAR(100))

CREATE TABLE GIANGVIEN(MAGV CHAR(4) CONSTRAINT PK_GV PRIMARY KEY ,

TENGV NVARCHAR(100),MACLB CHAR(4) CONSTRAINT FK_CLB_GV REFERENCES CAULACBO(MACLB))

CREATE TABLE SINHVIEN(MASV CHAR(4)CONSTRAINT PK_SV PRIMARY KEY,

TENSV NVARCHAR(100),MACLB CHAR(4) CONSTRAINT FK_CLB_SV REFERENCES CAULACBO(MACLB))

CREATE TABLE LOPNK(MALOPNK CHAR(4) CONSTRAINT PK_LOPNK PRIMARY KEY ,

NGAYMO DATETIME, MAGV CHAR(4) CONSTRAINT FK_LOPNK_GV REFERENCES GIANGVIEN(MAGV),HOCPHI INT)

CREATE TABLE BIENLAI(SOBIENLAI CHAR(4) CONSTRAINT PK_BIENLAI PRIMARY KEY ,

THANG INT, NAM INT, MALOPNK CHAR(4) CONSTRAINT FK_LOPNK_BIENLAI REFERENCES LOPNK(MALOPNK),

MASV CHAR(4) CONSTRAINT FK_SINHVIEN_BIENLAI REFERENCES SINHVIEN(MASV),SOTIEN INT)

SELECT * INTO CLB1 FROM CAULACBO WHERE TENKHOA='K1'

SELECT * INTO CLB2 FROM CAULACBO WHERE TENKHOA='K2'

SELECT * INTO CLB3 FROM CAULACBO WHERE TENKHOA='K3'

SELECT * INTO GV1 FROM GIANGVIEN WHERE MACLB IN ( SELECT MACLB FROM CLB1)

SELECT *

INTO GV2

FROM GIANGVIEN

WHERE MACLB IN(SELECT MACLB FROM CLB2)

SELECT *

INTO GV3

FROM GIANGVIEN

WHERE MACLB IN(SELECT MACLB FROM CLB3)

SELECT *

INTO SV1

FROM SINHVIEN

WHERE MACLB IN(SELECT MACLB FROM CLB1)

SELECT *

INTO SV2

FROM SINHVIEN

WHERE MACLB IN(SELECT MACLB FROM CLB2)

SELECT *

INTO SV3

FROM SINHVIEN

WHERE MACLB IN(SELECT MACLB FROM CLB3)

SELECT *

INTO LNK1

FROM LOPNK

WHERE MAGV IN(SELECT MAGV FROM GV1)

SELECT *

INTO LNK2

FROM LOPNK

WHERE MAGV IN(SELECT MAGV FROM GV2)

SELECT *

INTO LNK3

FROM LOPNK

WHERE MAGV IN(SELECT MAGV FROM GV3)

SELECT *

INTO BL1

FROM BIENLAI

WHERE MALOPNK IN(SELECT MALOPNK FROM LNK1)

SELECT *

INTO BL2

FROM BIENLAI

WHERE MALOPNK IN(SELECT MALOPNK FROM LNK2)

SELECT *

INTO BL3

FROM BIENLAI

WHERE MALOPNK IN(SELECT MALOPNK FROM LNK3)

UPDATE CAULACBO 

SET 

MACLB='1', TENKHOA='K2'

WHERE

MACLB='5' AND TENKHOA='K3'

IF EXISTS( SELECT * FROM CLB3 WHERE MACLB=5)

BEGIN

UPDATE CLB3

SET

MACLB='1', TENKHOA='K2'

WHERE MACLB='5'

INSERT INTO CLB2

SELECT * FROM CLB3 WHERE MACLB='1' AND TENKHOA='K2'

DELETE FROM CLB3 WHERE MACLB='1' AND TENKHOA='K2'

END

ELSE

PRINT 'KHONG TIM THAY'

SELECT SOBIENLAI,THANG,NAM,SOTIEN FROM BIENLAI

 WHERE MALOPNK IN(SELECT MALOPNK FROM LOPNK

WHERE MAGV='GV5')

IF EXISTS(SELECT SOBIENLAI,THANG,NAM,SOTIEN FROM BL1

WHERE MALOPNK IN(SELECT MALOPNK FROM LNK1 WHERE MAGV='GV5'))

SELECT SOBIENLAI,THANG,NAM,SOTIEN FROM BL1

WHERE MALOPNK IN(SELECT MALOPNK FROM LNK1 WHERE MAGV='GV5')

ELSE

IF EXISTS(SELECT SOBIENLAI,THANG,NAM,SOTIEN FROM BL2

WHERE MALOPNK IN(SELECT MALOPNK FROM LNK2 WHERE MAGV='GV5'))

SELECT SOBIENLAI,THANG,NAM,SOTIEN FROM BL2

WHERE MALOPNK IN(SELECT MALOPNK FROM LNK2 WHERE MAGV='GV5')

ELSE

SELECT SOBIENLAI,THANG,NAM,SOTIEN FROM BL3

WHERE MALOPNK IN(SELECT MALOPNK FROM LNK3 WHERE MAGV='GV5')

CREATE PROC SP_4A1 @MALOPNK CHAR(4), @MASV CHAR(4)

AS

SELECT SUM(SOTIEN) AS TONGTIEN FROM BIENLAI 

WHERE MALOPNK=@MALOPNK AND MASV=@MASV

exec SP_4A1 'L1','SV1'

CREATE PROC SP_42 @MALOPNK CHAR(4), @MASV CHAR(4)

AS

BEGIN

IF EXISTS(SELECT * FROM Bl1 WHERE MALOPNK=@MALOPNK AND MASV=@MASV)

SELECT SUM(SOTIEN) AS TONGTIEN FROM BL1

WHERE MALOPNK=@MALOPNK AND MASV=@MASV

ELSE IF EXISTS(SELECT * FROM BL2 

WHERE MALOPNK=@MALOPNK AND MASV=@MASV)

SELECT SUM(SOTIEN) AS TONGTIEN FROM BL2

WHERE MALOPNK=@MALOPNK AND MASV=@MASV

ELSE

SELECT SUM(SOTIEN) AS TONGTIEN FROM BL3

WHERE MALOPNK=@MALOPNK AND MASV=@MASV

END

exec sp_42 'L1','SV1'

SELECT MALOPNK, NGAYMO FROM LOPNK WHERE MONTH(NGAYMO)=8 AND YEAR(NGAYMO)=1998

IF EXISTS(SELECT MALOPNK, NGAYMO FROM LNK1 WHERE MONTH(NGAYMO)=8 AND YEAR(NGAYMO)=1998

)

SELECT MALOPNK, NGAYMO FROM LNK1 WHERE MONTH(NGAYMO)=8 AND YEAR(NGAYMO)=1998

ELSE

IF EXISTS( SELECT MALOPNK, NGAYMO FROM LNK2 WHERE MONTH(NGAYMO)=8 AND YEAR(NGAYMO)=1998)

SELECT MALOPNK, NGAYMO FROM LNK2 WHERE MONTH(NGAYMO)=8 AND YEAR(NGAYMO)=1998

ELSE

SELECT MALOPNK, NGAYMO FROM LNK3 WHERE MONTH(NGAYMO)=8 AND YEAR(NGAYMO)=1998