SQLzoo.cn

1a. Take a look at some of the data - select songs concerning house .

SELECT * FROM totp WHERE song LIKE '%house%'

SELECT * FROM totp WHERE song LIKE '%house%'

1b. Find the acts which played on 20th May 1982. The format used for dates varies from one SQL engine to another. Try any of

'20 May 1982'	(Oracle & SQL Server),
#20 May 1982#	(Access),
'1982-05-20'	(MySQL)

SELECT * FROM totp WHERE wk = #05/20/1982#

1c. Find details of the show that took place in the week following 20th May 1980 - you should select for shows between 20th and the 27th.

SELECT * FROM totp WHERE wk BETWEEN #05/20/80# AND #05/27/80#

2a. Show details of the shows that took place on any of your birthdays - for any year.

SELECT * FROM totp WHERE
  day(wk)=20 AND month(wk)=5

2b. The show used to go out on Thursday, more recently it's been on on Friday - show the number of shows for each day of the week.

SELECT weekday(wk), count(wk) FROM totp GROUP BY weekday(wk)

2c. Show the first and the last dates that 'Lulu' played Top of the Pops

SELECT min(wk), max(wk) FROM totp WHERE singer='Lulu'

3a. Which singers have had most appearances for each decade? Please don't try it as it takes a long time to execute...

select singer, decade, song_count from (select singer, decade, count(song) as song_count from (select singer, CInt(year(wk)/10)*10 as decade, song from totp) group by singer, decade) where decade & ':' & song_count in (select decade & ':' & song_count_max from (select decade , max(song_count) as song_count_max from (select singer, decade, count(song) as song_count from (select singer, CInt(year(wk)/10)*10 as decade, song from totp) group by singer, decade) group by decade))

3b. Which song has been most covered?

3c. Who's career has the longest span?

3d. Which are the one hit wonders?