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
)

Jundat95

Header Ads

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