# Difference between revisions of "Using Null Quiz"

Jump to: navigation, search

Test your understanding of the NULL value

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

1. Select the code which uses a JOIN correctly.

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

5. 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':

 'four' for Throd NULL for all teachers NULL for Shrivell 'two' for Cutflower 'two' for Deadyawn

6. 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```
 Shrivell Computing Throd Computing Splint Computing Spiregrain Other Cutflower Other Deadyawn Other
 Shrivell Computing Throd Computing Splint Computing Spiregrain Computing Cutflower Computing Deadyawn Computing
 Shrivell Computing Throd Computing Splint Computing
 Spiregrain Other Cutflower Other Deadyawn Other
 Shrivell 1 Throd 1 Splint 1 Spiregrain 0 Cutflower 0 Deadyawn 0
 Table-A Table-B Table-C Table-D Table-E

Your score is 0 / 0