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
 
{{JOIN tables 2}}
 
  
 
<quiz shuffle=none display=simple>
 
<quiz shuffle=none display=simple>
 
{Select the statement which lists the unfortunate directors of the movies which have caused financial loses (gross <  budget)
 
{Select the statement which lists the unfortunate directors of the movies which have caused financial loses (gross <  budget)
 
|type="()"}
 
|type="()"}
-A. SELECT JOIN(name FROM actor, movie ON actor.id:director WHERE gross < budget) GROUP BY name  
+
- SELECT JOIN(name FROM actor, movie ON actor.id:director WHERE gross < budget) GROUP BY name  
-B. SELECT name FROM actor INNER JOIN movie BY actor.id = director HAVING gross < budget  
+
- SELECT name FROM actor INNER JOIN movie BY actor.id = director HAVING gross < budget  
+C. 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  
-D. 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  
-E. SELECT name FROM director INNER JOIN movie ON movie.id = director.id 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 the correct example of JOINing three tables
 
|type="()"}
 
|type="()"}
-A. SELECT * FROM actor JOIN casting BY actor.id = actorid JOIN movie BY movie.id = movieid
+
- SELECT * FROM actor JOIN casting BY actor.id = actorid JOIN movie BY movie.id = movieid
-B. SELECT * FROM actor JOIN casting ON actor.id = actorid AND JOIN movie ON movie.id = movieid
+
- SELECT * FROM actor JOIN casting ON actor.id = actorid AND JOIN movie ON movie.id = movieid
-C. SELECT * FROM actor JOIN casting JOIN movie ON actor.id = actorid AND movie.id = movieid
+
- SELECT * FROM actor JOIN casting JOIN movie ON actor.id = actorid AND movie.id = movieid
-D. SELECT * FROM actor JOIN casting ON actor.id = actorid AND movie ON movie.id = movieid
+
- SELECT * FROM actor JOIN casting ON actor.id = actorid AND movie ON movie.id = movieid
+E. SELECT * FROM actor JOIN casting ON actor.id = actorid JOIN 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 the statement that shows the list of actors called 'John' by order of number of movies in which they acted
 
|type="()"}
 
|type="()"}
-A. 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 IN 'John %' GROUP BY name ORDER BY 2
-B. 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 actor JOIN casting ON actorid=actor.id WHERE name LIKE 'J%' GROUP BY name ORDER BY 2 DESC
+C. 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 ON actorid=actor.id WHERE name LIKE 'John %' GROUP BY name ORDER BY 2 DESC
-D. 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 (actorid ON actor.id) AND name LIKE 'John %' GROUP BY name ORDER BY 2 DESC
-E. SELECT name, COUNT(movieid) FROM casting JOIN actor WHERE name LIKE 'John %' GROUP BY name ORDER BY COUNT(movieid) DESC
+
- SELECT name, COUNT(movieid) FROM casting JOIN actor WHERE name LIKE 'John %' GROUP BY name ORDER BY COUNT(movieid) DESC
  
 
{Select the statement that lists all the actors that starred in movies directed by Ridley Scott
 
{Select the statement that lists all the actors that starred in movies directed by Ridley Scott
 
|type="()"}
 
|type="()"}
-A. 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 AND actor ON movie.id = movieid AND actor.id = actorid WHERE ord = NULL AND director = (SELECT id FROM actor WHERE name LIKE 'Ridley Scott')
-B. 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 JOIN actor ON movie.id = movieid OR actor.id = actorid WHERE ord = 1 AND director = (SELECT id FROM actor WHERE name LIKE 'Ridley Scott')
-C. 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 actorid = (SELECT id FROM actor WHERE name LIKE 'Ridley Scott')
+D. 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 = movieid JOIN actor ON actor.id = actorid WHERE ord = 1 AND director = (SELECT id FROM actor WHERE name LIKE 'Ridley Scott')
-E. 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 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'''
 
{Select the statement showing all movies with budget bigger than ALL movie with Harrison Ford is '''incorrect'''
 
|type="()"}
 
|type="()"}
-A. 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 WHERE name = 'Harrison Ford')
+B. 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 > ALL (SELECT budget FROM movie JOIN casting JOIN actor ON movie.id = movieid AND actor.id = actorid OR name = 'Harrison Ford')
-C. 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 > (SELECT MAX(budget) FROM movie JOIN casting JOIN actor ON movie.id = movieid AND actor.id = actorid WHERE name = 'Harrison Ford')
-D. 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 > ALL (SELECT budget FROM movie JOIN casting ON movie.id = movieid JOIN actor ON actor.id = actorid WHERE name = 'Harrison Ford')
-E. 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 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')
  
 
</quiz>
 
</quiz>
  
 
[[Category:Quizzes]]
 
[[Category:Quizzes]]

Revision as of 11:09, 7 August 2012

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

</quiz>