Difference between revisions of "JOIN Quiz 2"

From SQLZOO
Jump to: navigation, search
(15 intermediate revisions by 3 users not shown)
Line 1: Line 1:
 
JOIN Quiz - part 2
 
JOIN Quiz - part 2
  
<div class='db_ref' style='background:none'>
+
<quiz shuffle=none display=simple>
<table class="zoo">
+
{Select the statement which lists the unfortunate directors of the movies which have caused financial loses (gross < budget)
  <caption>movie</caption>
+
|type="()"}
    <tr>
+
- <syntaxhighlight lang='sql'> SELECT JOIN(name FROM actor, movie ON actor.id:director WHERE gross < budget) GROUP BY name </syntaxhighlight>
      <th>Field name</th>  
+
- <syntaxhighlight lang='sql'> SELECT name FROM actor INNER JOIN movie BY actor.id = director HAVING gross < budget </syntaxhighlight>
      <th>Type</th>  
+
+ <syntaxhighlight lang='sql'> SELECT name FROM actor INNER JOIN movie ON actor.id = director WHERE gross < budget </syntaxhighlight>
      <th>Notes</th>
+
- <syntaxhighlight lang='sql'> SELECT name FROM actor INNER JOIN movie ON actor.id:director WHERE gross < budget </syntaxhighlight>
    </tr>
+
- <syntaxhighlight lang='sql'> SELECT name FROM director INNER JOIN movie ON movie.id = director.id WHERE gross < budget </syntaxhighlight>
  
    <tr>
+
{Select the correct example of JOINing three tables
      <td notranslate="1">id</td>  
+
|type="()"}
      <td notranslate="1">INTEGER</td>  
+
- <syntaxhighlight lang='sql'> SELECT * FROM actor JOIN casting BY actor.id = actorid JOIN movie BY movie.id = movieid </syntaxhighlight>
      <td>An arbitrary unique identifier</td>
+
- <syntaxhighlight lang='sql'> SELECT * FROM actor JOIN casting ON actor.id = actorid AND JOIN movie ON movie.id = movieid </syntaxhighlight>
    </tr>
+
- <syntaxhighlight lang='sql'> SELECT * FROM actor JOIN casting JOIN movie ON actor.id = actorid AND movie.id = movieid </syntaxhighlight>
 +
- <syntaxhighlight lang='sql'> SELECT * FROM actor JOIN casting ON actor.id = actorid AND movie ON movie.id = movieid </syntaxhighlight>
 +
+ <syntaxhighlight lang='sql'> SELECT * FROM actor JOIN casting ON actor.id = actorid JOIN movie ON movie.id = movieid </syntaxhighlight>
  
    <tr>
+
{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>  
+
|type="()"}
      <td notranslate="1">CHAR(70)</td>  
+
- <syntaxhighlight 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>
      <td>The name of the film - usually in the language of the
+
- <syntaxhighlight 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>
      first release.</td>
+
+ <syntaxhighlight 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>
    </tr>
+
- <syntaxhighlight 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 lang='sql'> SELECT name, COUNT(movieid) FROM casting JOIN actor WHERE name LIKE 'John %' GROUP BY name ORDER BY COUNT(movieid) DESC </syntaxhighlight>
  
    <tr>
+
{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, casting, actor
    </tr>
+
  WHERE name='Paul Hogan' AND movieid=movie.id AND actorid=actor.id AND ord = 1
 
+
</syntaxhighlight>
    <tr>
+
<table style='float:left'><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>
      <td notranslate="1">director</td>  
+
<table style='float:left'><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>
      <td notranslate="1">INT</td>  
+
<table style='float:left'><caption>Table-C</caption><tr><td>"Crocodile" Dundee</td></tr><tr><td>Paul Hogan</td></tr><tr><td>1</td></tr></table>
      <td>A reference to the actor table.</td>
+
<table style='float:left'><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>
    </tr>
+
<table style='float:left'><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>
 
+
    <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>
+
{Which of the following uses of JOIN is correct?
+
 
|type="()"}
 
|type="()"}
- SELECT teacher.name, dept.name FROM teacher JOIN dept ON (dept = id)
+
- Table-A
- SELECT teacher.name, dept.name FROM teacher, dept INNER JOIN ON (teacher.dept = dept.id)
+
+ Table-B
- SELECT teacher.name, dept.name FROM teacher, dept JOIN WHERE(teacher.dept = dept.id)
+
- Table-C
- SELECT teacher.name, dept.name FROM teacher OUTER JOIN dept ON dept.id
+
- Table-D
+ SELECT teacher.name, dept.name FROM teacher LEFT OUTER JOIN dept ON (teacher.dept > dept.id)
+
- Table-E
  
{Select the correct statement that shows the name of department which employs Cutflower
+
{Select the statement that lists all the actors that starred in movies directed by Ridley Scott
 
|type="()"}
 
|type="()"}
- SELECT dept.name FROM teacher JOIN dept ON (dept.id = (SELECT dept FROM teacher WHERE name = 'Cutflower'))
+
- <syntaxhighlight lang='sql'> 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') </syntaxhighlight>
- SELECT dept.name FROM teacher JOIN dept ON (dept.id = teacher.dept) WHERE dept.id = (SELECT dept FROM teacher HAVING name = 'Cutflower')
+
- <syntaxhighlight lang='sql'> 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') </syntaxhighlight>
+ SELECT dept.name FROM teacher JOIN dept ON (dept.id = teacher.dept) WHERE teacher.name = 'Cutflower'
+
- <syntaxhighlight lang='sql'> 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') </syntaxhighlight>
- SELECT dept.name FROM teacher JOIN dept WHERE dept.id = (SELECT dept FROM teacher WHERE name = 'Cutflower')
+
+ <syntaxhighlight lang='sql'> 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') </syntaxhighlight>
- SELECT name FROM teacher JOIN dept ON (id = dept) WHERE id = (SELECT dept FROM teacher WHERE name = 'Cutflower')
+
- <syntaxhighlight lang='sql'> 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') </syntaxhighlight>
 
+
  
{Which of following uses of JOIN shows list of all the departments and number of employed teachers?
+
{Select the statement showing all movies with budget bigger than ALL movie with Harrison Ford is '''incorrect'''
 
|type="()"}
 
|type="()"}
- SELECT dept.name, COUNT(*) FROM teacher LEFT JOIN dept ON dept.id = teacher.dept
+
- <syntaxhighlight lang='sql'> SELECT title FROM movie WHERE budget > ALL (SELECT budget FROM movie JOIN casting JOIN actor ON movie.id = movieid AND actor.id = actorid WHERE name = 'Harrison Ford')</syntaxhighlight>
- SELECT dept.name, COUNT(teacher.name) FROM teacher, dept JOIN ON dept.id = teacher.dept GROUP BY dept.name
+
+ <syntaxhighlight lang='sql'> SELECT title FROM movie WHERE budget > ALL (SELECT budget FROM movie JOIN casting JOIN actor ON movie.id = movieid AND actor.id = actorid OR name = 'Harrison Ford')</syntaxhighlight>
- SELECT dept.name, COUNT(teacher.name) FROM teacher JOIN dept ON dept.id = teacher.dept GROUP BY dept.name
+
- <syntaxhighlight lang='sql'> SELECT title FROM movie WHERE budget > (SELECT MAX(budget) FROM movie JOIN casting JOIN actor ON movie.id = movieid AND actor.id = actorid WHERE name = 'Harrison Ford')</syntaxhighlight>
- SELECT dept.name, COUNT(teacher.name) FROM teacher LEFT OUTER JOIN dept ON dept.id = teacher.dept GROUP BY dept.name
+
- <syntaxhighlight lang='sql'> SELECT title FROM movie WHERE budget > ALL (SELECT budget FROM movie JOIN casting ON movie.id = movieid JOIN actor ON actor.id = actorid WHERE name = 'Harrison Ford')</syntaxhighlight>
+ SELECT dept.name, COUNT(teacher.name) FROM teacher RIGHT JOIN dept ON dept.id = teacher.dept GROUP BY dept.name
+
- <syntaxhighlight lang='sql'> SELECT title FROM movie WHERE budget > (SELECT MAX(budget) FROM movie JOIN casting JOIN actor ON movie.id = movieid AND actor.id = actorid AND name = 'Harrison Ford')</syntaxhighlight>
  
{Using <code>SELECT name, dept, COALESCE(dept, 0) AS result FROM teacher</code> on <code>teacher</code> table will:
+
{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 style='float:left'><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 style='float:left'><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 style='float:left'><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 style='float:left'><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 style='float:left'><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>
 
|type="()"}
 
|type="()"}
- display 0 in result column for all teachers
+
- Table-A
+ display 0 in result column for all teachers without department
+
+ Table-B
- do nothing - the statement is incorrect
+
- Table-C
- set dept value of all teachers to 0
+
- Table-D
- set dept value of all teachers without department to 0
+
- Table-E
  
 
</quiz>
 
</quiz>
  
 
[[Category:Quizzes]]
 
[[Category:Quizzes]]

Revision as of 10:49, 12 July 2013

JOIN Quiz - part 2

<quiz shuffle=none display=simple> {Select the statement which lists the unfortunate directors of the movies which have caused financial loses (gross < budget) |type="()"}

-
 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 |type="()"}

-
 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 |type="()"}

-
 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, casting, actor 
  WHERE name='Paul Hogan' AND movieid=movie.id AND actorid=actor.id 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

|type="()"} - Table-A + Table-B - Table-C - Table-D - Table-E

{Select the statement that lists all the actors that starred in movies directed by Ridley Scott |type="()"}

-
 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')

{Select the statement showing all movies with budget bigger than ALL movie with Harrison Ford is incorrect |type="()"}

-
 SELECT title FROM movie WHERE budget > ALL (SELECT budget FROM movie JOIN casting JOIN actor ON movie.id = movieid AND actor.id = actorid WHERE name = 'Harrison Ford')
+
 SELECT title FROM movie WHERE budget > ALL (SELECT budget FROM movie JOIN casting JOIN actor ON movie.id = movieid AND actor.id = actorid OR name = 'Harrison Ford')
-
 SELECT title FROM movie WHERE budget > (SELECT MAX(budget) FROM movie JOIN casting JOIN actor ON movie.id = movieid AND actor.id = actorid WHERE name = 'Harrison Ford')
-
 SELECT title FROM movie WHERE budget > ALL (SELECT budget FROM movie JOIN casting ON movie.id = movieid JOIN actor ON actor.id = actorid WHERE name = 'Harrison Ford')
-
 SELECT title FROM movie WHERE budget > (SELECT MAX(budget) FROM movie JOIN casting JOIN actor ON movie.id = movieid AND actor.id = actorid AND name = 'Harrison Ford')

{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

|type="()"} - Table-A + Table-B - Table-C - Table-D - Table-E

</quiz>