【Tips】
- sqlzoo官网链接:
- 每个系列开头介绍所用表及其信息
- 每道题均测试通过,含有题目描述、代码和结果
- 题目为自己简写,最好在官网查看具体题目
- 部分测试结果不完整,仅为一部分截图
NULL值之老师和学系练习链接:
SELECT teacher.name FROM teacher LEFT JOIN dept ON teacher.dept=dept.id WHERE dept.name IS NULL
SELECT teacher.name, dept.name FROM teacher INNER JOIN dept ON (teacher.dept=dept.id)
SELECT teacher.name,dept.name FROM teacher LEFT JOIN dept ON teacher.dept=dept.id
SELECT teacher.name,dept.name FROM dept LEFT JOIN teacher ON dept.id=teacher.dept
SELECT name,COALESCE(mobile,‘07986 444 2266‘) FROM teacher --若mobile为空,返回‘07986 444 2266‘
SELECT teacher.name,COALESCE(dept.name,‘None‘) FROM teacher LEFT JOIN dept ON teacher.dept=dept.id
SELECT COUNT(name),COUNT(mobile) FROM teacher
SELECT dept.name,COUNT(teacher.name) FROM teacher RIGHT JOIN dept ON dept.id=teacher.dept GROUP BY dept.name
SELECT name, CASE WHEN dept=1 OR dept=2 THEN ‘Sci‘ ELSE ‘Art‘ END FROM teacher
SELECT name, CASE WHEN dept=1 OR dept=2 THEN ‘Sci‘ WHEN dept=3 THEN ‘Art‘ ELSE ‘None‘ END FROM teacher