1、 挑选出courses表中没有被student中CID2学习的课程的课程名称：
查看courses表中内容：
mysql> SELECT * FROM courses;
+-----+-------------------+-----+
| CID | Cname | TID |
+-----+-------------------+-----+
| 1 | Hamagong | 2 |
| 2 | TaiJiquan | 3 |
| 3 | Yiyangzhi | 6 |
| 4 | Jinshejianfa | 1 |
| 5 | Qianzhuwandushou | 4 |
| 6 | Qishangquan | 5 |
| 7 | Qiankundanuoyi | 7 |
| 8 | Wanliduxing | 8 |
| 9 | Pixiejianfa | 3 |
| 10 | Jiuyinbaiguzhua | 7 |
+-----+-------------------+-----+
查看教师表内容：
mysql> SELECT * FROM tutors;
+-----+--------------+--------+------+
| TID | Tname | Gender | Age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfeng | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
+-----+--------------+--------+------+
查看student表中内容：
mysql> SELECT * FROM student;
+------+--------------+------+--------+------+------+------+---------------------+
| SID | Name | Age | Gender | CID1 | CID2 | TID | CreatTime |
+------+--------------+------+--------+------+------+------+---------------------+
| 1 | Guojing | 19 | M | 2 | 7 | 3 | 2012-04-06 10:00:00 |
| 2 | Yangguo | 1 | M | 2 | 3 | 1 | 2013-04-06 10:00:00 |
| 3 | Dingdian | 25 | M | 2 | 3 | 1 | 2012-04-04 10:00:00 |
| 4 | Hufei | 31 | M | 8 | 10 | 5 | 2012-04-06 10:00:00 |
| 5 | Huangrong | 16 | F | 5 | 9 | 9 | 2012-04-06 10:00:00 |
| 6 | YueLingshang | 18 | F | 8 | 4 | NULL | 2014-04-06 10:00:00 |
| 7 | Zhangwuji | 20 | M | 1 | 7 | NULL | 2012-04-06 10:00:00 |
| 8 | Xuzhu | 26 | M | 2 | 4 | NULL | 2012-04-06 10:00:00 |
| 9 | LingHuchong | 22 | M | 11 | NULL | NULL | 2012-04-06 10:00:00 |
| 10 | Yilin | 19 | F | 18 | NULL | NULL | 2012-04-06 10:00:00 |
| 3907 | stu2 | 23 | F | 4 | 1 | 6 | 2012-04-06 10:00:00 |
| 3908 | stu3 | 23 | F | 4 | 1 | 6 | 2012-04-06 10:00:00 |
+------+--------------+------+--------+------+------+------+---------------------+
mysql> SELECT Cname FROM courses WHERE CID IN (SELECT DISTINCT CID2 FROM student WHERE CID2 IS NOT NULL);
+-----------------+
| Cname |
+-----------------+
| Hamagong |
| Yiyangzhi |
| Jinshejianfa |
| Qiankundaluoyi |
| Pixiejianfa |
| Jiuyinbaguzhang |
+-----------------+
2、挑选没有教授任何课程的老师：
mysql> SELECT Tname FROM tutors WHERE TID NOT IN (SELECT DISTINCT TID FROM courses);
+-------------+
| Tname |
+-------------+
| Ningzhongze |
+-------------+
3、找出students表中CID1有两个或两个以上同学学习了同一门课程的课程名称
mysql> select CID1,COUNT(CID1) FROM students group by CID1 HAVING COUNT(CID1)>1;
+------+-------------+
| CID1 | COUNT(CID1) |
+------+-------------+
| 2 | 3 |
| 8 | 2 |
+------+-------------+
mysql> SELECT Cname FROM courses WHERE CID IN (SELECT COUNT(CID1) FROM students group by CID1 HAVING COUNT(CID1)
>1);+-----------+
| Cname |
+-----------+
| TaiJiquan |
| Yiyangzhi |
+-----------+
4、 显示每一个老师及其所教授的课程：没有教授的课程保持为NULL:
mysql> SELECT tutors.Tname,courses.Cname FROM tutors,courses WHERE tutors.TID=courses.TID;
+--------------+-------------------+
| Tname | Cname |
+--------------+-------------------+
| HuangYaoshi | Hamagong |
| Miejueshitai | TaiJiquan |
| YuCanghai | Yiyangzhi |
| HongQigong | Jinshejianfa |
| OuYangfeng | Qianzhuwandushou |
| YiDeng | Qishangquan |
| Jinlunfawang | Qiankundanuoyi |
| HuYidao | Wanliduxing |
| Miejueshitai | Pixiejianfa |
| Jinlunfawang | Jiuyinbaiguzhua |
+--------------+-------------------+
5、显示每一个课程及其相关的老师，没有老师教授的课程及其老师显示为NULL
mysql> SELECT courses.Cname,tutors.Tname FROM courses LEFT JOIN tutors ON courses.TID=tutors.TID;
+-------------------+--------------+
| Cname | Tname |
+-------------------+--------------+
| Hamagong | HuangYaoshi |
| TaiJiquan | Miejueshitai |
| Yiyangzhi | YuCanghai |
| Jinshejianfa | HongQigong |
| Qianzhuwandushou | OuYangfeng |
| Qishangquan | YiDeng |
| Qiankundanuoyi | Jinlunfawang |
| Wanliduxing | HuYidao |
| Pixiejianfa | Miejueshitai |
| Jiuyinbaiguzhua | Jinlunfawang |
+-------------------+--------------+
6、 显示每位同学CID1课程及其教授了相关课程的老师名称：
mysql> SELECT courses.Cname,tutors.Tname FROM courses RIGHT JOIN tutors ON courses.TID=tutors.TID;
+-------------------+--------------+
| Cname | Tname |
+-------------------+--------------+
| Jinshejianfa | HongQigong |
| Hamagong | HuangYaoshi |
| TaiJiquan | Miejueshitai |
| Pixiejianfa | Miejueshitai |
| Qianzhuwandushou | OuYangfeng |
| Qishangquan | YiDeng |
| Yiyangzhi | YuCanghai |
| Qiankundanuoyi | Jinlunfawang |
| Jiuyinbaiguzhua | Jinlunfawang |
| Wanliduxing | HuYidao |
| NULL | NingZhongze |
+-------------------+--------------+