View source for JOIN Quiz 2

JOIN Quiz - part 2

<div class=quiz>
<div class=q>Select the statement which lists the unfortunate directors of the movies which have caused financial loses (gross <  budget)
<syntaxhighlight class=d lang='sql'>
SELECT JOIN(name FROM actor, movie
       ON actor.id:director WHERE gross < budget)
 GROUP BY name </syntaxhighlight>
<syntaxhighlight class=d lang='sql'>
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>

<div class=q>Select the correct example of JOINing three tables
<syntaxhighlight class=d lang='sql'>
SELECT * 
  FROM actor JOIN casting BY actor.id = actorid
 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>

<div class=q>Select the statement that shows the list of actors called 'John' by order of number of movies in which they acted
<syntaxhighlight class=d lang='sql'>
SELECT name, COUNT(movieid)
  FROM actor JOIN casting ON actorid=actor.id
 WHERE name IN 'John %'
 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>

<div class=q>Select the result that would be obtained from the following code: 
<syntaxhighlight lang='sql'>
 SELECT title 
   FROM movie JOIN casting ON (movieid=movie.id)
              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>

<div class=q>Select the statement that lists all the actors that starred in movies directed by Ridley Scott who has id 351
<syntaxhighlight class=d lang='sql'>
SELECT name
  FROM movie JOIN casting
   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>

<div class=q>There are two sensible ways to connect movie and actor. They are:
<div class=d><ul>
  <li>link the director column in movies with the id column in actor</li>
  <li>join casting to itself</li>
</ul></div>
<div class=d><ul>
  <li>link the actor column in movies with the primary key in actor</li>
  <li>connect the primary keys of movie and actor via the casting table</li>
</ul></div>
<div class='d y'><ul>
  <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>
</ul></div>
<div class=d><ul>
  <li>link the director column in movies with the primary key in actor</li>
  <li>connect the primary keys of movie and casting via the actor table</li>
</ul></div>
<div class=d><ul>
  <li>link the movie column in actor with the director column in actor</li>
  <li>connect movie and actor via the casting table</li>
</ul></div>
</div>

<div class=q>Select the result that would be obtained from the following code: 
<syntaxhighlight lang='sql'>
 SELECT title, yr 
   FROM movie, casting, actor 
  WHERE name='Robert De Niro' AND movieid=movie.id AND actorid=actor.id AND ord = 3
</syntaxhighlight>
<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>
<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>
<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 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>

[[Category:Quizzes]]