本文共 5415 字，大约阅读时间需要 18 分钟。

数据库练习题解答

数据库表结构

类表

create table class (    classid int primary key,    classname varchar(20))

学生表

create table student (    studentid varchar(20) primary key,    studentname varchar(20),    studentage int,    studentsex char(10),    studentaddress varchar(50),    classid int references class(classid))

计算机表

create table computer (    studentid varchar(20) references student(studentid),    score float)

数据插入

类表插入

insert into class values(1, 'G1T01');insert into class values(2, 'G1T02');insert into class values(3, 'G1T03');insert into class values(4, 'G1T04');insert into class values(5, 'G1T05');insert into class values(6, 'G1T06');insert into class values(7, 'G1T07');

学生表插入

insert into student values('2010001', 'Jack', 21, '男', '湖北襄樊', 1);insert into student values('2010002', 'Scott', 22, '男', '湖北武汉', 2);insert into student values('2010003', 'Lucy', 23, '女', '湖北武汉', 3);insert into student values('2010004', 'Alan', 19, '女', '湖北襄樊', 4);insert into student values('2010005', 'Bill', 20, '男', '湖北襄樊', 5);insert into student values('2010006', 'Bob', 21, '男', '湖北宜昌', 6);insert into student values('2010007', 'Colin', 22, '女', '湖北襄樊', 6);insert into student values('2010008', 'Fred', 19, '男', '湖北宜昌', 5);insert into student values('2010009', 'Hunk', 19, '男', '湖北武汉', 4);insert into student values('2010010', 'Jim', 18, '男', '湖北襄樊', 3);

计算机表插入

insert into computer values('2010001', 90);insert into computer values('2010002', 80);insert into computer values('2010003', 70);insert into computer values('2010004', 60);insert into computer values('2010005', 75);insert into computer values('2010006', 85);

查询

–1 查询学生编号、姓名、计算机成绩

select s.studentid 学生编号, s.studentname 姓名, c.score 计算机成绩 from student sleft join computer c on s.studentid = c.studentid

–2 查询参加过考试的学生信息

select student.* from student, computerwhere student.studentid = computer.studentid

–3 查询学生编号、姓名、所在班级名称、计算机成绩

select student.studentid 编号, studentname 姓名, classname 班级名称, score 计算机成绩from student left join computer on student.studentid = computer.studentidright join class on student.classid = class.classid

–4 查询年龄大于19岁的学生编号、姓名、计算机成绩

select student.studentid 编号, studentname 姓名, studentage 年龄, score 计算机成绩from student left join computer on student.studentid = computer.studentidwhere studentage > 19

–5 查询姓名中包含有c的学生编号、姓名、计算机成绩

select student.studentid 编号, studentname 姓名, score 计算机成绩from student left join computer on student.studentid = computer.studentidwhere studentname like "%c%"

–6 查询计算机成绩大于80分的学生编号、姓名、班级名称

select student.studentid 编号, studentname 姓名, score 成绩, classname 班级名称from student, class, computerwhere computer.score > 80 and student.classid = class.classid and student.studentid = computer.studentid

–7 查询所有学生的信息和计算机成绩信息

select student.*, score from student left join computer on student.studentid = computer.studentid

–8 查询每个班的学生计算机成绩的平均分、最高分、最低分

select classname 班级名称, avg(score) 平均分, max(score) 最高分, min(score) 最低分from class, computer, studentwhere student.studentid = computer.studentid and student.classid = class.classidgroup by classname

–9 查询班级计算机平均分大于80的班级名称、平均分信息，并按平均分降序显示

select classname 班级名称, avg(score) 平均分from class, computer, studentwhere student.studentid = computer.studentid and student.classid = class.classidgroup by classnamehaving 平均分 > 80order by 平均分 desc

–10 查询和Jim住在同一个地方的学生的基本信息

select * from studentwhere studentaddress = (select studentaddress from student where studentname = "Jim")

–11 查询班级编号大于3的学生基本信息

select * from studentwhere student.classid > 3

–12 查询班级编号大于3的学生计算机平均分信息

select avg(score) 平均分from class, computer, studentwhere class.classid > 3 and student.classid = class.classid and student.studentid = computer.studentid

–13 查询班级编号大于3的男生的学生信息

select student.* from studentleft join computer on student.studentid = computer.studentidright join class on student.classid = class.classidwhere class.classid > 3

–14 查询男、女生的计算机平均成绩、最高分、最低分

select studentsex 性别, avg(score) 平均分, max(score) 最大值, min(score) 最小值from student, computerwhere student.studentid = computer.studentidgroup by 性别

–15 将参加过考试的学生的年龄更改为20

update student, computerset studentage = 20where student.studentid = computer.studentid

–16 查询每个班级的学生计算机平均分

select classname 班级名称, avg(score) 平均分from class, computer, studentwhere student.studentid = computer.studentid and student.classid = class.classidgroup by 班级名称

–17 删除姓名包含“c”字符的学生计算机成绩

update student, computerset score = nullwhere studentname like "%c%" and student.studentid = computer.studentid

–18 查询G1T07班学生的编号、姓名、班级名称、计算机成绩

select student.studentid 编号, studentname 姓名, classname 班级名称, score 计算机成绩from student, computer, classwhere classname = "G1T07" and student.studentid = computer.studentid and student.classid = class.classid

–19 查询年龄在20-25之间的学生的编号、姓名、年龄、计算机成绩

select student.studentid 编号, studentname 姓名, studentage 年龄, score 计算机成绩from student, computer, classwhere studentage >= 20 and studentage <= 25and student.studentid = computer.studentid and student.classid = class.classid

–20 查询成绩最高的学生的编号、姓名、计算机成绩、所在班级名称

select student.studentid 编号, studentname 姓名, score 计算机成绩, classname 班级名称from student, computer, classwhere score = (select max(score) from computer)and student.studentid = computer.studentid and student.classid = class.classid

–21 查询统计出每个班的平均分、显示平均分超过70分的信息、并按照降序显示信息

select classname 班级名称, avg(score) 平均分from student, class, computerwhere student.studentid = computer.studentid and student.classid = class.classidgroup by 班级名称having 平均分 > 70order by 平均分 desc