A Gentle Introduction to

SQL

Top of the pops

Looking at the data.

1a	Take a look at some of the data - select songs concerning `house` .	SELECT * FROM totp WHERE song LIKE '%house%'

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#

Date calculations

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'

Just for fun

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

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?