数据库查询题目

现有3张数据表如下：

项目表：记录项目基本信息。

房间表：记录房间基本信息，一个项目有多个房间，一个房间只属于一个项目。收款表：一个房间会有多笔款项类型、款项名称不一样的收款，一笔收款信息只属于一个房间。

表关系图如下：

项目表：s_project

房间表：s_room

根据提供的信息，完成如下题目（每题10分）：

1)写出项目表的创建语句（不考虑外键约束）

2)以标准总价升序s_room Total、签署日期降序Qsdate显示房间表中的记录

3)用一个SQL统计每一个项目款项类型为非贷款类房款的收款金额合计

（s_getin.itemtype=’非贷款类房款’）、款项类型为贷款类房款（（s_getin.itemtype=’贷款类房款’））的收款金额合计

个房间的收款合计(s_getin之和)，显示公司名称、父级项目名称、项目名称、房间代码、房号、建筑面积、标准单价、标准总价、签署日期、成交单价、成交总价、销售状态，收款金额合计

说明：项目开盘当日：项目表（s_project）开盘日期

成交的房间：房间表（s_room）销售状态为签约

5)查询所有销售状态为‘签约’的房间收款金额合计不等于成交总价的记录，

并计算出差额（s_getin.amout之和<>s_room.cjtotal）

6)更新所有已经收取银行按揭金额房间(存在s_getin.itemname=‘银行按揭’

的房间)的按揭放款日期（s_room.ajfkdate）为该房间银行按揭收款的收款日期(s_getin.getdate)

7)基于项目、房间、收款表，创建一张视图，包含字段有，公司名称、父级项

目名称、项目代码、项目名称、开盘日期、房间代码、房号、建筑面积、标准单价、标准总价、成交单价、签署日期、成交总价、收款金额合计。

8)查询各项目的房间的成交总价(s_room.cjtotal)在该项目降序排序为第6到

第10的房间的项目名称、房间代码、累计收款金额 (s_getin.amount之和) 9)基于收款表创建insert触发器，在收取银行按揭款(款项名称为‘银行按揭’)

后，根据收款日期自动更新房间表按揭放款日期

10)创建一个表函数 fn_test(y int,m int)

参数定义：y 为年份，m 为月份

返回值定义：return {项目名称 varchar(100),上旬销售套数 int，中旬销

要求输入年份和月份，输出每个项目该月份在上旬、中旬、下旬分别销售的套数。

时间维度定义：每月1日-10日为上旬，11日-20日为中旬，21日到月末为下旬。select*from s_room

--第二题

select*from dbo.s_room orderby total asc,qsdate desc

--第三题

select p.projname as项目名称,

sum(case g.itemtype when'贷款类房款'then g.amount else 0 end)as贷款类房款, sum(case g.itemtype when'非贷款类房款'then g.amount else 0 end)as非贷款类

房款

from s_geting,s_roomr,s_projectp

where g.roomguid=r.roomguid and r.projguid=p.projguid groupby p.projname

--第四题

select

pany as公司名称,

pb.projname as父级项目名称,

pa.projname as项目名称,

r.roomcode as房间代码,

r.roomno as房号,

r.bldarea as建筑面积,

r.price as标准单价,

r.total as标准总价,

r.qsdate as签署日期,

r.cjprice as成交单价,

r.cjtotal成交总价,

r.statusas销售状态,

SUM(g.amount)as收款金额合计

from s_projectpa,s_projectpb,s_roomr,s_geting

where pa.parentprojcode=pb.projcode

and pa.projguid=r.projguid

and r.roomguid=g.roomguid

and pa.kpdate=r.qsdate

and r.status='签约'

groupby pany,pb.projname,pa.projname,r.roomcode,r.roomno,r.bldarea,r. price,r.total,r.qsdate,r.cjprice,r.cjtotal,r.status

--第五题

select

r.roomno as房号,

r.cjtotal-sum(g.amount)as差额

from s_roomr,s_geting

where r.roomguid=g.roomguid

and r.status='签约'

groupby r.roomno,r.cjtotal

having r.cjtotal<>sum(g.amount)

--第六题

update s_room

set ajfkdate=(selecttop 1

getdate from s_geting where g.roomguid=s_room.roomguid and g.itemname='银行按揭')

update s_room

set ajfkdate=(selecttop 1

g.getdate from s_geting where g.roomguid=s_room.roomguid and g.itemname='银行按揭')

where exists(select 1 from s_geting where g.itemname='银行按揭

'and s_room.roomguid=g.roomguid)

--第七题

createview ProjectView