Difference between revisions of "JOIN Quiz 2"

From SQLZOO
Jump to: navigation, search
Line 1: Line 1:
 
JOIN Quiz - part 2
 
JOIN Quiz - part 2
  
−
<div class='db_ref' style='background:none'>
+
{{JOIN tables 2}}
−
<table class="zoo">
+
−
  <caption>movie</caption>
+
−
    <tr>
+
−
      <th>Field name</th>
+
−
      <th>Type</th>
+
−
      <th>Notes</th>
+
−
    </tr>
+
−
 
+
−
    <tr>
+
−
      <td notranslate="1">id</td>
+
−
      <td notranslate="1">INTEGER</td>
+
−
      <td>An arbitrary unique identifier</td>
+
−
    </tr>
+
−
 
+
−
    <tr>
+
−
      <td notranslate="1">title</td>
+
−
      <td notranslate="1">CHAR(70)</td>
+
−
      <td>The name of the film - usually in the language of the
+
−
      first release.</td>
+
−
    </tr>
+
−
 
+
−
    <tr>
+
−
      <td notranslate="1">yr</td>
+
−
      <td notranslate="1">DECIMAL(4)</td>
+
−
      <td>Year of first release.</td>
+
−
    </tr>
+
−
 
+
−
    <tr>
+
−
      <td notranslate="1">director</td>
+
−
      <td notranslate="1">INT</td>
+
−
      <td>A reference to the actor table.</td>
+
−
    </tr>
+
−
 
+
−
    <tr>
+
−
      <td notranslate="1">budget</td>
+
−
      <td notranslate="1">INTEGER</td>
+
−
      <td>How much the movie cost to make (in a variety of currencies unfortunately).</td>
+
−
    </tr>
+
−
    <tr>
+
−
      <td notranslate="1">gross</td>
+
−
      <td notranslate="1">INTEGER</td>
+
−
      <td>How much the movie made at the box office.</td>
+
−
    </tr>
+
−
</table>
+
−
 
+
−
<table class="zoo">
+
−
  <caption>actor</caption>
+
−
    <tr>
+
−
      <th>Field name</th>
+
−
 
+
−
      <th>Type</th>
+
−
 
+
−
      <th>Notes</th>
+
−
    </tr>
+
−
 
+
−
    <tr>
+
−
      <td notranslate="1">id</td>
+
−
 
+
−
      <td notranslate="1">INTEGER</td>
+
−
 
+
−
      <td>An arbitrary unique identifier</td>
+
−
    </tr>
+
−
 
+
−
    <tr>
+
−
      <td notranslate="1">name</td>
+
−
 
+
−
      <td notranslate="1">CHAR(36)</td>
+
−
 
+
−
      <td>The name of the actor (the term actor is used to refer to
+
−
      both male and female thesps.)</td>
+
−
    </tr>
+
−
</table>
+
−
 
+
−
<table class="zoo">
+
−
  <caption>casting</caption>
+
−
    <tr>
+
−
      <th>Field name</th>
+
−
 
+
−
      <th>Type</th>
+
−
 
+
−
      <th>Notes</th>
+
−
    </tr>
+
−
 
+
−
    <tr>
+
−
      <td notranslate="1">movieid</td>
+
−
 
+
−
      <td notranslate="1">INTEGER</td>
+
−
 
+
−
      <td>A reference to the movie table.</td>
+
−
    </tr>
+
−
 
+
−
    <tr>
+
−
      <td notranslate="1">actorid</td>
+
−
 
+
−
      <td notranslate="1">INTEGER</td>
+
−
 
+
−
      <td>A reference to the actor table.</td>
+
−
    </tr>
+
−
 
+
−
    <tr>
+
−
      <td notranslate="1">ord</td>
+
−
 
+
−
      <td notranslate="1">INTEGER</td>
+
−
 
+
−
<td>The ordinal position of the actor in the cast list. The
+
−
star of the movie will have ord value 1 the co-star will have
+
−
value 2, ...</td>
+
−
</tr>
+
−
</table>
+
−
</div>
+
  
 
<quiz shuffle=none display=simple>
 
<quiz shuffle=none display=simple>

Revision as of 09:45, 19 July 2012

JOIN Quiz - part 2


movie
Field name Type Notes
id INTEGER An arbitrary unique identifier
title CHAR(70) The name of the film - usually in the language of the first release.
yr DECIMAL(4) Year of first release.
director INT A reference to the actor table.
budget INTEGER How much the movie cost to make (in a variety of currencies unfortunately).
gross INTEGER How much the movie made at the box office.
actor
Field name Type Notes
id INTEGER An arbitrary unique identifier
name CHAR(36) The name of the actor (the term actor is used to refer to both male and female thesps.)
casting
Field name Type Notes
movieid INTEGER A reference to the movie table.
actorid INTEGER A reference to the actor table.
ord INTEGER The ordinal position of the actor in the cast list. The

star of the movie will have ord value 1 the co-star will have

value 2, ...

1. Which of the following statements lists the unfortunate directors of the movies which have caused financial loses? (gross < budget)

SELECT JOIN(name FROM actor, movie ON actor.id:director WHERE gross < budget) GROUP BY name
SELECT name FROM actor INNER JOIN movie BY actor.id = director HAVING gross < budget
SELECT name FROM actor INNER JOIN movie ON actor.id = director WHERE gross < budget
SELECT name FROM actor INNER JOIN movie ON actor.id:director WHERE gross < budget
SELECT name FROM director INNER JOIN movie ON movie.id = director.id WHERE gross < budget

2. Select the correct example of JOINing three tables

SELECT * FROM actor JOIN casting BY actor.id = actorid JOIN movie BY movie.id = movieid
SELECT * FROM actor JOIN casting ON actor.id = actorid AND JOIN movie ON movie.id = movieid
SELECT * FROM actor JOIN casting JOIN movie ON actor.id = actorid AND movie.id = movieid
SELECT * FROM actor JOIN casting ON actor.id = actorid AND movie ON movie.id = movieid
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 actor JOIN casting ON actorid=actor.id WHERE name IN 'John %' GROUP BY name ORDER BY 2
SELECT name, COUNT(movieid) FROM actor JOIN casting ON actorid=actor.id WHERE name LIKE 'J%' GROUP BY name ORDER BY 2 DESC
SELECT name, COUNT(movieid) FROM casting JOIN actor ON actorid=actor.id WHERE name LIKE 'John %' GROUP BY name ORDER BY 2 DESC
SELECT name, COUNT(movieid) FROM casting JOIN actor WHERE (actorid ON actor.id) AND name LIKE 'John %' GROUP BY name ORDER BY 2 DESC
SELECT name, COUNT(movieid) FROM casting JOIN actor WHERE name LIKE 'John %' GROUP BY name ORDER BY COUNT(movieid) DESC

4. Select the statement that lists all the actors that starred in movies directed by Ridley Scott

SELECT name FROM movie JOIN casting AND actor ON movie.id = movieid AND actor.id = actorid WHERE ord = NULL AND director = (SELECT id FROM actor WHERE name LIKE 'Ridley Scott')
SELECT name FROM movie JOIN casting JOIN actor ON movie.id = movieid OR actor.id = actorid WHERE ord = 1 AND director = (SELECT id FROM actor WHERE name LIKE 'Ridley Scott')
SELECT name FROM movie JOIN casting ON movie.id = movieid JOIN actor ON actor.id = actorid WHERE ord = 1 AND actorid = (SELECT id FROM actor WHERE name LIKE 'Ridley Scott')
SELECT name FROM movie JOIN casting ON movie.id = movieid JOIN actor ON actor.id = actorid WHERE ord = 1 AND director = (SELECT id FROM actor WHERE name LIKE 'Ridley Scott')
SELECT name FROM movie JOIN casting ON movie.id = actorid JOIN actor ON actor.id = movieid WHERE director = (SELECT id FROM actor WHERE name LIKE 'Ridley Scott')

Your score is 0 / 0
Retrieved from "http://sqlzoo.net/w/index.php?title=JOIN_Quiz_2&oldid=1845"
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense