Using Null/zh

http://sqlzoo.net/wiki/Using_Null/zh

Teacher(id,dept,name,phone,mobile)

dept(id,name)

dept.id=teacher.dept

语句：select dept.name from dept

显示结果为：name 【Computing，Design，Engineerin】

语句：select teacher.name from teacher。从表格teacher中列出所有的老师名称。

显示结果为：

name

Shrivell

Throd

Splint

Spiregrain

Cutflower

Deadyawn

语句：从表格teacher中查询显示所有学系名称name。根据结果可知学系名称相比于表格dept中的学系名称要少一种。

Select dept.name from dept

Join teacher on dept.id=teacher.dept

name

Computing

Computing

Computing

Design

1.列出學系department是NULL值的老師。

Select teacher.name from teacher

Where dept is null

 

2.注意INNER JOIN 不理會沒有學系的老師及沒有老師的學系。

selectteacher.name,dept.name

fromteacher join dept

on dept.id=teacher.dept

或者

SELECT teacher.name,dept.name

 FROM teacher INNER JOIN dept

           ON (teacher.dept=dept.id)

3.使用不同的JOIN(外連接)，來列出全部老師。

Teacher(id,dept,name,phone,mobile)

dept(id,name)

dept.id=teacher.dept

根据原题意，有些院系中可能没有老师，有些老师并没有列入到相应的院系中。所以依据teacher表格为主，即选用left join 或者right join 列出全部老师名称，name。

select teacher.name,dept.name

from teacher left join dept

on dept.id=teacher.dept

4.使用不同的JOIN(外連接)，來列出全部學系。

select teacher.name,dept.name from dept

left join teacher

on dept.id=teacher.dept

5.Use COALESCE to print the mobile number. Use the number'07986 444 2266' if there is no number given. Show teacher name and mobilenumber or '07986 444 2266'

select teacher.name,mobile,coalesce(mobile,’07986 4442266’)AS N_MOBILR

from teacher

说明：函数coalesce为对空值进行处理，可认为是对null的替换函数。

Coalesce（expression，value1，value2……，value）

例如coalesce（mobile，phone，110）“mobile”为待检测参数，如果mobile为空值，则检测参数“phone”，参数phone不为空值则显示结果为“phone”，倘若参数“phone”也为空值，则继续检测参数“110”，因为此处“110”不是空值，所以此处函数结果显示为“110”。

6.Use the COALESCE function and a LEFT JOIN to print theteacher name and department name. Use the string 'None' where there is nodepartment.

解答：同上一题，使用函数coalesce（）

select teacher.name,coalesce(dept.name,’None’) fromteacher

left join dept on teacher.dept=dept.id

7.使用COUNT來數算老師和流動電話數目。

select count(teacher.id),count(mobile)

from teacher

8.使用COUNT 和 GROUP

BY dept.name來顯示每一學系的老師數目。 使用 RIGHT JOIN 以確保工程系Engineering 是在當中。

select dept.name,count(teacher.name)

from dept left join teacher

on dept.id=teacher.dept

group by dept.name

9.Use CASE to show the name of each teacher followed by'Sci' if the teacher is in dept 1 or 2 and 'Art' otherwise.

select name, 

case when deptin(1,2) then 'Sci' 

else 'Art' end 

from teacher 

10.Use CASE to show the name of each teacher followed by 'Sci'if the teacher is in dept 1 or 2, show 'Art' if the teacher's dept is 3 and'None' otherwise.

selectteacher.name, 

case whenteacher.dept in(1,2) then 'Sci' 

when teacher.dept=3then 'Art' 

else 'None' end 

from teacher fromteacher

 

解答：函数case（），语句为

case when 条件1 then 结果1

when 条件2 then 结果2

else  结果3

end

CASE WHEN condition1THEN value1

       WHEN condition2 THEN value2 

       ELSE def_value

  END

 

原题中若teacher.dept=1或teacher.dept=2，则teacher.dept一栏显示“SCI”;

若teacher.dept=3，则teacher.dept一栏显示“Art”，若为其他值，则显示为“None”。所以case语句为

case whenteacher.dept in(1,2) then 'Sci' 

when teacher.dept=3then 'Art' 

else 'None' end