Difference between revisions of "JOIN Quiz 2"

From SQLZOO
Jump to: navigation, search
 
(20 intermediate revisions by 7 users not shown)
Line 1: Line 1:
 
JOIN Quiz - part 2
 
JOIN Quiz - part 2
  
<div class='db_ref' style='background:none'>
+
<div class=quiz>
<table class="zoo">
+
<div class=q>Select the statement which lists the unfortunate directors of the movies which have caused financial loses (gross <  budget)
  <caption>movie</caption>
+
<syntaxhighlight class=d lang='sql'>
    <tr>
+
SELECT JOIN(name FROM actor, movie
      <th>Field name</th>  
+
      ON actor.id:director WHERE gross < budget)
      <th>Type</th>  
+
GROUP BY name </syntaxhighlight>
      <th>Notes</th>
+
<syntaxhighlight class=d lang='sql'>
    </tr>
+
SELECT name
 +
FROM actor INNER JOIN movie BY actor.id = director
 +
HAVING gross < budget </syntaxhighlight>
 +
<syntaxhighlight class='d y' lang='sql'>
 +
SELECT name
 +
  FROM actor INNER JOIN movie ON actor.id = director
 +
WHERE gross < budget </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT name
 +
  FROM actor INNER JOIN movie ON actor.id:director
 +
WHERE gross < budget </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT name
 +
  FROM director INNER JOIN movie ON movie.id = director.id
 +
WHERE gross < budget </syntaxhighlight>
 +
</div>
  
    <tr>
+
<div class=q>Select the correct example of JOINing three tables
      <td notranslate="1">id</td>  
+
<syntaxhighlight class=d lang='sql'>
      <td notranslate="1">INTEGER</td>  
+
SELECT *
      <td>An arbitrary unique identifier</td>
+
  FROM actor JOIN casting BY actor.id = actorid
    </tr>
+
JOIN movie BY movie.id = movieid </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT *
 +
  FROM actor JOIN casting ON actor.id = actorid
 +
  AND JOIN movie ON movie.id = movieid </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT *
 +
  FROM actor JOIN casting
 +
  JOIN movie ON actor.id = actorid
 +
  AND movie.id = movieid </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT *
 +
  FROM actor JOIN casting ON actor.id = actorid
 +
  AND movie ON movie.id = movieid </syntaxhighlight>
 +
<syntaxhighlight class='d y' lang='sql'>
 +
SELECT *
 +
  FROM actor JOIN casting ON actor.id = actorid
 +
  JOIN movie ON movie.id = movieid </syntaxhighlight>
 +
</div>
  
    <tr>
+
<div class=q>Select the statement that shows the list of actors called 'John' by order of number of movies in which they acted
      <td notranslate="1">title</td>  
+
<syntaxhighlight class=d lang='sql'>
      <td notranslate="1">CHAR(70)</td>  
+
SELECT name, COUNT(movieid)
      <td>The name of the film - usually in the language of the
+
  FROM actor JOIN casting ON actorid=actor.id
      first release.</td>
+
WHERE name IN 'John %'
    </tr>
+
GROUP BY name ORDER BY 2 </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT name, COUNT(movieid)
 +
  FROM actor JOIN casting ON actorid=actor.id
 +
WHERE name LIKE 'J%'
 +
GROUP BY name ORDER BY 2 DESC </syntaxhighlight>
 +
<syntaxhighlight class='d y' lang='sql'>
 +
SELECT name, COUNT(movieid)
 +
  FROM casting JOIN actor ON actorid=actor.id
 +
WHERE name LIKE 'John %'
 +
GROUP BY name ORDER BY 2 DESC </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT name, COUNT(movieid)
 +
  FROM casting JOIN actor
 +
WHERE (actorid ON actor.id)
 +
  AND name LIKE 'John %'
 +
GROUP BY name ORDER BY 2 DESC </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT name, COUNT(movieid)
 +
  FROM casting JOIN actor
 +
WHERE name LIKE 'John %'
 +
GROUP BY name ORDER BY COUNT(movieid) DESC </syntaxhighlight>
 +
</div>
  
    <tr>
+
<div class=q>Select the result that would be obtained from the following code:
      <td notranslate="1">yr</td>  
+
<syntaxhighlight lang='sql'>
      <td notranslate="1">DECIMAL(4)</td>  
+
SELECT title
      <td>Year of first release.</td>
+
  FROM movie JOIN casting ON (movieid=movie.id)
    </tr>
+
              JOIN actor  ON (actorid=actor.id)
 +
  WHERE name='Paul Hogan' AND ord = 1
 +
</syntaxhighlight>
 +
<table class=d><caption>Table-A</caption><tr><td>"Crocodile" Dundee</td><td>1</td></tr><tr><td>Crocodile Dundee in Los Angeles</td><td>1</td></tr><tr><td>Flipper</td><td>1</td></tr><tr><td>Lightning Jack</td><td>1</td></tr></table>
 +
<table class='d y'><caption>Table-B</caption><tr><td>"Crocodile" Dundee</td></tr><tr><td>Crocodile Dundee in Los Angeles</td></tr><tr><td>Flipper</td></tr><tr><td>Lightning Jack</td></tr></table>
 +
<table class=d><caption>Table-C</caption><tr><td>"Crocodile" Dundee</td></tr><tr><td>Paul Hogan</td></tr><tr><td>1</td></tr></table>
 +
<table class=d><caption>Table-D</caption><tr><td>"Crocodile" Dundee</td><td>Paul Hogan</td><td>1</td></tr><tr><td>Crocodile Dundee in Los Angeles</td><td>Paul Hogan</td><td>1</td></tr><tr><td>Flipper</td><td>Paul Hogan</td><td>1</td></tr><tr><td>Lightning Jack</td><td>Paul Hogan</td><td>1</td></tr></table>
 +
<table class=d><caption>Table-E</caption><tr><td>"Crocodile" Dundee</td><td>Paul Hogan</td></tr><tr><td>Crocodile Dundee in Los Angeles</td><td>Paul Hogan</td></tr><tr><td>Flipper</td><td>Paul Hogan</td></tr><tr><td>Lightning Jack</td><td>Paul Hogan</td></tr></table>
 +
</div>
  
    <tr>
+
<div class=q>Select the statement that lists all the actors that starred in movies directed by Ridley Scott who has id 351
      <td notranslate="1">director</td>  
+
<syntaxhighlight class=d lang='sql'>
      <td notranslate="1">INT</td>  
+
SELECT name
      <td>A reference to the actor table.</td>
+
  FROM movie JOIN casting
    </tr>
+
  AND actor ON movie.id = movieid
 +
  AND actor.id = actorid
 +
WHERE ord = 1
 +
  AND actor = 351 </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT name
 +
  FROM movie JOIN casting
 +
  JOIN actor ON movie.id = movieid
 +
    OR actor.id = actorid
 +
WHERE ord = 1 AND director = 351 </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT name
 +
  FROM movie JOIN casting ON movie.id = movieid
 +
  JOIN actor ON actor.id = actorid
 +
WHERE ord = 1 AND actorid = 351 </syntaxhighlight>
 +
<syntaxhighlight class='d y' lang='sql'>
 +
SELECT name
 +
  FROM movie JOIN casting ON movie.id = movieid
 +
  JOIN actor ON actor.id = actorid
 +
WHERE ord = 1 AND director = 351 </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT name
 +
  FROM movie JOIN casting ON movie.id = actorid
 +
  JOIN actor ON actor.id = movieid
 +
WHERE director = 351 </syntaxhighlight>
 +
</div>
  
    <tr>
+
<div class=q>There are two sensible ways to connect movie and actor. They are:
      <td notranslate="1">budget</td>
+
<div class=d><ul>
      <td notranslate="1">INTEGER</td>
+
  <li>link the director column in movies with the id column in actor</li>
      <td>How much the movie cost to make (in a variety of currencies unfortunately).</td>
+
  <li>join casting to itself</li>
    </tr>
+
</ul></div>
    <tr>
+
<div class=d><ul>
      <td notranslate="1">gross</td>  
+
  <li>link the actor column in movies with the primary key in actor</li>
      <td notranslate="1">INTEGER</td>
+
  <li>connect the primary keys of movie and actor via the casting table</li>
      <td>How much the movie made at the box office.</td>
+
</ul></div>
    </tr>
+
<div class='d y'><ul>
</table>
+
  <li>link the director column in movies with the primary key in actor</li>
 
+
  <li>connect the primary keys of movie and actor via the casting table</li>
<table class="zoo">
+
</ul></div>
  <caption>actor</caption>
+
<div class=d><ul>
    <tr>
+
  <li>link the director column in movies with the primary key in actor</li>
      <th>Field name</th>
+
  <li>connect the primary keys of movie and casting via the actor table</li>
 
+
</ul></div>
      <th>Type</th>
+
<div class=d><ul>
 
+
  <li>link the movie column in actor with the director column in actor</li>
      <th>Notes</th>
+
  <li>connect movie and actor via the casting table</li>
    </tr>
+
</ul></div>
 
+
</div>
    <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>
+
<div class=q>Select the result that would be obtained from the following code:
      <td notranslate="1">ord</td>
+
<syntaxhighlight lang='sql'>
 
+
SELECT title, yr
      <td notranslate="1">INTEGER</td>
+
  FROM movie, casting, actor
 
+
  WHERE name='Robert De Niro' AND movieid=movie.id AND actorid=actor.id AND ord = 3
<td>The ordinal position of the actor in the cast list. The
+
</syntaxhighlight>
star of the movie will have ord value 1 the co-star will have
+
<table  class=d><caption>Table-A</caption><tr><td>A Bronx Tale</td><td>1993</td><td>3</td></tr><tr><td>Bang the Drum Slowly</td><td>1973</td><td>3</td></tr><tr><td>Limitless</td><td>2011</td><td>3</td></tr></table>
value 2, ...</td>
+
<table class='d y'><caption>Table-B</caption><tr><td>A Bronx Tale</td><td>1993</td></tr><tr><td>Bang the Drum Slowly</td><td>1973</td></tr><tr><td>Limitless</td><td>2011</td></tr></table>
</tr>
+
<table class=d><caption>Table-C</caption><tr><td>A Bronx Tale</td><td>3</td></tr><tr><td>Bang the Drum Slowly</td><td>3</td></tr><tr><td>Limitless</td><td>3</td></tr></table>
</table>
+
<table class=d><caption>Table-D</caption><tr><td>A Bronx Tale</td></tr><tr><td>Bang the Drum Slowly</td></tr><tr><td>Limitless</td></tr></table>
 +
<table class=d><caption>Table-E</caption><tr><td>A Bronx Tale</td><td>Robert De Niro</td><td>1993</td></tr><tr><td>Bang the Drum Slowly</td><td>Robert De Niro</td><td>1973</td></tr><tr><td>Limitless</td><td>Robert De Niro</td><td>2011</td></tr></table>
 
</div>
 
</div>
 
<quiz shuffle=none display=simple>
 
{Which of the following statements lists the unfortunate directors of the movies which have caused financial loses? (gross <  budget)
 
|type="()"}
 
+ 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 JOIN(name FROM actor, movie ON actor.id:director WHERE gross < budget) GROUP BY name
 
- SELECT name FROM director INNER JOIN movie ON movie.id = director.id WHERE gross < budget
 
- SELECT name FROM actor INNER JOIN movie BY actor.id = director HAVING gross < budget
 
 
</quiz>
 
  
 
[[Category:Quizzes]]
 
[[Category:Quizzes]]

Latest revision as of 15:51, 30 September 2016

JOIN Quiz - part 2

Select the statement which 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
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
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
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-A
"Crocodile" Dundee1
Crocodile Dundee in Los Angeles1
Flipper1
Lightning Jack1
Table-B
"Crocodile" Dundee
Crocodile Dundee in Los Angeles
Flipper
Lightning Jack
Table-C
"Crocodile" Dundee
Paul Hogan
1
Table-D
"Crocodile" DundeePaul Hogan1
Crocodile Dundee in Los AngelesPaul Hogan1
FlipperPaul Hogan1
Lightning JackPaul Hogan1
Table-E
"Crocodile" DundeePaul Hogan
Crocodile Dundee in Los AngelesPaul Hogan
FlipperPaul Hogan
Lightning JackPaul Hogan
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
   AND actor ON movie.id = movieid
   AND actor.id = actorid
 WHERE ord = 1
  AND actor = 351
SELECT name
  FROM movie JOIN casting
  JOIN actor ON movie.id = movieid
    OR actor.id = actorid
 WHERE ord = 1 AND director = 351
SELECT name
  FROM movie JOIN casting ON movie.id = movieid
  JOIN actor ON actor.id = actorid
 WHERE ord = 1 AND actorid = 351
SELECT name
  FROM movie JOIN casting ON movie.id = movieid
  JOIN actor ON actor.id = actorid
WHERE ord = 1 AND director = 351
SELECT name
  FROM movie JOIN casting ON movie.id = actorid
  JOIN actor ON actor.id = movieid
 WHERE director = 351
There are two sensible ways to connect movie and actor. They are:
  • link the director column in movies with the id column in actor
  • join casting to itself
  • link the actor column in movies with the primary key in actor
  • connect the primary keys of movie and actor via the casting table
  • link the director column in movies with the primary key in actor
  • connect the primary keys of movie and actor via the casting table
  • link the director column in movies with the primary key in actor
  • connect the primary keys of movie and casting via the actor table
  • link the movie column in actor with the director column in actor
  • connect movie and actor via the casting table
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-A
A Bronx Tale19933
Bang the Drum Slowly19733
Limitless20113
Table-B
A Bronx Tale1993
Bang the Drum Slowly1973
Limitless2011
Table-C
A Bronx Tale3
Bang the Drum Slowly3
Limitless3
Table-D
A Bronx Tale
Bang the Drum Slowly
Limitless
Table-E
A Bronx TaleRobert De Niro1993
Bang the Drum SlowlyRobert De Niro1973
LimitlessRobert De Niro2011