【SQL-SQLZOO】Tutorials-8.Using NULL

Using Null/zh

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 teacher.name,
 case when dept.name is null then null 
      else dept.name end 
from teacher left join dept on (teacher.dept=dept.id)

4.使用不同的JOIN(外連接)，來列出全部學系。
select case when teacher.name is null then null else teacher.name end,dept.name
from dept left join teacher on (teacher.dept=dept.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 teacher.name,coalesce(dept.name,'None')
from teacher left join dept on (teacher.dept=dept.id)

7.使用COUNT來數算老師和流動電話數目。
select count(name),count(mobile) from teacher

8.使用COUNT 和 GROUP BY dept.name來顯示每一學系的老師數目。 使用 RIGHT JOIN 以確保工程系Engineering 是在當中。
select dept.name,count(teacher.id)
from teacher right join dept on (teacher.dept=dept.id)
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 teacher.name,
 case when dept.id = 1 or dept.id = 2 then 'Sci'
      else 'Art' 
 end
from teacher left join dept on (teacher.dept=dept.id)

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 teacher.name,
 case when dept.id = 1 or dept.id = 2 then 'Sci'
      when dept.id = 3 then 'Art'
      else 'None'
 end
from teacher left join dept on (teacher.dept=dept.id)

Scottish Parliament/zh

1.一個成員被工黨逐出黨，現沒屬任何黨。找出他。
select name from msp
where party is null

2.列出每個黨及其領導人。
select name,leader from party

3.列出每個黨及其領導人，這些黨其實是沒有領導人的。
select name,leader from party
where leader is not null

4.列出政黨名單，當中最少有一名黨員在議會內。
select party.name
from party left join msp on (msp.party=party.code)
group by party.code,party.name
having count(msp.name) >= 1

5.列出議會成員的名單，如有所屬政黨，一同列出。確保 Canavan MSP, Dennis 是在名單中。 按msp.name順序排列。
select msp.name,party.name
from msp left join party on (msp.party=party.code)
order by msp.name

6.列出議會中每一政黨的黨員人數。
select party.name,count(msp.name)
from party inner join msp on (msp.party=party.code)
group by party.code,party.name

7.列出每一政黨的議會中黨員人數，包括沒有黨員在議會中的政黨。
select party.name,count(msp.name)
from party left join msp on (msp.party=party.code)
group by party.code,party.name

Quiz

1.Select the code which uses an outer join correctly.
Answer：

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

2.Select the correct statement that shows the name of department which employs Cutflower
Answer：

SELECT dept.name FROM teacher JOIN dept ON (dept.id = teacher.dept) WHERE teacher.name = 'Cutflower'

3.Select out of following the code which uses a JOIN to show a list of all the departments and number of employed teachers
Answer：

SELECT dept.name, COUNT(teacher.name) FROM teacher RIGHT JOIN dept ON dept.id = teacher.dept GROUP BY dept.name

4.Using SELECT name, dept, COALESCE(dept, 0) AS result FROM teacher on teacher table will:
Answer：

display 0 in result column for all teachers without department

5.Query:

SELECT name,
       CASE WHEN phone = 2752 THEN 'two'
            WHEN phone = 2753 THEN 'three'
            WHEN phone = 2754 THEN 'four'
            END AS digit
  FROM teacher

shows following ‘digit’:
Answer：

'four' for Throd

6.Select the result that would be obtained from the following code:

SELECT name, 
     CASE 
      WHEN dept 
       IN (1) 
       THEN 'Computing' 
      ELSE 'Other' 
     END 
 FROM teacher

Answer：

Summary

• 图源自菜鸟教程
  
• coalesce(x,y,…) 函数：返回第一个不是空的值，用于重置空值，也可以用case函数完成，例如coalesce(NULL,NULL,X)的返回值是x