SQL语句题目

#题目准备

CREATE DATABASE school CHARSET utf8;
USE school

CREATE TABLE student(
sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(20) NOT NULL COMMENT '姓名',
sage TINYINT UNSIGNED NOT NULL COMMENT '年龄',
ssex ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '性别'
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE course(
cno INT NOT NULL PRIMARY KEY COMMENT '课程编号',
cname VARCHAR(20) NOT NULL COMMENT '课程名字',
tno INT NOT NULL COMMENT '教师编号'
)ENGINE=INNODB CHARSET utf8;

CREATE TABLE sc (
sno INT NOT NULL COMMENT '学号',
cno INT NOT NULL COMMENT '课程编号',
score INT NOT NULL DEFAULT 0 COMMENT '成绩'
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE teacher(
tno INT NOT NULL PRIMARY KEY COMMENT '教师编号',
tname VARCHAR(20) NOT NULL COMMENT '教师名字'
)ENGINE=INNODB CHARSET utf8;
INSERT INTO student(sno,sname,sage,ssex)
VALUES (1,'zhang3',18,'m');

INSERT INTO student(sno,sname,sage,ssex)
VALUES
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f');

INSERT INTO student
VALUES
(5,'zh4',18,'m'),
(6,'zhao4',18,'m'),
(7,'ma6',19,'f');

INSERT INTO student(sname,sage,ssex)
VALUES
('oldboy',20,'m'),
('oldgirl',20,'f'),
('oldp',25,'m');

INSERT INTO teacher(tno,tname) VALUES
(101,'oldboy'),
(102,'hesw'),
(103,'oldguo');
DESC course;

INSERT INTO course(cno,cname,tno)
VALUES
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103);

DESC sc;

INSERT INTO sc(sno,cno,score)
VALUES
(1,1001,80),
(1,1002,59),
(2,1002,90),
(2,1003,100),
(3,1001,99),
(3,1003,40),
(4,1001,79),
(4,1002,61),
(4,1003,99),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82),
(8,1001,70),
(9,1003,80),
(10,1003,96);

1. 查询平均成绩大于60分的同学的学号和平均成绩；

select student.sname,student.sno,avg(sc.score) 
from student 
join sc on student.sno=sc.sno 
group by student.sno 
having avg(sc.score)>60;

2. 查询所有同学的学号、姓名、选课数、总成绩；

select student.sno,student.sname,count(sc.cno),sum(sc.score) from student 
join sc on student.sno=sc.sno 
group by student.sno;

3. 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

select cno,max(score),min(score) from sc group by cno;

4.统计各位老师,所教课程的及格率

select teacher.tname,course.cname,
count(case when sc.score>60 then 1 end)/count(sc.score) 
from teacher 
join course on teacher.tno=course.tno 
join sc on course.cno=sc.cno 
group by teacher.tno,course.cno;

5. 查询每门课程被选修的学生数

select cno,count(sno) from sc group by cno;

6. 查询出只选修了一门课程的全部学生的学号和姓名

select student.sno,student.sname,count(sc.cno) 
from student 
join sc on student.sno=sc.sno 
group by student.sno 
having count(sc.cno)=1;

7. 查询选修课程门数超过1门的学生信息

select student.sno,student.sname,student.sage,student.ssex,count(sc.cno) 
from student 
join sc on student.sno=sc.sno 
group by student.sno 
having count(sc.cno)>1;

8.统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表

SELECT course.cname,
GROUP_CONCAT(CASE WHEN sc.score>85 THEN student.sname END  ) AS 优秀,
GROUP_CONCAT(CASE WHEN sc.score>70 AND sc.score<=85 THEN student.sname END) AS 良好,
GROUP_CONCAT(CASE WHEN sc.score>=60 AND sc.score<=70 THEN student.sname END) AS 一般, 
GROUP_CONCAT(CASE WHEN sc.score<60 THEN student.sname END) AS 不及格
FROM course
JOIN sc ON course.cno=sc.cno
JOIN student ON sc.sno=student.sno
GROUP BY course.cname;

9. 查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select student.sno,student.sname,avg(sc.score) 
from student 
join sc on student.sno=sc.sno 
group by student.sno 
having avg(sc.score)>85;