【SQL】经典50题 [ 3 ]：Q31-Q50

原来的顺序有点乱，采用通用的50题哈

准备：表的建立

环境/系统/编译器：MACOS+Mysql+Sequel Pro

1. 删除表的语句（重复运行创建表会报错，置于最前）

drop table Student;
drop table SC;
drop table Course;
drop TABLE Teacher;

2. 创建表并插入数据

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

Q31. 查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)

1. 代码

SELECT Student.*
FROM Student
WHERE year(sage)=1990

2. 运行结果

3. 问题

问题: datetime语句

  
  

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

1. 代码

SELECT SC.cid ,AVG(SC.score) 课程平均分
FROM SC
GROUP BY SC.cid
ORDER BY 课程平均分 ASC,SC.cid DESC

2. 运行结果

3. 问题

问题: ORDER BY 课程平均分 ASC,SC.cid DESC

  
  

Q37. 查询不及格的课程，并按课程号从大到小排列

1. 代码

SELECT  SC.*
FROM SC
WHERE SC.score<60
ORDER BY SC.cid DESC

2. 运行结果

3. 问题

无
  
  

Q38. 查询课程编号为"01"且课程成绩在60分以上的学生的学号和姓名

1. 代码

SELECT Student.*
from Student
where Student.sid in 
(SELECT  SC.sid
FROM SC
WHERE SC.score>60 and SC.cid=01)

2. 运行结果

3. 问题

问题:

Q40. 查询选修“张三”老师所授课程的学生中，成绩最高的学生姓名及其成绩

1. 代码

SELECT Student.*
from Student
where Student.sid =
(SELECT  SC.sid
FROM SC
WHERE SC.cid in (SELECT Course.cid FROM Course WHERE Course.tid in (SELECT Teacher.tid  FROM Teacher WHERE Teacher.tname like"%张三%") )
ORDER BY SC.score DESC
LIMIT 1
)

2. 运行结果

3. 问题

问题： 结果只有一个，用where = 而不能用in

  
  

Q42. 查询每门功课成绩最好的前两名

1. 代码

SElECT A.*
FROM
(SELECT SC.sid, 
       SC.score,
       rank() over(partition by SC.sid order by SC.score ) course_rank
FROM SC) A -- 不能直接用course_rank这个别名在where里筛选
WHERE A.course_rank in (1,2)

2. 运行结果

3. 问题

问题：不能直接用course_rank这个别名在where里筛选
  
  

Q43. 统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

1. 代码

SELECT SC.cid,
		count(SC.sid) 选修的学生数
FROM SC
GROUP BY SC.cid
HAVING 选修的学生数>5
ORDER BY 选修的学生数 DESC, SC.cid ASC

2. 运行结果

3. 问题

问题1:
问题2:
  
  

Q44. 检索至少选修两门课程的学生学号

1. 代码

SELECT SC.SID,
		count(SC.CID) 选修的课程数
FROM SC
GROUP BY SC.SID
HAVING 选修的课程数>2

2. 运行结果

3. 问题

问题 :
  
  

Q45. 查询选修了全部课程的学生信息

1. 代码

SELECT Student.*
FROM Student
WHERE sid in
(
SELECT SC.SID
FROM SC
GROUP BY SC.SID
HAVING count(SC.CID)=(SELECT COUNT(DISTINCT CID) FROM SC)
)

2. 运行结果

3. 问题

问题 : HAVING count(SC.CID)=(SELECT COUNT(DISTINCT CID) FROM SC)
  
  

Q46. 查询各学生的年龄(❗️重点)

1. 代码

SELECT Student.*,year(curdate())-year(sage) student_age
FROM Student

2. 运行结果

3. 问题

问题 :year(curdate())-year(sage) student_age
  
  

Q47. 查询本周过生日的学生(❗️)

1. 代码

SELECT Student.*
FROM Student
WHERE weekofyear(curdate())=weekofyear(sage)

2. 运行结果

3. 问题

问题 :weekofyear(curdate())=weekofyear(sage)
  
  

Q48. 查询下周过生日的学生(❗️)

1. 代码

SELECT Student.*
FROM Student
WHERE weekofyear(curdate())-weekofyear(sage)=-1

2. 运行结果

3. 问题

问题 : weekofyear(curdate())
  
  

Q49. 查询本月过生日的学生(❗️)

1. 代码

SELECT Student.*
FROM Student
WHERE month(curdate())=month(sage)

2. 运行结果

3. 问题

问题 ：month( curdate() )
  
  

Q50. 查询下月过生日的学生(❗️重点)

1. 代码

SELECT Student.*
FROM Student
where month(date_sub(sage,interval 1 month)) = month(curdate())

2. 运行结果

3. 问题

问题 :month( date_sub( sage, interval 1 month ) )