Using Null -SQLZOO

Using Null -SQLZOO

前言

记录一下自己在https://sqlzoo.net复习sql时写的sql语句

此篇为Using Null 篇章

涉及sql知识点

• Null
• JOIN 、LEFT JOIN 、RIGHT JOIN
• COUNT
• COALESCE
• CASE

知识点讲解

想选出Null的值不能写?=NULL要写？ is NULL

JOIN(INNER JOIN) 产生的结果集是两表的交集

LEFT JOIN 产生的结果集是表1的所有以及表2中与表1匹配的值

RIGHT JOIN产生的结果集是表2的所有以及表1中与表2匹配的值

COUNT(?)计算某列出现的次数

COALESCE(? ,…)从括号里面获取第一个不为NULL的值，顺序为从左到右

CASE ?1( when ?2 then ?3) … else ?4 end 类似 if else

从?1(列名) 中进行条件判断，如果?1=?2(任意值)就返回?3

所有when都不匹配则返回else后面?4的值

题目涉及的表

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

select name from teacher where dept is null

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

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

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

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

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

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

5.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’

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

6.Use the COALESCE function and a LEFT JOIN to print the teacher name and department name. Use the string ‘None’ where there is no department.

select t.name,coalesce(d.name,'None') from teacher t left join dept d on t.dept=d.id

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

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

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

select d.name,count(teacher.name) from teacher right join dept d on teacher.dept=d.id
group by d.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 t.name ,case t.dept when 1  then 'Sci' when 2 then 'Sci' else  'Art' end from teacher t

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.

select t.name ,case t.dept when 1  then 'Sci' when 2 then 'Sci' when 3 then 'Art' else  'None' end from teacher t