mysql数据库 二十一练习题 及答案 (mysql练习题)

数据库二十一练习题

create table class
(
classid int primary key,
classname varchar(20)
)
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’);
create table student
(
studentid varchar(20) primary key,
studentname varchar(20),
studentage int,
studentsex char(10),
studentaddress varchar(50),
classid int references class(classid)
)
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);
create table computer
(
studentid varchar(20) references student(studentid),
score float
)
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 s 
left join computer c 
on s.studentid=c.studentid 

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

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

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

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

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

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

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

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

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

select student.studentid 编号,studentname 姓名,score 成绩,classname 班级名称
from student,class,computer
where 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,student
where student.studentid = computer.studentid and student.classid = class.classid
group by classname

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

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

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

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

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

select *
from student
where student.classid > 3

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

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

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

select student.*
from student
left join computer
on student.studentid = computer.studentid
right join class
on student.classid = class.classid
where class.classid > 3

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

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

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

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

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

–16查询出每个班级的学生的平均分（查询的结果中包含平均分和班级名称）

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

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

update student,computer
set score = null
where studentname like "%c%" and student.studentid = computer.studentid

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

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

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

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

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

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

select student.studentid 编号,studentname 姓名,score 计算机成绩,classname 班级名称
from student,computer,class
where 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,computer
where student.studentid = computer.studentid and student.classid = class.classid
group by 班级名称
having 平均分 > 70
order by 平均分 desc