-
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 |
… |
-
列出学系department是null的老师姓名
sql">select name from teacher where dept is null;
-
使用inner join 不列出没有学系的老师和没有没有老师的学系
sql">SELECT teacher.name, dept.name FROM teacher INNER JOIN dept ON (teacher.dept=dept.id);
-
使用连接的方式列出全部老师和其对应的系名
sql">select t.name,d.name from teacher t left join dept d on (d.id = t.dept);
-
列出全部学系和其对应的教师名称
sql">select t.name,d.name from teacher t right join dept d on (d.id = t.dept);
-
使用COALESCE函数列出姓名及其电话号码,没有号码的使用’07986 444 2266’
sql">select name,COALESCE(mobile,'07986 444 2266') from teacher;
-
使用COALESCE函数和JOIN方式打印出教师名称和其学系名称,没有学系的补null
sql">select t.name,COALESCE(d.name,'None') from teacher t left join dept d on (t.dept = d.id);
-
使用count函数来计算老师和移动电话数目
sql">select count(id),count(mobile) from teacher;
-
使用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;
-
使用case语句完成以下:如果教师在第一或第二个部门则其后显示sci,否者其后显示art
sql">select name,(case when dept in (1,2) then 'Sci' else 'Art' end) from teacher;
-
如果教师在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 |
-
选择正确使用外连接的语句
sql">SELECT teacher.name, dept.name FROM teacher LEFT OUTER JOIN dept ON (teacher.dept = dept.id);
-
显示名称为cutflower所对应部门的名称
sql">SELECT dept.name FROM teacher JOIN dept ON (dept.id = teacher.dept) WHERE teacher.name = 'Cutflower';
-
能够显示所有部门和教师数量的语句。
sql">SELECT dept.name, COUNT(teacher.name) FROM teacher RIGHT JOIN dept ON dept.id = teacher.dept GROUP BY dept.name;
-
使用语句SELECT name, dept, COALESCE(dept, 0) AS result FROM teacher得到的结果
sql">display 0 in result column for all teachers without department;
-
下列语句得到的结果:
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
-
下列语句得出的结果:
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