数据库0413作业

一、            设有一数据库，包括四个表：学生表（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='计算机导论')