[SQL] – Revise Query

Dưới là đề để tạo CSDL:

pass:DulieuTHCSDL

https://lookaside.fbsbx.com/file/DulieuTHCSDL.pdf?token=AWy2_DEmFubIT2AbxAZwoeWFDu9Bup4Lus-THP34Cxf7uD5-6YzTti-ju6VewgrOBqQP6It1NezwWE-1OIGvpiuUm6yufuCJL9eykjT-DDZ3sch8_wcXbxKW8K9roa2XPwdqxG04HKOE5P9Qvr6-ueSPIR0SYurad32Y8G-EKC9Qvw

Câu 1: Thêm các ràng buộc sau
1. Đơn giá lớn hơn 0 và Ngày trả lớn hơn hay bằng ngày thuê.
2. Tạo ràng buộc giá trị mặc định ban đầu cho ngày thuê là ngày hiện hành.
Câu 2:
Dùng lệnh INSERT nhập mỗi bảng 1 record, dữ liệu tùy ý nhưng dữ liệu khách hàng là thông tin của sinh viên.
Câu 3:
1. Liệt kê các phòng mà khách hàng thuê chưa trả. Thông tin bao gồm: Mã phòng, Mô tả, Loại phòng được sắp xếp theo phòng, nếu cùng phòng thì sắp xếp theo Số ngày ở giảm dần.
2.Liệt kê các phòng có đơn giá phòng rẻ nhất . Thông tin bao gồm: Mã phòng, Mô tả và Đơn giá phòng.
3. Liệt kê danh sách các khách hàng có ký tự đầu của mã khách hàng là A đến H
4.Liệt kê các phòng chưa có khách hàng thuê.
5.Thống kê tổng số phòng có mã là 1 và 3. Gồm Loại phòng và Số lượng phòng.
6.Cho biết khách hàng nào có tổng tiền phải trả trên 2.000.000
7.Liệt kê danh sách các khách hàng đã thuê tất cả các phòng ở tầng 1 dãy nhà A.
8. Dùng lệnh Select into tạo bảng HoaDonTinhTien cho những khách hàng đã trả phòng từ tháng 6 đến tháng 12 của năm 2007 gồm MAKH, TENKH, QUOCTICH, DIENTHOAI, TONGTIEN được sắp xếp theo TenKH. Trong đó TONGTIEN được tính bằng (Số ngày ở * Đơn giá) – Giảm giá
Câu 4:
Thêm vào bảng ThuePhong Field SoNgay và GiamGia, kiểu dữ liệu sinh viên tự quy định; Sau đó dùng lệnh UPDATE cập nhật dữ liệu cho 2 field này theo công thức sau:
+ Nếu ngày thuê và ngày trả trong cùng ngày thì được tính một ngày , còn lại số ngày thuê phòng (SoNgay) bằng NgayTra – NgayThue
+ Giảm giá: giảm 200.000 cho khách hàng có số ngày ở trên 20 ngày.

HƯỚNG DẪN:

–Bài 1:
–Câu 1.
ALTER TABLE [dbo].[LoaiPhong] ADD CONSTRAINT PRICE CHECK ( [DonGiaPhong] >0 )
ALTER TABLE [dbo].[ThuePhong] ADD CONSTRAINT CHK_DATE CHECK ([NgayTra] >= [NgayThue])
–Câu 2.
ALTER TABLE [dbo].[ThuePhong] ADD CONSTRAINT NGAYTHUE_DF DEFAULT GETDATE() FOR [NgayThue]

-Bài 2:
INSERT INTO [dbo].[KhachHang] VALUES (‘12052017′,’Shouko’,’Japan’,’08***’)
INSERT INTO [dbo].[LoaiPhong] VALUES (4, 999998)
INSERT INTO [dbo].[Phong] VALUES (‘B11′,4,N’Phòng số 11 tầng 4 dãy nhà B’, 4)
INSERT INTO [dbo].[ThuePhong] VALUES (‘15026121′,’B11′,”,’12/06/2019’)

–Bài 3
–Cau 1:
SELECT P.[MaPHG],P.[MoTa],P.[LoaiPHG] –DATEDIFF ( DD , TP.[NgayThue] , TP.[NgayTra] ) AS SoNgay
FROM [dbo].[KhachHang] KH JOIN [dbo].[ThuePhong] TP on KH.MaKH = TP.MaKH
JOIN [dbo].[Phong] P on P.MaPHG = tp.MaPHG
WHERE NgayTra IS NULL
–GROUP BY P.[MaPHG],P.[MoTa],P.[LoaiPHG] ORDER BY P.[MaPHG], DATEDIFF ( DD , TP.[NgayThue] , TP.[NgayTra] ) DESC

–Cau 2:
SELECT P.[MaPHG],P.[MoTa],LP.[DonGiaPhong] FROM [dbo].[Phong] P JOIN [dbo].[LoaiPhong] LP ON P.LoaiPHG = LP.LoaiPHG
WHERE LP.[DonGiaPhong] <= ALL (SELECT LP.[DonGiaPhong] FROM [dbo].[Phong] P JOIN [dbo].[LoaiPhong] LP ON P.LoaiPHG = LP.LoaiPHG)

–Cau 3:
SELECT *
FROM KhachHang kh
WHERE MaKH BETWEEN ‘A%’ AND ‘H%’

–Cau 4:
SELECT *
FROM [dbo].[ThuePhong] WHERE [MaKH] NOT IN (SELECT MaKH
FROM [dbo].[KhachHang])

–Cau 5
SELECT LP.[LoaiPHG], COUNT(MaPHG) AS SoLuong
FROM [dbo].[Phong] P JOIN [dbo].[LoaiPhong] LP ON P.LoaiPHG = LP.LoaiPHG
WHERE P.LoaiPHG in (1,3)
GROUP BY LP.[LoaiPHG]

–Cau 6
SELECT KH.MaKH, ( (Day(NgayTra) – DAY(NgayThue)) * DonGiaPhong) AS TongTien
FROM [dbo].[KhachHang] KH JOIN [dbo].[ThuePhong] TP ON KH.MaKH = TP.MaKH
JOIN [dbo].[Phong] P ON P.MaPHG = TP.MaPHG
JOIN [dbo].[LoaiPhong] LP ON LP.LoaiPHG = P.LoaiPHG
WHERE (Day(NgayTra) – DAY(NgayThue)) * DonGiaPhong > 2000000

–Cau 7
SELECT *
FROM [dbo].[KhachHang] KH JOIN [dbo].[ThuePhong] TP ON KH.MaKH = TP.MaKH
JOIN [dbo].[Phong] P ON P.MaPHG = TP.MaPHG
where P.[MoTa] LIKE ‘A%’ AND P.[Tang] = 1

–Cau 8
SELECT KH.MaKH, KH.TenKH, KH.QuocTich, KH.DienThoai , ( (Day(NgayTra) – DAY(NgayThue)) * DonGiaPhong) AS TONGTIEN INTO HOADONTINHTIEN
FROM [dbo].[KhachHang] KH JOIN [dbo].[ThuePhong] TP ON KH.MaKH = TP.MaKH
JOIN [dbo].[Phong] P ON P.MaPHG = TP.MaPHG
JOIN [dbo].[LoaiPhong] LP ON P.LoaiPHG = P.LoaiPHG
WHERE MONTH(NgayTra) BETWEEN 6 AND 12 AND YEAR(NgayTra) = 2007
ORDER BY KH.TenKH DESC

—Bai 4
ALTER TABLE ThuePhong
ADD Songay int
ALTER TABLE ThuePhong
ADD GiamGia int

UPDATE [dbo].[ThuePhong] SET [Songay] = CASE WHEN NgayThue = NgayTra THEN 1
ELSE Day(NgayTra) – DAY(NgayThue) END

UPDATE ThuePhong
SET GiamGia = 200000
FROM [dbo].[KhachHang] KH JOIN [dbo].[ThuePhong] TP on KH.MaKH = TP.MaKH
WHERE Songay > 20