JOIN Quiz 2 - Revision history

79.191.79.150 at 19:59, 7 September 2012

2012-09-07T19:59:11Z

Connor at 13:14, 7 August 2012

2012-08-07T13:14:10Z

Connor at 13:05, 7 August 2012

2012-08-07T13:05:43Z

Connor at 13:01, 7 August 2012

2012-08-07T13:01:03Z

Connor at 11:09, 7 August 2012

2012-08-07T11:09:57Z

Connor at 11:01, 23 July 2012

2012-07-23T11:01:06Z

Connor at 10:46, 23 July 2012

2012-07-23T10:46:12Z

Marek at 10:11, 19 July 2012

2012-07-19T10:11:44Z

Marek at 09:45, 19 July 2012

2012-07-19T09:45:18Z

Marek at 09:37, 19 July 2012

2012-07-19T09:37:38Z

@@ Line 56: / Line 56: @@
 {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 style='float:left'><caption>Table-C</caption><tr><td>A Bronx Tale</td><td>1993</td><td>3</td></tr><tr><td>Bang the Drum Slowly</td><td>1973</td><td>3</td></tr><tr><td>Limitless</td><td>2011</td><td>3</td></tr></table>
+<table style='float:left'><caption>Table-A</caption><tr><td>A Bronx Tale</td><td>1993</td><td>3</td></tr><tr><td>Bang the Drum Slowly</td><td>1973</td><td>3</td></tr><tr><td>Limitless</td><td>2011</td><td>3</td></tr></table>
-<table style='float:left'><caption>Table-C</caption><tr><td>A Bronx Tale</td><td>1993</td></tr><tr><td>Bang the Drum Slowly</td><td>1973</td></tr><tr><td>Limitless</td><td>2011</td></tr></table>
+<table style='float:left'><caption>Table-B</caption><tr><td>A Bronx Tale</td><td>1993</td></tr><tr><td>Bang the Drum Slowly</td><td>1973</td></tr><tr><td>Limitless</td><td>2011</td></tr></table>
 <table style='float:left'><caption>Table-C</caption><tr><td>A Bronx Tale</td><td>3</td></tr><tr><td>Bang the Drum Slowly</td><td>3</td></tr><tr><td>Limitless</td><td>3</td></tr></table>
-<table style='float:left'><caption>Table-C</caption><tr><td>A Bronx Tale</td></tr><tr><td>Bang the Drum Slowly</td></tr><tr><td>Limitless</td></tr></table>
+<table style='float:left'><caption>Table-D</caption><tr><td>A Bronx Tale</td></tr><tr><td>Bang the Drum Slowly</td></tr><tr><td>Limitless</td></tr></table>
-<table style='float:left'><caption>Table-C</caption><tr><td>A Bronx Tale</td><td>Robert De Niro</td><td>1993</td></tr><tr><td>Bang the Drum Slowly</td><td>Robert De Niro</td><td>1973</td></tr><tr><td>Limitless</td><td>Robert De Niro</td><td>2011</td></tr></table>
+<table style='float:left'><caption>Table-E</caption><tr><td>A Bronx Tale</td><td>Robert De Niro</td><td>1993</td></tr><tr><td>Bang the Drum Slowly</td><td>Robert De Niro</td><td>1973</td></tr><tr><td>Limitless</td><td>Robert De Niro</td><td>2011</td></tr></table>
 |type="()"}
 - Table-A

@@ Line 54: / Line 54: @@
 - 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')
 </quiz>
 [[Category:Quizzes]]

@@ Line 27: / Line 27: @@
 {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
 |type="()"}
 - Table-A
-- Table-B
++ Table-B
 - Table-C
 - Table-D
-+ Table-E
+- Table-E
 {Select the statement that lists all the actors that starred in movies directed by Ridley Scott

@@ Line 25: / Line 25: @@
 - 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

@@ Line 1: / Line 1: @@
 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="()"}
--A. SELECT JOIN(name FROM actor, movie ON actor.id:director WHERE gross < budget) GROUP BY name
+- SELECT JOIN(name FROM actor, movie ON actor.id:director WHERE gross < budget) GROUP BY name
--B. SELECT name FROM actor INNER JOIN movie BY actor.id = director HAVING gross < budget
+- SELECT name FROM actor INNER JOIN movie BY actor.id = director HAVING gross < budget
-+C. 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
--D. 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
--E. SELECT name FROM director INNER JOIN movie ON movie.id = director.id 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="()"}
--A. SELECT * FROM actor JOIN casting BY actor.id = actorid JOIN movie BY movie.id = movieid
+- SELECT * FROM actor JOIN casting BY actor.id = actorid JOIN movie BY movie.id = movieid
--B. SELECT * FROM actor JOIN casting ON actor.id = actorid AND JOIN movie ON movie.id = movieid
+- SELECT * FROM actor JOIN casting ON actor.id = actorid AND JOIN movie ON movie.id = movieid
--C. SELECT * FROM actor JOIN casting JOIN movie ON actor.id = actorid AND movie.id = movieid
+- SELECT * FROM actor JOIN casting JOIN movie ON actor.id = actorid AND movie.id = movieid
--D. SELECT * FROM actor JOIN casting ON actor.id = actorid AND movie ON movie.id = movieid
+- SELECT * FROM actor JOIN casting ON actor.id = actorid AND movie ON movie.id = movieid
-+E. SELECT * FROM actor JOIN casting ON actor.id = actorid JOIN 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="()"}
--A. 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 IN 'John %' GROUP BY name ORDER BY 2
--B. 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 actor JOIN casting ON actorid=actor.id WHERE name LIKE 'J%' GROUP BY name ORDER BY 2 DESC
-+C. 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 ON actorid=actor.id WHERE name LIKE 'John %' GROUP BY name ORDER BY 2 DESC
--D. 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 (actorid ON actor.id) AND name LIKE 'John %' GROUP BY name ORDER BY 2 DESC
--E. SELECT name, COUNT(movieid) FROM casting JOIN actor WHERE name LIKE 'John %' GROUP BY name ORDER BY COUNT(movieid) 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="()"}
--A. 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 AND actor ON movie.id = movieid AND actor.id = actorid WHERE ord = NULL AND director = (SELECT id FROM actor WHERE name LIKE 'Ridley Scott')
--B. 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 JOIN actor ON movie.id = movieid OR actor.id = actorid WHERE ord = 1 AND director = (SELECT id FROM actor WHERE name LIKE 'Ridley Scott')
--C. 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 actorid = (SELECT id FROM actor WHERE name LIKE 'Ridley Scott')
-+D. 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 = movieid JOIN actor ON actor.id = actorid WHERE ord = 1 AND director = (SELECT id FROM actor WHERE name LIKE 'Ridley Scott')
--E. 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 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="()"}
--A. 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 WHERE name = 'Harrison Ford')
-+B. 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 > ALL (SELECT budget FROM movie JOIN casting JOIN actor ON movie.id = movieid AND actor.id = actorid OR name = 'Harrison Ford')
--C. 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 > (SELECT MAX(budget) FROM movie JOIN casting JOIN actor ON movie.id = movieid AND actor.id = actorid WHERE name = 'Harrison Ford')
--D. 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 > ALL (SELECT budget FROM movie JOIN casting ON movie.id = movieid JOIN actor ON actor.id = actorid WHERE name = 'Harrison Ford')
--E. 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 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')
 </quiz>
 [[Category:Quizzes]]

@@ Line 1: / Line 1: @@
 JOIN Quiz - part 2
-<div class='db_ref' style='background:none'>
+{{JOIN tables 2}}
-<table class="zoo">
-   <caption>movie</caption>
-    <tr>
-      <th>Field name</th>
-      <th>Type</th>
-      <th>Notes</th>
-    </tr>
-−
-    <tr>
-      <td notranslate="1">id</td>
-      <td notranslate="1">INTEGER</td>
-      <td>An arbitrary unique identifier</td>
-    </tr>
-−
-    <tr>
-      <td notranslate="1">title</td>
-      <td notranslate="1">CHAR(70)</td>
-      <td>The name of the film - usually in the language of the
-      first release.</td>
-    </tr>
-−
-    <tr>
-      <td notranslate="1">yr</td>
-      <td notranslate="1">DECIMAL(4)</td>
-      <td>Year of first release.</td>
-    </tr>
-−
-    <tr>
-      <td notranslate="1">director</td>
-      <td notranslate="1">INT</td>
-      <td>A reference to the actor table.</td>
-    </tr>
-−
-    <tr>
-      <td notranslate="1">budget</td>
-      <td notranslate="1">INTEGER</td>
-      <td>How much the movie cost to make (in a variety of currencies unfortunately).</td>
-    </tr>
-    <tr>
-      <td notranslate="1">gross</td>
-      <td notranslate="1">INTEGER</td>
-      <td>How much the movie made at the box office.</td>
-    </tr>
-</table>
-−
-<table class="zoo">
-   <caption>actor</caption>
-    <tr>
-      <th>Field name</th>
-−
-      <th>Type</th>
-−
-      <th>Notes</th>
-    </tr>
-−
-    <tr>
-      <td notranslate="1">id</td>
-−
-      <td notranslate="1">INTEGER</td>
-−
-      <td>An arbitrary unique identifier</td>
-    </tr>
-−
-    <tr>
-      <td notranslate="1">name</td>
-−
-      <td notranslate="1">CHAR(36)</td>
-−
-      <td>The name of the actor (the term actor is used to refer to
-      both male and female thesps.)</td>
-    </tr>
-</table>
-−
-<table class="zoo">
-   <caption>casting</caption>
-    <tr>
-      <th>Field name</th>
-−
-      <th>Type</th>
-−
-      <th>Notes</th>
-    </tr>
-−
-    <tr>
-      <td notranslate="1">movieid</td>
-−
-      <td notranslate="1">INTEGER</td>
-−
-      <td>A reference to the movie table.</td>
-    </tr>
-−
-    <tr>
-      <td notranslate="1">actorid</td>
-−
-      <td notranslate="1">INTEGER</td>
-−
-      <td>A reference to the actor table.</td>
-    </tr>
-−
-    <tr>
-      <td notranslate="1">ord</td>
-−
-      <td notranslate="1">INTEGER</td>
-−
-<td>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, ...</td>
-</tr>
-</table>
-</div>
 <quiz shuffle=none display=simple>