数据库第三次上机作业

最新推荐文章于 2020-03-01 14:23:10 发布

阿袁的小园子

最新推荐文章于 2020-03-01 14:23:10 发布

阅读量1.1k

点赞数 4

本文链接：https://blog.csdn.net/yuanren201/article/details/89067103

版权

啊哈第三次上机主要是DQL的进阶与关系代数的练习，对我来说可以说是难度陡增，光是第一题就花了我一上午，需要学的知识点一下子就变多了好多。

先来总结一下：
1）自然连接两个表：table1 join table2 on case
2）去除连接表以后的重复行：目前我只知道手动的方法，就是select ... from table1 join table2 on case时在select后面的选择语句里给这个列名加个表约束，比如table1.列名或者table2.列名这样子
3）解决子查询多重嵌套过于繁琐的问题：用with newtable1 as(select …)。这个真的方便
4）得到前X位的数据信息：在select和属性名字之间加一个top x即可。

助教给出的三个表的SQL定义语句为：

导入顾客信息表：

CREATE TABLE [dbo].[Customer] (
    [CID] float not null primary key,
    [Gender] nvarchar(255),
    [City] nvarchar(255)
    )

导入食品信息表：

CREATE TABLE [dbo].[Food] (
[FID] float not null primary key,
[Name] nvarchar(255),
[City] nvarchar(255),
[Price] float
)

导入订单信息表：

CREATE TABLE [dbo].[Order] (
[OID] float not null primary key,
[CID] float foreign key references Customer(CID),
[FID] float foreign key references Food(FID),
[Quantity] float
)

下面是题目以及我的SQL语句：

--1. 选出3号顾客买过的小于50元的食物的数量(要求连接前先表裁剪)

select Quantity 
from (
select 
FID,Quantity  
from [Order] where CID=3
)  
as [table] join Food 
on [table]."FID"=Food."FID" 
where Food."Price"<50

在这里插入图片描述
from…as语句是一个表裁剪语句，得到一个裁剪后的小表，join …on是按条件连接表格，最后一个总的select…from语句是按条件查询符合的列

--2. 选出消费额之和最大的顾客ID

select cid
from(
	select
			CID,
			sum(expend) as sumexpend
			from(
			select 
			CID,Quantity*Price as expend 
			from [Order] inner join Food on [Order].FID=Food.FID
			)
			as newtable
			group by CID
)
as newtable2
--这个条件语句是根据消费金额与最大消费金额之间的比较来得出消费最多的消费者的编号
where sumexpend=
(
--这个查询语句是从所有消费金额中选出最大消费金额
select
max(sumexpend)
from(
			--这段查询得到的是所有顾客的消费总额及其顾客号
			select
			CID,
			sum(expend) as sumexpend
			from(
			select 
			CID,Quantity*Price as expend 
			from [Order] inner join Food on [Order].FID=Food.FID
			)
			as newtable
			group by CID
			-----------------------------------------------
)
as newtable2
)

这道题告诉我这个笨B，嵌套查询以后一定要给这个表起个别名，不然会死的

--3. 选出销售额之和最大的城市名City

select
city
from(
	--这段得到的是所有城市及其消费额的情况
	select
	city,
	sum(sumexpend) as sum_city
	from(
		select sumexpend,City
		from(
					--这段查询得到的是所有顾客的消费总额及其顾客号
					select
					CID,
					sum(expend) as sumexpend
					from(
					select 
					CID,Quantity*Price as expend 
					from [Order] inner join Food on [Order].FID=Food.FID
					)
					as newtable1
					group by CID
					-----------------------------------------------
		)
		as newtable2
		 join Customer on newtable2.CID=Customer.CID
	)
	as newtable3
	group by city
	----------------------------------------------------------------
	)
as newtable4
where sum_city=(
	select
	max(sum_city)
	from(
		--这段得到的是所有城市及其消费额的情况
		select
		city,
		sum(sumexpend) as sum_city
		from(
			select sumexpend,City
			from(
						--这段查询得到的是所有顾客的消费总额及其顾客号
						select
						CID,
						sum(expend) as sumexpend
						from(
						select 
						CID,Quantity*Price as expend 
						from [Order] inner join Food on [Order].FID=Food.FID
						)
						as newtable1
						group by CID
						-----------------------------------------------
			)
			as newtable2
			 join Customer on newtable2.CID=Customer.CID
		)
		as newtable3
		group by city
		----------------------------------------------------------------
		)
		as newtable4
)

--4. 选出个人平均消费额超过北京地区"订单平均消费"的女性顾客的ID

先插一个小知识点，查询特定行时所用列的特定值是用单引号括起来的：where city='北京'

select
CID
from(
	--求得所有女性消费者的消费均额
	select
	CID,
	sum_expend/count_order as ave,
	count_order,
	sum_expend
	from(
		select
		newtable1.CID,
		count_order,
		sum_expend,
		Gender,
		City
		from(
			select
			newtable.CID,
			count(oid) as count_order,
			sum(expend) as sum_expend
			from(	
			--这段查询得到的是所有顾客的一次消费的消费总额及其它信息
				select 
				CID,
				oid,
				Quantity*Price as expend 
				from [Order] inner join Food on [Order].FID=Food.FID
	
			)	
			as newtable
			group by cid

		)
		as newtable1
		join Customer on newtable1.CID=Customer.CID
	)
	as newtable2
	where Gender='女'
	---------------------------------------------------
)
as newtable3
where ave>
(
	select
	*
	from(
	
	
			select
			sum_expend/count_order as ave
			from(
				select
				City,
				sum(count_order) as count_order,
				sum(sum_expend) as sum_expend
				from(
					--这段得到的是顾客们的订单总数和消费总额
					select
						cid,
						count(oid) as count_order,
						sum(expend) as sum_expend
						from(	--这段查询得到的是所有顾客的一次消费的消费总额及其它信息
							select 
							CID,
							oid,
							Quantity*Price as expend 
							from [Order] inner join Food on [Order].FID=Food.FID
	
						)	
						as newtable
						group by cid
				)
				as newtable1
				join Customer on Customer.CID=newtable1.CID
				group by City
			)
			as newtable2
			where City='北京'
	)
	as newtable3
)

--5. 选出个人总消费额不超过北京地区“人均总消费"的男性顾客的ID

select
cid
from(
	select
	cid,
	sum(expend) as sum_expend
	from(
		select
		CID,oid,expend,Gender
		from(	
			select
			Customer.CID,
			oid,
			expend,
			Gender,
			City
			from(
					select 
					[Order].CID,
					oid,
					Quantity*Price as expend 
					from [Order] inner join Food on [Order].FID=Food.FID
				)
				as newtable1
				join Customer on Customer.CID=newtable1.CID
		)
		as newtable2
		where Gender='男'
	)
	as newtable3
	group by CID
)
as newtable4
where sum_expend<=
(
	select
	sum(sumexpend)/count(CID) as ave

	from(
			select
			newtable2.CID,
			city,
			sumexpend
			from(
				--这段查询得到的是所有顾客的消费总额及其顾客号
				select
				CID,
				sum(expend) as sumexpend
				from(
					select 
					[Order].CID,
					Quantity*Price as expend 
					from [Order] inner join Food on [Order].FID=Food.FID
				)
				as newtable1
				group by CID
				-----------------------------------------------
			)
			as newtable2
			join Customer on Customer.CID=newtable2.CID
	
	)
	as newtable3
	where city='北京'
	group by city
)

--6. 选出消费能力最高（消费额之和最大）的顾客们来自的城市名City（注意和上一道题目的区别）

select
city
from(
	select
	cid
	from(
		select
				CID,
				sum(expend) as sumexpend
				from(
				select 
				CID,Quantity*Price as expend 
				from [Order] inner join Food on [Order].FID=Food.FID
				)
				as newtable
				group by CID
	)
	as newtable2
	--这个条件语句是根据消费金额与最大消费金额之间的比较来得出消费最多的消费者的编号
	where sumexpend=
	(
	--这个查询语句是从所有消费金额中选出最大消费金额
	select
	max(sumexpend)
	from(
				--这段查询得到的是所有顾客的消费总额及其顾客号
				select
				CID,
				sum(expend) as sumexpend
				from(
				select 
				CID,Quantity*Price as expend 
				from [Order] inner join Food on [Order].FID=Food.FID
				)
				as newtable
				group by CID
	)
	as newtable2
	)
)
as newtable3
join Customer on Customer.CID=newtable3.CID

--7. 选出男性的每次订单的平均消费额

select
sum(expend)/count(oid)
from(
		select
		CID,oid,expend,Gender
		from(	
			select
			Customer.CID,oid,expend,Gender,City
			from(
					select 
					[Order].CID,
					oid,
					Quantity*Price as expend 
					from [Order] inner join Food on [Order].FID=Food.FID
				)
				as newtable1
				join Customer on Customer.CID=newtable1.CID
		)
		as newtable2
		where Gender='男'
)
as newtable3
group by gender

--8. 选出所有订单消费额大于50的订单ID

select
oid
from(
	select
	OID,
	quantity*price as expend
	from [Order]
	join Food on Food.FID=[Order].FID
)
as newtable
where expend>50

--9. 选出购买过“茶”类产品的顾客ID

select
cid
from(
	select
	cid,
	count(cid) as huhu
	from(
		select
		cid,[name]
		from
		[Order]
		join food  on food.FID=[Order].FID
	)
	as newtable
	where [name] like '%茶%'
	group by cid
)
as newtable1

这道题其实可以直接查询，不用group by进行合并，但是不用group by的话会出现重复信息，也就是重复的cid，所以我这里先用group by 把相同cid的行给合并了。

--10. 选出既买过伊修加德的食物，又买过黄金港的食物的顾客ID

with 
t1 as
(
	select 
	cid,city
	from [Order]
	join food on Food.FID=[Order].FID
	where city='黄金港'
	group by cid,city
),
t2 as
(
	select 
	cid,city
	from [Order]
	join food on Food.FID=[Order].FID
	where city='伊修加德'
	group by cid,city
)
select 
cid
from t1
where cid in (select cid from t2)

--11. 选出买过重庆或者四川两地的食物的顾客ID

with 
t1 as
(
	select 
	cid,city
	from [Order]
	join food on Food.FID=[Order].FID
	where city='重庆' or city='四川'
	group by cid,city
)
select 
cid
from t1
group by cid

--12. 选出只买过来自于重庆和四川两地的食物的顾客ID

with 
t1 as
(
	select 
	cid,
	city as c1
	from [Order]
	join food on Food.FID=[Order].FID
	where city='重庆' 
	group by cid,city
),
t2 as
(
	select 
	cid,
	city as c2
	from [Order]
	join food on Food.FID=[Order].FID
	where city='四川' 
	group by cid,city
)
select 
t1.cid
from t1 join t2 on t1.CID=t2.CID
where c1='重庆' and c2='四川'

--13. 选出没有购买记录的顾客ID

with 
t1 as(
select cid
from [Order]
group by cid
),
t2 as(
select cid
from Customer
)
select
*
from t2
where cid not in (select cid from t1)

--14. 选出TOP3消费量（订单金额之和）最大的食物ID

with 
t1 as(
	select 
	food.fid,oid,
	quantity*price as expend
	from [Order]
	join food on [Order].FID=food.FID
),

t2 as(
select
fid,
sum(expend) as sum
from t1
group by fid
)

select top 3 fid from t2 order by sum desc

导入顾客信息表：
CREATE TABLE [dbo].[Customer] (
[CID] float not null primary key,
[Gender] nvarchar(255),
[City] nvarchar(255)
)

导入食品信息表：
CREATE TABLE [dbo].[Food] (
[FID] float not null primary key,
[Name] nvarchar(255),
[City] nvarchar(255),
[Price] float
)

导入订单信息表：
CREATE TABLE [dbo].[Order] (
[OID] float not null primary key,
[CID] float foreign key references Customer(CID),
[FID] float foreign key references Food(FID),
[Quantity] float
)