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 value FROM teacher;
注:COALESCE函数用法:
1)返回第一个非null值。eg: SELECT COALESCE(NULL,NULL,3) ; 返回结果为3
SELECT COALESCE(NULL,1,2); 返回结果为1
2)修改默认值。eg: 以上例题即将空值修改为07986442266(COALESCE (mobile,'07986 444 2266') AS value)
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 IN (1,2) THEN 'Sci' ELSE 'Art' END AS subject FROM teacher;
注:此处的case when .. then .. end函数,不可省略end,另外为了美观,将该列重命名的子句需跟在end之后。