Difference between revisions of "More JOIN operations"

From SQLZOO
Jump to: navigation, search
(Undo revision 9756 by 195.230.174.90 (talk))
(38 intermediate revisions by 12 users not shown)
Line 1: Line 1:
 +
<p>This tutorial introduces the notion of a join. The database
 +
consists of three tables
 +
  <code>movie</code>
 +
  ,
 +
  <code>actor</code>
 +
  and
 +
  <code>casting</code>
 +
  .
 +
</p>
  
 +
<table style='display:inline-block;margin:2ex;'><caption>movie</caption>
 +
<tr>
 +
<td><b>id</b></td>
 +
<td>title</td>
 +
<td>yr</td>
 +
<td><i>director</i></td>
 +
<td>budget</td>
 +
<td>gross</td>
 +
</tr>
 +
</table>
 +
 +
<table style='display:inline-block;margin:2ex;'><caption>actor</caption>
 +
<tr>
 +
<td><b>id</b></td>
 +
<td>name</td>
 +
</tr>
 +
</table>
 +
 +
<table style='display:inline-block;margin:2ex;'><caption>casting</caption>
 +
<tr>
 +
<td><b><i>movieid</i></b></td>
 +
<td><b><i>actorid</i></b></td>
 +
<td><b>ord</b></td>
 +
</tr>
 +
</table>
 +
 +
[[File:movie-er.png]]
 +
</div>
 +
  <p>
 +
    [[More details about the database.]]
 +
  </p>
 +
 +
 +
<div class="progress_panel"><div>
 +
  <div class="summary">Summary</div>
 +
  <div class="progressbarbg">
 +
    <div class="progressbar"></div>
 +
  </div>
 +
</div></div>
 +
 +
==1962 movies==
 +
<div class='qu'>
 +
List the films where the '''yr''' is 1962 [Show '''id''', '''title'''] 
 +
<source lang='sql' class='def'>
 +
SELECT id, title
 +
FROM movie
 +
WHERE yr=1962
 +
</source>
 +
 +
<source lang='sql' class='ans'>
 +
SELECT id, title
 +
FROM movie
 
  WHERE yr=1962
 
  WHERE yr=1962
 
</source>
 
</source>
 
</div>
 
</div>
  
 +
==When was Citizen Kane released?==
 
<div class='qu'>
 
<div class='qu'>
 
Give year of 'Citizen Kane'.     
 
Give year of 'Citizen Kane'.     
Line 16: Line 78:
 
</div>
 
</div>
  
 +
==Star Trek movies==
 
<div class='qu'>
 
<div class='qu'>
 
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.  
 
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.  
Line 28: Line 91:
 
</div>
 
</div>
  
<h2>Looking at the '''id''' field.</h2>
+
==id for actor Glenn Close==
<div class='qu'>
 
What are the titles of the films with '''id''' 11768, 11955, 21191 
 
<source lang='sql' class='def'>
 
</source>
 
 
 
<source lang='sql' class='ans'>
 
SELECT title
 
FROM movie
 
WHERE id IN (11768, 11955, 21191)
 
</source>
 
</div>
 
 
 
 
<div class='qu'>
 
<div class='qu'>
 
What '''id''' number does the actor 'Glenn Close' have?   
 
What '''id''' number does the actor 'Glenn Close' have?   
Line 53: Line 104:
  
  
 +
==id for Casablanca==
 
<div class='qu'>
 
<div class='qu'>
 
What is the '''id''' of the film 'Casablanca'     
 
What is the '''id''' of the film 'Casablanca'     
Line 65: Line 117:
 
</div>
 
</div>
  
<h2>[[Get to the point]]</h2>
+
[[Get to the point]]
 +
==Cast list for Casablanca==
 
<div class='qu'>
 
<div class='qu'>
Obtain the cast list for 'Casablanca'.  
+
Obtain the cast list for 'Casablanca'.
Use the '''id''' value that you obtained in the previous question.  
+
<div title='what is a cast list?' class=hint>
 +
The cast list is the names of the actors who were in the movie.
 +
</div>
 +
Use '''movieid=11768''', (or whatever value you got from the previous question)  
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
Line 82: Line 138:
 
</div>
 
</div>
  
 +
==Alien cast list==
 
<div class='qu'>
 
<div class='qu'>
 
Obtain the cast list for the film 'Alien'   
 
Obtain the cast list for the film 'Alien'   
Line 96: Line 153:
 
</div>
 
</div>
  
 +
==Harrison Ford movies==
 
<div class='qu'>
 
<div class='qu'>
 
List the films in which 'Harrison Ford' has appeared     
 
List the films in which 'Harrison Ford' has appeared     
Line 110: Line 168:
 
</div>
 
</div>
  
 +
==Harrison Ford as a supporting actor==
 
<div class='qu'>
 
<div class='qu'>
List the films where 'Harrison Ford' has appeared - but not in the star role.  
+
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.  
 
[Note: the '''ord''' field of casting gives the position of the actor.  
 
If ord=1 then this actor is in the starring role]     
 
If ord=1 then this actor is in the starring role]     
Line 127: Line 186:
 
</div>
 
</div>
  
 +
==Lead actors in 1962 movies==
 
<div class='qu'>
 
<div class='qu'>
 
List the films together with the leading star for all 1962 films.       
 
List the films together with the leading star for all 1962 films.       
Line 141: Line 201:
 
</source>
 
</source>
 
</div>
 
</div>
 +
<hr>
 +
Harder Questions
 +
<hr>
  
<h2>Harder Questions</h2>
+
==Busy years for John Travolta==
 
<div class='qu'>
 
<div class='qu'>
Which were the busiest years for 'John Travolta', show the year and the number of movies he made each year for any year in which he made at least 2 movies.       
+
Which were the busiest years for 'John Travolta', show the year and the number of movies he made each year for any year in which he made more than 2 movies.       
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
SELECT yr,COUNT(title) FROM
 
SELECT yr,COUNT(title) FROM
Line 175: Line 238:
 
</source>
 
</source>
 
</div>
 
</div>
 +
==Lead actor in Julie Andrews movies==
 +
<div class='qu'>
 +
List the film title and the leading actor for all of the films 'Julie Andrews' played in.
 +
<div class='hint' title='Did you get "Little Miss Marker twice"?'>
 +
Julie Andrews starred in the 1980 remake of Little Miss Marker and not the original(1934).
  
<div class='qu'>
+
Title is not a unique field, create a table of IDs in your subquery
List the film title and the leading actor for all of the films 'Julie Andrews' played in. 
+
</div>
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 +
SELECT movieid FROM casting
 +
WHERE actorid IN (
 +
  SELECT id FROM actor
 +
  WHERE name='Julie Andrews')
 
</source>
 
</source>
  
Line 192: Line 264:
 
     AND name='Julie Andrews')
 
     AND name='Julie Andrews')
 
</source>
 
</source>
 +
{{#ev:youtube|BcNIDK5qYx8|854x480}}
 
</div>
 
</div>
  
 +
==Actors with 30 leading roles==
 
<div class='qu'>
 
<div class='qu'>
Obtain a list in alphabetical order of actors who've had at least 30 starring roles.         
+
Obtain a list, in alphabetical order, of actors who've had at least 30 starring roles.         
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
Line 210: Line 284:
  
 
<div class='qu'>
 
<div class='qu'>
List the 1978 films by order of cast list size.        
+
<span class='param respectorder'></span>
 +
List the films released in the year 1978 ordered by the number of actors in the cast, then by title.
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
  
 
<source lang='sql' class='ans'>
 
<source lang='sql' class='ans'>
SELECT title, COUNT(actorid)
+
  SELECT title, COUNT(actorid)
   FROM casting, movie
+
   FROM casting,movie              
 
   WHERE yr=1978
 
   WHERE yr=1978
    AND movieid=movie.id
+
        AND movieid=movie.id
 
   GROUP BY title
 
   GROUP BY title
   ORDER BY 2 DESC
+
   ORDER BY 2 DESC,1 ASC
 
</source>
 
</source>
 
</div>
 
</div>

Revision as of 20:50, 26 November 2017

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

Movie-er.png </div>

More details about the database.


Summary

1962 movies

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

SELECT id, title
 FROM movie
 WHERE yr=1962
SELECT id, title
 FROM movie
 WHERE yr=1962

When was Citizen Kane released?

Give year of 'Citizen Kane'.


SELECT yr 
FROM movie 
WHERE title='Citizen Kane'

Star Trek movies

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

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


SELECT id FROM actor
  WHERE name= 'Glenn Close'


id for Casablanca

What is the id of the film 'Casablanca'


SELECT id 
FROM movie 
WHERE title='Casablanca'

Get to the point

Cast list for Casablanca

Obtain the cast list for 'Casablanca'.

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 casting, actor
  WHERE movieid=(SELECT id 
             FROM movie 
             WHERE title='Casablanca')
    AND actorid=actor.id

Alien cast list

Obtain the cast list for the film 'Alien'


SELECT name
  FROM movie, casting, actor
  WHERE title='Alien'
    AND movieid=movie.id
    AND actorid=actor.id

Harrison Ford movies

List the films in which 'Harrison Ford' has appeared


SELECT title
  FROM movie, casting, actor
 WHERE name='Harrison Ford'
    AND movieid=movie.id
    AND actorid=actor.id

Harrison Ford as a supporting actor

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 movie, casting, actor
 WHERE name='Harrison Ford'
    AND movieid=movie.id
    AND actorid=actor.id
  AND ord<>1

Lead actors in 1962 movies

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


SELECT title, name
  FROM movie, casting, actor
 WHERE yr=1962
    AND movieid=movie.id
    AND actorid=actor.id
    AND ord=1

Harder Questions


Busy years for John Travolta

Which were the busiest years for 'John Travolta', 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='John Travolta'
GROUP BY yr
HAVING COUNT(title)=(SELECT MAX(c) FROM
(SELECT yr,COUNT(title) AS c FROM
   movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
 where name='John Travolta'
 GROUP BY yr) AS t
)
SELECT yr,COUNT(title) FROM
  movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
where name='John Travolta'
GROUP BY yr
HAVING COUNT(title)=(SELECT MAX(c) FROM
(SELECT yr,COUNT(title) AS c FROM
movie JOIN casting ON movie.id=movieid 
JOIN actor   ON actorid=actor.id
 where name='John Travolta'
 GROUP BY yr) AS t
)

Lead actor in Julie Andrews movies

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

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

SELECT movieid FROM casting
WHERE actorid IN (
  SELECT id FROM actor
  WHERE name='Julie Andrews')
SELECT title, name
  FROM movie, casting, actor
  WHERE movieid=movie.id
    AND actorid=actor.id
    AND ord=1
    AND movieid IN
    (SELECT movieid FROM casting, actor
     WHERE actorid=actor.id
     AND name='Julie Andrews')

Actors with 30 leading roles

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


SELECT name
    FROM casting JOIN actor
      ON  actorid = actor.id
    WHERE ord=1
    GROUP BY name
    HAVING COUNT(movieid)>=30

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


  SELECT title, COUNT(actorid)
  FROM casting,movie                
  WHERE yr=1978
        AND movieid=movie.id
  GROUP BY title
  ORDER BY 2 DESC,1 ASC

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


SELECT DISTINCT d.name
FROM actor d JOIN casting a ON (a.actorid=d.id)
   JOIN casting b on (a.movieid=b.movieid)
   JOIN actor c on (b.actorid=c.id 
                and c.name='Art Garfunkel')
  WHERE d.id!=c.id
Clear your results

That is definitely enough. Students should, under no circumstances look at the next tutorial, concerning outer joins.