SQL数据结构练习题

一、            设有一数据库，包括四个表：学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）。四个表的结构分别如表1-1的表（一）~表（四）所示，数据如表1-2的表（一）~表（四）所示。用SQL语句创建四个表并完成相关题目。

                 表1-1数据库的表结构 

表（一）Student (学生use)                         

属性名	数据类型	可否为空	含 义
Sno	varchar (20)	否	学号（主码）
Sname	varchar (20)	否	学生姓名
Ssex	varchar (20)	否	学生性别
Sbirthday	Datetime	可	学生出生年月
Class	varchar (20)	可	学生所在班级

 

表（二）Course（课程表）

属性名	数据类型	可否为空	含 义
Cno	varchar (20)	否	课程号（主码）
Cname	varchar (20)	否	课程名称
Tno	varchar (20)	否	教工编号

表（三）Score(成绩表)

属性名	数据类型	可否为空	含 义
Id	Int		主键 自增
Sno	varchar (20)	否	学号
Cno	varchar (20)	否	课程号
Degree	Decimal(4,1)	可	成绩
//主码：Sno+ Cno

表（四）Teacher(教师表)

属性名	数据类型	可否为空	含 义
Tno	varchar (20)	否	教工编号（主码）
Tname	varchar (20)	否	教工姓名
Tsex	varchar (20)	否	教工性别
Tbirthday	datetime	可	教工出生年月
Prof	varchar (20)	可	职称
Depart	varchar (20)	否	教工所在部门

表1-2数据库中的数据

表（一）Student

Sno	Sname	Ssex	Sbirthday	class
108	曾华	男	1977-09-01	95033
105	匡明	男	1975-10-02	95031
107	王丽	女	1976-01-23	95033
101	李军	男	1976-02-20	95033
109	王芳	女	1975-02-10	95031
103	陆君	男	1974-06-03	95031

 

表（二）Course

Cno	Cname	Tno
3-105	计算机导论	825
3-245	操作系统	804
6-166	数字电路	856
9-888	高等数学	831

表（三）Score

 

Sno	Cno	Degree
103	3-245	86
105	3-245	75
109	3-245	68
103	3-105	92
105	3-105	88
109	3-105	76
101	3-105	64
107	3-105	91
108	3-105	78
101	6-166	85
107	6-166	79
108	6-166	81

表（四）Teacher

Tno	Tname	Tsex	Tbirthday	Prof	Depart
804	李诚	男	1958-12-02	副教授	计算机系
856	张旭	男	1969-03-12	讲师	电子工程系
825	王萍	女	1972-05-05	助教	计算机系
831	刘冰	女	1977-08-14	助教	电子工程系

 

1、 查询Student表中的所有记录的Sname、Ssex和Class列。

2、 查询教师所有的单位即不重复的Depart列。  

3、 查询Student表的所有记录。

4、 查询Score表中成绩在60到80之间的所有记录。

5、 查询Score表中成绩为85，86或88的记录。

6、 查询Student表中“95031”班或性别为“女”的同学记录。

7、 以Class降序查询Student表的所有记录。

8、 以Cno升序、Degree降序查询Score表的所有记录。

9、 查询“95031”班的学生人数。

10、 查询Score表中的最高分的学生学号和课程号。

11、 查询每门课的平均成绩。

12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

13、查询分数大于70，小于90的Sno列。

14、查询所有学生的Sname、Cno和Degree列。

15、查询所有学生的Sno、Cname和Degree列。

16、查询所有学生的Sname、Cname和Degree列。

17、查询“95033”班学生的平均分。

18、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

19、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

20、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

21、查询“张旭“教师任课的学生成绩。

22、查询考计算机导论的学生成绩

23、查询李诚老师教的课程名称

24、教高等数学的老师是哪个系的

 

25、查询选修某课程的同学人数多于5人的教师姓名。

 

26、查询95033班和95031班全体学生的记录。

 

27、查询成绩表中存在有85分以上成绩的课程Cno.

 

28、查询出“计算机系“教师所教课程的成绩表。

 

29、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的 Cno、Sno和Degree.

 

30、查询所有教师和同学的name、sex和birthday.

 

31、查询所有“女”教师和“女”同学的name、sex和birthday.

 

32、查询所有成绩比3-105课程平均成绩低的同学的成绩表.

 

33、查询所有任课教师的Tname和Depart.

 

34、查询所有未讲课的教师的Tname和Depart. 

 

35、查询至少有2名男生的班号。

 

36、查询Student表中不姓“王”的同学记录。

 

37、查询Student表中每个学生的姓名和年龄。

 

38、查询Student表中最大和最小的Sbirthday日期值。

 

39、以班号和年龄从大到小的顺序查询Student表中的全部记录。

 

40、查询“男”教师及其所上的课程。

 

41、查询最高分同学的Sno、Cno和Degree列。

 

42、查询和“李军”同性别的所有同学的Sname.

43、查询和“李军”同性别并同班的同学Sname.

 

44、查询所有选修“计算机导论”课程的“男”同学的成绩表。

 

1.mysql> select Sname ,Ssex ,Class from Student;
+-------+------+-------+
| Sname | Ssex | Class |
+-------+------+-------+
| 曾华 | 男 | 95033 |
| 匡明 | 男 | 95031 |
| 王丽 | 女 | 95033 |
| 李军 | 男 | 95033 |
| 王芳 | 女 | 95031 |
| 陆君 | 男 | 95031 |
+-------+------+-------+
6 rows in set
2.
select distinct Depart from teacher;
+------------+
| Depart |
+------------+
| 计算机系 |
| 电子工程系 |
+------------+
2 rows in set
3.mysql> select Sname ,Ssex ,Class from Student;
+-------+------+-------+
| Sname | Ssex | Class |
+-------+------+-------+
| 曾华 | 男 | 95033 |
| 匡明 | 男 | 95031 |
| 王丽 | 女 | 95033 |
| 李军 | 男 | 95033 |
| 王芳 | 女 | 95031 |
| 陆君 | 男 | 95031 |
+-------+------+-------+
6 rows in set
4.
mysql> select * from score where Degree>60 and Degree<80;
+----+-----+-------+--------+
| Id | Sno | Cno | Degree |
+----+-----+-------+--------+
| 2 | 105 | 3-245 | 75 |
| 3 | 109 | 3-245 | 68 |
| 6 | 109 | 3-105 | 76 |
| 7 | 101 | 3-10 | 64 |
| 9 | 108 | 3-105 | 78 |
| 11 | 107 | 6-166 | 79 |
+----+-----+-------+--------+
6 rows in set
5
mysql> select * from score where Degree in (85,86,88);
+----+-----+-------+--------+
| Id | Sno | Cno | Degree |
+----+-----+-------+--------+
| 1 | 103 | 3-245 | 86 |
| 5 | 105 | 3-105 | 88 |
| 10 | 101 | 6-166 | 85 |
+----+-----+-------+--------+
3 rows in set
6
mysql> select * from Student where Class='95031' or Ssex='女';
+-----+-------+------+---------------------+-------+
| Son | Sname | Ssex | Sbirthday | Class |
+-----+-------+------+---------------------+-------+
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+-------+------+---------------------+-------+
4 rows in set
7
mysql> select * from student order by Class desc;
+-----+-------+------+---------------------+-------+
| Son | Sname | Ssex | Sbirthday | Class |
+-----+-------+------+---------------------+-------+
| 108 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 107 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 101 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 109 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 103 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+-------+------+---------------------+-------+
6 rows in set
8
mysql> select * from score order by Cno , degree desc;
+----+-----+-------+--------+
| Id | Sno | Cno | Degree |
+----+-----+-------+--------+
| 4 | 103 | 3-105 | 92 |
| 8 | 107 | 3-105 | 91 |
| 5 | 105 | 3-105 | 88 |
| 9 | 108 | 3-105 | 78 |
| 6 | 109 | 3-105 | 76 |
| 7 | 101 | 3-105 | 64 |
| 1 | 103 | 3-245 | 86 |
| 2 | 105 | 3-245 | 75 |
| 3 | 109 | 3-245 | 68 |
| 10 | 101 | 6-166 | 85 |
| 12 | 108 | 6-166 | 81 |
| 11 | 107 | 6-166 | 79 |
+----+-----+-------+--------+
12 rows in set
9
mysql> select count(*) from student where Class='95031';
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set
10
mysql> select Sno,Cno from score where Degree=(select max(Degree) from score ) ;
+-----+-------+
| Sno | Cno |
+-----+-------+
| 103 | 3-105 |
+-----+-------+
1 row in set
11
mysql> select avg(Degree) from score group by Cno;
+-------------+
| avg(Degree) |
+-------------+
| 81.5 |
| 76.33333 |
| 81.66667 |
+-------------+
3 rows in set
12
select avg(Degree) from score where Cno = (select Cno from score group by Cno having count(Cno like '3%')>5);
+-------------+
| avg(Degree) | groud 查出来是个集合 avg不能跟集合同时显示。
+-------------+
| 81.5 |
+-------------+
1 row in set
13
mysql> select Sno from Score where Degree>70 and Degree<90;
+-----+
| Sno |
+-----+
| 103 |
| 105 |
| 105 |
| 109 |
| 108 |
| 101 |
| 107 |
| 108 |
+-----+
8 rows in set
14
mysql> select Sname,Cno,Degree from student join score on student.Sno =score.Sno;
+-------+-------+--------+
| Sname | Cno | Degree |
+-------+-------+--------+
| 陆君 | 3-245 | 86 |
| 匡明 | 3-245 | 75 |
| 王芳 | 3-245 | 68 |
| 陆君 | 3-105 | 92 |
| 匡明 | 3-105 | 88 |
| 王芳 | 3-105 | 76 |
| 李军 | 3-105 | 64 |
| 王丽 | 3-105 | 91 |
| 曾华 | 3-105 | 78 |
| 李军 | 6-166 | 85 |
| 王丽 | 6-166 | 79 |
| 曾华 | 6-166 | 81 |
+-------+-------+--------+
12 rows in set
15
mysql> select Sno,Cname,Degree from course join score on course.Cno =score.Cno;
+-----+------------+--------+
| Sno | Cname | Degree |
+-----+------------+--------+
| 103 | 操作系统 | 86 |
| 105 | 操作系统 | 75 |
| 109 | 操作系统 | 68 |
| 103 | 计算机导论 | 92 |
| 105 | 计算机导论 | 88 |
| 109 | 计算机导论 | 76 |
| 101 | 计算机导论 | 64 |
| 107 | 计算机导论 | 91 |
| 108 | 计算机导论 | 78 |
| 101 | 数字电路 | 85 |
| 107 | 数字电路 | 79 |
| 108 | 数字电路 | 81 |
+-----+------------+--------+
12 rows in set
16
mysql> select Sname,Cname,Degree from student join score join course on student.Sno =score.Sno and score.Cno=course.Cno;
+-------+------------+--------+
| Sname | Cname | Degree |
+-------+------------+--------+
| 陆君 | 操作系统 | 86 |
| 匡明 | 操作系统 | 75 |
| 王芳 | 操作系统 | 68 |
| 陆君 | 计算机导论 | 92 |
| 匡明 | 计算机导论 | 88 |
| 王芳 | 计算机导论 | 76 |
| 李军 | 计算机导论 | 64 |
| 王丽 | 计算机导论 | 91 |
| 曾华 | 计算机导论 | 78 |
| 李军 | 数字电路 | 85 |
| 王丽 | 数字电路 | 79 |
| 曾华 | 数字电路 | 81 |
+-------+------------+--------+
12 rows in set
17
mysql> select avg(Degree) from score as a join student as b on a.Sno=b.Sno where b.Class='95033' ;
+-------------+
| avg(Degree) |
+-------------+
| 79.66667 |
+-------------+
1 row in set
18
mysql> select * from score where Cno='3-105' and Degree>(select Degree from score where Sno=109 and Cno='3-105');
+----+-----+-------+--------+
| Id | Sno | Cno | Degree |
+----+-----+-------+--------+
| 4 | 103 | 3-105 | 92 |
| 5 | 105 | 3-105 | 88 |
| 8 | 107 | 3-105 | 91 |
| 9 | 108 | 3-105 | 78 |
+----+-----+-------+--------+
4 rows in set
19
mysql> select * from score where Cno='3-105' and Degree>(select Degree from score where Sno=109 and Cno='3-105');
+----+-----+-------+--------+
| Id | Sno | Cno | Degree |
+----+-----+-------+--------+
| 4 | 103 | 3-105 | 92 |
| 5 | 105 | 3-105 | 88 |
| 8 | 107 | 3-105 | 91 |
| 9 | 108 | 3-105 | 78 |
+----+-----+-------+--------+
4 rows in set
20
select Sno,Sname,Sbirthday from student where Year(Sbirthday)=(select Year(Sbirthday) from student where Sno=108);
+-----+-------+------------+
| Sno | Sname | Sbirthday |
+-----+-------+------------+
| 108 | 曾华 | 1977-09-01 |
+-----+-------+------------+
1 row in set

21
mysql> select Degree from score as a join course as b join teacher as c on b.Ton=c.Ton and a.Cno=b.Cno where Tname='张旭';
+--------+
| Degree |
+--------+
| 85 |
| 79 |
| 81 |
+--------+
3 rows in set
22

mysql> select Degree from score as a join course as b on a.Cno=b.Cno where Cname='计算机导论';
+--------+
| Degree |
+--------+
| 92 |
| 88 |
| 76 |
| 64 |
| 91 |
| 78 |
+--------+
6 rows in set
23
mysql> select Cname from course as a join teacher as b on a.Ton=b.Ton where Tname='李诚';
+----------+
| Cname |
+----------+
| 操作系统 |
+----------+
1 row in set
24
mysql> select Depart from teacher as a join course as b on a.Ton=b.Ton where Cname='高等数学';
+------------+
| Depart |
+------------+
| 电子工程系 |
+------------+
1 row in set
25
mysql> select distinct Tname from teacher as a join score as b join course as c on a.Ton=c.Ton and b.Cno=c.Cno where
-> b.Cno = (select Cno from score group by Cno having count(Cno)>5);
+-------+
| Tname |
+-------+
| 王萍 |
+-------+
1 row in set
26
mysql> select * from student as a join score as b join course as c on a.Sno=b.Sno and b.Cno=c.Cno where a.Class in(95033,95031);
+-----+-------+------+------------+-------+----+-----+-------+--------+-------+------------+-----+
| Sno | Sname | Ssex | Sbirthday | Class | Id | Sno | Cno | Degree | Cno | Cname | Ton |
+-----+-------+------+------------+-------+----+-----+-------+--------+-------+------------+-----+
| 103 | 陆君 | 男 | 1974-06-03 | 95031 | 1 | 103 | 3-245 | 86 | 3-245 | 操作系统 | 804 |
| 105 | 匡明 | 男 | 1975-10-02 | 95031 | 2 | 105 | 3-245 | 75 | 3-245 | 操作系统 | 804 |
| 109 | 王芳 | 女 | 1975-02-10 | 95031 | 3 | 109 | 3-245 | 68 | 3-245 | 操作系统 | 804 |
| 103 | 陆君 | 男 | 1974-06-03 | 95031 | 4 | 103 | 3-105 | 92 | 3-105 | 计算机导论 | 825 |
| 105 | 匡明 | 男 | 1975-10-02 | 95031 | 5 | 105 | 3-105 | 88 | 3-105 | 计算机导论 | 825 |
| 109 | 王芳 | 女 | 1975-02-10 | 95031 | 6 | 109 | 3-105 | 76 | 3-105 | 计算机导论 | 825 |
| 101 | 李军 | 男 | 1976-02-20 | 95033 | 7 | 101 | 3-105 | 64 | 3-105 | 计算机导论 | 825 |
| 107 | 王丽 | 女 | 1976-01-23 | 95033 | 8 | 107 | 3-105 | 91 | 3-105 | 计算机导论 | 825 |
| 108 | 曾华 | 男 | 1977-09-01 | 95033 | 9 | 108 | 3-105 | 78 | 3-105 | 计算机导论 | 825 |
| 101 | 李军 | 男 | 1976-02-20 | 95033 | 10 | 101 | 6-166 | 85 | 6-166 | 数字电路 | 856 |
| 107 | 王丽 | 女 | 1976-01-23 | 95033 | 11 | 107 | 6-166 | 79 | 6-166 | 数字电路 | 856 |
| 108 | 曾华 | 男 | 1977-09-01 | 95033 | 12 | 108 | 6-166 | 81 | 6-166 | 数字电路 | 856 |
+-----+-------+------+------------+-------+----+-----+-------+--------+-------+------------+-----+
12 rows in set
27
mysql> select distinct Cno from score where Degree>85;
+-------+
| Cno |
+-------+
| 3-245 |
| 3-105 |
+-------+
2 rows in set
28
mysql> select Degree from score as a join teacher as b join course as c on a.Cno=c.Cno and b.Ton=c.Ton where Depart='计算机系';
+--------+
| Degree |
+--------+
| 86 |
| 75 |
| 68 |
| 92 |
| 88 |
| 76 |
| 64 |
| 91 |
| 78 |
+--------+
9 rows in set
29
mysql> select a.Cno,a.Sno,a.Degree from score as a join score as b on a.Sno=b.Sno where a.Cno='3-105' and b.Cno='3-245'
-> and a.Degree>b.Degree;
+-------+-----+--------+
| Cno | Sno | Degree |
+-------+-----+--------+
| 3-105 | 103 | 92 |
| 3-105 | 105 | 88 |
| 3-105 | 109 | 76 |
+-------+-----+--------+
3 rows in set
30
mysql> select Tname as name,Tsex as sex,Tbirthday as birthday from teacher union select Sname ,Ssex ,Sbirthday from student;
+------+-----+------------+
| name | sex | birthday |
+------+-----+------------+
| 李诚 | 男 | 1958-12-02 |
| 张旭 | 男 | 1969-03-12 |
| 王萍 | 女 | 1972-05-05 |
| 刘冰 | 女 | 1977-08-14 |
| 曾华 | 男 | 1977-09-01 |
| 匡明 | 男 | 1975-10-02 |
| 王丽 | 女 | 1976-01-23 |
| 李军 | 男 | 1976-02-20 |
| 王芳 | 女 | 1975-02-10 |
| 陆君 | 男 | 1974-06-03 |
+------+-----+------------+
10 rows in set
31
mysql> select Tname as name,Tsex as sex,Tbirthday as birthday from teacher where Tsex='女' union select Sname as name ,Ssex as sex ,Sbirthday as birthday from student where Ssex='女';
+------+-----+------------+
| name | sex | birthday |
+------+-----+------------+
| 王萍 | 女 | 1972-05-05 |
| 刘冰 | 女 | 1977-08-14 |
| 王丽 | 女 | 1976-01-23 |
| 王芳 | 女 | 1975-02-10 |
+------+-----+------------+
4 rows in set
32

mysql> select * from score where Degree<(select avg(Degree) from score where Cno='3-105');
+----+-----+-------+--------+
| Id | Sno | Cno | Degree |
+----+-----+-------+--------+
| 2 | 105 | 3-245 | 75 |
| 3 | 109 | 3-245 | 68 |
| 6 | 109 | 3-105 | 76 |
| 7 | 101 | 3-105 | 64 |
| 9 | 108 | 3-105 | 78 |
| 11 | 107 | 6-166 | 79 |
| 12 | 108 | 6-166 | 81 |
+----+-----+-------+--------+
7 rows in set
33
mysql> select distinct Tname, Depart from course as a join score as b join student as c inner join teacher as d
-> on a.Cno=b.Cno and a.Ton=d.Ton and b.Sno=c.Sno;
+-------+------------+
| Tname | Depart |
+-------+------------+
| 李诚 | 计算机系 |
| 王萍 | 计算机系 |
| 张旭 | 电子工程系 |
+-------+------------+
3 rows in set
34
select distinct Tname, Depart from course as a join score as b join student as c right join teacher as d
-> on a.Cno=b.Cno and a.Ton=d.Ton and b.Sno=c.Sno where c.Sno is null;
+-------+------------+
| Tname | Depart |
+-------+------------+
| 刘冰 | 电子工程系 |
+-------+------------+
1 row in set

35
mysql> select Class from student group by class having (select count(Ssex) from student where Ssex='男')>=2;
+-------+
| Class |
+-------+
| 95031 |
| 95033 |
+-------+
2 rows in set
36
mysql> select * from student where Sname not like '王%';
+-----+-------+------+------------+-------+
| Sno | Sname | Ssex | Sbirthday | Class |
+-----+-------+------+------------+-------+
| 108 | 曾华 | 男 | 1977-09-01 | 95033 |
| 105 | 匡明 | 男 | 1975-10-02 | 95031 |
| 101 | 李军 | 男 | 1976-02-20 | 95033 |
| 103 | 陆君 | 男 | 1974-06-03 | 95031 |
+-----+-------+------+------------+-------+
4 rows in set
37
mysql> select Sname, (Year(now())- Year(Sbirthday)) as nl from student;
+-------+----+
| Sname | nl |
+-------+----+
| 曾华 | 43 |
| 匡明 | 45 |
| 王丽 | 44 |
| 李军 | 44 |
| 王芳 | 45 |
| 陆君 | 46 |
+-------+----+
6 rows in set

38
mysql> select max(day(Sbirthday)),min(day(Sbirthday))from student;
+---------------------+---------------------+
| max(day(Sbirthday)) | min(day(Sbirthday)) |
+---------------------+---------------------+
| 23 | 1 |
+---------------------+---------------------+
1 row in set
39
mysql> select * from student order by Class desc,Sbirthday;
+-----+-------+------+------------+-------+
| Sno | Sname | Ssex | Sbirthday | Class |
+-----+-------+------+------------+-------+
| 107 | 王丽 | 女 | 1976-01-23 | 95033 |
| 101 | 李军 | 男 | 1976-02-20 | 95033 |
| 108 | 曾华 | 男 | 1977-09-01 | 95033 |
| 103 | 陆君 | 男 | 1974-06-03 | 95031 |
| 109 | 王芳 | 女 | 1975-02-10 | 95031 |
| 105 | 匡明 | 男 | 1975-10-02 | 95031 |
+-----+-------+------+------------+-------+
6 rows in set

40
mysql> select distinct Tname,Cname from course as a join score as b join student as c join teacher as d
-> on a.Cno=b.Cno and a.Ton=d.Ton and b.Sno=c.Sno
-> where Tsex = '男' ;
+-------+----------+
| Tname | Cname |
+-------+----------+
| 李诚 | 操作系统 |
| 张旭 | 数字电路 |
+-------+----------+
2 rows in set
41
mysql> select b.Sno, b.Cno,b.Degree from course as a join score as b join student as c join teacher as d
-> on a.Cno=b.Cno and a.Ton=d.Ton and b.Sno=c.Sno
-> where Degree=(select max(Degree) from score );
+-----+-------+--------+
| Sno | Cno | Degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
+-----+-------+--------+
1 row in set
42
mysql> select Sname from student where Ssex=(select Ssex from student where Sname='李军');
+-------+
| Sname |
+-------+
| 曾华 |
| 匡明 |
| 李军 |
| 陆君 |
+-------+
4 rows in set
43
mysql> select Sname from student where Ssex=(select Ssex from student where Sname='李军')
-> and Class = (select Class from student where Sname='李军');
+-------+
| Sname |
+-------+
| 曾华 |
| 李军 |
+-------+
2 rows in set
44
mysql> select Degree from course as a join score as b join student as c join teacher as d
-> on a.Cno=b.Cno and a.Ton=d.Ton and b.Sno=c.Sno
-> where Cname='计算机导论' and Ssex='男';
+--------+
| Degree |
+--------+
| 92 |
| 88 |
| 64 |
| 78 |
+--------+
4 rows in set