数据库开发技术作业：SQL查询挑战

"本次作业涉及一个选课系统和一个课程知识管理系统的数据库，要求使用SQL语句完成多项查询任务。" 在选课系统数据库中，根据提供的信息，我们可以推断出以下几个关键表： 1. `t_student`：包含学生信息，如`studentId`, `studentName`, `grade`等字段。 2. `t_course`：存储课程信息，可能有`courseId`, `courseName`等字段。 3. `t_score`：记录学生的课程得分，可能包含`studentId`, `courseId`, `year`, `score`等字段。 4. `t_grade`：可能用于定义年级，如`grade`字段。 5. `t_comment`：题目提到但未描述，可能用于评论或反馈。 针对作业中的SQL查询要求，我们可以逐一解析： 1. 查询每个年级学生的总数量: 使用`GROUP BY`和`COUNT(*)`，语句如下： ```sql SELECT grade, COUNT(*) AS studentCnt FROM t_student GROUP BY grade; ``` 2. 查询学生“Sara”的所有课程和得分: 结合`t_student`和`t_score`，可能需要`INNER JOIN`： ```sql SELECT t_course.courseId, courseName, score FROM t_student INNER JOIN t_score ON t_student.studentId = t_score.studentId INNER JOIN t_course ON t_score.courseId = t_course.courseId WHERE t_student.studentName = 'Sara'; ``` 3. 查询2013级学生的总分排名: 需要计算每个学生的总分，然后排序： ```sql SELECT studentId, studentName, SUM(score) AS totalScore FROM t_score WHERE grade = '2013' GROUP BY studentId ORDER BY totalScore DESC; ``` 4. 查询2013级学生所有课程成绩均为优秀的学生列表: 使用`WHERE`过滤条件，确保所有分数都优秀： ```sql SELECT studentId, studentName FROM t_student WHERE studentId IN ( SELECT studentId FROM t_score WHERE grade = '2013' AND score >= 90 ) GROUP BY studentId HAVING COUNT(*) = (SELECT COUNT(*) FROM t_score WHERE grade = '2013'); ``` 5. 查询2015年选择课程“离散数学”的学生的姓名及成绩： ```sql SELECT studentName, score FROM t_student INNER JOIN t_score ON t_student.studentId = t_score.studentId WHERE year = '2015' AND courseName = '离散数学' ORDER BY courseId, score DESC; ``` 6. 查询2015年各课程最高分的学生及得分： ```sql SELECT courseId, courseName, studentId, studentName, score FROM t_score WHERE year = '2015' AND score = ( SELECT MAX(score) FROM t_score WHERE year = '2015' AND courseId = t_score.courseId ) ORDER BY courseId; ``` 7. 查询2015年所有课程和各自的选课人数： ```sql SELECT courseId, courseName, COUNT(DISTINCT studentId) AS studentCount FROM t_score WHERE year = '2015' GROUP BY courseId, courseName; ``` 8. 查询2015年不及格人数最多的课程名称及挂科人数： ```sql SELECT courseId, courseName, COUNT(*) AS failedCnt FROM t_score WHERE year = '2015' AND score < 60 GROUP BY courseId, courseName ORDER BY failedCnt DESC LIMIT 1; ``` 9. 查询各年课程号为233的平均分，按照选课时间从后向前排序： ```sql SELECT year, AVG(score) AS avgScore FROM t_score WHERE courseId = 233 GROUP BY year ORDER BY year DESC; ``` 10. 查询老师“Steven”教授的所有课程的合格率： ```sql SELECT courseId, courseName, SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS passRate FROM t_score INNER JOIN t_course ON t_score.courseId = t_course.courseId WHERE teacherName = 'Steven' GROUP BY courseId, courseName; ``` 11. 查询在2015年同时选择“离散数学”和“微积分”两门课程的学生姓名列表： ```sql SELECT DISTINCT s1.studentName FROM t_student s1 WHERE s1.studentId IN ( SELECT studentId FROM t_score WHERE year = '2015' AND courseName = '离散数学' ) AND s1.studentId IN ( SELECT studentId FROM t_score WHERE year = '2015' AND courseName = '微积分' ); ``` 12. 查询在2015年选课数量大于3门的学生姓名列表和选课数量： ```sql SELECT studentId, studentName, COUNT(*) AS selectCnt FROM t_score WHERE year = '2015' GROUP BY studentId, studentName HAVING selectCnt > 3; ``` 对于知识管理系统部分，由于没有具体描述和字段，这里无法给出具体的SQL查询示例，但可以根据`knowledge.sql`文件中的表结构进行相应的查询设计。