sqlzoo 8.Using Null 答案

有什么问题，欢迎评论或私聊。转载请私聊博主，谢谢。

原题链接：https://sqlzoo.net/wiki/Using_Null

其他题解连接：https://blog.csdn.net/aiqiyizz/article/details/109057732

难度不大，摸了好久的鱼。。

8 Using Null

8.1 NULL, INNER JOIN, LEFT JOIN, RIGHT JOIN

SELECT name
FROM teacher
WHERE dept IS NULL

8.2

直接给的例子

SELECT teacher.name, dept.name
 FROM teacher INNER JOIN dept
           ON (teacher.dept=dept.id)

8.3

SELECT teacher.name, dept.name
 FROM teacher LEFT JOIN dept
           ON (teacher.dept=dept.id)

8.4

SELECT teacher.name, dept.name
 FROM teacher RIGHT JOIN dept
           ON (teacher.dept=dept.id)

8.5 Using the COALESCE function

COALESCE的应用

SELECT name, COALESCE(mobile, '07986 444 2266')
FROM teacher

8.6

SELECT teacher.name, COALESCE(dept.name, 'None')
FROM teacher LEFT JOIN dept ON teacher.dept = dept.id

8.7

SELECT COUNT(name), COUNT(mobile)
FROM teacher

8.8

SELECT dept.name, COUNT(teacher.dept) AS 'the number of staff'
FROM teacher RIGHT JOIN dept ON teacher.dept = dept.id
GROUP BY dept.name

8.9 Using CASE

不难，但是写的时候脑子抽了一下

SELECT name, CASE WHEN dept IN (1, 2) THEN 'Sci' ELSE 'Art' END
FROM teacher

8.10

SELECT
  name,
  CASE
    WHEN dept IN (1, 2) THEN 'Sci'
    WHEN dept = 3 THEN 'Art'
    ELSE 'None'
  END
FROM
  teacher