• sqlzoo–select basics,select from world

• sqlzoo–select from nobel ,select in select

• sqlzoo–sum and count

• sqlzoo–the join operation

• sqlzoo–More JOIN operations

• sqlzoo–using null

• sqlzoo–self join

sqlzoousing_null_9">sqlzoo–using null

table: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
…

table:dept学系

id編號	name名稱
1	Computing
2	Design
3	Engineering
…

1. 列出学系department是null的老师姓名

   sql">select name from teacher 
   where dept is null;
   

2. 使用inner join 不列出没有学系的老师和没有没有老师的学系

   sql">SELECT teacher.name, dept.name
    FROM teacher INNER JOIN dept
              ON (teacher.dept=dept.id);
   

3. 使用连接的方式列出全部老师和其对应的系名

   sql">select t.name,d.name from teacher t 
   left join dept d on (d.id = t.dept);
   

4. 列出全部学系和其对应的教师名称

   sql">select t.name,d.name from teacher t 
   right join dept d on (d.id = t.dept);
   

5. 使用COALESCE函数列出姓名及其电话号码，没有号码的使用’07986 444 2266’

   sql">select name,COALESCE(mobile,'07986 444 2266') 
   from teacher;
   

6. 使用COALESCE函数和JOIN方式打印出教师名称和其学系名称，没有学系的补null

   sql">select t.name,COALESCE(d.name,'None') 
   from teacher t  left join dept d
   on (t.dept = d.id);
   

7. 使用count函数来计算老师和移动电话数目

   sql">select count(id),count(mobile) from teacher; 
   

8. 使用count和group by dept.name 方式来显示每一个学系老师的数量

   sql">select d.name,count(t.name) from teacher t
   right join dept d on (d.id = t.dept)
   group by d.name;
   

9. 使用case语句完成以下：如果教师在第一或第二个部门则其后显示sci,否者其后显示art

   sql">select name,(case
   when dept in (1,2) then 'Sci'
   else 'Art'
   end)
    from teacher;
   

10. 如果教师在1，2部门则显示每个教师的姓名其后显示sci,如果教师部门是3，则显示art,否则显示none

    sql">select name,(case
    when dept in (1,2) then 'Sci'
    when dept = 3 then 'Art'
    else 'None'
    end) from teacher;
    

Using Null Quiz

table:teacher

id	dept	name	phone
101	1	Shrivell	2753
102	1	Throd	2754
103	1	Splint
104		Spiregrain
105	2	Cutflower	3212
106		Deadyawn

table:dept

id	name
1	Computing
2	Design
3	Engineering

1. 选择正确使用外连接的语句

   sql">SELECT teacher.name, dept.name FROM teacher 
   LEFT OUTER JOIN dept ON (teacher.dept = dept.id);
   

2. 显示名称为cutflower所对应部门的名称

   sql">SELECT dept.name FROM teacher JOIN dept ON (dept.id = teacher.dept) WHERE teacher.name = 'Cutflower';
   

3. 能够显示所有部门和教师数量的语句。

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

4. 使用语句SELECT name, dept, COALESCE(dept, 0) AS result FROM teacher得到的结果

   sql">display 0 in result column for all teachers without department;
   

5. 下列语句得到的结果：

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

   sql">'four' for Throd
   

6. 下列语句得出的结果：

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

   Shrivell	Computing
   Throd	Computing
   Splint	Computing
   Spiregrain	Other
   Cutflower	Other
   Deadyawn	Other