题目:新建一个数据库ClassManager,包括四个表:学生表(Student)、课程表(Course)、成绩表(Score)以及教师信息表(Teacher)。四个表的结构分别如表1-1的表(一)~表(四)所示,数据如表1-2的表(一)~表(四)所示。用SQL语句创建四个表并完成相关题目。
一.表1-1数据库的表结构
表(一)Student (学生表)
属性名
数据类型
可否为空
含 义
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-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
助教
电子工程系
三.完成以下操作
0.根据上述题目和表格,创建相应数据库,创建相应表结构,并插入给定数据
create databaseClassManager;useClassManager1;create tablestudent (
snovarchar(20) not null comment '学号',
snamevarchar(20) not null comment '学生姓名',
ssexvarchar(20) not null comment '学生性别',
sbirthdaydatetime comment '学生出生年月',
classvarchar(20) comment '学生所在班级');create tablecourse (
cnovarchar(20) not null comment '课程号',
cnamevarchar(20) not null comment '课程名称',
tnovarchar(20) not null comment '教工编号');create tablescore (
snovarchar(20) not null comment '学号',
cnovarchar(20) not null comment '课程号',
degreedecimal(4,1) comment '成绩');create tableteacher (
tnovarchar(20) not null comment '教工编号',
tnamevarchar(20) not null comment '教工姓名',
tsexvarchar(20) not null comment '教工性别',
tbirthdaydatetime comment '教工出生年月',
profvarchar(20) comment '职称',
departvarchar(20) not null comment '教工所在部门');insert into student (sno, sname, ssex, sbirthday, class) values ('108', '曾华','男','1977-09-01','95033');insert into student (sno, sname, ssex, sbirthday, class) values ('105', '匡明','男','1975-10-02','95031');insert into student (sno, sname, ssex, sbirthday, class) values ('107', '王丽','女','1976-01-23','95033');insert into student (sno, sname, ssex, sbirthday, class) values ('101', '李军','男','1976-02-20','95033');insert into student (sno, sname, ssex, sbirthday, class) values ('109', '王芳','女','1975-02-10','95031');insert into student (sno, sname, ssex, sbirthday, class) values ('103', '陆君','男','1974-06-03','95031');insert into course (cno, cname, tno) values ('3-105', '计算机导论', '825');insert into course (cno, cname, tno) values ('3-245', '操作系统', '804');insert into course (cno, cname, tno) values ('6-166', '数字电路', '856');insert into course (cno, cname, tno) values ('9-888', '高等数学', '831');insert into score (sno, cno, degree) values ('103', '3-245', '86');insert into score (sno, cno, degree) values ('105', '3-245', '75');insert into score (sno, cno, degree) values ('109', '3-245', '68');insert into score (sno, cno, degree) values ('103', '3-105', '92');insert into score (sno, cno, degree) values ('105', '3-105', '88');insert into score (sno, cno, degree) values ('109', '3-105', '76');insert into score (sno, cno, degree) values ('101', '3-105', '64');insert into score (sno, cno, degree) values ('107', '3-105', '91');insert into score (sno, cno, degree) values ('108', '3-105', '78');insert into score (sno, cno, degree) values ('101', '6-166', '85');insert into score (sno, cno, degree) values ('107', '6-166', '79');insert into score (sno, cno, degree) values ('108', '6-166', '81');insert into teacher (tno, tname, tsex, tbirthday, prof, depart) values ('804', '李诚','男','1958-12-02','副教授','计算机系');insert into teacher (tno, tname, tsex, tbirthday, prof, depart) values ('856', '张旭','男','1969-03-12','讲师','电子工程系');insert into teacher (tno, tname, tsex, tbirthday, prof, depart) values ('825', '王萍','女','1972-05-05','助教','计算机系');insert into teacher (tno, tname, tsex, tbirthday, prof, depart) values ('831', '刘冰','女','1977-08-14','助教','电子工程系');
1. 查询Student表中的所有记录的Sname、Ssex和Class列。
select sname, ssex, class from student;
2. 查询教师所有的单位即不重复的Depart列。
select distinct depart from teacher;
3. 查询Student表的所有记录。
select * from student;
4. 查询Score表中成绩在60到80之间的所有记录。
select * from score where degree between 60 and 80;
5. 查询Score表中成绩为85,86或88的记录。
select * from score where degree in (85, 86, 88);
6. 查询Student表中“95031”班或性别为“女”的同学记录。
select * from student where class = '95031' or ssex = '女';
7. 以Class降序查询Student表的所有记录。
select * from student order by class desc;
8. 以Cno升序、Degree降序查询Score表的所有记录。
select * from score order by cno asc, degree desc;
9. 查询“95031”班的学生人数。
select count(*) from student where class = '95031';
10.查询Score表中的最高分的学生学号和课程号。
select * from score sc where degree = (select max(degree) as max from score where cno = sc.cno group by cno);
11.查询每门课的平均成绩。
select cno, avg(degree) as avg from score group by cno;
12.查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select cno, avg(degree) as avg from score where cno like '3%' group by cno having count(*) >= 5;
13.查询分数大于70,小于90的Sno列。
select sno from score where degree > 70 and degree < 90;
14.查询所有学生的Sname、Cno和Degree列。
select sname, cno, degree from student s left join score sc on sc.sno = s.sno;
15.查询所有学生的Sno、Cname和Degree列。
select s.sno, c.cname, sc.degree from student s left join score sc on sc.sno = s.sno left join course c on c.cno = sc.cno;
16.查询所有学生的Sname、Cname和Degree列。
select s.sname, c.cname, sc.degree from student s left join score sc on sc.sno = s.sno left join course c on c.cno = sc.cno;
17.查询“95033”班学生的平均分。
select cno , avg(degree) from score sc where sc.sno in (select sno from student where class = '95033') group by cno;
18.查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select * from score where sno = '109' and cno = '3-105';
19.查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select * from score where cno = '3-105' and degree > (select degree from score where sno = '109'and cno = '3-105'); --此处理解为只看‘3-105’课程成绩
select * from score where degree > (select degree from score where sno = '109'and cno = '3-105'); --此处为各科成绩都算。
20.查询和学号为108、101的同学同年出生的所有学生的Sno、Sname和Sbirthday列。(使用year()方法对生日字段求年份)
select sno,sname,sbirthday from student where year(sbirthday) in (select year(sbirthday) from student where sno in ('108', '101'));
21.查询“张旭“教师任课的学生成绩。
select sc.*from score sc inner join course c on c.cno = sc.cno inner join teacher t on t.tno = c.tno where t.tname = '张旭'; --合并多个表然后查询
select * from score where cno in(select cno from course where tno in(select tno from teacher where tname ='张旭')); --嵌套查询
22.查询选修某课程的同学人数多于5人的教师姓名。
select tname from teacher where tno = (select tno from course where cno = (select cno from score group by cno having count(*) > 5));
23.查询95033班和95031班全体学生的记录。
select * from student where class in ('95033', '95031');
24.查询存在有85分以上成绩的课程Cno。
select distinct cno from score where degree > 85;
25.查询出“计算机系“教师所教课程的成绩表。
select * from score where cno in (select cno from course where tno in (select tno from teacher where depart = '计算机系'));
26.查询“计算 机系”与“电子工程系“不同职称的教师的Tname和Prof。
select tname, prof from teacher where depart = '计算机系' and prof not in (select prof from teacher where depart = '电子工程系')union select tname, prof from teacher where depart = '电子工程系' and prof not in (select prof from teacher where depart = '计算机系');
27.查询所有教师和同学的name、sex和birthday。
select sname as `name`, ssex as `sex`, sbirthday as `birthday` from student union select tname, tsex,tbirthday from teacher;
28.查询所有“女”教师和“女”同学的name、sex和birthday。
select sname as `name` , ssex as `sex`, sbirthday as `birthday`from student where ssex = '女' union select tname, tsex , tbirthday from teacher where tsex = '女';
29.查询成绩比该课程平均成绩低的同学的成绩表。
select * from score sc where degree < (select avg(degree) as avg from score where cno = sc.cno group by cno);
30.查询所有任课教师的Tname和Depart。
select tname, depart from teacher where tno in (select tno from course);
31.查询所有未讲课的教师的Tname和Depart。
select tname, depart from teacher where tno in (select tno from course where cno not in (select cno from score)); --此处理解为没有成绩的课程为未讲课的课程。
32.查询至少有2名男生的班号。
select class from student where ssex = '男' group by class having count(*) >= 2;
33.查询Student表中不姓“王”的同学记录。
select * from student where sname not like '王%';
34.查询Student表中每个学生的姓名和年龄(使用year()方法对日期字段)。
select sname, (year(now()) - year(sbirthday)) as nian_ling from student;
35.查询Student表中最大和最小的Sbirthday日期值。
select max(sbirthday) as max,min(sbirthday) as min from student;
36.以班号和年龄从大到小的顺序查询Student表中的全部记录。
select * from student order by class desc, sbirthday asc;
37.查询“男”教师及其所上的课程。
select t.*, c.cno, c.cname from teacher t inner join course c on c.tno = t.tno where t.tsex = '男';
38.查询最高分同学的Sno、Cno和Degree列。
select sno, cno, degree from score sc where degree = (select max(degree) as max from score where cno = sc.cno group by cno);
39.查询和“李军”同性别的所有同学的Sname。
select sname from student where ssex in (select ssex from student where sname = '李军');
40.查询和“李军”同性别并同班的同学Sname。
select sname from student where ssex in (select ssex from student where sname = '李军') and class in (select class from student where sname = '李军');
41.查询所有选修“计算机导论”课程的“男”同学的成绩表。
select * from score where cno in (select cno from course where cname = '计算机导论')and sno in (select sno from student where ssex = '男');