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);

select * from class;
select * from computer;
select * from student;

–1查詢出學生的編號，姓名，計算機成績

select class.classid as 編號,studentname as 姓名,score as 計算機成績 
from student,class,computer
where student.classid = class.classid and student.studentid = computer.studentid

–2查詢參加過考試的學生資訊

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

–3查詢出學生的編號、姓名、所在班級名稱、計算機成績

select student.studentid 編號,studentname 姓名,classname 班級名稱,score 計算機成績
from student,class,computer
where student.studentid = computer.studentid and student.classid = class.classid

–4查詢出年齡大于19歲的學生編號、姓名、計算機成績

select student.studentid 編號,studentname 姓名,studentage 年齡,score 計算機成績
from student,computer
where studentage > 19 and student.studentid = computer.studentid

–5查詢出姓名中包含有c的學生編號、姓名、計算機成績

select student.studentid 編號,studentname 姓名,score 計算機成績
from student,computer
where student.studentname like "%c%" and student.studentid = computer.studentid

–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 *
from student,computer
where 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 class,computer,student
where class.classid > 3 and studentsex = "男" and student.classid = class.classid and student.studentid = computer.studentid

–14查詢男、女生的計算機平均成績、最高分、最低分

–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