|
|
| Line 1: |
Line 1: |
| − | 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>
| |
| − | <th>mobile</th>
| |
| − | </tr>
| |
| − | <tr>
| |
| − | <td align='right'>101</td>
| |
| − | <td align='right'>1</td>
| |
| − | <td>Shrivell</td>
| |
| − | <td align='right'>2753</td>
| |
| − | <td>07986 555 1234</td>
| |
| − | </tr>
| |
| − | <tr>
| |
| − | <td align='right'>102</td>
| |
| − | <td align='right'>1</td>
| |
| − | <td>Throd</td>
| |
| − | <td align='right'>2754</td>
| |
| − | <td>07122 555 1920</td>
| |
| − | </tr>
| |
| − | <tr><td align='right'>103</td>
| |
| − | <td align='right'>1</td>
| |
| − | <td>Splint</td>
| |
| − | <td align='right'>2293</td>
| |
| − | <td></td>
| |
| − | </tr>
| |
| − | <tr><td align='right'>104</td>
| |
| − | <td align='right'></td>
| |
| − | <td>Spiregrain</td>
| |
| − | <td align='right'>3287</td>
| |
| − | <td></td>
| |
| − | </tr>
| |
| − | <tr>
| |
| − | <td align='right'>105</td>
| |
| − | <td align='right'>2</td>
| |
| − | <td>Cutflower</td>
| |
| − | <td align='right'>3212</td>
| |
| − | <td>07996 555 6574</td>
| |
| − | </tr>
| |
| − | <tr>
| |
| − | <td align='right'>106</td>
| |
| − | <td align='right'></td>
| |
| − | <td>Deadyawn</td>
| |
| − | <td align='right'>3345</td>
| |
| − | <td></td>
| |
| − | </tr>
| |
| − | </table>
| |
| − | <table class='zoo'>
| |
| − | <caption>dept</caption>
| |
| − | <tr>
| |
| − | <th>id</th>
| |
| − | <th>name</th>
| |
| − | </tr>
| |
| − | <tr>
| |
| − | <td align='right'>1</td>
| |
| − | <td>Computing</td>
| |
| − | </tr>
| |
| − | <tr>
| |
| − | <td align='right'>2</td>
| |
| − | <td>Design</td>
| |
| − | </tr>
| |
| − | <tr>
| |
| − | <td align='right'>3</td>
| |
| − | <td>Engineering</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')
| |
| − |
| |
| − |
| |
| − | {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]]
| |
