# Difference between revisions of "JOIN Quiz 2"

JOIN Quiz - part 2

1. 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`

2. 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`

3. 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`

4. 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
 Table-A Table-B Table-C Table-D Table-E

5. Select the statement that lists all the actors that starred in movies directed by 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')` ` 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')`

6. Select the statement showing all movies with budget bigger than ALL movie with Harrison Ford is incorrect

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

7. 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
 Table-A Table-B Table-C Table-D Table-E

Your score is 0 / 0