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

Table-A

"Crocodile" Dundee	1
Crocodile Dundee in Los Angeles	1
Flipper	1
Lightning Jack	1

Table-B

"Crocodile" Dundee

Crocodile Dundee in Los Angeles

Flipper

Lightning Jack

Table-C

"Crocodile" Dundee

Paul Hogan

1

Table-D

"Crocodile" Dundee	Paul Hogan	1
Crocodile Dundee in Los Angeles	Paul Hogan	1
Flipper	Paul Hogan	1
Lightning Jack	Paul Hogan	1

Table-E

"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

Table-A

A Bronx Tale	1993	3
Bang the Drum Slowly	1973	3
Limitless	2011	3

Table-B

A Bronx Tale	1993
Bang the Drum Slowly	1973
Limitless	2011

Table-C

A Bronx Tale	3
Bang the Drum Slowly	3
Limitless	3

Table-D

A Bronx Tale

Bang the Drum Slowly

Limitless

Table-E

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