Header Ads

Bài tập quản lý nhà cho thuê (tạo bảng và truy vấn)

Bài tập quản lý nhà cho thuê (tạo bảng và truy vấn)


Cho cơ sở dữ liệu quản lý nhà cho thuê bao gồm các bảng dữ liệu sau:
+ Bảng KHACH để lưu danh sách các khách hàng gồm các thuộc tính sau:
Tên Thuộc tính
Giải thích
MaK
Mã khách hàng
TenK
Tên khách hàng
ĐiaChi
Địa chỉ khách
SoDT
Số điện thoại
+ Bảng NHA để lưu trữ thông tin về các ngôi nhà cho thuê, gồm các thuộc tính sau:
 Tên Thuộc tính
Giải thích
MaN
Mã nhà
TenChuNha
Tên chủ nhà
GiaThue
Giá thuê
DiaChiNha
Địa chỉ nhà

+Bảng HOPDONG để lưu trữ thông tin về các hợp đồng thuê nhà của khách, gồm các thuộc tính sau:
 Tên Thuộc tính
Giải thích
SoHĐ
Số hợp đồng
MaN
Mã nhà
MaK
Mã khách
NgayBĐ
Ngày bắt đầu hợp đồng
NgayKT
Ngày kết thúc hợp đồng
Yêu cầu:
1/ Hãy tạo cấu trúc cơ sở dữ liệu trên (Dùng trình soạn thảo Query Analyzer; Với mỗi bảng nếu có ràng buộc khoá chính hoặc ràng buộc khoá ngoài yêu cầu sv phải định nghĩa đầy đủ).
Thực hiện các câu lệnh truy vấn sau:
1.       Đưa ra danh sách các ngôi nhà đang được thuê
2.       Đưa ra thông tin về khách hàng đã thuê ngôi nhà có mã là ‘NHA09’
3.       Đưa ra thông tin về những ngôi nhà hiện chưa có người nào thuê
2/ Tạo View để tổng hợp thông tin về những ngôi nhà có giá thuê cao nhất.
3/ Tạo View để tổng hợp thông tin về các khách hàng ngày hôm nay đã hết hạn hợp đồng.
4/ Tạo View để tổng hợp thông tin về các ngôi nhà chưa từng được ai thuê.



use master
CREATE DATABASE QLNhaChoThue
USE QLNhaChoThue

/* Tạo bảng Khách */
 CREATE TABLE KHACH
 (
 Mak varchar(4) PRIMARY KEY,
 TenK nvarchar(40) NOT NULL,
 DiaChi nvarchar(40) NOT NULL,
 SoDT varchar(20) NULL
 )

 /* Tạo bảng Nhà */
 CREATE TABLE NHA
 (
 MaN varchar(4) PRIMARY KEY,
 TenChuNha nvarchar(40) NOT NULL,
 GiaThue int NOT NULL,
 DiaChiNha nvarchar(40) NOT NULL
 )

/* Tạo bảng Hợp đồng */
CREATE TABLE HOPDONG
(
 SoHD varchar(4) PRIMARY KEY,
 MaN varchar(4) NOT NULL,
 MaK varchar(4) NOT NULL,
 NgayBD smalldatetime NOT NULL,
 NgayKT smalldatetime NOT NULL
)

/* Thêm kết nối khóa ngoài */

ALTER TABLE HOPDONG 
ADD CONSTRAINT KN_1_HOPDONG  FOREIGN KEY (MaN) REFERENCES NHA(MaN),
 CONSTRAINT KN_2_HOPDONG FOREIGN KEY (MaK) REFERENCES KHACH(MaK)

INSERT INTO KHACH(Mak,TenK,DiaChi)
 VALUES ('MK01','Chu Xuân Linh','Quảng Ninh'),
   ('MK02','Phạm Xuân Tú','Thái Nguyên'),
   ('MK03','Ngô Doãn Tình','Hải Dương')

INSERT INTO NHA
 VALUES ('MN01','Trần Văn Cương','400000','Hải Dương'),
   ('MN02','Dương Xuân Tùng','800000','Bắc Giang'),
   ('MN03','Trần Văn Cương','600000','Bắc Giang'),
   ('MN04','Tô Thành Đồng','400000','Thái Nguyên'),
   ('MN05','Trần Văn Cương','300000','Bắc Giang'),
   ('MN06','Trần Văn Cương','4000000','Hà Nội'),
   ('MN07','Trần Văn Cương','1400000','Quảng Ninh')

INSERT INTO HOPDONG
 VALUES ('HD01','MN05','MK02','04/23/2014','05/14/2014'),
   ('HD02','MN05','MK02','04/23/2014','05/14/2014'),
   ('HD03','MN02','MK02','04/23/2014','05/14/2014'),
   ('HD04','MN04','MK01','04/23/2014','05/14/2014'),
   ('HD05','MN04','MK01','01/23/2015','05/14/2015')

Use QuanLyNha

-- Dưa ra danh sách ngôi nhà đang được thuê.
Select *From tblNha Where MaN in
(
 
Select MaN From tblHopDong Where DATEDIFF(DAY,GETDATE(),NgayBD)<0
and DATEDIFF(DAY,GETDATE(),NgayKT)>0
) -- Đưa ra thông tin khách hàng đã thuê ngôi nhà có mã nhà MN09. Select *From tblKhach Where MaK In ( Select MaK From tblHopDong Where MaN ='MN01' ) -- Đưa ra thông tin ngôi nhà hiện nay chưa người nào thuê. Select *From tblNha Where MaN In ( Select MaN From tblHopDong Where GETDATE() > NgayKT ) -- View Tổng hợp thông tin về ngôi nhà có giá cao nhất. Create View View_NGCN As Select *From tblNha Where GiaThue In ( Select MAX(GiaThue) From tblNha ) --View khách hàng hôm nay đã hết hợp đồng. Create View View_KHHD AS Select *From tblKhach Where MaK In ( Select MaK From tblHopDong Where DATEDIFF(DAY,GETDATE(),NgayKT) = 0 ) --View tổng hợp ngôi nhà chưa từng được ai thuê. Create View View_NCT AS Select *From tblNha Where MaN Not In ( Select MaN From tblHopDong )

No comments