select 学生.学号,max(学生.姓名) 姓名,avg(选课.成绩) 平均分,count(选课.课程号) 选课门数 from 学生,选课,课程 where 学生.学号=选课.学号 and 选课.课程号=课程.课程号 group by 学生.学号 having count(选课.课程号)>=5 order by 平均分 desc
/*学生(学号,姓名,性别,年龄,系) 课程(科称号,课程名称) 选课(学号,课程号,成绩) 将选课在5门以上(包括5门)的学生的学号,姓名,平均分和选课门数按平均分降序排序*/ --问题一: create table student( student_id number primary key,student_name varchar2(20),sex varchar2(4), age number,department varchar2(20)); create table course(course_id number primary key,course_name varchar2(20)); create table ch_course(student_id number,course_id number, degrees number,foreign key(student_id) references student(student_id)); DROP TABLE CH_COURSE select * from student ; select * from course ; select * from ch_course; ---------------答案如下,注意此问题旨在考核分组函数及having的使用方法 select t1.student_id , t1.student_name , avg(t3.degrees) , count(*) as "选课门数" from student t1, course t2, ch_course t3 where t1.student_id = t3.student_id and t2.course_id = t3.course_id group by t1.student_id,student_name having count(*)>=5
select t1.student_id , t1.student_name , avg(t3.degrees) avg_degress, count(*) as "选课门数" from student t1, course t2, ch_course t3 where t1.student_id = t3.student_id and t2.course_id = t3.course_id group by t1.student_id,student_name having count(*)>=5 order by avg_degress 忘记加排序了,补上!
select 学生.学号,max(学生.姓名) 姓名,avg(选课.成绩) 平均分,count(选课.课程号) 选课门数
from 学生,选课,课程
where 学生.学号=选课.学号 and 选课.课程号=课程.课程号
group by 学生.学号
having count(选课.课程号)>=5
order by 平均分 desc
课程(科称号,课程名称)
选课(学号,课程号,成绩)
将选课在5门以上(包括5门)的学生的学号,姓名,平均分和选课门数按平均分降序排序*/
--问题一:
create table student(
student_id number primary key,student_name varchar2(20),sex varchar2(4),
age number,department varchar2(20));
create table course(course_id number primary key,course_name varchar2(20));
create table ch_course(student_id number,course_id number,
degrees number,foreign key(student_id) references student(student_id));
DROP TABLE CH_COURSE
select * from student ;
select * from course ;
select * from ch_course;
---------------答案如下,注意此问题旨在考核分组函数及having的使用方法
select t1.student_id ,
t1.student_name ,
avg(t3.degrees) ,
count(*) as "选课门数"
from student t1, course t2, ch_course t3
where t1.student_id = t3.student_id
and t2.course_id = t3.course_id
group by t1.student_id,student_name
having count(*)>=5
t1.student_name ,
avg(t3.degrees) avg_degress,
count(*) as "选课门数"
from student t1, course t2, ch_course t3
where t1.student_id = t3.student_id
and t2.course_id = t3.course_id
group by t1.student_id,student_name
having count(*)>=5
order by avg_degress
忘记加排序了,补上!