SQLZOO(Using Null)Writeup

转载自SQLZOO(Using Null)Writeup

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 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

4.

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

RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。

5.

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

6.

SELECT teacher.name, COALESCE(dept.name,'None')
 FROM teacher left JOIN dept
           ON (teacher.dept=dept.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 name,
case when dept in(1,2) then 'Sci'
else 'Art' end
from teacher

10.

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

CASE语句使用如下

case x
    when situation1 then ...
    when situation2 then ...
    else ...end