solutions to More Join Operation Questions (Movie Database)

Tags: t-sql, sqlzoo, sql

While meandering around the web for sql exercises and tutorials I came across

It is the perfect place to get hands on knowledge for SQL, especially JOINS!

Below are solutions to all the questions on the More JOIN Operations tutorial. Questions 1-6 are plain straightforward. Questions 7-11 are basic join questions and 12-16 and pretty hard. I ended up spending quite some time trying to solve the last bunch.

Here are all the solutions in case you got stumped. (I just included all questions for completeness)

Problem 1 : List the films where the yr is 1962 [Show id, title]

This one is pretty straigh-forward.

Problem 2 : Give year of 'Citizen Kane'.

This one too :

Problem 3 : 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.)

Problem 4 : What are the titles of the films with id 11768, 11955, 21191

Problem 5 : What id number does the actor 'Glenn Close' have?

Problem 6 : What is the id of the film 'Casablanca'

Real join questions begin :

Problem 7 : Obtain the cast list for 'Casablanca'. Use the id value that you obtained in the previous question.

Problem 8 : Obtain the cast list for the film 'Alien'

Problem 9 : List the films in which 'Harrison Ford' has appeared

Problem 10 : List the films where 'Harrison Ford' has appeared - but not in the star role. [Note: the ord field of casting gives the position of the actor. If ord=1 then this actor is in the starring role]

Problem 11: List the films together with the leading star for all 1962 films.

Hard Questions.

These questions are a little bit hard. Give some time to crack them before looking at the solutions.

Problem 12 : Which were the busiest years for 'John Travolta'. Show the number of movies he made for each year.

Problem 13 : List the film title and the leading actor for all of 'Julie Andrews' films.

Problem 14 : Obtain a list of actors in who have had at least 30 starring roles. (Yep, the result set this generates is a empty set)

Problem 15 : List the 1978 films by order of cast list size.

Problem 16 : List all the people who have worked with 'Art Garfunkel'.

And there you have it! Hope it was helpful.

Comments powered by Disqus