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

[hehehedada]

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)

[等不到来世]

--(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