Using Null Quiz

From SQLZOO
Revision as of 12:43, 15 August 2012 by Marek (Talk | contribs)

Jump to: navigation, search

Using Null Quiz

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

<quiz shuffle=none display=simple> {Select the code which uses a JOIN correctly. |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')

{Select out of following the code which uses a JOIN to show a 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

{Query: SELECT name, CASE WHEN phone = 2752 THEN 'two' WHEN phone = 2753 THEN 'three' WHEN phone = 2754 THEN 'four' END AS digit FROM teacher shows following 'digit': |type="()"} + 'four' for Throd - NULL for all teachers - NULL for Shrivell - 'two' for Cutflower - 'two' for Deadyawn

{Select the result that would be obtained from the following code: SELECT name, CASE WHEN dept IN (1) THEN 'Computing' ELSE 'Other' END FROM teacher

Table-A
ShrivellComputing
ThrodComputing
SplintComputing
SpiregrainOther
CutflowerOther
DeadyawnOther
Table-B
ShrivellComputing
ThrodComputing
SplintComputing
SpiregrainComputing
CutflowerComputing
DeadyawnComputing
Table-C
ShrivellComputing
ThrodComputing
SplintComputing
Table-D
Spiregrain Other
Cutflower Other
Deadyawn Other
Table-E
Shrivell 1
Throd 1
Splint 1
Spiregrain 0
Cutflower 0
Deadyawn 0

|type="()"} + Table-A - Table-B - Table-C - Table-D - Table-E

</quiz>