SQL ZOO我的答案之NULL值

NULL, INNER JOIN, LEFT JOIN, RIGHT JOIN

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,dept.name FROM teacher LEFT JOIN dept ON (teacher.dept=dept.id)

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

     SELECT teacher.name,dept.name FROM teacher RIGHT JOIN dept 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 teacher.name,COALESCE(teacher.mobile,'07986 444 2266') AS mobile FROM teacher

    COALESCE(x,y,z)按顺序取x或者y或者z的值，略过空值NULL

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') AS department FROM teacher LEFT JOIN dept ON (teacher.dept=dept.id)

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

      SELECT COUNT(name),COUNT(mobile) FROM teacher

      SQL COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）；SQL COUNT(*) 返回记录条数

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

      SELECT dept.name,COUNT(teacher.name) 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=1 OR dept=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.

     SELECT teacher.name,CASE WHEN (dept=1 OR dept=2) THEN 'Sci'
                         WHEN dept=3 THEN 'Sci'    
                         ELSE 'None'  END
     FROM teacher

 

1、一個成員被工黨逐出黨，現沒屬任何黨。找出他。

     SELECT Name FROM msp WHERE Party IS NULL

2、列出每個黨及其領導人。

     SELECT Name,Leader FROM party GROUP BY Name,Leader

4、列出政黨名單，當中最少有一名黨員在議會內。

    select party.name from msp join party on msp.party=party.code  group by party.name