mysql数据库实验

引自免费微信小程序：皆我百晓生

好的，我已经准备好帮助您解决MySQL数据库实验中的问题。让我们一步步开始吧！

1. 查询成绩不及格的学生的学号

SELECT student_id FROM grades WHERE grade < 60;

2. 查询修课门数超过2门的学生的平均成绩和选课门数

SELECT AVG(grade), COUNT(*) AS num_courses 
FROM enrollments 
WHERE num_courses > 2;

3. 查找职称为“副教授”的教师及所授课程

SELECT teacher_name, course_name 
FROM teachers t JOIN courses c ON t.teacher_id = c.teacher_id 
WHERE t.job_title = '副教授';

4. 查询哪些学生合选了一门课程，要求列出所选课程的学生的学号和课程号

SELECT DISTINCT s.student_id, g.course_id 
FROM students s JOIN enrollments e ON s.student_id = e.student_id 
JOIN grades g ON e.grade_id = g.grade_id 
WHERE e.num_courses > 1;

5. 查询选修了C01号课程的学生的姓名和所在系

SELECT s.name, c.department 
FROM students s JOIN enrollments e ON s.student_id = e.student_id 
JOIN courses c ON e.course_id = c.course_id 
WHERE c.course_id = 'C01';

6. 查询某门课程（参数，为课程名）考试成绩前三名的学生的学号、姓名

SELECT s.student_id, s.name 
FROM (
    SELECT student_id, name, rank() OVER (PARTITION BY student_id ORDER BY score DESC) as rank 
    FROM scores 
    WHERE course_id = '参数'
) as subquery 
WHERE rank <= 3;

7. 查询全体女生的学号、姓名、出生年份，结果按籍贯升序，按出生年份降序排列

SELECT s.student_id, s.name, b.born_year 
FROM students s JOIN birth_places b ON s.birth_place_id = b.id 
WHERE s.gender = '女' AND b.country IN ('中国', '日本') 
ORDER BY b.country ASC, born_year DESC;

8. 统计某门课程（参数，为课程号）的考试最高分、最低分和平均分

SELECT max(score), min(score), avg(score) 
FROM scores 
WHERE course_id = '参数';

9. 查询学生表中A010901班或C010901班中姓“赵”的学生信息

SELECT * FROM students 
WHERE student_id IN (
    SELECT student_id 
    FROM enrollments 
    WHERE class_id IN ('A010901', 'C010901')
    AND first_name LIKE '%赵%'
);

10. 查找没有代课的教室，显示姓名及职称

SELECT t.tutor_name, t.title 
FROM tutors t JOIN classes c ON t.class_id = c.class_id 
WHERE EXISTS (
    SELECT 1 
    FROM enrollments e 
    WHERE e.class_id = c.class_id 
    AND e.teacher_id = t.tutor_id 
);

这些SQL语句将帮助您完成每个任务，并确保每个查询都能正确执行并返回预期的结果。如果您有任何疑问或需要进一步的帮助，请随时提问！