sqlzoo习题答案--Using Null

最新推荐文章于 2022-07-30 20:22:46 发布

原创最新推荐文章于 2022-07-30 20:22:46 发布 · 572 阅读

0 ·

CC 4.0 BY-SA版权

MySql 专栏收录该内容

9 篇文章

订阅专栏

本文提供了解决SQLZOO中关于使用COALESCE函数处理NULL值的习题答案，重点关注如何在教师移动电话号码缺失时显示默认值'07986 444 2266'，展示教师姓名及其手机号码或默认值。

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 name,coalesce(mobile,'07986 444 2266')as mobile from teacher

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 teacher.name,coalesce(dept.name,'None')as department
from teacher left join dept
on teacher.dept=dept.id

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

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 name,
      case when dept=1 then 'Sci'
              when dept=2 then 'Sci'
              else 'Art'
      end
from teacher