数据库实现（pgAdmin）房产信息管理系统开发

问题描述

针对一个房产信息管理系统开发，其设计的数据库EstateDB包括业主表（Owner）、房产表（Estate）、产权登记表（Registration）。各数据表的字段结构定义见表1到表3所示。

表1 业主表（Owner）

字段名称	字段编码	数据类型	字段大小	必填字段	备注
身份证号	PersonID	Char	18	是	主键
姓名	Name	Varchar	20	是
性别	Gender	Char	2	是
职业	Occupation	Varchar	20	是
身份地址	Addr	Varchar	50	是
电话	Tel	Varchar	11	是

表2 房产表（Estate）

字段名称	字段编码	数据类型	字段大小	必填字段	备注
房产编号	EstateID	Char	15	是	主键
房产名称	EstateName	Varchar	50	是
房产楼盘	EstateBuildName	Varchar	50	是
房产地址	EstateAddr	Varchar	60	是
房产城市	EstateCity	Varchar	60	是
房产类型	EstateType	Char	4	是	取值范围：住宅、商铺、车位、别墅
产权面积	PropertyArea	Numeric	(5,2)	是
使用面积	UsableArea	Numeric	(5,2)	是
竣工日期	CompletedDate	Date		是
产权年限	YearLength	Int		是	默认值70
备注	Remark	Varchar	100	否

表3 产权登记表（Registration）

字段名称	字段编码	数据类型	字段大小	必填字段	备注
登记编号	RegisterID	Int		是	主键
身份证号	PersonID	Char	18	是	外键
房产编号	EstateID	Char	15	是	外键
购买金额	Price	Money		是
购买日期	PurchasedDate	Date		是
交付日期	DeliverDate	Date		是

请根据各表的字段组成、字段名称、字段编码、字段数据类型、字段属性约束、参照完整性约束等信息，编写SQL语句完成对该数据库创建与数据操作处理，具体要求如下：

1. 编写并运行SQL语句，创建数据库EstateDB。
2. 编写并运行SQL语句，在数据库EstateDB中创建上述三个数据库表，并定义其完整性约束。
3. 准备样本数据，编写并运行SQL语句，在上述三个数据库表中添加数据。
4. 编写并运行SQL语句，查询类别为“商铺”的房产信息。
5. 编写并运行SQL语句，查询竣工日期为2024年12月1日后，产权面积90平米以上的“住宅”的房产信息。
6. 编写并运行SQL语句，查询个人在各地购买住宅2套以上的业主基本信息。
7. 编写并运行SQL语句，查询个人在特定城市购买住宅2套以上的业主基本信息。
8. 编写并运行SQL语句，统计2024年度某城市的各类房产销售面积。
9. 创建SQL视图，通过视图查询指定身份证号下，该业主的购置房产信息（“登记编号”，“房产名称”，“ 房产类型”，“产权面积”，“购买金额”，“购买日期”，“房产楼盘”，“房产城市”），并按日期降序排列。
10. 创建SQL视图，分组统计2024年度各城市的住宅销售套数与总销售金额

SQL实现

建立数据库

create database EstateDB;

建表

业主表

create table owner
(
	personid char(18) primary key,
	name varchar(20) NOT NULL,
	gender char(2) NOT NULL,
	occupation varchar(20) NOT NULL,
	addr varchar(50) NOT NULL,
	tel varchar(11) NOT NULL	
)

房产表

create table estate
(
	estateid char(15) NOT NULL PRIMARY KEY,
	estatename varchar(50) NOT NULL,
	estatebuildname varchar(50) NOT NULL,
	estateaddr varchar(60) NOT NULL,
	estatecity varchar(40) NOT NULL,
	estatetype char(4) NOT NULL check(estatetype in('住宅','商铺','车位','别墅')),
	propertyarea numeric(5,2) NOT NULL,
	usablearea numeric(5,2) NOT NULL,
	completeddate date NOT NULL,
	yearlength int NOT NULL DEFAULT 70,
	remark varchar(100) NULL
)

产权登记表 

create table registration
(
	registerid int NOT NULL PRIMARY KEY,
	personid char(18) NOT NULL,
	estateid char(15) NOT NULL,
	price money NOT NULL,
	purchaseddate date NOT NULL,
	deliverdate date NOT NULL,
	FOREIGN KEY(personid) references owner(personid),
	FOREIGN KEY(estateid) references estate(estateid)
)

添加数据

本来想把数据传上去，但是会以资源的形式，要付费访问，放在这篇文章又显得冗余，所以放在了另一篇文章里

业主表，房产表，产权登记表数据

查表

查询类别为“商铺”的房产信息

SELECT *
FROM estate 
WHERE estatetype = '商铺'

查询竣工日期为2024年12月1日后，产权面积90平米以上的“住宅”的房产信息

SELECT *
FROM estate 
WHERE completeddate >'2024-12-1' AND propertyarea > 90 AND  estatetype = '住宅'

查询个人在各地购买住宅2套以上的业主基本信息

这里主要考察内连接JOIN... ON 相关字段的列值满足等值连接条件时，才会提取形成新的结果集（AS的作用是给表起个别名，方便引用）

GROUP用来形成分组，以及学会内置函数（COUNT ）的使用

HAVING子句限定分组统计的条件

SELECT O.personid,O.name,O.occupation,O.tel,O.addr,COUNT(*) AS 住宅数量
FROM owner as O
JOIN registration as R ON O.personid=R.personid
JOIN estate as E ON E.estateid=R.estateid
WHERE E.estatetype='住宅' 
GROUP by O.personid
HAVING COUNT(*)>2

查询个人在特定城市购买住宅2套以上的业主基本信息

与上一个基本类似，只是加了一个查询条件

SELECT O.personid,O.name,O.occupation,O.tel,O.addr,COUNT(*) AS 住宅数量
FROM owner as O
JOIN registration as R ON O.personid=R.personid
JOIN estate as E ON E.estateid=R.estateid
WHERE E.estatetype = '住宅' AND E.estatecity = '成都'
GROUP by O.personid
HAVING COUNT(*) >= 2

统计2024年度某城市的各类房产销售面积

由于给出的数据并没有2024年的销售数据，这里查2023年的。

SELECT E.estatetype as 房产类型,SUM(E.propertyarea) AS 销售面积
FROM registration as R
JOIN estate as E ON E.estateid = R.estateid
WHERE E.estatecity = '成都' 
AND R.purchaseddate BETWEEN '2023-1-1' AND '2023-12-31' 
GROUP by E.estatetype

视图查询指定身份证号下，该业主的购置房产信息,并按日期降序排列

主要考点是创建视图，还有排序ORDER的掌握

CREATE VIEW estate_info AS
SELECT O.personid,R.registerid,E.estatename,E.estatetype,E.propertyarea,
R.price,R.purchaseddate,E.estatebuildname,E.estatecity
FROM registration as R
JOIN estate AS E ON R.estateid = E.estateid
JOIN owner AS O ON R.personid = O.personid
ORDER by R.purchaseddate DESC

由于我们在创建视图时已经按降序排列，所以差的时候得到的也是降序  

SELECT *
FROM estate_info
WHERE personid = '500104194306074676'

创建SQL视图，分组统计2024年度各城市的住宅销售套数与总销售金额

CREATE VIEW city_info AS 
SELECT E.estatecity AS 城市,COUNT(*) AS 销售套数,SUM(R.price) AS 销售金额
FROM estate AS E
JOIN registration AS R ON R.estateid = E.estateid
WHERE E.estatetype = '住宅' AND
R.purchaseddate BETWEEN '2023-1-1' AND '2023-12-31'
GROUP BY E.estatecity

 接下来进行查询

SELECT *
FROM city_info