Thi thử thực hành Cơ sở dữ liệu

CREATE DATABASE QLRESORT;
      GO
      USE QLRESORT;
      GO

      CREATE TABLE PHONG
      (
          MaPH CHAR(5) PRIMARY KEY,
          LoaiPhong VARCHAR(50),
          GiaPhong MONEY,
          TinhTrangPhong VARCHAR(20),
          DienTich FLOAT
      );

      CREATE TABLE DICHVU
      (
          MaDV CHAR(5) PRIMARY KEY,
          TenDV VARCHAR(50),
          DonGia MONEY
      );

      CREATE TABLE KHACHHANG
      (
          MaKH CHAR(5) PRIMARY KEY,
          HoTen VARCHAR(50),
          CCCD VARCHAR(12),
          NgaySinh SMALLDATETIME,
          GioiTinh VARCHAR(10),
          SoDT VARCHAR(15),
          Email VARCHAR(50),
          DiaChi VARCHAR(150)
      );

      CREATE TABLE DATPHONG
      (
          MaDatPhong CHAR(5) PRIMARY KEY,
          MaKH CHAR(5),
          MaPH CHAR(5),
          NgayDat SMALLDATETIME,
          NgayCheckIn SMALLDATETIME,
          NgayCheckOut SMALLDATETIME,
          TrangThai VARCHAR(20),

          FOREIGN KEY (MaKH) REFERENCES KHACHHANG(MaKH),
          FOREIGN KEY (MaPH) REFERENCES PHONG(MaPH)
      );

      CREATE TABLE THANHTOAN
      (
          MaThanhToan CHAR(5) PRIMARY KEY,
          MaDatPhong CHAR(5),
          SoTien MONEY,
          PTThanhToan VARCHAR(20),
          TTThanhToan VARCHAR(20),
          NgayThanhToan SMALLDATETIME,

          FOREIGN KEY (MaDatPhong) REFERENCES DATPHONG(MaDatPhong)
      );

      CREATE TABLE CTDICHVU
      (
          MaDatPhong CHAR(5),
          MaDV CHAR(5),
          NgaySuDung SMALLDATETIME,
          SoLuong INT,
          ThanhTien MONEY,

          PRIMARY KEY (MaDatPhong, MaDV, NgaySuDung),
          FOREIGN KEY (MaDatPhong) REFERENCES DATPHONG(MaDatPhong),
          FOREIGN KEY (MaDV) REFERENCES DICHVU(MaDV)
      );

ALTER TABLE PHONG ADD CONSTRAINT CK_PHONG_Deluxe
          CHECK (LoaiPhong <> 'Deluxe' OR GiaPhong > 500000);

ALTER TABLE KHACHHANG ADD CONSTRAINT CK_KHACHHANG_SoDT
          CHECK (SoDT LIKE '0%');

CREATE TRIGGER TRG_INSERTUPDATE_CTDICHVU_NgaySuDung
      ON CTDICHVU
      AFTER INSERT, UPDATE
      AS
      BEGIN
          IF EXISTS (
              SELECT *
              FROM inserted i
              JOIN DATPHONG dp ON i.MaDatPhong = dp.MaDatPhong
              WHERE i.NgaySuDung < dp.NgayCheckIn OR i.NgaySuDung > dp.NgayCheckOut
          )
          BEGIN
              RAISERROR (N'Ngay su dung dich vu khong nam trong thoi gian luu tru!', 16, 1);
              ROLLBACK TRANSACTION;
              RETURN;
          END
      END;
      GO

SELECT DISTINCT DV.MaDV, TenDV
      FROM CTDICHVU CT
      JOIN DATPHONG DP ON CT.MaDatPhong = DP.MaDatPhong
      JOIN PHONG PH ON DP.MaPH = PH.MaPH
      JOIN DICHVU DV ON CT.MaDV = DV.MaDV
      WHERE YEAR(NgaySuDung) = 2025 AND MONTH(NgaySuDung) BETWEEN 1 AND 6
        AND DP.TrangThai = 'Da xac nhan' AND GiaPhong > 2000000;

SELECT PH.MaPH, LoaiPhong, COUNT(DP.MaDatPhong) AS SoLanDat
      FROM PHONG PH
      JOIN DATPHONG DP ON PH.MaPH = DP.MaPH
      WHERE DP.TrangThai = 'Da xac nhan' AND YEAR(DP.NgayDat) BETWEEN 2024 AND 2025
      GROUP BY PH.MaPH, LoaiPhong
      ORDER BY SoLanDat DESC;

(SELECT DP.MaDatPhong
       FROM DATPHONG DP
       JOIN PHONG PH ON DP.MaPH = PH.MaPH
       JOIN CTDICHVU CT ON DP.MaDatPhong = CT.MaDatPhong
       JOIN DICHVU DV ON CT.MaDV = DV.MaDV
       WHERE PH.LoaiPhong = 'Deluxe' AND YEAR(DP.NgayCheckIn) = 2025
         AND CT.NgaySuDung = DP.NgayCheckIn AND DV.TenDV = 'Spa')
      INTERSECT
      (SELECT DP.MaDatPhong
       FROM DATPHONG DP
       JOIN PHONG PH ON DP.MaPH = PH.MaPH
       JOIN CTDICHVU CT ON DP.MaDatPhong = CT.MaDatPhong
       JOIN DICHVU DV ON CT.MaDV = DV.MaDV
       WHERE PH.LoaiPhong = 'Deluxe' AND YEAR(DP.NgayCheckIn) = 2025
         AND CT.NgaySuDung = DP.NgayCheckIn AND DV.TenDV = 'Massage');

SELECT DV.MaDV, TenDV, DP.MaDatPhong, SUM(ThanhTien) AS TongTienDichVu
      FROM CTDICHVU CT
      JOIN DATPHONG DP ON CT.MaDatPhong = DP.MaDatPhong
      JOIN DICHVU DV ON DV.MaDV = CT.MaDV
      WHERE DV.MaDV IN (
          SELECT TOP 1 WITH TIES MaDV
          FROM CTDICHVU
          WHERE YEAR(NgaySuDung) = 2025 AND MONTH(NgaySuDung) BETWEEN 7 AND 9
          GROUP BY MaDV
          ORDER BY SUM(SoLuong) DESC
      )
      AND DP.MaDatPhong IN (
          SELECT TOP 1 WITH TIES MaDatPhong
          FROM CTDICHVU
          WHERE YEAR(NgaySuDung) = 2025 AND MONTH(NgaySuDung) BETWEEN 7 AND 9
          GROUP BY MaDatPhong
          ORDER BY SUM(ThanhTien) ASC
      )
      GROUP BY DV.MaDV, TenDV, DP.MaDatPhong;

SELECT DV.MaDV, DV.TenDV
      FROM CTDICHVU CT
      JOIN DICHVU DV ON CT.MaDV = DV.MaDV
      JOIN DATPHONG DP ON DP.MaDatPhong = CT.MaDatPhong
      WHERE DV.TenDV <> 'Massage'
        AND DP.MaPH IN (
            SELECT TOP 3 WITH TIES DP2.MaPH
            FROM DATPHONG DP2
            JOIN CTDICHVU CT2 ON DP2.MaDatPhong = CT2.MaDatPhong
            WHERE DP2.TrangThai = 'Da xac nhan'
            GROUP BY DP2.MaPH
            ORDER BY SUM(CT2.ThanhTien) DESC
        )
      GROUP BY DV.MaDV, DV.TenDV
      HAVING COUNT(DISTINCT DP.MaPH) = 3;