求助!一条很简单的SQL题!

hehehedada 2015-11-23 05:32:30
2. Consider the following table containing information about a CD collection:

CD (artist, album, tracks, company, year)

Names of artist and album are the table’s primary key, tracks is the number of tracks on the album, company refers to the record company that has produced it and year denotes its release year.

Write SQL queries to produce the following results:

(a) All albums that were released before (or in the same year as) ‘The Colour and the Shape’ by the
‘Foo Fighters’. (10 marks)


(b) All artists whose name appears as part of the title of some album in the database. For example, you
would list ‘Prince’ if the database contains both ‘Purple Rain’ by ‘Prince’ and the album ‘Prince
Charming’ by ‘Adam Ant’. (10 marks)


(c) All albums containing either the minimum or maximum number of tracks among all albums in the
database. (10 marks)


(d) All artists with more than one album along with their average number of tracks per album. Use
GROUP BY and HAVING to achieve this result. (10 marks)


(e) The same as in (d). This time, however, you may only use GROUP BY and subqueries, but no
HAVING. (10 marks)
...全文
151 点赞 收藏 1
写回复
1 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
等不到来世 2015-11-25
--(a):
select album from CD
where [year]<=(select [year] from CD where artist='Foo Fighters' and album='The Colour and the Shape')

--(b):
select artist from CD as t
where exists(select 1 from CD where CHARINDEX(t.artist,album)>0)

--(c):
select album from CD
where  tracks in ((select MIN(tracks) from CD),(select MAX(tracks) from CD))

--(d):
select artist,SUM(tracks)/COUNT(album) as AvgTrack 
from CD
group by artist
having COUNT(album)>1


--(e):
select artist,SUM(tracks)/COUNT(album) as AvgTrack 
from CD as t
where exists(select 1 from CD where artist=t.artist and album<>t.album)
group by artist
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2015-11-23 05:32
社区公告
暂无公告