SQL数据库查询
在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中
[解决办法]
--Ordinary_scores这字段为分数字段?
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
[解决办法]
select cource_name as 课程名称,count(*) as 选课人数,max(total) as 最高成绩,sum(total)/count(*) as 平均成绩 from T_student_scores a,T_course_info b where a.Course_no =b.Course_no
