在Student库中我建立三个表
create table T_student_info
(
Student_no char(6) not null primary key,
Student_name char(20) not null,
Sex char(2),
Birth datetime,
Enter_date int,
Address char(50)
)
create table T_course_info
(
Course_no char(8) not null primary key,
Course_name char(50) not null,
Credit int,
Classhour int
)
create table T_student_scores
(
Course_no char(8) not null primary key references T_course_info(Course_no),
Student_no char(6) not null references T_student_info(Student_no),
Ordinary_scores decimal(7,2),
End_scores decimal(7,2),
Total_scores decimal(7,2)
)
--需要统计每门课程的选课人数/最高分/平均分将统计结果保存在表T_total中
create table T_student_info
(
Student_no char(6) not null primary key,
Student_name char(20) not null,
Sex char(2),
Birth datetime,
Enter_date int,
Address char(50)
)
create table T_course_info
(
Course_no char(8) not null primary key,
Course_name char(50) not null,
Credit int,
Classhour int
)
create table T_student_scores
(
Course_no char(8) not null primary key references T_course_info(Course_no),
Student_no char(6) not null references T_student_info(Student_no),
Ordinary_scores decimal(7,2),
End_scores decimal(7,2),
Total_scores decimal(7,2)
)
--需要统计每门课程的选课人数/最高分/平均分将统计结果保存在表T_total中
SELECT
b.Course_no,
b.Course_no,
[选课人数]=COUNT(*),
[最高分]=MAX(Ordinary_scores),
[平均分]=AVG(Ordinary_scores)
FROM T_student_scores AS a
INNER JOIN T_course_info AS b ON a.Course_no=b.Course_no
GROUP BY b.Course_no,b.Course_no
End_scores 期末成绩