一、 设有一数据库，包括四个表：学生表（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(成绩表)
|
属性名 |
数据类型 |
可否为空 |
含 义 |
|
Sno |
varchar (20) |
否 |
学号（外码） |
|
Cno |
varchar (20) |
否 |
课程号（外码） |
|
Degree |
Decimal(4,1)
|
可 |
成绩 |
|
|
|||
表（四）Teacher(教师表)
|
属性名 |
数据类型 |
可否为空 |
含 义 |
|
Tno |
varchar (20) |
否 |
教工编号（主码） |
|
Tname |
varchar (20) |
否 |
教工姓名 |
|
Tsex |
varchar (20) |
否 |
教工性别 |
|
Tbirthday |
datetime |
可 |
教工出生年月 |
|
Prof |
varchar (20) |
可 |
职称 |
|
Depart |
varchar (20) |
否 |
教工所在部门 |
1、查询Student表中的所有记录的Sname、Ssex和Class列。
SELECT sname,ssex,class FROM student1
2、 查询教师所有的单位即不重复的Depart列。
SELECT DISTINCT depart FROM teacher1
3、查询Student表的所有记录。
SELECT * FROM student1
4、 查询Score表中成绩在60到80之间的所有记录。
SELECT * FROM score1 WHERE degree>=60 AND degree<=80
5、 查询Score表中成绩为85，86或88的记录。
SELECT * FROM score1 WHERE degree IN (85,86,88)
6、 查询Student表中“95031”班或性别为“女”的同学记录。
SELECT * FROM student1 WHERE class="95031" OR ssex='女'
7、以Class降序查询Student表的所有记录。
SELECT * FROM student1 ORDER BY class DESC
8、以Cno升序、Degree降序查询Score表的所有记录。
SELECT * FROM score1 ORDER BY cno ASC ,degree DESC
9、查询“95031”班的学生人数。
SELECT * FROM student1 WHERE class="95031"
10、查询每门课的平均成绩。
SELECT cno,AVG(degree) FROM score1 GROUP BY cno;
11、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT cno,AVG(degree) FROM score1 WHERE cno LIKE '3%' GROUP BY cno HAVING COUNT(cno)>5;
12、查询分数大于70，小于90的Sno列。
SELECT sno FROM score1 WHERE degree>=70 AND degree<=90;
13、查询所有学生的Sname、Cno和Degree列。
SELECT sname,cno,degree FROM student1 JOIN score1
14、查询所有学生的Sno、Cname和Degree列。
SELECT sno,cname,degree FROM course JOIN score1
15、查询所有学生的Sname、Cname和Degree列。
SELECT sname,cname,degree FROM score1 JOIN student1 ON score1.`sno`=student1.`sno` JOIN course ON course.`cno`=score1.`cno`
16、查询“95033”班学生的平均分。
SELECT AVG(degree) AS '平均分' FROM student1 JOIN score1 ON student1.`sno`=score1.`sno` WHERE class="95033"
17、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
SELECT sno,sname,sbirthfay FROM student1 WHERE YEAR(sbirthfay)=(SELECT YEAR(sbirthfay) FROM student1 WHERE sno='108')
18、查询“张旭“教师任课的学生成绩（姓名）。
SELECT degree FROM score1 WHERE cno IN(SELECT cno FROM course WHERE tno IN(SELECT tno FROM teacher1 WHERE tname='张旭'))
19、查询考计算机导论的学生成绩
SELECT degree FROM score1 WHERE cno IN(SELECT cno FROM course WHERE cname='计算机导论')
20、查询李诚老师教的课程名称
SELECT cname FROM teacher1 JOIN course ON teacher1.`tno`=course.`tno` WHERE tname='李诚'
21、教高等数学的老师是哪个系的
SELECT depart FROM course JOIN teacher1 ON teacher1.`tno`=course.`tno` WHERE cname='高等数学'
22、查询选修某课程的同学人数多于5人的教师姓名。
SELECT tname FROM teacher1 WHERE tno IN(SELECT tno FROM course WHERE cno IN(SELECT cno FROM score1 GROUP BY cno HAVING COUNT(*)>5))
23、查询95033班和95031班全体学生的记录。
SELECT * FROM student1 WHERE class="95033" OR class="95031"
24、查询存在有85分以上成绩的课程Cno.
SELECT DISTINCT(cno) FROM score1 WHERE degree>85
25、查询出“计算机系“教师所教课程的成绩表。
SELECT degree FROM score1 WHERE cno IN(SELECT cno FROM course WHERE tno IN(SELECT tno FROM teacher1 WHERE depart='计算机系'))
26、 查询所有教师和同学的name、sex和birthday.
SELECT sname,ssex,sbirthfay FROM student1
UNION
SELECT tname,tsex,tbirthday FROM teacher1
27、查询所有“女”教师和“女”同学的name、sex和birthday.
SELECT sname,ssex,sbirthfay FROM student1 WHERE ssex='女'
UNION
SELECT tname,tsex,tbirthday FROM teacher1 WHERE tsex='女'
28、 查询所有任课教师的Tname和Depart.
SELECT tname,depart FROM teacher1 WHERE prof='讲师'
29、查询所有未讲课的教师的Tname和Depart.
SELECT tname,depart FROM teacher1 WHERE prof<>'讲师'
30、查询至少有2名男生的班号。
SELECT class FROM student1 GROUP BY ssex HAVING COUNT('男')>2
31、查询Student表中不姓“王”的同学记录。
SELECT * FROM student1 WHERE sname NOT LIKE '王%'
32、查询Student表中每个学生的姓名和年龄。
SELECT sname,sbirthfay FROM student1
33、查询Student表中最大和最小的Sbirthday日期值。
SELECT MAX(sbirthfay),MIN(sbirthfay) FROM student1
34、以班号和年龄从大到小的顺序查询Student表中的全部记录。
SELECT * FROM student1 ORDER BY class DESC,sbirthfay ASC
35、查询“男”教师及其所上的课程。
SELECT DISTINCT(cname),tname FROM teacher1 JOIN course ON teacher1.`tno`=course.`tno` WHERE tsex='男'
36、查询最高分同学的Sno、Cno和Degree列。
SELECT sno,cno,degree FROM score1 WHERE degree IN (SELECT MAX(degree) FROM score1)
37、查询和“李军”同性别的所有同学的Sname.
SELECT sname FROM student1 WHERE ssex=( SELECT ssex FROM student1 WHERE sname='李军')
38、查询和“李军”同性别并同班的同学Sname.
SELECT sname FROM student1 WHERE ssex=( SELECT ssex FROM student1 WHERE sname='李军') AND class=
(SELECT class FROM student1 WHERE sname='李军')
39、查询所有选修“计算机导论”课程的“男”同学的成绩表。
SELECT degree FROM score1 WHERE sno IN (SELECT sno FROM student1 WHERE ssex='男') AND cno IN(SELECT cno FROM course WHERE cname='计算机导论')
