# Using Null Quiz

Test your understanding of the NULL value

teacher
id dept name phone
101 1 Shrivell 2753
102 1 Throd 2754
103 1 Splint
104 Spiregrain
105 2 Cutflower 3212
dept
id name
1 Computing
2 Design
3 Engineering
Select the code which uses an outer 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)
```
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')
```
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
```
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
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
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