Using Null Quiz
Test your understanding of the NULL value
id | dept | name | phone |
---|---|---|---|
101 | 1 | Shrivell | 2753 |
102 | 1 | Throd | 2754 |
103 | 1 | Splint | |
104 | Spiregrain | ||
105 | 2 | Cutflower | 3212 |
106 | Deadyawn | ||
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
'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
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 |