Difference between revisions of "JOIN Quiz 2"

From SQLZOO
Jump to: navigation, search
Line 114: Line 114:
  
 
<quiz shuffle=none display=simple>
 
<quiz shuffle=none display=simple>
−
{Which of the following uses of JOIN is correct?
+
{Which of the following statements lists the unfortunate directors of the movies which have caused financial loses? (gross <  budget)
 
|type="()"}
 
|type="()"}
−
- SELECT teacher.name, dept.name FROM teacher JOIN dept ON (dept = id)
+
+ SELECT name FROM actor INNER JOIN movie ON actor.id = director WHERE gross < budget
−
- SELECT teacher.name, dept.name FROM teacher, dept INNER JOIN ON (teacher.dept = dept.id)
+
- SELECT name FROM actor INNER JOIN movie ON actor.id:director WHERE gross < budget
−
- SELECT teacher.name, dept.name FROM teacher, dept JOIN WHERE(teacher.dept = dept.id)
+
- SELECT JOIN(name FROM actor, movie ON actor.id:director WHERE gross < budget) GROUP BY name  
−
- SELECT teacher.name, dept.name FROM teacher OUTER JOIN dept ON dept.id
+
- SELECT name FROM director INNER JOIN movie ON movie.id = director.id WHERE gross < budget
−
+ SELECT teacher.name, dept.name FROM teacher LEFT OUTER JOIN dept ON (teacher.dept > dept.id)
+
- SELECT name FROM actor INNER JOIN movie BY actor.id = director HAVING gross < budget
−
 
+
−
{Select the correct statement that shows the name of department which employs Cutflower
+
−
|type="()"}
+
−
- SELECT dept.name FROM teacher JOIN dept ON (dept.id = (SELECT dept FROM teacher WHERE name = 'Cutflower'))
+
−
- SELECT dept.name FROM teacher JOIN dept ON (dept.id = teacher.dept) WHERE dept.id = (SELECT dept FROM teacher HAVING name = 'Cutflower')
+
−
+ SELECT dept.name FROM teacher JOIN dept ON (dept.id = teacher.dept) WHERE teacher.name = 'Cutflower'
+
−
- SELECT dept.name FROM teacher JOIN dept WHERE dept.id = (SELECT dept FROM teacher WHERE name = 'Cutflower')
+
−
- SELECT name FROM teacher JOIN dept ON (id = dept) WHERE id = (SELECT dept FROM teacher WHERE name = 'Cutflower')
+
−
 
+
−
 
+
−
{Which of following uses of JOIN shows list of all the departments and number of employed teachers?
+
−
|type="()"}
+
−
- SELECT dept.name, COUNT(*) FROM teacher LEFT JOIN dept ON dept.id = teacher.dept
+
−
- SELECT dept.name, COUNT(teacher.name) FROM teacher, dept JOIN ON dept.id = teacher.dept GROUP BY dept.name
+
−
- SELECT dept.name, COUNT(teacher.name) FROM teacher JOIN dept ON dept.id = teacher.dept GROUP BY dept.name
+
−
- SELECT dept.name, COUNT(teacher.name) FROM teacher LEFT OUTER JOIN dept ON dept.id = teacher.dept GROUP BY dept.name
+
−
+ SELECT dept.name, COUNT(teacher.name) FROM teacher RIGHT JOIN dept ON dept.id = teacher.dept GROUP BY dept.name
+
−
 
+
−
{Using <code>SELECT name, dept, COALESCE(dept, 0) AS result FROM teacher</code> on <code>teacher</code> table will:
+
−
|type="()"}
+
−
- display 0 in result column for all teachers
+
−
+ display 0 in result column for all teachers without department
+
−
- do nothing - the statement is incorrect
+
−
- set dept value of all teachers to 0
+
−
- set dept value of all teachers without department to 0
+
  
 
</quiz>
 
</quiz>
  
 
[[Category:Quizzes]]
 
[[Category:Quizzes]]

Revision as of 16:11, 18 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, ...

1. Which of the following statements lists the unfortunate directors of the movies which have caused financial loses? (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 JOIN(name FROM actor, movie ON actor.id:director WHERE gross < budget) GROUP BY name
SELECT name FROM director INNER JOIN movie ON movie.id = director.id WHERE gross < budget
SELECT name FROM actor INNER JOIN movie BY actor.id = director HAVING gross < budget

Your score is 0 / 0
Retrieved from "http://sqlzoo.net/w/index.php?title=JOIN_Quiz_2&oldid=1747"
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense