Using Null
Language: English • 日本語 • 中文
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
...
Teachers and Departments
The school includes many departments. Most teachers work exclusively for a single department. Some teachers have no department.
Selecting NULL values.
Summary
NULL, INNER JOIN, LEFT JOIN, RIGHT JOIN
1.
List the teachers who have NULL for their department.
Why we cannot use =
You might think that the phrase dept=NULL would work here but it doesn't - you can use the phrase dept IS NULL
That's not a proper explanation.
SELECT name
FROM teacher
WHERE dept IS NULL
Submit SQL restore default
Correct answer
name
Spiregrain
Deadyawn
2.
Note the INNER JOIN misses the teachers with no department and the departments with no teacher.
SELECT [Link], [Link]
FROM teacher INNER JOIN dept
ON ([Link]=[Link])
Submit SQL restore default
Correct answer
name name
Shrivell Computing
Throd Computing
Splint Computing
Cutflower Design
3.
Use a different JOIN so that all teachers are listed.
SELECT [Link], [Link]
FROM teacher LEFT JOIN dept ON [Link]=[Link]
Submit SQL restore default
Correct answer
name name
Shrivell Computing
Throd Computing
Splint Computing
Spiregrain
Cutflower Design
Deadyawn
4.
Use a different JOIN so that all departments are listed.
SELECT [Link], [Link]
FROM teacher RIGHT JOIN dept ON [Link]=[Link]
Submit SQL restore default
Correct answer
name name
Shrivell Computing
Throd Computing
Splint Computing
Cutflower Design
Engineering
Using the COALESCE function
5.
Use COALESCE to print the mobile number. Use the number '07986 444 2266' if there is no number given. Show teacher name and mobile
number or '07986 444 2266'
SELECT [Link], COALESCE([Link], '07986 444 2266')
FROM teacher LEFT JOIN dept ON [Link]=[Link]
Submit SQL restore default
Correct answer
name COALESCE(teac..
Shrivell 07986 555 1234
Throd 07122 555 1920
Splint 07986 444 2266
Spiregrain 07986 444 2266
Cutflower 07996 555 6574
Deadyawn 07986 444 2266
6.
Use the COALESCE function and a LEFT JOIN to print the teacher name and department name. Use the string 'None' where there is no
department.
SELECT [Link], COALESCE([Link], 'None')
FROM teacher LEFT JOIN dept ON [Link]=[Link]
Submit SQL restore default
Correct answer
name COALESCE(dept..
Shrivell Computing
Throd Computing
Splint Computing
Spiregrain None
Cutflower Design
Deadyawn None
7.
Use COUNT to show the number of teachers and the number of mobile phones.
SELECT COUNT(name), COUNT(mobile)
FROM teacher
Submit SQL restore default
Correct answer
COUNT(name) COUNT(mobile)
6 3
8.
Use COUNT and GROUP BY [Link] to show each department and the number of staff. Use a RIGHT JOIN to ensure that the Engineering
department is listed.
SELECT [Link], COUNT([Link])
FROM teacher RIGHT JOIN dept ON [Link]=[Link]
GROUP BY [Link]
Submit SQL restore default
Correct answer
name COUNT(teacher..
Computing 3
Design 1
Engineering 0
Using CASE
9.
Use CASE to show the name of each teacher followed by 'Sci' if the teacher is in dept 1 or 2 and 'Art' otherwise.
SELECT [Link],
CASE WHEN dept=1 THEN 'Sci'
WHEN dept=2 THEN 'Sci'
ELSE 'Art' END
FROM teacher LEFT JOIN dept ON [Link]=[Link]
Submit SQL restore default
Correct answer
name CASE WHEN dep..
Shrivell Sci
Throd Sci
Splint Sci
Spiregrain Art
Cutflower Sci
Deadyawn Art
10.
Use CASE to show the name of each teacher followed by 'Sci' if the teacher is in dept 1 or 2, show 'Art' if the teacher's dept is 3 and 'None'
otherwise.
SELECT [Link],
CASE WHEN dept=1 THEN 'Sci'
WHEN dept=2 THEN 'Sci'
WHEN dept=3 THEN 'Art'
ELSE 'None' END
FROM teacher LEFT JOIN dept ON [Link]=[Link]
Submit SQL restore default
Correct answer
name CASE WHEN dep..
Shrivell Sci
Throd Sci
Splint Sci
Spiregrain None
Cutflower Sci
Deadyawn None
Clear your results
Using Null Quiz
Retrieved from ‘[Link]
This page was last modified on 1 June 2022, at 01:56.