sqlzoo--using null

• 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

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的老师姓名

   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. 使用连接的方式列出全部老师和其对应的系名

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

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

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

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

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

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

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

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

   select count(id),count(mobile) from teacher; 
   

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

   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

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

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

    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. 选择正确使用外连接的语句

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

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

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

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

   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得到的结果

   display 0 in result column for all teachers without department;
   

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

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

   'four' for Throd
   

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

   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