JOIN Quiz 2

JOIN Quiz - part 2

Select the statement which lists the unfortunate directors of the movies which have caused financial loses (gross < budget)

JOINs perform INNER JOINs by default - they both only return matched rows which are present in both tables

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

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', ordered by the number of movies in which they acted from highest to lowest

ORDER BY argument can take arguments of column_name or column_number or an aggregating function

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 JOIN casting ON (movieid=movie.id)
              JOIN actor   ON (actorid=actor.id)
  WHERE name='Paul Hogan' 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

Select the statement that lists all the leading actors that starred in movies directed by Ridley Scott (who has id 351)

SELECT name
  FROM movie JOIN casting
   AND actor ON movie.id = movieid
   AND actor.id = actorid
 WHERE ord = 1
  AND actor = 351

SELECT name
  FROM movie JOIN casting
  JOIN actor ON movie.id = movieid
    OR actor.id = actorid
 WHERE ord = 1 AND director = 351

SELECT name
  FROM movie JOIN casting ON movie.id = movieid
  JOIN actor ON actor.id = actorid
 WHERE ord = 1 AND actorid = 351

SELECT name
  FROM movie JOIN casting ON movie.id = movieid
  JOIN actor ON actor.id = actorid
WHERE ord = 1 AND director = 351

SELECT name
  FROM movie JOIN casting ON movie.id = actorid
  JOIN actor ON actor.id = movieid
 WHERE director = 351

There are two sensible ways to connect movie and actor. They are:

• link the director column in movies with the id column in actor
• join casting to itself

• link the actor column in movies with the primary key in actor
• connect the primary keys of movie and actor via the casting table

• link the director column in movies with the primary key in actor
• connect the primary keys of movie and actor via the casting table

• link the director column in movies with the primary key in actor
• connect the primary keys of movie and casting via the actor table

• link the movie column in actor with the director column in actor
• connect movie and actor via the casting table

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