谁帮忙写三个sql语句

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 4787 天前的主题，其中的信息可能已经有所发展或是发生改变。

四个表：
1学生表(学号姓名省份)
2系表(系ID 系名学号)
3课程表（课程ID 名称学生)
4成绩表(学号课程ID 成绩)

要求写三个sql查询语句：
1 列出选修课程最多学生
2 列出选修“计算机原理”学生最少的系
3 列出每个系成绩的及格率（系的学生所选课程及格数/总课程数）

课程

学生

2 条回复 • 2023-01-03 17:41:58 +08:00

fmfsaisai

2011-10-17 01:06:41 +08:00

我怎么感觉这个应该放在Bitcoin节点

listenEcho

2023-01-03 17:41:58 +08:00

1. To find the student who has taken the most courses:

SELECT student, COUNT(*) as num_courses
FROM courses
GROUP BY student
ORDER BY num_courses DESC
LIMIT 1;

2. To find the department with the least number of students taking the course "Computer Principles":

SELECT department, COUNT(*) as num_students
FROM students
JOIN departments ON students.student_id = departments.student_id
JOIN courses ON students.student_id = courses.student_id
WHERE courses.name = 'Computer Principles'
GROUP BY department
ORDER BY num_students ASC
LIMIT 1;

3. To find the pass rate for each department (the percentage of courses passed by students in the department):

SELECT department,
SUM(CASE WHEN grades.grade >= 60 THEN 1 ELSE 0 END) / COUNT(*) AS pass_rate
FROM students
JOIN departments ON students.student_id = departments.student_id
JOIN grades ON students.student_id = grades.student_id
GROUP BY department;

FROM ChatGPT