Difference between revisions of "JOIN Quiz 2"

From SQLZOO
Jump to: navigation, search
(Created page with "JOIN Quiz - part 2 <div class='db_ref' style='background:none'> <table class='zoo'> <caption>teacher</caption> <tr> <th>id</th> <th>dept</th> <th>name</th> <th>phone</th> <t...")
 
Line 81: Line 81:
 
- SELECT teacher.name, dept.name FROM teacher OUTER JOIN dept ON 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 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 = teacher.dept) WHERE dept.id = (SELECT dept FROM teacher HAVING name = 'Cutflower')
 +
- SELECT dept.name FROM teacher JOIN dept WHERE dept.id = (SELECT dept FROM teacher WHERE name = '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 teacher.name = 'Cutflower'
 +
- SELECT name FROM teacher JOIN dept ON (id = dept) WHERE id = (SELECT dept FROM teacher WHERE name = 'Cutflower')
 +
  
 
</quiz>
 
</quiz>
  
 
[[Category:Quizzes]]
 
[[Category:Quizzes]]

Revision as of 13:17, 18 July 2012

JOIN Quiz - part 2

teacher
id dept name phone mobile
101 1 Shrivell 2753 07986 555 1234
102 1 Throd 2754 07122 555 1920
103 1 Splint 2293
104 Spiregrain 3287
105 2 Cutflower 3212 07996 555 6574
106 Deadyawn 3345
dept
id name
1 Computing
2 Design
3 Engineering
Syntax error

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 = teacher.dept) WHERE dept.id = (SELECT dept FROM teacher HAVING name = 'Cutflower')
SELECT dept.name FROM teacher JOIN dept WHERE dept.id = (SELECT dept FROM teacher WHERE name = '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 teacher.name = 'Cutflower'
SELECT name FROM teacher JOIN dept ON (id = dept) WHERE id = (SELECT dept FROM teacher WHERE name = 'Cutflower')

Your score is 0 / 0
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense