Difference between revisions of "JOIN Quiz 2"
Line 4: | Line 4: | ||
{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="()"} | ||
− | - SELECT JOIN(name FROM actor, movie ON actor.id:director WHERE gross < budget) GROUP BY name | + | - <syntaxhighlight lang='sql'> SELECT JOIN(name FROM actor, movie ON actor.id:director WHERE gross < budget) GROUP BY name </syntaxhighlight> |
− | - SELECT name FROM actor INNER JOIN movie BY actor.id = director HAVING gross < budget | + | - <syntaxhighlight lang='sql'> SELECT name FROM actor INNER JOIN movie BY actor.id = director HAVING gross < budget </syntaxhighlight> |
− | + SELECT name FROM actor INNER JOIN movie ON actor.id = director WHERE gross < budget | + | + <syntaxhighlight lang='sql'> SELECT name FROM actor INNER JOIN movie ON actor.id = director WHERE gross < budget </syntaxhighlight> |
− | - SELECT name FROM actor INNER JOIN movie ON actor.id:director WHERE gross < budget | + | - <syntaxhighlight lang='sql'> SELECT name FROM actor INNER JOIN movie ON actor.id:director WHERE gross < budget </syntaxhighlight> |
− | - SELECT name FROM director INNER JOIN movie ON movie.id = director.id WHERE gross < budget | + | - <syntaxhighlight lang='sql'> SELECT name FROM director INNER JOIN movie ON movie.id = director.id WHERE gross < budget </syntaxhighlight> |
{Select the correct example of JOINing three tables | {Select the correct example of JOINing three tables | ||
|type="()"} | |type="()"} | ||
− | - SELECT * FROM actor JOIN casting BY actor.id = actorid JOIN movie BY movie.id = movieid | + | - <syntaxhighlight lang='sql'> SELECT * FROM actor JOIN casting BY actor.id = actorid JOIN movie BY movie.id = movieid </syntaxhighlight> |
− | - SELECT * FROM actor JOIN casting ON actor.id = actorid AND JOIN movie ON movie.id = movieid | + | - <syntaxhighlight lang='sql'> SELECT * FROM actor JOIN casting ON actor.id = actorid AND JOIN movie ON movie.id = movieid </syntaxhighlight> |
− | - SELECT * FROM actor JOIN casting JOIN movie ON actor.id = actorid AND movie.id = movieid | + | - <syntaxhighlight lang='sql'> SELECT * FROM actor JOIN casting JOIN movie ON actor.id = actorid AND movie.id = movieid </syntaxhighlight> |
− | - SELECT * FROM actor JOIN casting ON actor.id = actorid AND movie ON movie.id = movieid | + | - <syntaxhighlight lang='sql'> SELECT * FROM actor JOIN casting ON actor.id = actorid AND movie ON movie.id = movieid </syntaxhighlight> |
− | + SELECT * FROM actor JOIN casting ON actor.id = actorid JOIN movie ON movie.id = movieid | + | + <syntaxhighlight lang='sql'> SELECT * FROM actor JOIN casting ON actor.id = actorid JOIN movie ON movie.id = movieid </syntaxhighlight> |
{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="()"} | ||
− | - SELECT name, COUNT(movieid) FROM actor JOIN casting ON actorid=actor.id WHERE name IN 'John %' GROUP BY name ORDER BY 2 | + | - <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> |
− | - SELECT name, COUNT(movieid) FROM actor JOIN casting ON actorid=actor.id WHERE name LIKE 'J%' GROUP BY name ORDER BY 2 DESC | + | - <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> |
− | + SELECT name, COUNT(movieid) FROM casting JOIN actor ON actorid=actor.id WHERE name LIKE 'John %' GROUP BY name ORDER BY 2 DESC | + | + <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> |
− | - 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 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> |
− | - SELECT name, COUNT(movieid) FROM casting JOIN actor WHERE name LIKE 'John %' GROUP BY name ORDER BY COUNT(movieid) DESC | + | - <syntaxhighlight lang='sql'> SELECT name, COUNT(movieid) FROM casting JOIN actor WHERE name LIKE 'John %' GROUP BY name ORDER BY COUNT(movieid) DESC </syntaxhighlight> |
− | {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 | + | {Select the result that would be obtained from the following code: |
+ | <syntaxhighlight lang='sql'> | ||
+ | SELECT title | ||
+ | FROM movie, casting, actor | ||
+ | WHERE name='Paul Hogan' AND movieid=movie.id AND actorid=actor.id AND ord = 1 | ||
+ | </syntaxhighlight> | ||
<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> | <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> | ||
<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> | <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> | ||
Line 41: | Line 46: | ||
{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="()"} | ||
− | - 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 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 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 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 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 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 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 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 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 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> |
{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="()"} | ||
− | - 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 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 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 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 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 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 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 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 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 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> |
− | {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 | + | {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-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-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> |
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
"Crocodile" Dundee | 1 |
Crocodile Dundee in Los Angeles | 1 |
Flipper | 1 |
Lightning Jack | 1 |
"Crocodile" Dundee |
Crocodile Dundee in Los Angeles |
Flipper |
Lightning Jack |
"Crocodile" Dundee |
Paul Hogan |
1 |
"Crocodile" Dundee | Paul Hogan | 1 |
Crocodile Dundee in Los Angeles | Paul Hogan | 1 |
Flipper | Paul Hogan | 1 |
Lightning Jack | Paul Hogan | 1 |
"Crocodile" Dundee | Paul Hogan |
Crocodile Dundee in Los Angeles | Paul Hogan |
Flipper | Paul Hogan |
Lightning Jack | Paul 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
A Bronx Tale | 1993 | 3 |
Bang the Drum Slowly | 1973 | 3 |
Limitless | 2011 | 3 |
A Bronx Tale | 1993 |
Bang the Drum Slowly | 1973 |
Limitless | 2011 |
A Bronx Tale | 3 |
Bang the Drum Slowly | 3 |
Limitless | 3 |
A Bronx Tale |
Bang the Drum Slowly |
Limitless |
A Bronx Tale | Robert De Niro | 1993 |
Bang the Drum Slowly | Robert De Niro | 1973 |
Limitless | Robert De Niro | 2011 |
|type="()"} - Table-A + Table-B - Table-C - Table-D - Table-E
</quiz>