实验02 数据查询

/*******
实验02 数据查询
*******/

【实验内容】
USE NetMusicShop

0.准备工作
（1）设置Users表的UserPassword列不能为空。
ALTER TABLE Users ALTER COLUMN UserPassword varchar(6) NOT NULL

–（2）建立歌曲表Songs和收藏表Collections。
CREATE TABLE Songs(
SongID tinyint identity(1,1),
SongNumber tinyint,
AlbumID tinyint,
SongTitle varchar(256) NOT NULL,
SongDuration char(8),
SongContent varchar(4096),
SongUploadDate smalldatetime ,
SongUrl varchar(200),
SongFormat varchar(10),
SongLanguage varchar(10),
SongType tinyint,
SongSinger varchar(32),
SongIsRecommend bit,
PRIMARY KEY(SongID))

CREATE TABLE Collections(
CollectionID tinyint identity(1,1) CONSTRAINT PK_Collections PRIMARY KEY,
SongID tinyint,
UserName varchar(20),
CollectionDate smalldatetime)

–将本次用的实验用数据脚本运行，插入数据

1.简单数据查询
（1）用中文标题显示所有专辑的专辑名称、发行公司、发行时间、专辑类别和专辑歌手信息，并按发行时间的降序排列。

SELECT AlbumName AS 专辑名称,AlbumIssueCompany AS 发行公司,AlbumIssueDate AS 发行时间,AlbumType AS 专辑类别,AlbumSinger AS 专辑歌手
FROM Album
ORDER BY AlbumIssueDate DESC

–（2）查找专辑名称中包含“歌”且会员价格低于80元的专辑，显示其全部信息。
SELECT *
FROM Album
WHERE AlbumMemberPrice<80 AND AlbumName LIKE ‘%歌%’

–（3）查找收藏歌曲在2首以上的用户及其收藏的歌曲数，显示中文标题。
SELECT UserName 用户名,COUNT(*) 收藏歌曲数
FROM Collections
GROUP BY UserName HAVING COUNT(*)>= 2

–（4）显示各公司发行的专辑最低和最高的市场价格。
SELECT AlbumIssueCompany 发行公司,MIN(AlbumMarketPrice) 最低市场价格,
MAX(AlbumMarketPrice) 最高市场价格
FROM Album
GROUP BY AlbumIssueCompany

–（5）查找所包含的歌曲数大于等于10首的专辑，显示专辑名和所包含的歌曲数。

SELECT AlbumName 专辑名,COUNT(*) 歌曲数
FROM Album,Songs
WHERE Album.AlbumID=Songs.AlbumID
GROUP BY AlbumName HAVING COUNT(*)>=10

–（6）列出演唱流行歌曲的歌手名单（流行/Pop）
SELECT DISTINCT SongSinger
FROM Songs,MusicCategory
WHERE Songs.SongType=MusicCategory.CategoryID
AND CategoryName=’流行/Pop’

–（7）查找最近5年来销售额位列前3名的专辑信息
SELECT Sale.AlbumID,SUM(TotalPrice),AlbumName
FROM Sale,Orders, Album
WHERE Sale.OrderID=Orders.OrderID
and Sale.AlbumID = Album.AlbumID
AND YEAR(OrderDate) BETWEEN YEAR(GETDATE())-5 AND YEAR(GETDATE())
GROUP BY Sale.AlbumID,AlbumName
ORDER BY SUM(TotalPrice)DESC

–（8）查询没有被收藏的歌曲
SELECT *
FROM Songs left OUTER JOIN Collections ON Songs.SongID=Collections.SongID
WHERE Collections.SongID IS NULL