【SQL Server】又一次数据库练习——题目+思路+答案

 

 

前排提示，本文的 E-R图结构 以及 表结构

来自于练习：【SQL Server】一次数据库练习——题目+思路+答案
 
 

use NetMusicShop
go


---- PPT4 —— 复杂数据查询 ----

--1.在购买了歌手刘欢专辑的客户中查询一次购买数量最多的客户的用户名
select o.UserName
from Sale s, Orders o, Album a
where s.OrderID = o.OrderID and s.AlbumID = a.AlbumID
and a.AlbumSinger = '刘欢'
and s.Quantity >= all(select s.Quantity from Sale s, Orders o, Album a where s.OrderID = o.OrderID and s.AlbumID = a.AlbumID)
-- 分析：括号中的子查询结果是一次购买的数量
-- 另外，也可以用 a.Quantity = max(select...)



--2.查找被所有用户收藏的歌曲(两种实现方式：相关嵌套查询，不相关嵌套查询)

-- 相关子查询
select * from Songs s	-- 变量
where not exists 
(
select * from Users u	-- 变量
where not exists
(
select * from Collections where SongID = s.SongID and UserName = u.UserName
)
)
-- 分析：像是两个for语句，判断s和u的每一种组合在Collections表中是否存在；还使用了补集的思想，非常巧妙，背也要背会

-- 无关子查询
select *
from Songs
where SongID in
(
select SongID
from Collections c
group by SongID having count(*) = (select count(*) from Users)
)
-- 分析：思路就是，如果某首歌被5个人收藏了(User表中一共5个人)，那么便符合



--3.查找一首歌曲都没有收藏的用户(两种实现方式：相关嵌套查询，不相关嵌套查询)

-- 相关子查询
select *
from Users u	-- 可变的量，每次执行子查询时该值都变一次(下一行)
where not exists 
(
select * from Collections cl where u.UserName = cl.UserName		-- 子查询重复多次，每次的u.UserName都不一样
)

-- 无关子查询
select *
from Users
where UserName not in
(
select distinct UserName
from Collections
)





---- PPT5 —— 视图相关练习 ----

--1.商家开展“买200送100”的促销活动，为一次购买某专辑总价格在200元(含)以上的用户的会员卡充值100元
update Users
set UserAdvancePayment = UserAdvancePayment + 100
where  UserName IN 
(
select UserName 
from Sale,Orders 
where Sale.OrderID=Orders.OrderID and TotalPrice >= 200
)


--2.新建“专辑销售统计表”Sales_Static，其结构能存放专辑名，销售量和销售额。将各个专辑销售量和销售额的统计结果存入Sales_Static表中
-- 先建表
create table Sales_Staic
(
	AlbumName		varchar(64),
	Quantity		tinyint not null,
	TotalPrice		numeric(10, 2) not null,
)
-- 插入数据
insert into Sales_Staic
select AlbumName, Quantity, TotalPrice 
from Album a, (select AlbumID, SUM(Quantity) as Quantity, SUM(TotalPrice) as TotalPrice from Sale group by AlbumID) as s
where  a.AlbumID = s.AlbumID 
-- 分析：向已存在的表中插入数据(快速备份数据)


--3.创建视图SongByAlbumName，该视图包括“歌曲表”中所有列以及专辑名称，并按歌曲顺序号由小到大排列
create view SongByAlbumName
as
select top 100 percent s.*, a.AlbumName
from Songs s, Album a
where s.AlbumID = a.AlbumID
order by SongID asc
-- 分析：考察点是创建视图
-- 但是有一点要极为注意：select + order by 的组合返回的是游标而非table，因此直接select传给view会报错
-- 因而必须加上 top 100 percent 转化成table


--4.查询此视图
select * from SongByAlbumName


--5.将此视图SongId为5的歌曲修改歌曲名称为“北京2077”，看看是否成功，如果失败怎么办？
update SongByAlbumName
set SongTitle = '北京2077'
where SongID = 5
-- 成功


--6.将此视图SongId为5的歌曲修改歌曲名称为“北京2077”，专辑名称改为“魔杰座”，看看是否成功，如果失败怎么办？
update SongByAlbumName
set SongTitle = '北京2077', AlbumName = '魔杰座'
where SongID = 5
-- 失败

-- 分析：以上两个题都是"通过视图修改数据"
--
-- 但实际上修改的是视图吗？不是，是【基表】。
-- 先要明白视图的本质：它只是一个【引用了基表的select语句】，而并非一个实际的【table】，并不含有记录
--
-- 因此："通过视图修改数据"就是修改基表，修改的规则自然也和修改基表(update)的规则一样：
-- 基表要分开一个个更新，你不能通过一个语句影响了多个基表!
-- 那么正确的姿势也就很明了了：

update SongByAlbumName
set SongTitle = '北京2077' where SongID = 5		
update SongByAlbumName
set AlbumName = '魔杰座' where SongID = 5

-- 还有一个小细节，执行后显示：
--(1 行受影响)
--(1 行受影响)
-- 指的是Songs表中的一行、Album表中的一行
-- 而在视图SongByAlbumName中的显示中有很多行受到了影响





---- PPT6 —— 完整性相关练习 ----

--1.创建主键约束，将User表的"用户名"列设为主键 (新建主键后，再删掉它)
alter table Users							-- 对约束的操作是表(table)层面的
add constraint pk primary key(UserName)		-- 添加主键时要保证其非空 not null

alter table Users
drop constraint pk		--删除主键



--2.创建外键约束，将Songs表中的AlbumID设置为外键FK_Songs_Album，
--	该外键参照Album表的主键AlbumID，且违约时采用"级联更新"和"级联删除"的策略
alter table Songs
add constraint FK_Songs_Album foreign key(AlbumID) references Album(AlbumID)
on update cascade
on delete set null	

-- 补充：
-- 级联更新demo：Students表中有学号003，Score表中有003与成绩共同组成的记录(Score引用Students中的学号)
--				如果Students表中修改学号003为333，那么Score表中关于003的记录同样被级联更新为333
-- 级联删除demo：还是上面的例子
--				如果Students表中删除学号003，那么Score表中关于003的记录被级联删除



--3.创建唯一性约束，为Songs表的"歌曲名"列创建唯一性约束IX_SongTitle
alter table Songs
add constraint IX_SongTitle unique(SongTitle)



--4.创建检查约束，为Album表的"专辑语言"列创建一个检查约束CK_Language，
--	取值范围为汉语普通话、粤语、英语、日语、韩语、多国、其他
alter table Album
add constraint CK_Language check(AlbumLanguage in('汉语普通话','粤语','英语','日语','韩语','多国','其他'))