Difference between revisions of "Using Null/zh"

From SQLZOO
Jump to navigation Jump to search
(Created page with " <div class='ref_section'> <table class='zoo db_ref'> <caption>teacher</caption> <tr> <th>id</th> <th>dept</th> <th>name</th> <th>phone</th> <th>mobile</th> </tr> <tr> <td a...")
 
 
Line 2: Line 2:
 
<div class='ref_section'>
 
<div class='ref_section'>
 
<table class='zoo db_ref'>
 
<table class='zoo db_ref'>
<caption>teacher</caption>
+
<caption>teacher老師</caption>
 
<tr>
 
<tr>
<th>id</th>
+
<th>id編號</th>
<th>dept</th>
+
<th>dept學系</th>
<th>name</th>  
+
<th>name名字</th>  
<th>phone</th>
+
<th>phone內線電話</th>
<th>mobile</th>  
+
<th>mobile流動電話</th>  
 
</tr>
 
</tr>
 
<tr>
 
<tr>
Line 55: Line 55:
 
</table>
 
</table>
 
<table class='zoo db_ref'>
 
<table class='zoo db_ref'>
<caption>dept</caption>
+
<caption>dept學系</caption>
 
<tr>
 
<tr>
<th>id</th>
+
<th>id編號</th>
<th>name</th>
+
<th>name名稱</th>
 
</tr>
 
</tr>
 
<tr>
 
<tr>
Line 78: Line 78:
 
</div>
 
</div>
  
==Teachers and Departments==
+
==老師和學系==
<p>The school includes many departments. Most teachers work exclusively for a single department.
+
<p>一間學校有很多學系,大多數老師只為一個學系工作。
Some teachers have no department.</p>
+
有些老師沒有學系。</p>
  
 
[[Selecting NULL values.]]
 
[[Selecting NULL values.]]
  
 
<div class="progress_panel"><div>
 
<div class="progress_panel"><div>
   <div class="summary">Summary</div>
+
   <div class="summary"></div>
 
   <div class="progressbarbg">
 
   <div class="progressbarbg">
 
     <div class="progressbar"></div>
 
     <div class="progressbar"></div>
Line 97: Line 97:
 
<h2>NULL, INNER JOIN, LEFT JOIN, RIGHT JOIN</h2>
 
<h2>NULL, INNER JOIN, LEFT JOIN, RIGHT JOIN</h2>
 
<div class='qu'>
 
<div class='qu'>
List the teachers who have NULL for their department.
+
列出學系department是NULL值的老師。
<div class='hint' title='Why we cannot use ='>
+
<div class='hint' title='為何不能用 ='>
You might think that the phrase dept=NULL would work here but it doesn't - you can use the phrase <code>dept IS NULL</code>
+
你可能會以為 dept=NULL 是可行的,但它不是。你要使用<code>dept IS NULL</code>
  
<div title="That's not a proper explanation." class='hint'>
+
<div title="這不是正確解釋" class='hint'>
No it's not, but you can read a better explanation at Wikipedia:[http://en.wikipedia.org/wiki/Null_%28SQL%29 NULL.]  
+
不,不是,但你可以看更好的解說,在維基百科:[http://en.wikipedia.org/wiki/Null_%28SQL%29 NULL.]  
 
</div>
 
</div>
 
</div>
 
</div>
Line 116: Line 116:
  
 
<div class='qu'>
 
<div class='qu'>
Note the INNER JOIN misses the teachers with no department and the departments with no teacher.
+
注意INNER JOIN 不理會沒有學系的老師及沒有老師的學系。
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
SELECT teacher.name, dept.name
 
SELECT teacher.name, dept.name
Line 131: Line 131:
  
 
<div class='qu'>
 
<div class='qu'>
Use a different JOIN so that all teachers are listed. 
+
使用不同的JOIN(外連接),來列出全部老師。
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
Line 143: Line 143:
  
 
<div class='qu'>
 
<div class='qu'>
Use a different JOIN so that all departments are listed. 
+
使用不同的JOIN(外連接),來列出全部學系。
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
Line 181: Line 181:
  
 
<div class='qu'>
 
<div class='qu'>
Use COUNT to show the number of teachers and the number of mobile phones.   
+
使用COUNT來數算老師和流動電話數目。
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
Line 192: Line 192:
  
 
<div class='qu'>
 
<div class='qu'>
Use COUNT and GROUP BY '''dept.name''' to show each department and the number of staff.
+
使用COUNT 和 GROUP BY '''dept.name'''來顯示每一學系的老師數目。
Use a RIGHT JOIN to ensure that the Engineering department is listed.   
+
使用 RIGHT JOIN 以確保工程系Engineering 是在當中。
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>

Latest revision as of 17:25, 26 March 2016

teacher老師
id編號 dept學系 name名字 phone內線電話 mobile流動電話
101 1 Shrivell 2753 07986 555 1234
102 1 Throd 2754 07122 555 1920
103 1 Splint 2293
104 Spiregrain 3287
105 2 Cutflower 3212 07996 555 6574
106 Deadyawn 3345
...
dept學系
id編號 name名稱
1 Computing
2 Design
3 Engineering
...

老師和學系

一間學校有很多學系,大多數老師只為一個學系工作。 有些老師沒有學系。

Selecting NULL values.

NULL, INNER JOIN, LEFT JOIN, RIGHT JOIN

列出學系department是NULL值的老師。

你可能會以為 dept=NULL 是可行的,但它不是。你要使用dept IS NULL

不,不是,但你可以看更好的解說,在維基百科:NULL.

SELECT name
FROM teacher
WHERE dept IS NULL

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

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

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

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

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

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

Using the COALESCE function

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

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

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

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

使用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

Using 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.

SELECT name, CASE WHEN dept IN (1,2) 
THEN 'Sci'
ELSE 'Art' END
FROM teacher


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 name, CASE WHEN dept IN (1,2) 
THEN 'Sci'
WHEN dept = 3 
THEN 'Art'
ELSE 'None' END
FROM teacher
Clear your results