Using Null

1.

select name
from teacher
where dept is null

2.

SELECT teacher.name, dept.name
 FROM teacher INNER JOIN dept
           ON (teacher.dept=dept.id)

3.

SELECT teacher.name,dept.name
FROM teacher 
LEFT JOIN dept ON teacher.dept = dept.id

笔记：左查询（LEFT JOIN）将左边表的主键全部保留；右查询反之；INNER JOIN 则仅保留相同的连接项

4.

查询所有部门的教师名称

select teacher.name,dept.name
from dept 
left join teacher on teacher.dept = dept.id

5.

select name,coalesce(mobile,'07986 444 2266' )
from teacher

6.

select x.name,coalesce(y.name,'None')
from teacher as x 
left join dept as y on x.dept = y.id

7.

select count(name),count(mobile)
from teacher

8.

select dept.name,count(teacher.name)
from teacher
right join dept on teacher.dept = dept.id
group by dept.name

9.

select 
  teacher.name,
  case
    when dept.id in (1,2) then 'Sci'
    else 'Art'
  end
from teacher
left join dept on teacher.dept = dept.id

10.

select
  teacher.name,
  case
    when teacher.dept in (1,2) then 'Sci'
    when teacher.dept = 3 then 'Art'
    else 'None'
  end
from teacher
left join dept on teacher.dept = dept.id