# Difference between revisions of "JOIN Quiz 2"

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

<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 `SELECT name, dept, COALESCE(dept, 0) AS result FROM teacher` on `teacher` 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>