# Difference between revisions of "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)
```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' 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
```
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
Select the statement that lists all the 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 actor.id = actorid
JOIN actor ON movie.id = movieid
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
```
 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