SQL Answers

SELECT .. GROUP BY
1a. Show the total number of prizes awarded.
A : SELECT COUNT(winner) FROM nobel

1b. List each subject - just once
A : select distinct subject from nobel

1c. Show the total number of prizes awarded for Physics
A : select count(subject) from nobel where subject = 'Physics'

2a. For each subject show the subject and the number of prizes.
A : select subject, count(winner) from nobel group by subject

2b. For each subject show the first year that the prize was awarded.
A: select subject, min(yr) from nobel group by subject

2c. For each subject show the number of prizes awarded in the year 2000.
A : select subject, count(winner) from nobel where yr = 2000 group by subject

3b. For each subject show how many years have had prizes awarded.
A : select subject, count(distinct yr) from nobel group by subject

4a. Show the years in which three prizes were given for Physics.
A : select yr from nobel where subject = 'Physics' group by yr, subject having count(subject) > 2

4b. Show winners who have won more than once.
A : select winner from nobel group by winner having count(winner) > 1

4c. Show winners who have won more than one subject.
A : select winner from nobel group by winner having count(distinct subject) > 1

5a. Show the year and subject where 3 prizes were given. Show only years 2000 onwards.
A : SELECT yr, subject
FROM nobel
WHERE yr>=2000
GROUP BY yr, subject
HAVING COUNT(subject)=3

Select JOIN
1a. Find the title and artist who recorded the song 'Alison'.
A : SELECT title, artist
FROM album JOIN track
ON (album.asin=track.album)
WHERE song = 'Alison'


1b. Which artist recorded the song 'Exodus'?
A : SELECT artist
FROM album JOIN track
ON (album.asin=track.album)
WHERE song = 'Exodus'

1c. Show the song for each track on the album 'Blur'
A : SELECT song
FROM track JOIN album
ON (album.asin = track.album)
WHERE album.title = 'Blur'

2a. For each album show the title and the total number of track.
A : SELECT title, COUNT(*)
FROM album JOIN track ON (asin=album)
GROUP BY title

2b. For each album show the title and the total number of tracks containing the word 'Heart' (albums with no such tracks need not be shown).
A : SELECT title, count(*)
FROM album JOIN track
ON(album = asin)
WHERE track.song like '%Heart%'
GROUP BY title

2c. A "title track" is where the song is the same as the title. Find the title tracks.
A : SELECT title
FROM album JOIN track
ON (album.asin = track.album)
WHERE album.title = track.song

2d. An "eponymous" album is one where the title is the same as the artist (for example the album 'Blur' by the band 'Blur'). Show the eponymous albums.
A : SELECT artist FROM album
WHERE title = artist

3a. Find the songs that appear on more than 2 albums. Include a count of the number of times each shows up.
A : SELECT song, count(asin)
FROM album JOIN track
ON (album.asin = track.album)
GROUP BY song
HAVING count(song) >2

3b. A "good value" album is one where the price per track is less than 50 pence. Find the good value album - show the title, the price and the number of tracks.
A : SELECT title, price, count(track.song)
FROM album JOIN track
ON (album.asin = track.album)
GROUP BY album.title
HAVING (album.price/count(track.song)) < 0.5

3c. Wagner's Ring cycle has an imposing 173 tracks, Bing Crosby clocks up 101 tracks.
List albums so that the album with the most tracks is first. Show the title and the number of tracks
SELECT title, count(track.song)
FROM album JOIN track
ON (album.asin = track.album)
GROUP BY title
ORDER BY count(track.song) desc

Movie Database

List the films where the yr is 1962 [Show id, title]
select id, title from movie where yr = '1962'


List all of the Star Trek movies, include the id title and yr. (All of these movies include the words Star Trek in the title.)
SELECT id, title, yr FROM movie WHERE title like 'Star Trek%'


What are the titles of the films with id 1, 2, 3
select title FROM movie where id in (1,2,3)


Obtain the cast list for the film 'Alien'
SELECT name FROM actor JOIN casting
ON (casting.actorid = actor.id)
WHERE movieid = (select id from movie where title = 'Alien')


List the films in which 'Harrison Ford' has appeared
SELECT title from movie join casting
ON (movie.id = casting.movieid)
WHERE actorid = (select id FROM actor where name ='Harrison Ford')


List the films where 'Harrison Ford' has appeared - but not in the star role.
select title from movie join casting on (movie.id = casting.movieid)
WHERE ord != 1 and actorid = (select id FROM actor where name = 'Harrison Ford')


List the films together with the leading star for all 1962 films.
select title, name from movie join casting on (movie.id = casting.movieid) join actor on (casting.actorid = actor.id) where movie.yr =
'1962' and casting.ord = 1