Difference between revisions of "JOIN Quiz 2"

Revision as of 10:46, 23 July 2012

JOIN Quiz - part 2

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

actor
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.)

casting
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, ...

@@ Line 4: / Line 4: @@
 <quiz shuffle=none display=simple>
-{Which of the following statements lists the unfortunate directors of the movies which have caused financial loses? (gross <  budget)
+{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
@@ Line 36: / Line 36: @@
 - 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')
-{Which of these statements showing all movies with budget bigger than ALL movie with Harrison Ford is '''incorrect'''?
+{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 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 * 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 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 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 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')