SQLZOO：Using Null

数据表：teacher-dept

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
...

Q1

List the teachers who have NULL for their department.

SELECT name FROM teacher WHERE dept IS NULL

IS NULL | IS NOT NULL

Q2

Note the INNER JOIN misses the teachers with no department and the departments with no teacher.

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

INNER JOIN

INNER JOIN 关键字在表中存在至少一个匹配时返回行

只返回那些在两个表中都有匹配的记录

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

参数说明：

• columns：要显示的列名
• table1：表1的名称
• table2：表2的名称
• column_name：表中用于连接的列名

注释：INNER JOIN 与 JOIN 是相同的

Q3

Use a different JOIN so that all teachers are listed.

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

LEFT JOIN

LEFT JOIN 关键字从左表返回所有的行，即使右表中没有匹配

如果右表中没有匹配，则结果为 NULL

Q4

Use a different JOIN so that all departments are listed.

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

RIGHT JOIN

RIGHT JOIN 关键字从右表返回所有的行，即使左表中没有匹配

如果左表中没有匹配，则结果为 NULL

---

Using the COALESCE function

Q5

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 name,COALESCE(mobile,'07986 444 2266') FROM teacher

COALESCE

COALESCE接受任意数量参数并返回第一个非空值

  COALESCE(x,y,z) = x if x is not NULL
  COALESCE(x,y,z) = y if x is NULL and y is not NULL
  COALESCE(x,y,z) = z if x and y are NULL but z is not NULL
  COALESCE(x,y,z) = NULL if x and y and z are all NULL

Q6

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 teacher.name,COALESCE(dept.name,'None') 
FROM teacher LEFT JOIN dept ON teacher.dept=dept.id

Q7

Use COUNT to show the number of teachers and the number of mobile phones.

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

Q8

Use COUNT and GROUP BY dept.name to show each department and the number of staff. Use a RIGHT JOIN to ensure that the Engineering department is listed.

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

---

Using CASE

CASE

CASE语句遍历条件并在满足第一个条件时返回一个值

一旦条件为真，它将停止读取并返回结果

如果没有条件为 true，则返回 ELSE 子句中的值

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

---

Q9

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 name,
    CASE 
        WHEN dept IN (1,2) THEN 'Sci'
        ELSE 'Art'
    END
FROM teacher

Q10

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 name,
    CASE 
        WHEN dept IN (1,2) THEN 'Sci'
        WHEN dept=3 THEN 'Art'
        ELSE 'None'
    END
FROM teacher