经典SQL50题（31~50）

创建表SQL

create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
create table Course(cid varchar(10),cname varchar(10),tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(tid varchar(10),tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(sid varchar(10),cid varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

表结构

学生表 Student
sid 学生编号 sname 学生姓名 sage 出生年月 ssex 学生性别
课程表 Course
cid 课程编号 cname 课程名称 tid 教师编号
教师表 Teacher
tid 教师编号 tname 教师姓名
成绩表 SC
sic 学生编号 cid 课程编号 score 分数

31~50题

-- 31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
SELECT Student.sid,Student.sname
FROM Student
WHERE
YEAR(Student.sage)=1990

-- 32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
SELECT SC.cid,AVG(SC.score) avgScore
FROM SC
GROUP BY SC.cid
ORDER BY avgScore ASC,SC.cid DESC

-- 37、查询不及格的课程，并按课程号从大到小排列
SELECT SC.cid,SC.score
FROM SC
WHERE SC.score<60
ORDER BY SC.cid DESC

-- 38、查询课程编号为"01"且课程成绩在60分以上的学生的学号和姓名；
SELECT Student.sid,Student.sname
FROM Student
LEFT JOIN SC ON Student.sid=SC.sid
WHERE SC.score>60
AND SC.cid='01'
GROUP BY Student.sid,Student.sname

-- 40、查询选修“张三”老师所授课程的学生中，成绩最高的学生姓名及其成绩
SELECT Student.sname,SC.score
FROM Student
LEFT JOIN SC ON Student.sid=SC.sid
WHERE
SC.score in(
SELECT MAX(SC.score)
FROM SC 
INNER JOIN Course ON Course.cid=SC.cid
INNER JOIN Teacher ON Course.tid=Teacher.tid AND Teacher.tname='张三'
)

-- 42、查询每门功课成绩最好的前两名
SELECT t1.cid,t1.sid,t1.score,(SELECT COUNT(*)+1 FROM SC t2 WHERE t2.cid=t1.cid AND t2.score>t1.score) AS rank
FROM SC t1
HAVING rank<3
ORDER BY t1.cid ASC,t1.score DESC

-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT SC.cid,COUNT(DISTINCT SC.sid) num
FROM SC
GROUP BY SC.cid
HAVING num>5
ORDER BY num DESC,SC.cid ASC

-- 44、检索至少选修两门课程的学生学号
SELECT SC.sid,COUNT(DISTINCT SC.cid) AS num
FROM SC
GROUP BY SC.sid
HAVING num>=2

-- 45、查询选修了全部课程的学生信息
SELECT SC.sid,Student.sname,COUNT(DISTINCT SC.cid) AS num
FROM SC
LEFT JOIN Student ON SC.sid=Student.sid
GROUP BY SC.sid,Student.sname
HAVING num=(SELECT COUNT(DISTINCT Course.cid) FROM Course)

-- 46、查询各学生的年龄
SELECT Student.sname,TIMESTAMPDIFF(YEAR,Student.sage,CURDATE()) AS age
FROM Student

-- 47、查询本周过生日的学生
SELECT Student.sname,Student.sage
FROM Student
WHERE WEEKOFYEAR(Student.sage)=WEEKOFYEAR(CURDATE())

-- 48、查询下周过生日的学生
SELECT Student.sname,Student.sage
FROM Student
WHERE WEEKOFYEAR(Student.sage)=WEEKOFYEAR(DATE_ADD(CURDATE(),INTERVAL 1 WEEK))

-- 49、查询本月过生日的学生
SELECT Student.sname,Student.sage
FROM Student
WHERE MONTH(Student.sage)=MONTH(CURDATE())

-- 50、查询下月过生日的学生
SELECT Student.sname,Student.sage
FROM Student
WHERE MONTH(SUBDATE(Student.sage,INTERVAL 1 MONTH))=MONTH(CURDATE())