CREATE DATABASE QUANLYKHACHSAN

USE QUANLYKHACHSAN

CREATE TABLE KHACHHANG(

MAK CHAR(10) PRIMARY KEY,

TENK CHAR(40) NOT NULL,

DIACHI CHAR(40),

SDT CHAR(15)

DROP TABLE PHONG (

MAP CHAR(10) PRIMARY KEY,

TENP CHAR(30) NOT NULL,

LOAIP CHAR(20),            --LOAI PHONG--

DONGIA FLOAT

CREATE TABLE DATPHONG(

MAK CHAR(10),

MAP CHAR(10),

NGAYNHAN    SMALLDATETIME,    --NGAY NHAN PHONG--

NGAYTRA    SMALLDATETIME,        --NGAY TRA PHONG--

CONSTRAINT KC_DATPHONG PRIMARY KEY(MAK,MAP,NGAYNHAN),

CONSTRAINT KN_DATPHONG FOREIGN KEY (MAK) REFERENCES KHACHHANG(MAK),

CONSTRAINT KN1_DATPHONG FOREIGN KEY (MAP) REFERENCES PHONG(MAP),

/*2.TAO THU TUC DE TANG GIA CUA TAT CA CAC PHONG TRONG KHACH SAN LEN 5%*/

CREATE PROC VD2

AS

UPDATE PHONG

SET DONGIA=DONGIA*1.05

EXEC VD2

/*3.TAO VIEW DE TONG HOP THONG TIN VE CAC KHACH HANG HIEN TAI DANG THUE PHONG TAI KHACH SAN*/

CREATE VIEW VD3

AS

SELECT * FROM KHACHHANG

WHERE MAK IN(SELECT MAK FROM DATPHONG

                WHERE NGAYTRA>GETDATE())

/*4.TAO VIEW DE TONG HOP THONG TIN HOA DON THANH TOAN CUA KHACH HANG(HOA DON GOM CAC

THUOC TINH SAU:(MAK,TENK,MAP,DONGIA,NGAYNHAN,NGAYTRA,THANH TIEN)*/

CREATE VIEW VD4

AS

SELECT KHACHHANG.MAK,TENK,PHONG.MAP,DONGIA,NGAYNHAN,NGAYTRA,

CONVERT(INT,(NGAYTRA-NGAYNHAN))*DONGIA AS THANHTIEN

FROM KHACHHANG,PHONG,DATPHONG

WHERE KHACHHANG.MAK=DATPHONG.MAK AND PHONG.MAP=DATPHONG.MAP

GROUP BY KHACHHANG.MAK,TENK,PHONG.MAP,DONGIA,NGAYNHAN,NGAYTRA

/*5.TAO VIEW DE TONG HOP THONG TIN VE CAC KHACH HANG CO SO NGAY DAT PHONG LON NHAT*/

CREATE VIEW TG

AS

SELECT KHACHHANG.MAK,TENK,DIACHI,SDT,SUM(CONVERT(INT,(NGAYTRA-NGAYNHAN))) AS NGAYTHUE

FROM KHACHHANG,DATPHONG

WHERE KHACHHANG.MAK=DATPHONG.MAK

GROUP BY KHACHHANG.MAK,TENK,DIACHI,SDT

CREATE VIEW NGAYMAX

AS

SELECT * FROM TG

WHERE NGAYTHUE IN (SELECT MAX(NGAYTHUE)

                    FROM TG)

SELECT * FROM NGAYMAX

/*6.TAO THU TUC DE DUA RA DANH SACH CAC PHONG HIEN TAI CHUA CO KHACH HANG DAT PHONG*/

CREATE PROC VD6

AS

SELECT * FROM PHONG

WHERE MAP NOT IN (SELECT MAP FROM DATPHONG

                WHERE NGAYTRA>GETDATE())

EXEC VD6

/*7.TAO THU TUC DE DUA RA DANH SACH KHACH HANG NGAY HOM NAY PHAI TRA PHONG*/

CREATE PROC VD7

AS

SELECT * FROM KHACHHANG

WHERE MAK IN(SELECT MAK FROM DATPHONG

                WHERE CONVERT(CHAR(10),NGAYTRA)=CONVERT(CHAR(10),GETDATE()))

EXEC VD7

/*8.TAO TRIGGER DE KIEM TRA DU LIEU KHI CAP NHAT VAO BANG PHONG PHAI DAM BAO DU LIEU DONGIA>0*/

CREATE TRIGGER TG8

ON PHONG

FOR INSERT

AS

IF EXISTS (SELECT DONGIA FROM PHONG

            WHERE DONGIA<0)

BEGIN

    PRINT 'DU LIEU NHAP KHONG HOP LE'

    ROLLBACK TRAN

END

ELSE PRINT 'DU LIEU NHAP THANH CONG'

/*9.TAO TRIGGER DE KIEM TRA DU LIEU KHI NHAP VAO BANG DATPHONG PHAI DAM BAO NGAYTRA PHONG LUON

LON HON HOAC BANG NGAY NHAN PHONG*/

 ALTER TRIGGER TG9

ON DATPHONG

FOR INSERT

AS

IF EXISTS (SELECT NGAYTRA,NGAYNHAN FROM DATPHONG

            WHERE CONVERT(INT,NGAYTRA)<CONVERT(INT,NGAYNHAN))

BEGIN

    PRINT 'DU LIEU NHAP KHONG HOP LE'

    ROLLBACK TRAN

END

ELSE PRINT 'DU LIEU NHAP THANH CONG'

INSERT INTO DATPHONG

VALUES('K03','P03','11/20/2010','11/19/2010')

/*10.DUNG KIEU DU LIEU CURSOR DE DUA RA DANH SACH KHACH HANG NGAY HOM NAY PHAI TRA PHONG*/

--KHAI BAO--

DECLARE CS10 CURSOR FOR

SELECT KHACHHANG.* FROM KHACHHANG

WHERE MAK IN(SELECT MAK FROM DATPHONG

                WHERE CONVERT(CHAR(10),NGAYTRA)=CONVERT(CHAR(10),GETDATE()))

--MO--

OPEN CS10

--XU LY--

PRINT 'DANH SACH KHACH HANG NGAY HOM NAY PHAI TRA PHONG LA'

PRINT 'MAK         TENK                                DIACHI                            SDT'

DECLARE @MAK CHAR(10),@TENK CHAR(30),@DIACHI CHAR(30),@SDT CHAR(15)

FETCH NEXT FROM CS10

INTO @MAK,@TENK,@DIACHI,@SDT

WHILE @@FETCH_STATUS=0

BEGIN

    PRINT @MAK+@TENK+@DIACHI+@SDT

    FETCH NEXT FROM CS10

    INTO @MAK,@TENK,@DIACHI,@SDT

END

--DONG--

CLOSE CS10

--HUY--

DEALLOCATE CS10