t_SQL统计有学生选修的课程门数
时间: 2024-02-03 07:15:29 浏览: 110
可以使用以下 SQL 语句来统计学生选修的课程门数:
```
SELECT s.student_id, COUNT(*) AS course_count
FROM student s
INNER JOIN course_selection cs ON s.student_id = cs.student_id
GROUP BY s.student_id;
```
其中,`student` 表存储学生的信息,`course_selection` 表存储学生选修的课程信息。通过内连接将两个表关联起来,然后按照学生 ID 进行分组,统计每个学生选修的课程数量。使用 `COUNT(*)` 函数可以统计每个学生选修的课程数量,最终结果会返回每个学生的 ID 和选修的课程门数。
相关问题
表2.11字段名定义表 字段名 字段说明 字段名 字段说明 Student_no 学号 Credit 学分 Student_name 姓名 Classhour 学时 Sex 性别 Student_no 学号 Birth 出生年月 Course_no 课程编号 Enter_date 入学年份 Ordinary_scores 平时成绩 Address 家庭住址 End_scores 期末成绩 Course_no 课程编号 Total_scores 总评成绩 Course_name 课程名 3 数据库操作 3.1 创建数据库 创建数据库Student。 3.2 创建数据表 根据图2.26和表2.11,创建数据表T_student_info、T_course_info 、T_student_scores。 3.3 创建数据表间的关系 根据物理数据原型,创建数据关系。 3.4 数据操作 用SQL语句完成如下操作: 向T_student_info表插入数据: “102011,刘德华,男,03/23/1991,2009,湖南长沙劳动东路168号”; 查询出1991年出生的学生信息; 查询选修了“数据结构”的学生姓名、平时成绩、期末成绩、总评成绩; 创建视图查询学生的学号、姓名、课程名、总评成绩; 统计每门课程的选课人数、最高分、平均分,并将统计结果保存在表T_total中。
以下是实现上述需求的 SQL 语句:
1. 创建数据库
```
CREATE DATABASE Student;
```
2. 创建数据表 T_student_info
```
CREATE TABLE T_student_info (
Student_no CHAR(6) PRIMARY KEY,
Student_name VARCHAR(20),
Sex VARCHAR(2),
Birth DATE,
Enter_date CHAR(4),
Address VARCHAR(50)
);
```
3. 创建数据表 T_course_info
```
CREATE TABLE T_course_info (
Course_no CHAR(6) PRIMARY KEY,
Course_name VARCHAR(20),
Credit NUMERIC(3, 1),
Classhour NUMERIC(3, 0)
);
```
4. 创建数据表 T_student_scores
```
CREATE TABLE T_student_scores (
Student_no CHAR(6),
Course_no CHAR(6),
Ordinary_scores NUMERIC(4, 1),
End_scores NUMERIC(4, 1),
Total_scores NUMERIC(4, 1),
PRIMARY KEY (Student_no, Course_no),
FOREIGN KEY (Student_no) REFERENCES T_student_info (Student_no),
FOREIGN KEY (Course_no) REFERENCES T_course_info (Course_no)
);
```
5. 创建数据表间的关系
```
-- 一个学生可以选多门课程
ALTER TABLE T_student_scores ADD CONSTRAINT FK_T_student_scores_1
FOREIGN KEY (Student_no) REFERENCES T_student_info (Student_no);
-- 一门课程可以被多个学生选
ALTER TABLE T_student_scores ADD CONSTRAINT FK_T_student_scores_2
FOREIGN KEY (Course_no) REFERENCES T_course_info (Course_no);
```
6. 向 T_student_info 表插入数据
```
INSERT INTO T_student_info (Student_no, Student_name, Sex, Birth, Enter_date, Address)
VALUES ('102011', '刘德华', '男', '1991-03-23', '2009', '湖南长沙劳动东路168号');
```
7. 查询出 1991 年出生的学生信息
```
SELECT *
FROM T_student_info
WHERE YEAR(Birth) = 1991;
```
8. 查询选修了“数据结构”的学生姓名、平时成绩、期末成绩、总评成绩
```
SELECT T_student_info.Student_name, T_student_scores.Ordinary_scores, T_student_scores.End_scores, T_student_scores.Total_scores
FROM T_student_info
JOIN T_student_scores ON T_student_info.Student_no = T_student_scores.Student_no
JOIN T_course_info ON T_student_scores.Course_no = T_course_info.Course_no
WHERE T_course_info.Course_name = '数据结构';
```
9. 创建视图查询学生的学号、姓名、课程名、总评成绩
```
CREATE VIEW V_student_scores AS
SELECT T_student_info.Student_no, T_student_info.Student_name, T_course_info.Course_name, T_student_scores.Total_scores
FROM T_student_info
JOIN T_student_scores ON T_student_info.Student_no = T_student_scores.Student_no
JOIN T_course_info ON T_student_scores.Course_no = T_course_info.Course_no;
```
10. 统计每门课程的选课人数、最高分、平均分,并将统计结果保存在表 T_total 中
```
CREATE TABLE T_total (
Course_no CHAR(6) PRIMARY KEY,
Num_selected INT,
Max_score NUMERIC(4, 1),
Avg_score NUMERIC(4, 1)
);
INSERT INTO T_total (Course_no, Num_selected, Max_score, Avg_score)
SELECT T_student_scores.Course_no, COUNT(*), MAX(T_student_scores.Total_scores), AVG(T_student_scores.Total_scores)
FROM T_student_scores
GROUP BY T_student_scores.Course_no;
```
写一份数据库原理的实验报告使用企业管理器建立学生选课数据库“StdentW101”,数据 库表为S(学生信息表)、C(课程信息表)、SC(选课信息 表):1.检索丁宝康老师所授课程的课程号和课程名 2.检索年龄大于19岁的男学生的学号和姓名。 3. 检索至少选修两门课程的学生学号。 4. 统计有学生选修的课程门数。 5. 求选修课程号为2的学生的平均年龄。 6.求马良老师所授课程的每门课程的学生平均成绩 7.统计每门课程的学生选修人数(超过1人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列。 8. 检索姓名以王打头的所有学生的姓名和年龄。 9.在SC中检索成绩为空值的学生学号和课程号。SNO CNO GHADE 96001 1 92 96001 2 85 96001 3 88 96002 2 90 96002 3 80 96002 1 7.5 96003 95 96003 7 98 96004 1 92 96005 1 59 96005 6 84
实验目的:
1. 了解数据库的基本原理和操作;
2. 掌握企业管理器的使用方法;
3. 实现基本的SQL语句。
实验步骤:
1. 打开企业管理器,新建一个名为“StdentW101”的数据库,并在其中创建三个表格,分别为S(学生信息表)、C(课程信息表)和SC(选课信息表)。
2. 对于S表,创建以下字段:SNO(学号)、SNAME(姓名)、SEX(性别)、AGE(年龄)和BIRTHDAY(出生日期)。
3. 对于C表,创建以下字段:CNO(课程号)、CNAME(课程名称)和TNO(教师号)。
4. 对于SC表,创建以下字段:SNO(学号)、CNO(课程号)和GRADE(成绩)。
5. 向S表中插入学生信息,向C表中插入课程信息,向SC表中插入选课信息。
6. 使用SQL语句实现以下查询:
1. 检索丁宝康老师所授课程的课程号和课程名
```sql
SELECT C.CNO, C.CNAME
FROM C
WHERE C.TNO = (
SELECT T.TNO
FROM T
WHERE T.TNAME = '丁宝康'
)
```
2. 检索年龄大于19岁的男学生的学号和姓名。
```sql
SELECT S.SNO, S.SNAME
FROM S
WHERE S.AGE > 19 AND S.SEX = '男'
```
3. 检索至少选修两门课程的学生学号。
```sql
SELECT SC.SNO
FROM SC
GROUP BY SC.SNO
HAVING COUNT(*) >= 2
```
4. 统计有学生选修的课程门数。
```sql
SELECT COUNT(DISTINCT SC.CNO)
FROM SC
```
5. 求选修课程号为2的学生的平均年龄。
```sql
SELECT AVG(S.AGE)
FROM S
WHERE S.SNO IN (
SELECT SC.SNO
FROM SC
WHERE SC.CNO = 2
)
```
6. 求马良老师所授课程的每门课程的学生平均成绩
```sql
SELECT SC.CNO, AVG(SC.GRADE)
FROM SC, C
WHERE SC.CNO = C.CNO AND C.TNO = (
SELECT T.TNO
FROM T
WHERE T.TNAME = '马良'
)
GROUP BY SC.CNO
```
7. 统计每门课程的学生选修人数(超过1人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列。
```sql
SELECT SC.CNO, COUNT(DISTINCT SC.SNO) AS NUM
FROM SC
GROUP BY SC.CNO
HAVING COUNT(DISTINCT SC.SNO) > 1
ORDER BY NUM DESC, SC.CNO ASC
```
8. 检索姓名以王打头的所有学生的姓名和年龄。
```sql
SELECT S.SNAME, S.AGE
FROM S
WHERE S.SNAME LIKE '王%'
```
9. 在SC中检索成绩为空值的学生学号和课程号。
```sql
SELECT SC.SNO, SC.CNO
FROM SC
WHERE SC.GRADE IS NULL
```
实验结论:
通过企业管理器建立学生选课数据库“StdentW101”,并使用SQL语句实现了多种查询操作,实现了对数据库的基本操作和实际应用。