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' endfrom teacher

10.

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

CASE语句使用如下

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