JOIN Quiz 2
JOIN Quiz - part 2
| Field name | Type | Notes |
|---|---|---|
| id | INTEGER | An arbitrary unique identifier |
| title | CHAR(70) | The name of the film - usually in the language of the first release. |
| yr | DECIMAL(4) | Year of first release. |
| director | INT | A reference to the actor table. |
| budget | INTEGER | How much the movie cost to make (in a variety of currencies unfortunately). |
| gross | INTEGER | How much the movie made at the box office. |
| Field name | Type | Notes |
|---|---|---|
| id | INTEGER | An arbitrary unique identifier |
| name | CHAR(36) | The name of the actor (the term actor is used to refer to both male and female thesps.) |
| Field name | Type | Notes |
|---|---|---|
| movieid | INTEGER | A reference to the movie table. |
| actorid | INTEGER | A reference to the actor table. |
| ord | INTEGER | The ordinal position of the actor in the cast list. The
star of the movie will have ord value 1 the co-star will have value 2, ... |
<quiz shuffle=none display=simple> {Which of the following statements 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 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') </quiz>
