Sqlzoo習(xí)題練習(xí)：Using NULL
習(xí)題鏈接：<u>http://sqlzoo.net/wiki/Using_Null</u>
一所學(xué)校會包括許多部門撼班。大多數(shù)教師只為一個(gè)部門工作斑唬。有些老師沒有部門胯舷。
下面是Using NULL習(xí)題中要用到數(shù)據(jù)庫中的兩個(gè)表格teacher和dept（部門），分別存儲著老師的信息及老師所在的部門信息谋旦。
下面為Using NULL 習(xí)題內(nèi)容：
知識點(diǎn)：NULL 值
NULL 值是遺漏的未知數(shù)據(jù)剩失。
默認(rèn)地屈尼，表的列可以存放 NULL 值。
如果表中的某個(gè)列是可選的拴孤，那么我們可以在不向該列添加值的情況下插入新記錄或更新已有的記錄脾歧。這意味著該字段將以 NULL 值保存。
NULL 值的處理方式與其他值不同演熟。
NULL 用作未知的或不適用的值的占位符鞭执。
注釋：無法比較 NULL 和 0；它們是不等價(jià)的芒粹。
測試 NULL 值時(shí)蚕冬，必須使用 IS NULL 和 IS NOT NULL 操作符。
--#1
/*
List the teachers who have NULL for their department.
*/
SELECT teacher.name
FROM teacher
WHERE dept IS NULL;
知識點(diǎn)：內(nèi)連接（INNER JOIN）
INNER JOIN 關(guān)鍵字
在表中存在至少一個(gè)匹配時(shí)是辕，INNER JOIN 關(guān)鍵字返回行。
INNER JOIN 關(guān)鍵字語法
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
注釋：INNER JOIN 與 JOIN 是相同的猎提。
--#2
/*
Note the INNER JOIN misses the teachers with no department and the departments with no teacher.
*/
SELECT teacher.name AS teacher_name,
dept.name AS dept_name
FROM teacher INNER JOIN dept
ON (teacher.dept = dept.id);
知識點(diǎn)：LEFT JOIN 關(guān)鍵字
LEFT JOIN 關(guān)鍵字會從左表 (table_name1) 那里返回所有的行获三，即使在右表 (table_name2) 中沒有匹配的行。
LEFT JOIN 關(guān)鍵字語法
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
注釋：在某些數(shù)據(jù)庫中锨苏， LEFT JOIN 稱為 LEFT OUTER JOIN疙教。
--#3
/*
Use a different JOIN so that all teachers are listed.
*/
SELECT teacher.name AS teacher_name,
dept.name AS dept_name
FROM teacher LEFT JOIN dept
ON (teacher.dept = dept.id);
知識點(diǎn)：RIGHT JOIN 關(guān)鍵字
RIGHT JOIN 關(guān)鍵字會從右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中沒有匹配的行伞租。
RIGHT JOIN 關(guān)鍵字語法
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
注釋：在某些數(shù)據(jù)庫中贞谓， RIGHT JOIN 稱為 RIGHT OUTER JOIN。
--#4
/*
Use a different JOIN so that all departments are listed.
*/
SELECT teacher.name AS teacher_name,
dept.name AS dept_name
FROM teacher RIGHT JOIN dept
ON (teacher.dept = dept.id);
知識點(diǎn)：COALESCE用法
COALESCE返回其參數(shù)中第一個(gè)非空表達(dá)式葵诈。
語法
COALESCE (expression [ ,...n ] )
參數(shù)
Expression表示任何類型的表達(dá)式裸弦。
返回類型
返回?cái)?shù)據(jù)類型優(yōu)先級最高的 expression 的數(shù)據(jù)類型。如果所有表達(dá)式都不可為 Null作喘，則結(jié)果的類型也不可為 Null理疙。
注釋
如果所有參數(shù)均為 NULL，則 COALESCE 返回 NULL泞坦。
--#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
/*
Use COUNT to show the number of teachers and the number of mobile phones.
*/
SELECT COUNT(name),COUNT(mobile)
FROM teacher ;
--#8
/*
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 teacher RIGHT JOIN dept
ON (teacher.dept = dept.id)
GROUP BY dept.name;
知識點(diǎn)：Case When用法
Case具有兩種格式窖贤。簡單Case函數(shù)和Case搜索函數(shù)。
第一種 格式 : 簡單Case函數(shù) :
格式說明
case 列名
when 條件值1 then 選擇項(xiàng)1
when 條件值2 then 選項(xiàng)2.......
else 默認(rèn)值 end
第二種 格式 :Case搜索函數(shù)
格式說明
case
when 列名= 條件值1 then 選擇項(xiàng)1
when 列名=條件值2 then 選項(xiàng)2.......
else 默認(rèn)值 end
--#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 teacher.dept IN (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.
*/
SELECT teacher.name,
CASE
WHEN teacher.dept IN (1,2)
THEN 'Sci'
WHEN teacher.dept = 3
THEN 'Art'
ELSE 'None'
END
FROM teacher;
