用sql语言编写,按照目前湖工大的成绩管理情况,设计成绩管理系统。要求系统可以存储学生基本信息,和课程基本信息,学生选课情况以及课程得分(考试后进行补录)。课程有必修,选修和公选不同类型。各学院学生只能选修该专业的选修课。公选课可以大范围选修。且补考,重修成绩要单独对待。可以根据学分统计发放学位信息。并提供学位信息查询(包括已经发放的学位证查询,以及没有发放的学位,要显示该生还需多少学分,什么类型课程多少学分等提示。)策略分析题目:1)按照学生学号,查询该生所有的课程选择及得分情况,并进行平均分,绩点计算。显示该生三类课程的门数和平均分。(必修,选修和任选课),其中任选课程若不及格不计入。2)按照课程号,查询该课程所有学生的得分情况,并进行平均分,方差等计算。按照成绩进行排序。3)根据班号,统计该班学生的不及格的全部信息。,用sql语言写4)统计每个学院的平均绩点和排名 5)查询某一学期的学生选课情况和课程表 6)统计历年学位授予情况和学位授予率等
时间: 2024-03-14 12:47:17 浏览: 29
针对这个问题,我可以为您提供以下 SQL 语句来实现所需的功能:
1. 按照学生学号查询该生所有的课程选择及得分情况,并进行平均分、绩点计算,并显示该生三类课程的门数和平均分(必修、选修和任选课),其中任选课程若不及格不计入:
```sql
SELECT
student.student_id,
student.student_name,
AVG(course_selection.course_score) AS avg_score,
SUM(CASE WHEN course.course_type = '必修课' THEN 1 ELSE 0 END) AS required_num,
SUM(CASE WHEN course.course_type = '选修课' AND student.major = course.course_major THEN 1 ELSE 0 END) AS elective_num,
SUM(CASE WHEN course.course_type = '公选课' THEN 1 ELSE 0 END) AS public_num
FROM
student
LEFT JOIN course_selection ON student.student_id = course_selection.student_id
LEFT JOIN course ON course_selection.course_id = course.course_id
WHERE
student.student_id = 'xxxxxx'
AND (course_selection.course_score >= 60 OR course.course_type != '任选课')
GROUP BY
student.student_id,
student.student_name;
```
2. 按照课程号查询该课程所有学生的得分情况,并进行平均分、方差等计算,并按照成绩进行排序:
```sql
SELECT
course.course_id,
course.course_name,
AVG(course_selection.course_score) AS avg_score,
VAR_POP(course_selection.course_score) AS variance
FROM
course
LEFT JOIN course_selection ON course.course_id = course_selection.course_id
WHERE
course.course_id = 'xxxxxx'
GROUP BY
course.course_id,
course.course_name
ORDER BY
course_selection.course_score DESC;
```
3. 根据班号统计该班学生的不及格的全部信息:
```sql
SELECT
student.student_id,
student.student_name,
course.course_id,
course.course_name,
course_selection.course_score
FROM
student
LEFT JOIN course_selection ON student.student_id = course_selection.student_id
LEFT JOIN course ON course_selection.course_id = course.course_id
WHERE
student.class_id = 'xxxxxx'
AND course_selection.course_score < 60;
```
4. 统计每个学院的平均绩点和排名:
```sql
SELECT
student.college,
AVG(course_selection.grade_point) AS avg_grade_point,
RANK() OVER (PARTITION BY student.college ORDER BY AVG(course_selection.grade_point) DESC) AS rank
FROM
student
LEFT JOIN course_selection ON student.student_id = course_selection.student_id
GROUP BY
student.college;
```
5. 查询某一学期的学生选课情况和课程表:
```sql
SELECT
student.student_id,
student.student_name,
course_selection.course_id,
course.course_name,
course_selection.class_time
FROM
student
LEFT JOIN course_selection ON student.student_id = course_selection.student_id
LEFT JOIN course ON course_selection.course_id = course.course_id
WHERE
course_selection.semester = 'xxxxxx';
```
6. 统计历年学位授予情况和学位授予率等:
```sql
SELECT
student.degree_type,
COUNT(*) AS degree_num,
COUNT(*) / (SELECT COUNT(*) FROM student) AS degree_rate
FROM
student
WHERE
student.degree_type IS NOT NULL
GROUP BY
student.degree_type;
```
总之,以上 SQL 语句可以帮助实现成绩管理系统中的常见功能,但具体实现还需要根据实际情况进行调整和完善。