数据库实现（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实现
建立数据库
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 ANDestatetype = '住宅' 查询个人在各地购买住宅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
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]