JOIN Quiz 2

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" 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

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

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

</quiz>