SQL语句面试问答（三）

DROP TABLE IF EXISTS 'students';
CREATE TABLE 'students' (
	'Sid' varchar(11) NOT NULL, -- 学生ID 
	'Sname' varchar(20) DEFAULT NULL, -- 学生名字
	'Sage' int(11) DEFAULT NULL, -- 学生年龄
	PRIMARY KEY ('Sid')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO 'students' VALUES ('s01','巧虎','18')；
INSERT INTO 'students' VALUES ('s02','琪琪','19')；
INSERT INTO 'students' VALUES ('s03','桃乐比','18')；
INSERT INTO 'students' VALUES ('s04','圆圆','17');
INSERT INTO 'students' VALUES ('s05','美美','20');
INSERT INTO 'students' VALUES ('s06','多比','25');

DROP TABLE IF EXISTS 'teachers';
CREATE TABLE 'teachers' (
	'Tid' varchar(11) NOT NULL, -- 老师id
	'Tname' varchar(20) DEFAULT NULL, -- 老师名字
	PRIMARY KEY ('Tid')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO 'teachers' VALUES ('1','汪老师')；
INSERT INTO 'teachers' VALUES ('2','石老师')；
INSERT INTO 'teachers' VALUES ('3','李老师')；
INSERT INTO 'teachers' VALUES ('4','熊老师')；
INSERT INTO 'teachers' VALUES ('5','王老师')；
INSERT INTO 'teachers' VALUES ('6','梁老师')；

DROP TABLE IF EXISTS 'courses';
CREATE TABLE 'courses'(
	'Cid' varchar(11) NOT NULL, -- 课程id
	'Cname' varchar(20) DEFAULT NULL, -- 课程名字
	'Cteacher' varchar(20) DEFAULT NULL, -- 课程老师
	PRIMARY KEY ('Cid')
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO 'courses' VALUES ('c01','美术','汪老师')；
INSERT INTO 'courses' VALUES ('c02','音乐','石老师')；
INSERT INTO 'courses' VALUES ('c03','体能','李老师')；
INSERT INTO 'courses' VALUES ('C04','自行车','熊老师')；
INSERT INTO 'courses' VALUES ('c05','钢琴','王老师')；

DROP TABLE IF EXISTS 'results';
CREATE TABLE 'results'(
	'id' int(11) NOT NULL AUTO_INCREMENT, -- 成绩id
	'Sid' varchar(11) DEFAULT NULL, -- 学生 id
	'Cid' varchar(11) DEFAULT NULL, -- 课程 id
	'Cscore' varchar(11) DEFAULT NULL, -- 课程成绩
	PRIMARY KEY ('id')
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

INSERT INTO 'results' VALUES ('1','s01','c01','58');
INSERT INTO 'results' VALUES ('2','s01','c02','59');
INSERT INTO 'results' VALUES ('3','s01','c03','55')；
INSERT INTO 'results' VALUES ('4','s02','c02','83');
INSERT INTO 'results' VALUES ('5','s02','c05','79');
INSERT INTO 'results' VALUES ('6','s02','c04','77')；
INSERT INTO 'results' VALUES ('7','s03','c01','55')；
INSERT INTO 'results' VALUES ('8','s03','c03','81')；
INSERT INTO 'results' VALUES ('9','s03','c04','73');
INSERT INTO 'results' VALUES ('10','s04','c01','67');
INSERT INTO 'results' VALUES ('11','s04','c02','78')；
INSERT INTO 'results' VALUES ('12','s04','c03','82')；
INSERT INTO 'results' VALUES ('13','s04','c05','80');
INSERT INTO 'results' VALUES ('14','s05','c01','61');
INSERT INTO 'results' VALUES ('15','s04','c04','78');

1、查询并统计同龄学生人数

select Sage as 年龄,count(*) as 人数 from students group by Sage order by Sage

2、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

select Cid, avg(CScore) from results group by Cid order by avg(CScore) ,Cid desc

3、按平均成绩从高到低显示所有学生的平均成绩

select Sid, avg(CScore) from results group by Sid order by avg(Cscore) desc

4、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分

select r.Cid as 课程ID, c.Cname as 课程name, max(r.Cscore) as 最高分, min(r.Cscore) as 最低分, avg(r.Cscore) as 平均分
from result as r inner join courses as c on r.cid=c.cid
group by 课程ID

5、按各科平均成绩从低到高顺序排序

select Cid, avg(Cscore) from results group by Cid

6、查询学生的总成绩并进行排名

select Sid， sum(Cscore) from results group by Sid order by sum(Cscore) desc

7、检索至少选修两门课程的学生学号

select Sid， count(Cid) from results order by Sid having count(Cid)>=2

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

select r.Sid,s.Sname from student as s 
inner join result as r where s.Sid=r.Sid
group by r.Sid having count(r.Cid)=1

9、查询每门课都在70分以上的学生id

select Sid from results group by Sid having min(Cscore)>70

10、查询课程编号为"c01"且课程成绩在60分以上的学生的学号和姓名

select r.Sid, s.Sname from students as s 
inner join results as r where s.Sid=r.Sid
where r.sid='c01' and r.Cscore>=60

11、查询平均成绩大于60分的同学的学号和平均成绩

select Sid, avg(Cscore) from results group by Sid having avg(Cscore)>60

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

select r.Sid,s.Sname,count(r.Cid),sum(r.Cscore) 
from students as s inner join results as r where s.Sid=r.Sid
group by r.Sid