MySQL练习题

/*1:建立学生表*/
create table student (
sno char(3) primary key,
sname char(4),
sgender char(2),
sage int,
sclass char(5)
);
insert into student values('108','曾华','男',19,'95033');
insert into student values('105','匡明','男',20,'95031');
insert into student values('107','王丽','女',20,'95033');
insert into student values('101','李军','男',19,'95033');
insert into student values('109','王芳','女',22,'95031');
insert into student values('103','陆君','男',20,'95031');
/*2:建立教师表*/
create table teacher(
tno char(3) primary key,
tname char(4),
tgender char(2),
tage int ,
tlevel char(6),
tlesson char(8)
);
insert into teacher values('804','李成','男',42,'副教授','计算机系');
insert into teacher values('856','张旭','男',35,'讲师','电子工程');
insert into teacher values('825','王萍','女',28,'助教','计算机系');
insert into teacher values('831','刘冰','女',25,'助教','电子工程');
/*3:建立课程表*/
create table course (
cno char(5) primary key,
cname char(10),
tno char(3),
foreign key(tno) references teacher(tno)
);
insert into course values('3-105','计算机导论','825');
insert into course values('3-245','操作系统','804');
insert into course values('6-166','数字电路','856');

/*4:建立选课表*/
create table sc(
sno char(3),
cno char(5),
primary key(sno,cno),
score int,
foreign key(sno) references student(sno),
foreign key(cno) references course(cno)
);
insert into sc values('103','3-245',86);
insert into sc values('105','3-245',75);
insert into sc values('109','3-245',68);
insert into sc values('103','3-105',92);
insert into sc values('105','3-105',88);
insert into sc values('109','3-105',76);
insert into sc values('101','3-105',64);
insert into sc values('107','3-105',91);
insert into sc values('108','3-105',78);
insert into sc values('101','6-166',85);
insert into sc values('107','6-166',79);
insert into sc values('108','6-166',81);
/*5:所有表内容*/
select * from student;
select * from course;
select * from teacher;
select * from sc;

所有表信息如下:

student(学生)表

作业题:
1.查询选修课程'3-105'且成绩在60到80之间的所有记录。
注释:用于指定某个范围使用between and,也可以使用and连接符;
答案:

SELECT * FROM sc WHERE cno='3-105' AND score BETWEEN 60 AND 80;

2.查询成绩为85、86或88的记录。
注释:用于制定某个集合使用 in 关键字,也可以使用 or 连接符;
答案:

SELECT * FROM sc WHERE score=85 or score=86 or score=88;
SELECT * FROM sc WHERE score in (85,86,88);

3.查询'95031'班的学生人数。
注释:count(*)用于计算结果总数;
答案: 

SELECT count(*) FROM student WHERE sclass=95031;

4.查询最低分大于70,且最高分小于90的学号列。
注释:having后面跟聚合函数:avg,min,max,count;having语句只能跟在:group by语句后面;
答案:

SELECT sno,cno FROM sc GROUP BY sno,cno HAVING MIN(score)>70 and MAX(score)<90;

5.查询至少有5名学生选修并以3开头的课程的平均成绩。
注释:group by 语句置于where语句后面表示根据什么条件来分组;
答案:

select avg(score) as avg_score from sc where cno like '3%' group by cno having count(cno) >= 5;

6.查询平均分大于80分的学生的成绩表
注释:having后面跟聚合函数avg();
答案:

SELECT student.sname AS 姓名,AVG(sc.score) AS 平均分 FROM sc JOIN student ON sc.sno = student.sno GROUP BY    student.sname HAVING avg(sc.score>80);

7.查询'95033'班每个学生所选课程的平均分。
注释:此题是根据 '95033'班学生的学号进行的分组,使用where语句限制group by语句的分组条件;
答案:

SELECT avg(sc.score) AS 平均分,student.sclass AS 课程 FROM student JOIN sc ON student.sno = sc.sno WHERE student.sclass = '95033' GROUP BY student.sno,student.sname;

8.以选修 '3-105'为例,查询成绩高于'109'号同学的所有同学的记录。
注释:此题使用典型的嵌套查询,层层深入;
答案:

SELECT student.sno as 学号,student.sname as 姓名,sc.score as 分数 FROM sc JOIN student ON sc.sno=student.sno WHERE sc.cno='3-105' AND sc.score>(SELECT score FROM sc WHERE cno='3-105' AND sno=109);

9.查询与学号为'108'的同学同岁的所有学生的学号、姓名和年龄。
注释:当查询的结果集返回只有一个时关键字in的作用等价于'='的作用,但是注意'='只能用与返回结果集只有一个,而 in 可以有多个结果;
答案:

SELECT student.sno as 学号,student.sname as 姓名,student.sage as 年龄 FROM student WHERE sage=(SELECT sage from student WHERE sno=108);

10.查询每个老师授课不及格学生名单

SELECT teacher.tname as 老师姓名,course.cname as 课程名称,student.sname as 学生姓名,sc.score as 成绩 from sc join student on sc.sno=student.sno join course on sc.cno=course.cno join teacher on course.tno=teacher.tno WHERE sc.score<60 ORDER BY teacher.tname,course.cname,student.sname;

11.查询所有学生的平均成绩

SELECT course.cname as 课程名称,avg(sc.score) as 平均成绩 FROM sc JOIN course on sc.cno=course.cno GROUP BY sc.cno;

12.查询每个课程的最高分，显示课程名字和学生姓名和分数

SELECT course.cname as 课程名字,student.sname as 学生姓名,sc.score as 最高分 from sc join student on sc.sno=student.sno join course on sc.cno=course.cno WHERE sc.score=(SELECT max(sc1.score) FROM sc sc1 WHERE sc.cno=sc1.cno);  

13.查询每个学生的授课老师

SELECT s.sname AS 学生姓名, t.tname AS 授课老师 FROM sc JOIN student s ON sc.sno = s.sno JOIN course c ON sc.cno = c.cno JOIN teacher t ON c.tno = t.tno ORDER BY s.sname, t.tname; 

14.查询每个班级的及格率 

SELECT s.sclass AS 班级, COUNT(sc.sno) AS 总人数, SUM(CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END) AS 及格人数,ROUND(SUM(CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END) * 100.0 / COUNT(sc.sno),2 ) AS 及格率 FROM sc JOIN student s ON sc.sno = s.sno GROUP BY s.sclass ORDER BY s.sclass;