sqlzoo练习14-NULL quiz

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

image

NULL-quiz

练习

1. select the code which uses an outer join correctly.

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

2. 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';

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

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

display 0 in result column for all teachers without department

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

image

image

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

image