sqlzoo练习15-null-quiz

sqlzoo练习15-NULL quiz

主要是对上节中学习到的NULL空值的理解与复习

练习

select the code which uses an outer join correctly.

1
2
3

select teacher.name, dept.name
from teacher
left outer join dept on (teacher.dept=dept.id) -- 左连接

Select the correct statement that shows the name of department which employs Cutflower -

选出雇佣了Cutfolowerl老师的系

select dept.name
from teacher
join dept on (dept.id=teacher.dept)
where teacher.name='Cutflower';

Select out of following the code which uses a JOIN to show a list of all the departments and number of employed teachers

选出所有的院系和该院系雇佣的老师的数目

select dept.name, count(teacher.name)  -- 统计老师的个数
from teacher
right join dept on dept.id=teacher.dept
group by dept.name

Using SELECT name, dept, COALESCE(dept, 0) AS result FROM teacher on teacher table will:

1	display 0 in result column for all teachers without department

the result of Query

主要考察的是case语句的用法

select name,
     case when phone=2752 then 'two'
          when phone=2753 then 'three'
          when phone=2754 then 'four'
     end as digit
from teacher

Select the result that would be obtained from the following code:

select name,
    case when dept in (1)
         then 'Computing'  -- 如果dept编号是1，则标记为computing；否则是Other
    else 'Other' end
from teacher;