sqlzoo - 8 Using Null & 8+ Numeric Examples / NSS Tutorial

- 代表省略 与前面题型相同
* 代表错题，重点看
? 代表有疑问

---

Using Null

5. COALESCE

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


显示老师名字及手机号，手机号为空则显示为 ‘07986 444 2266’

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

COALESCE的使用：

COALESCE(x,y,z) = x if x is not NULL
COALESCE(x,y,z) = y if x is NULL and y is not NULL
COALESCE(x,y,z) = z if x and y are NULL but z is not NULL
COALESCE(x,y,z) = NULL if x and y and z are all NULL

COALESCE里面元素都为空则返回NULL, 否则从左到右返回非空元素。

8. The number of staff in each department

Use COUNT and GROUP BY dept.name to show each department and the number of staff. Use a RIGHT JOIN to ensure that the Engineering department is listed.


列出每个部门及其老师的个数

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

为了显示所有部门，包括没有人的部门Engineering，使用left join，在group by时使用的是dept.name

9. CASE

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


如果老师的dept为1或2，则显示‘Sci’，否则显示‘Art’

select name, 
case when dept = '1' or dept = '2' then 'Sci' 
else 'Art' 
end 
from teacher

dept = '1' or dept = '2' 也可以写为 dept in ( '1' , '2')
dept为NULL时，也会执行else，输出Art

CASE的使用：

CASE WHEN condition1 THEN value1
           WHEN condition2 THEN value2
           ELSE def_value
END
记得加END！

NSS Tutorial

8. * Number of Computing Students in Manchester

Show the institution, the total sample size and the number of computing students for institutions in Manchester for ‘Q01’.


列出回答了Q01问题且机构名包含’Manchester’的机构，以及这些机构中的总样本数，和样本数中计算机专业的人数

-- 这是符合条件的机构及各机构的总样本数
SELECT institution, sum(sample)
  FROM nss
 WHERE question='Q01'
   AND (institution LIKE '%Manchester%') 
group by institution

-- 这是符合条件的机构及各机构计算机专业的样本数
SELECT institution, sum(sample)
  FROM nss
 WHERE question='Q01'
   AND (institution LIKE '%Manchester%') 
   AND subject='(8) Computer Science' 
group by institution

-- 为了将两列条件不同的信息组合在一起，使用CASE！！
SELECT institution, sum(sample), 
sum(case when subject='(8) Computer Science'
		 then sample 
		 end) as comp
  FROM nss
 WHERE question='Q01'
   AND (institution LIKE '%Manchester%') 
group by institution

为了将两列条件不同的信息组合在一起，可以使用CASE！！
在case中进行条件的附加

sum()在case语句外，而不是在里面进行运算（如下），否则出错：

case when subject='(8) Computer Science'
	 then sum(sample) 
	 end) as comp