1. 查询所有“公共课”且学时数大于35的课程ID、课程名称与学时数；
select course_id, course_name, class_hour from course where type = 'A' and course.class_hour > 35;
2. 查询学生信息，找出姓“李”，且姓名为2个字的学生，列出学生姓名，性别与出生日期；
select student_name, gender, year(birth_date) from student where student_name like '李_';
3. 找出出生日期在1980年以前的教师，列出所有信息；
select * from teacher where year(birth_date) < '1980';
4. 查询1980年以后出生的“副教授”的所有信息；
select * from teacher where year(birth_date) > '1980' and title = '副教授';
5. 查询在1980、1978、1975年出生的教授的所有信息
select * from teacher where year(birth_date) in ('1980', '1978', '1975') and title = '教授';
6. 查询教师的工号、姓名与出生日期，并按年龄的升序排列；
select teacher_id, teacher_name, year(birth_date) from teacher order by year(birth_date) desc;
7. 查询所有年龄在18-21之间的学生学号，性别，年龄，按年龄的升序排序后，再按学号的降序排列；
select student_id, gender, (year(now()) -year(birth_date)) age from student
where (year(now()) -year(birth_date)) between 18 and 21 order by age, student_id desc;
8. 按统计每个专业的学生人数，显示5专业名称与人数；
SELECT major_name, sum(quantity) as student_count FROM class
join major on class.major_id = major.major_id GROUP BY major_name;
9. 统计每种课程类型的平均学时，显示课程类型与平均学；
select type, avg(class_hour) as avg_class_hour from course GROUP BY type;
10. 查询总成绩排名4-6名的名同学，列出学生姓名与总成绩；
select student_name, sum(grade) as total_score from student s
join enrollment e on s.student_id = e.student_id
group by s.student_id order by total_score desc
limit 3 offset 3;
11. 查询总成绩高于400分的同学，列出学生姓名与总成绩。
select student_name, sum(grade) as total_score from student s
join enrollment e on s.student_id = e.student_id
group by s.student_id having total_score > 400;
12. 查询没有选择“微信小程序开发”课程的学生学号、姓名、班级名称。
select s.student_id,s.student_name,c.class_name from student s
left join class c on s.class_id = c.class_id
where student_id not in (
SELECT student_id from enrollment where course_id =(
select course_id from course where course_name ='微信小程序开发'));
13. 查询选择“数据库技术与应用课程”的所有学生姓名；
select student_name from student where student_id in (
select student_id from enrollment where course_id = (
select course_id from course where course_name = '数据库技术与应用'));
14. 查询比李玉华老师教授的任意一门得分髙的选课记录，显示学生学号，课程编号与考试成绩；
select student_id, course_id, grade from enrollment where grade > any (
select grade from enrollment where teacher_id =(
select teacher_id from teacher where teacher_name ='李玉华'));
15. 查询class表中班级名称包含“软件”且班级人数大于42的班级名称、班级人数；
select class_name, quantity from class where class_name like '%软件%' and quantity > 42;
16. 查询“裴瑜”老师所教授的学生姓名、性别与电话；
select student_name,gender ,phone from student where student_id in (
SELECT student_id from enrollment where teacher_id =(
select teacher_id from teacher where teacher_name ='裴瑜'));
17. 查询家庭地址中包含“贵州”的学生姓名、电话、家庭地址，并按学生的年龄降序排序；
select student_name, phone, address from student where address like '%贵州%' order by birth_date;
18. 查询选修了课程2100208且考试成绩高于80分学生学号、姓名；
select student_id, student_name from student where student_id
in (select student_id from enrollment where course_id = 2100208 and grade > 80);
19. 查询学生表中年龄最小的15位同学学号、姓名、年龄；
select student_id, student_name, year(now()) - year(birth_date) as age from student order by age limit 15;
20. 查询考试成绩排名前5的学生信息，显示学生姓名、平均分数；
select student_name, avg(grade) as avg_grade from student
join enrollment on student.student_id = enrollment.student_id
group by student.student_id order by avg_grade desc limit 5;
