列出所有课程被选修的详细情况，包括课程号、课程名、学号、姓名及成绩

为了列出所有课程的详细选修情况，通常需要查看一个结合了课程信息（如课程号和课程名）、学生信息（如学号和姓名）以及成绩信息的数据表。这样的表通常被称为“课程选课记录”或“学生成绩表”，假设它叫做`Enrollments`，其结构可能如下：

- `Enrollments`表：有字段如 `CourseCode`, `CourseName`, `StudentID`, `StudentName`, `Grade`

查询语句可以如下所示：

SELECT CE.CourseCode AS 课程号, CE.CourseName AS 课程名, E.StudentID AS 学号, E.StudentName AS 姓名, E.Grade AS 成绩
FROM Enrollments CE
JOIN Students E ON CE.StudentID = E.StudentID
ORDER BY CourseCode, StudentID;

这个查询将`Enrollments`表与`Students`表通过`StudentID`关联起来，展示每个学生对每门课程的选择及其对应的成绩，并按照课程号和学号排序。

相关问题

多表连接查询 (1) 查询选修了【数据库原理】的计算机系的学生学号和姓名。 (2) 查询每一门课的间接先行课(即先行课的先行课)，显示课程编号和该门课程的间接先行课编号。 (3) 查询学生的学号、姓名、选修课程的名称和成绩。 (4) 查询选修了课程的学生姓名。 (5) 查询所有学生的信息和所选修的课程, 要求显示学生的学号、姓名、课程号及课程名。没有选课的同学对应的选课信息为空。 (6) 查询所有课程的课程编号、课程名称及选课人数，没有被选的课程选课人数显示为0。 (7) 列出所有学生所有可能的选修情况，要求显示学生的学号、姓名、课程号及课程名。 (8) 查找计算机系的学生选修课程数大于2 的学生的姓名、 平均成绩和选课门数，并按平均成绩降序排列。

1.

SELECT s.id, s.name
FROM student s
JOIN course_selection cs ON s.id = cs.student_id
JOIN course c ON cs.course_id = c.id
WHERE s.department = '计算机系'
AND c.name = '数据库原理';

2.

WITH RECURSIVE prerequisites AS (
  SELECT course_id, prerequisite_id
  FROM prerequisite
  UNION
  SELECT p.course_id, pr.prerequisite_id
  FROM prerequisites pr
  JOIN prerequisite p ON pr.course_id = p.prerequisite_id
)
SELECT c.id, pr.prerequisite_id
FROM course c
LEFT JOIN prerequisites pr ON c.id = pr.course_id;

3.

SELECT s.id, s.name, c.name, cs.grade
FROM student s
JOIN course_selection cs ON s.id = cs.student_id
JOIN course c ON cs.course_id = c.id;

4.

SELECT s.name
FROM student s
JOIN course_selection cs ON s.id = cs.student_id;

5.

SELECT s.id, s.name, c.id, c.name
FROM student s
LEFT JOIN course_selection cs ON s.id = cs.student_id
LEFT JOIN course c ON cs.course_id = c.id;

6.

SELECT c.id, c.name, COUNT(cs.student_id) AS num_students
FROM course c
LEFT JOIN course_selection cs ON c.id = cs.course_id
GROUP BY c.id, c.name;

7.

SELECT s.id, s.name, c.id, c.name
FROM student s
CROSS JOIN course c;

8.

WITH course_counts AS (
  SELECT s.id, s.name, COUNT(cs.course_id) AS num_courses
  FROM student s
  JOIN course_selection cs ON s.id = cs.student_id
  WHERE s.department = '计算机系'
  GROUP BY s.id, s.name
  HAVING COUNT(cs.course_id) > 2
),
average_grades AS (
  SELECT s.id, AVG(cs.grade) AS avg_grade
  FROM student s
  JOIN course_selection cs ON s.id = cs.student_id
  GROUP BY s.id
)
SELECT cc.name, ag.avg_grade, cc.num_courses
FROM course_counts cc
JOIN average_grades ag ON cc.id = ag.id
ORDER BY ag.avg_grade DESC;

数据库StudentCourse 有表SC含Sno作学号,Cno作课程号，Grade作成绩。表Student含Sno作学号,Sname作姓名，Ssex作性别，Sbirthday作出生日期，Sdept作所在系，Memo作备注。表Course含Cno作课程号，Cname作姓名，PreCourse作先行课,PreCno作选修课程号，Credit作学分，Semester作开课学期。①查询选修了【数据库原理】的计算机系的学生学号和姓名。②查询每一门课的间接先行课(印先行课的先行课)。③查询学生的学号、姓名，选修课程的名称和成绩。④查询选修了课程的学生姓名。⑤查询所有学生的信息和所选修的课程的课程号。⑥查询已被选修的课程的情况和所有课程的名字。⑦列出学生所有可能的选修情况。⑧查找计算机系的学生选修课程数大于2的学生的姓名、平均成绩和选课门数，并按平均成绩降序排列：

①查询选修了【数据库原理】的计算机系的学生学号和姓名。

SELECT Student.Sno, Student.Sname
FROM Student, SC, Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Cname = '数据库原理' AND Student.Sdept = '计算机系';

②查询每一门课的间接先行课(印先行课的先行课)。

WITH RECURSIVE pre_course(cno, pre_cno) AS (
    SELECT Cno, PreCno FROM Course WHERE PreCno IS NOT NULL
    UNION ALL
    SELECT pre_course.cno, Course.PreCno FROM pre_course JOIN Course ON pre_course.pre_cno = Course.Cno
)
SELECT cno, group_concat(DISTINCT pre_cno) as indirect_pre_courses
FROM pre_course
GROUP BY cno;

③查询学生的学号、姓名，选修课程的名称和成绩。

SELECT Student.Sno, Student.Sname, Course.Cname, SC.Grade
FROM Student, SC, Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno;

④查询选修了课程的学生姓名。

SELECT DISTINCT Student.Sname
FROM Student, SC, Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Cname = '数据库原理';

⑤查询所有学生的信息和所选修的课程的课程号。

SELECT Student.*, SC.Cno
FROM Student LEFT JOIN SC ON Student.Sno = SC.Sno;

⑥查询已被选修的课程的情况和所有课程的名字。

SELECT Course.*, SC.Sno, SC.Grade
FROM Course LEFT JOIN SC ON Course.Cno = SC.Cno;

⑦列出学生所有可能的选修情况。

SELECT Student.Sno, Student.Sname, Course.Cno, Course.Cname
FROM Student CROSS JOIN Course;

⑧查找计算机系的学生选修课程数大于2的学生的姓名、平均成绩和选课门数，并按平均成绩降序排列。

SELECT Student.Sname, AVG(SC.Grade) AS avg_grade, COUNT(*) as course_count
FROM Student JOIN SC ON Student.Sno = SC.Sno JOIN Course ON SC.Cno = Course.Cno
WHERE Student.Sdept = '计算机系'
GROUP BY Student.Sno
HAVING COUNT(*) > 2
ORDER BY AVG(SC.Grade) DESC;