数据库第三章实践作业

教材：《数据库系统原理、设计与编程》陆鑫 张凤荔 陈安龙/编著

第三章 数据库操作语言SQL 课后习题

五、实践操作题

作业
--创建三张表
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(60) 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,
 CONSTRAINT PersonID_FK FOREIGN KEY("PersonID")
 REFERENCES "Owner"("PersonID") ON DELETE CASCADE,
 constraint EstateID_FK FOREIGN KEY("EstateID")
 REFERENCES "Estate"("EstateID") ON DELETE CASCADE
)

--数据库修改测试：
ALTER TABLE "Owner" DROP COLUMN "Tel";
ALTER TABLE "Owner" ADD "Tel" varchar(11) NOT NULL;

--插入数据
--完善Owner表：
INSERT INTO "Owner" VALUES('510722200011268436','诸慧','女','学生','四川绵阳市','15596543698');
INSERT INTO "Owner" VALUES('110221200301012836','周共','男','学生','北京市','15832956645');
INSERT INTO "Owner" VALUES('370503198904032299','张三','男','律师','山东营口市','13945236544');
INSERT INTO "Owner" VALUES('440402199804078098','刘潜','男','服务员','广东珠江市','13646986566');
INSERT INTO "Owner" VALUES('120223199401018302','关银屏','女','陆军','天津市','15932656565');
INSERT INTO "Owner" VALUES('110221200301012617','曹笑','男','程序猿','四川成都市','15732696565');
INSERT INTO "Owner" VALUES('360103199103012782','郭灵','女','教师','江西南昌市','15963556552');
INSERT INTO "Owner" VALUES('320582199905175153','孙云','男','医生','江苏张家港市','13665623233');
--完善Estate表：
INSERT INTO "Estate" VALUES('A','承泰国际','三里花城新蕊公馆','绵阳','绵阳','住宅','120','110','2020/1/1','70',NULL);
INSERT INTO "Estate" VALUES('B','万达国际','德商天骄城学府','北京','北京','别墅','700','653','2017/3/29','70',NULL);
INSERT INTO "Estate" VALUES('C','东山府第','未来之城','营口','营口','住宅','120','113','2009/6/6','70',NULL);
INSERT INTO "Estate" VALUES('D','浩华地产','雅居乐锦尚雅宸','深圳','深圳','别墅','450','423','2019/11/2','70',NULL);
INSERT INTO "Estate" VALUES('E','承泰国际','蓝光未来','成都','成都','车位','20','15','2016/1/24','70',NULL);
INSERT INTO "Estate" VALUES('F','浩华地产','人居柏云庭','成都','成都','住宅','260','232','2020/6/11','70',NULL);
INSERT INTO "Estate" VALUES('G','万达国际','荣海乐府','南昌','南昌','商铺','190','172','2020/3/23','70',NULL);
INSERT INTO "Estate" VALUES('H','东山府第','朗诗·峰境','苏州','苏州','商铺','200','183','2017/6/13','70',NULL);
INSERT INTO "Estate" VALUES('I','承泰国际','皇都花圃','北京','北京','住宅','360','300','2000/10/16','70',NULL);
INSERT INTO "Estate" VALUES('J','东山府第','红叶别墅','广州','广州','住宅','150','130','2003/3/16','70',NULL);
INSERT INTO "Estate" VALUES('K','浩华地产','锦灏佳园','杭州','杭州','住宅','162','131','2008/2/13','70',NULL);
INSERT INTO "Estate" VALUES('L','东山府第','云间水庄二期','上海','上海','别墅','500','450','2019/3/18','70',NULL);
INSERT INTO "Estate" VALUES('M','承泰国际',' 静安雅筑','北京','北京','商铺','200','150','2018/1/26','70',NULL);
INSERT INTO "Estate" VALUES('N','万达国际',' 学苑风仪','深圳','深圳','商铺','200','170','2016/11/3','70',NULL);
INSERT INTO "Estate" VALUES('O','浩华地产','九歌花圃','德阳','德阳','住宅','136','120','2017/2/26','70',NULL);
INSERT INTO "Estate" VALUES('P','承泰国际','公寓枫情','天津','天津','商铺','200','190','2017/1/7','70',NULL);
--完善Register表：
INSERT INTO "Registration" VALUES('1','510722200011268436','A','1325232','2019/6/12','2020/1/12');
INSERT INTO "Registration" VALUES('2','110221200301012836','B','122136987','2016/11/30','2017/6/19');
INSERT INTO "Registration" VALUES('3','370503198904032299','C','3266954','2009/1/23','2010/3/6');
INSERT INTO "Registration" VALUES('4','440402199804078098','D','31146252','2019/1/20','2019/12/12');
INSERT INTO "Registration" VALUES('5','120223199401018302','E','126952','2016/2/24','2016/4/15');
INSERT INTO "Registration" VALUES('6','110221200301012617','F','3632542','2020/12/18','2021/3/16');
INSERT INTO "Registration" VALUES('7','360103199103012782','G','1236024','2019/9/12','2020/6/12');
INSERT INTO "Registration" VALUES('8','320582199905175153','H','2999634','2017/8/17','2017/12/13');

--选择商铺项
SELECT *
FROM "Estate"
WHERE "EstateType"='商铺';

--竣工日期2018/12/1之后，产权面积大于90平方米的住宅的房产信息
SELECT *
FROM "Estate"
WHERE "CompletedDate">='2018/12/1' AND "ProPertyArea">'90' AND "EstateType"='住宅';

--为了体现一人多套房产，我们对表Registration添加如下内容
INSERT INTO "Registration" VALUES('9','510722200011268436','I','97523621','2000/12/12','2001/9/13');
INSERT INTO "Registration" VALUES('10','120223199401018302','J','3256564','2001/12/25','2003/11/26');
INSERT INTO "Registration" VALUES('11','510722200011268436','K','3565346','2007/9/30','2008/12/3');
INSERT INTO "Registration" VALUES('12','440402199804078098','L','96236565','2016/6/30','2019/9/8');
INSERT INTO "Registration" VALUES('13','440402199804078098','M','22132321','2018/9/17','2018/11/4');
INSERT INTO "Registration" VALUES('14','440402199804078098','N','13326465','2014/1/1','2016/12/30');
INSERT INTO "Registration" VALUES('15','440402199804078098','O','1296357','2016/1/3','2017/6/8');
INSERT INTO "Registration" VALUES('16','370503198904032299','P','9615362','2016/9/16','2017/8/19');

--查询个人在各地购买住宅两套以上的业主信息。
SELECT A."PersonID" AS 身份证号,A."Name" AS 名字,A."Gender" AS 性别,A."Occupation" AS 职业,A."Addr" AS 住址,A."Tel" AS 电话,COUNT(A."PersonID") AS 总套数
FROM "Owner" AS A JOIN "Registration" AS B
ON A."PersonID"=B."PersonID"
GROUP BY A."PersonID"
HAVING COUNT(A."PersonID")>=2;

--查询个人在特定的城市购买住宅两套以上的业主的基本信息
--为了效果明显，我们修改数据
UPDATE "Estate"
SET "EstateCity"='成都'
WHERE "EstateID" = 'C';
UPDATE "Estate"
SET "EstateCity"='成都'
WHERE "EstateID" = 'G';
UPDATE "Estate"
SET "EstateCity"='成都'
WHERE "EstateID" = 'O';
UPDATE "Estate"
SET "EstateCity"='北京'
WHERE "EstateID" = 'D';
UPDATE "Estate"
SET "EstateCity"='北京'
WHERE "EstateID" = 'L';
UPDATE "Estate"
SET "EstateCity"='北京'
WHERE "EstateID" = 'P';
UPDATE "Estate"
SET "EstateAddr"='成都'
WHERE "EstateID" = 'C';
UPDATE "Estate"
SET "EstateAddr"='成都'
WHERE "EstateID" = 'G';
UPDATE "Estate"
SET "EstateAddr"='成都'
WHERE "EstateID" = 'O';
UPDATE "Estate"
SET "EstateAddr"='北京'
WHERE "EstateID" = 'D';
UPDATE "Estate"
SET "EstateAddr"='北京'
WHERE "EstateID" = 'L';
UPDATE "Estate"
SET "EstateAddr"='北京'
WHERE "EstateID" = 'P';
UPDATE "Estate"
SET "EstateCity" = '绵阳'
WHERE "EstateID" = 'K';
UPDATE "Estate"
SET "EstateAddr" = '绵阳'
WHERE "EstateID" = 'K';
UPDATE "Estate"
SET "EstateType" = '住宅'
WHERE "EstateID" = 'L';
UPDATE "Estate"
SET "EstateType" = '住宅'
WHERE "EstateID" = 'M';
--查询
SELECT A."PersonID" AS 身份证号,A."Name" AS 名字,A."Gender" AS 性别,A."Occupation" AS 职业,A."Addr" AS 住址,A."Tel" AS 电话,COUNT(A."PersonID") AS 总套数
FROM "Owner" AS A JOIN "Registration" AS B
ON A."PersonID"=B."PersonID" 
JOIN "Estate" AS C ON B."EstateID"=C."EstateID"
WHERE C."EstateType"='住宅'
GROUP BY C."EstateCity",A."PersonID"
HAVING COUNT(A."PersonID")>=2;

--统计2018年度某城市各类房产销售面积
--为了效果明显，我们改一些购买日期
UPDATE "Registration"
SET "PurchasedDate" = '2018/7/03'
WHERE "EstateID" = 'B';
UPDATE "Registration"
SET "PurchasedDate" = '2018/6/13'
WHERE "EstateID" = 'D';
UPDATE "Registration"
SET "PurchasedDate" = '2018/4/06'
WHERE "EstateID" = 'G';
UPDATE "Registration"
SET "PurchasedDate" = '2018/3/30'
WHERE "EstateID" = 'P';
SELECT "EstateType" AS 房产类型,SUM("ProPertyArea") AS 销售面积之和
FROM "Estate" AS A JOIN "Registration" AS B
ON A."EstateID"=B."EstateID"
WHERE B."PurchasedDate" BETWEEN '2018/01/01' AND '2018/12/30'
GROUP BY "EstateType";

--统计2018年度某城市各类房产销售金额
--方案一：
SELECT "EstateType" AS 房产类型,SUM("Price") AS 销售金额之和
FROM "Estate","Registration"
WHERE "Estate"."EstateID"="Registration"."EstateID"  AND  "Registration"."PurchasedDate" BETWEEN '2018/01/01' AND '2018/12/30'
GROUP BY "EstateType";
--方案二：
SELECT "EstateType" AS 房产类型,SUM("Price") AS 销售金额之和
FROM "Estate" AS A JOIN "Registration" AS B
ON A."EstateID"=B."EstateID"
WHERE B."PurchasedDate" BETWEEN '2018/01/01' AND '2018/12/30'
GROUP BY "EstateType";

--视图一
CREATE VIEW "View_One" AS
SELECT A."PersonID" AS "身份证号",A."EstateID" AS "房产编号",A."EstateName" AS "房产名称",A."EstateType" AS "房产类型",A."ProPertyArea" AS "产权面积",B."Price" AS "购买金额",B."PurchasedDate" AS "购买日期","EstateBuildName" AS "房产楼盘","EstateAddr" AS "房产城市"
FROM "Estate" AS A JOIN"Registration" AS B 
ON A."EstateID"=B."EstateID";

SELECT "房产编号","房产名称","房产类型","产权面积","购买金额","购买日期","房产楼盘","房产城市"
FROM "View_One"
WHERE 身份证号 = '510722200011268436'
ORDER BY 购买日期 DESC;

--统计2018年各个城市销售总套数/总金额
--统计
CREATE VIEW "View_Two" AS
SELECT A."EstateCity" AS 城市,SUM(B."Price") AS 总金额,COUNT(A."EstateCity") AS 总套数
FROM "Estate" AS A JOIN "Registration" AS B
ON A."EstateID"=B."EstateID"
WHERE B."PurchasedDate" BETWEEN '2018/01/01' AND '2018/12/30'
GROUP BY A."EstateCity"

注意：本人认真写的数据库作业，但是我其实也不确定有没有错的，看的人参考就好，哈哈。