SQLzoo 习题记录07-More JOIN operations & Quiz

目录

More JOIN operations

1. 1962 movies
2. When was Citizen Kane released?
3. Star Trek movies
4. id for actor Glenn Close
5. id for Casablanca
6. Cast list for Casablanca
7. Alien cast list
8. Harrison Ford movies
9. Harrison Ford as a supporting actor
10. Lead actors in 1962 movies
11. Busy years for Rock Hudson
12. Lead actor in Julie Andrews movies

13-15. Actors with 15 leadign roles

• 13.
• 14.
• 15.

JOIN Quiz 2

• 1. Select the statement which lists the unfortunate directors of the movies which have caused financial loses (gross < budget)
• 2. Select the correct example of JOINing three tables
• 3. Select the statement that shows the list of actors called 'John' by order of number of movies in which they acted
• 4. Select the result that would be obtained from the following code:
• 5. Select the statement that lists all the actors that starred in movies directed by Ridley Scott who has id 351
• 6. There are two sensible ways to connect movie and actor. They are:
• 7. Select the result that would be obtained from the following code:

---

More JOIN operations

网址：More JOIN operations - SQLZOO

This tutorial introduces the notion of a join. The database consists of three tables movie , actor and casting .

movie

id

title

yr

director

budget

gross

 

actor

id

name

 

casting

movieid

actorid

ord

 

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 yr (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 of the film 'Casablanca'

• SELECT id FROM movie
• WHERE title = 'Casablanca';

Cast list for Casablanca

6.

Obtain the cast list for 'Casablanca'.

what is a cast list?

The cast list is the names of the actors who were in the movie.

Use movieid=11768, (or whatever value you got from the previous question)

• SELECT name FROM actor A
• LEFT JOIN Casting C ON a.id=c.actorid
• LEFT JOIN movie M ON m.id = c.movieid
• WHERE movieid=27;

Alien cast list

7.

Obtain the cast list for the film 'Alien'

• SELECT name FROM actor A
• LEFT JOIN Casting C ON a.id=c.actorid
• LEFT JOIN movie M ON m.id = c.movieid
• WHERE title ='Alien';

Harrison Ford movies

8.

List the films in which 'Harrison Ford' has appeared

• SELECT title FROM actor A
• LEFT JOIN Casting C ON a.id=c.actorid
• LEFT JOIN movie M ON m.id = c.movieid
• 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. [Note: the ord field of casting gives the position of the actor. If ord=1 then this actor is in the starring role]

• SELECT title FROM actor A
• LEFT JOIN Casting C ON a.id=c.actorid
• LEFT JOIN movie M ON m.id = c.movieid
• 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 actor A
• LEFT JOIN Casting C ON a.id=c.actorid
• LEFT JOIN movie M ON m.id = c.movieid
• WHERE yr = 1962
•       AND ord = 1;

Harder Questions

---

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 Julie Andrews movies

12.

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

Did you get "Little Miss Marker twice"?

Julie Andrews starred in the 1980 remake of Little Miss Marker and not the original(1934).

Title is not a unique field, create a table of IDs in your subquery(不知道为什么我没有遇到提示的问题，不过这题的代码是真的长，希望有人能指导一下更加简化的写法呢~）

P.S.橙色部分是重复字段，关联三张表用的

• Select title,name FROM actor A
• LEFT JOIN Casting C ON a.id=c.actorid
• LEFT JOIN movie M ON m.id = c.movieid
• Where title IN(SELECT title FROM actor A
•                        LEFT JOIN Casting C ON a.id=c.actorid
•                        LEFT JOIN movie M ON m.id = c.movieid
•                        Where name = 'Julie Andrews')
•      And ord = 1；

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
• LEFT JOIN casting c ON a.id = c.actorid
• WHERE ord=1
• GROUP BY name
• HAVING COUNT(*) >=15
• ORDER BY name;

14.

List the films released in the year 1978 ordered by the number of actors in the cast, then by title.

• SELECT title,COUNT(DISTINCT actorid) FROM movie M
• LEFT JOIN casting C ON m.id = c.movieid
• WHERE yr = 1978
• GROUP BY title
• ORDER BY COUNT(DISTINCT actorid) desc,title;

15.

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

P.S.橙色部分是重复字段，关联actor 和 casting表用的。

• SELECT DISTINCT name FROM actor A
• LEFT JOIN casting C ON a.id=c.actorid
• Where name <> 'Art Garfunkel'
•   And movieid IN(SELECT movieid From actor A
•                             LEFT JOIN casting C ON a.id=c.actorid
•                             WHERE name = 'Art Garfunkel')
• ORDER BY name;

JOIN Quiz 2

网址：JOIN Quiz 2 - SQLZOO

1. Select the statement which lists the unfortunate directors of the movies which have caused financial loses (gross < budget)

• SELECT name FROM actor
• INNER JOIN movie ON actor.id = director
• WHERE gross < budget;

2. Select the correct example of JOINing three tables

• SELECT * FROM actor
• JOIN casting ON actor.id = actorid
• JOIN movie ON movie.id = movieid;

3. Select the statement that shows the list of actors called 'John' by order of number of movies in which they acted

• SELECT name, COUNT(movieid) FROM casting
• JOIN actor ON actorid=actor.id
• WHERE name LIKE 'John %'
• GROUP BY name
• ORDER BY 2 DESC;

4. Select the result that would be obtained from the following code:

•  SELECT title 
     FROM movie JOIN casting ON (movieid=movie.id)
                JOIN actor   ON (actorid=actor.id)
    WHERE name='Paul Hogan' AND ord = 1

Table-B

"Crocodile" Dundee Crocodile Dundee in Los Angeles Flipper Lightning Jack

5. Select the statement that lists all the actors that starred in movies directed by Ridley Scott who has id 351

• SELECT name FROM movie
• JOIN casting ON movie.id = movieid
• JOIN actor ON actor.id = actorid
• WHERE ord = 1
• AND director = 351;

6. There are two sensible ways to connect movie and actor. They are:

• link the director column in movies with the primary key in actor
• connect the primary keys of movie and actor via the casting table

7. Select the result that would be obtained from the following code:

•  SELECT title, yr 
     FROM movie, casting, actor 
    WHERE name='Robert De Niro' AND movieid=movie.id AND actorid=actor.id AND ord = 3

Table-B

A Bronx Tale 1993 Bang the Drum Slowly 1973 Limitless 2011