数据库实现（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语句完成对该数据库创建与数据操作处理，详细要求如下：

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

SQL实现

建立数据库

1. create database EstateDB;

复制代码

建表

业主表

1. create table owner
2. (
3.         personid char(18) primary key,
4.         name varchar(20) NOT NULL,
5.         gender char(2) NOT NULL,
6.         occupation varchar(20) NOT NULL,
7.         addr varchar(50) NOT NULL,
8.         tel varchar(11) NOT NULL       
9. )

复制代码

    房产表

1. create table estate
2. (
3.         estateid char(15) NOT NULL PRIMARY KEY,
4.         estatename varchar(50) NOT NULL,
5.         estatebuildname varchar(50) NOT NULL,
6.         estateaddr varchar(60) NOT NULL,
7.         estatecity varchar(40) NOT NULL,
8.         estatetype char(4) NOT NULL check(estatetype in('住宅','商铺','车位','别墅')),
9.         propertyarea numeric(5,2) NOT NULL,
10.         usablearea numeric(5,2) NOT NULL,
11.         completeddate date NOT NULL,
12.         yearlength int NOT NULL DEFAULT 70,
13.         remark varchar(100) NULL
14. )

复制代码

产权登记表 

1. create table registration
2. (
3.         registerid int NOT NULL PRIMARY KEY,
4.         personid char(18) NOT NULL,
5.         estateid char(15) NOT NULL,
6.         price money NOT NULL,
7.         purchaseddate date NOT NULL,
8.         deliverdate date NOT NULL,
9.         FOREIGN KEY(personid) references owner(personid),
10.         FOREIGN KEY(estateid) references estate(estateid)
11. )

复制代码

添加数据

本来想把数据传上去，但是会以资源的情势，要付费访问，放在这篇文章又显得冗余，所以放在了另一篇文章里
业主表，房产表，产权登记表数据
查表

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

1. SELECT *
2. FROM estate
3. WHERE estatetype = '商铺'

复制代码

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

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

复制代码

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

这里主要考察内毗连JOIN... ON 相关字段的列值满足等值毗连条件时，才会提取形成新的结果集（AS的作用是给表起个别名，方便引用）
GROUP用来形成分组，以及学会内置函数（COUNT ）的使用
HAVING子句限定分组统计的条件

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

复制代码

查询个人在特定都会购买住宅2套以上的业主根本信息

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

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

复制代码

统计2024年度某都会的各类房产贩卖面积

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

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

复制代码

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

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

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

复制代码

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

1. SELECT *
2. FROM estate_info
3. WHERE personid = '500104194306074676'

复制代码

创建SQL视图，分组统计2024年度各都会的住宅贩卖套数与总贩卖金额

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

复制代码

 接下来进行查询

1. SELECT *
2. FROM city_info

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。