SQLZOO Select Basics Practices Solutions.

1962 movies

1.

List the films where the yr is 1962 [show id, title]

								
SELECT id, title
FROM movie
WHERE yr=1962
								
							

When was Citizen Kane released

2.

Give year of 'Citizen Kane'.

								
SELECT yr
FROM movie
WHERE title = 'citizen kane';
								
							

Star Trek movies

3.

List all of the Star Trek movies, include the id, title and year (all of these movies include the words Star Trek in the title). Order results by year.

								
SELECT id, title, yr
FROM movie 
WHERE title like '%star trek%'
ORDER BY yr;
								
							

id for actor Glenn Close

4.

What id number does the actor 'Glenn Close' have?

								
SELECT id
FROM actor
WHERE name = 'glenn close';
								
							

id for Casablanca

5.

What is the id for the film 'Casablanca'

								
SELECT id
FROM movie
WHERE title = 'casablanca';
								
							

Cast list for Casablanca

6.

Obtain the cast list for 'casablanca'
Use movieid=11768, (or whatever value you got from the previous question)

								
SELECT name
FROM actor
JOIN casting ON id = actorid
WHERE movieid = 11768;

If movieid is not known or can change

SELECT name
FROM actor
JOIN casting ON id = actorid
WHERE movieid = (SELECT id FROM movie WHERE title = 'casablanca');
								
							

Alien cast List

7.

Obtain the cast list for the film 'Alien'

								
select name
from actor A
join casting C on A.id = C.actorid
JOIN movie M ON C.movieid = M.id
WHERE title = 'alien';
								
							

harrison Ford movies

8.

List the films in which 'Harrison Ford' has appeared

								
SELECT title FROM movie M
JOIN casting C ON M.id = C.movieid
JOIN actor A ON C.actorid = A.id
WHERE name = 'harrison ford';
    
								
							

Harrison Ford as a supporting actor

9.

List the films where 'Harrison Ford' has appeared - but not in the starring role.

								
SELECT title FROM movie M
JOIN casting C ON M.id = C.movieid
JOIN actor A ON C.actorid = A.id
WHERE name = 'harrison ford'
AND ord != 1;
								
							

Lead actors in 1962 movies

10.

List the films together with the leading star for all 1962 films.

								
SELECT title, name
FROM movie M
JOIN casting C ON M.id = C.movieid
JOIN actor A ON C.actorid = A.id
WHERE yr = 1962
AND ord = 1;
								
							

Busy years for Rock Hudson

11.

Which were the busiest years for 'Rock Hudson', show the year and the number of movies he made each year for any year in which he made more than 2 movies.

								
SELECT yr,COUNT(title) FROM
  movie JOIN casting ON movie.id=movieid
        JOIN actor   ON actorid=actor.id
WHERE name='rock hudson'
GROUP BY yr
HAVING COUNT(title) > 2;
								
							

Lead actor in Jolie Andrews movies

12.

List the film title and the leading actor for all of the films 'Julie Andrews' played in.

								
SELECT title, A.name
FROM movie M
JOIN casting C ON M.id = C.movieid
JOIN actor A ON A.id = C.actorid
WHERE ord = 1
AND M.id  IN (
SELECT movieid FROM casting C
JOIN actor A ON C.actorid = A.id
WHERE A.name = 'Julie Andrews');
								
							

Actors with 15 leading roles

13.

Obtain a list, in alphabetical order, of actors who've had at least 15 starring roles.

								
SELECT name FROM actor A
JOIN casting C ON A.id = C.actorid
JOIN movie M ON C.movieid = M.id
WHERE ord = 1
GROUP BY name
HAVING COUNT(title) >= 15
ORDER BY A.name;
								
							

Released in the year 1978

14.

List the films released in the year 1978, and how many actors they cast, ordered first by the number of actors in the cast, then by title.

								
SELECT title, COUNT(actorid) FROM movie M
JOIN casting C ON M.id = C.movieid
JOIN actor A ON C.actorid = A.id
WHERE M.yr = 1978
GROUP BY title
ORDER BY count(actorid) DESC, title;