Difference between revisions of "JOIN Quiz 2"

From SQLZOO
Jump to: navigation, search
(Blanked the page)
Line 1: Line 1:
 +
JOIN Quiz - part 2
  
 +
<div class='db_ref' style='background:none'>
 +
<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>
 +
{Which of the following uses of JOIN is correct?
 +
|type="()"}
 +
- SELECT teacher.name, dept.name FROM teacher JOIN dept ON (dept = id)
 +
- SELECT teacher.name, dept.name FROM teacher, dept INNER JOIN ON (teacher.dept = dept.id)
 +
- SELECT teacher.name, dept.name FROM teacher, dept JOIN WHERE(teacher.dept = dept.id)
 +
- SELECT teacher.name, dept.name FROM teacher OUTER JOIN dept ON dept.id
 +
+ SELECT teacher.name, dept.name FROM teacher LEFT OUTER JOIN dept ON (teacher.dept > dept.id)
 +
 +
{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>
 +
 +
[[Category:Quizzes]]

Revision as of 15:01, 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 uses of JOIN is correct?

SELECT teacher.name, dept.name FROM teacher JOIN dept ON (dept = id)
SELECT teacher.name, dept.name FROM teacher, dept INNER JOIN ON (teacher.dept = dept.id)
SELECT teacher.name, dept.name FROM teacher, dept JOIN WHERE(teacher.dept = dept.id)
SELECT teacher.name, dept.name FROM teacher OUTER JOIN dept ON dept.id
SELECT teacher.name, dept.name FROM teacher LEFT OUTER JOIN dept ON (teacher.dept > dept.id)

2. Select the correct statement that shows the name of department which employs Cutflower

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')

3. Which of following uses of JOIN shows list of all the departments and number of employed teachers?

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

4. Using SELECT name, dept, COALESCE(dept, 0) AS result FROM teacher on teacher table will:

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

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

Variants
Actions
Reference
Toolbox
Google AdSense