数据库学习7-28
select * from STUDENT;
select * from STU_COURSE;
select * from SCLASS;
select * from MAJOR;
select * from COURSE;
1.查询所有学员姓名，年龄（显示整数，如24.75岁显示为24岁），所学专业名称，所属班级名称
select STU_NAME,
CLASS_NAME,
MAJOR_NAME,
round (months_between(sysdate , s.stu_birthday) / 12)
from STUDENT s, MAJOR m, SCLASS c
where s.STU_MAJOR = m.major_id
and s.stu_class = c.class_id;
2.查询专业编号，专业名称，专业人数
select MAJOR_ID,MAJOR_NAME,count(s.STU_ID)
from MAJOR m,STUDENT s
where m.MAJOR_ID=s.STU_MAJOR(+)
group by MAJOR_ID,MAJOR_NAME;
3.查询员学号，学员姓名，其平均分，并按照平均分由高至低排序
select s.STU_ID, STU_NAME, avgSCORE
from STUDENT s,
STU_COURSE c,
(select STU_ID, avg(SCORE) avgSCORE
from STU_COURSE
group by STU_ID) d
where s.STU_ID = c.STU_ID
and s.STU_ID = d.STU_ID
order by avgSCORE desc;
4.查询在701课程上，分数高于平均分的学生信息和考试分数
select s.*, SCORE
from STUDENT s, STU_COURSE c
where s.STU_ID = c.STU_ID
and c.COURSE_ID = 701
and SCORE > (select avg(SCORE) from STU_COURSE where c.COURSE_ID =701);
5.查询已修得学分超过6分的学生信息（所选考试分数不低于60视为修的学分）
select *
from student
where stu_id in (select s.stu_id
from student s, stu_course sc, course c
where s.stu_id = sc.stu_id
and sc.course_id = c.c_id
and sc.score >= 60
group by s.stu_id
having sum(c_score) > 6);
6.查询所有科目不及格（考试分数低于60）的学员的信息
select *
from STUDENT s where not exists (select 1
from stu_course sc
where s.stu_id = sc.stu_id
and SCORE >= 60);
7.查询所有科目平均分高于80的学生的学号，姓名，所在班级名称，平均分
select s.STU_ID, s.STU_NAME, c.CLASS_NAME, avg(t.score)
from STUDENT s, SCLASS c, STU_COURSE t
where s.stu_class = c.class_id and s.stu_id = t.stu_id having avg(t.score) > 80
group by s.STU_ID, s.STU_NAME, c.CLASS_NAME;
8.查询所有科目平均分比学生lu的平均分高的学生
select * from STUDENT where STU_ID in
(select s.STU_ID
from STUDENT s, STU_COURSE t
where s.stu_id = t.stu_id having
avg(score) > (select avg(score)
from STU_COURSE t, STUDENT s
where STU_NAME = \’Lu\’
and s.stu_id = t.stu_id)
group by s.STU_ID);
9.查询各班级名称，人数和考试总分（班级所有学生各项考试分数求和）
select CLASS_NAME, count(distinct s.STU_ID), sum(SCORE)
from STU_COURSE t, SCLASS c, STUDENT s
where s.stu_class = c.class_id(+)
and s.stu_id = t.stu_id group by CLASS_NAME;
10.查询班级人数高于各班级平均人数的班级编号，班级名称
select CLASS_ID, CLASS_NAME
from SCLASS c, STUDENT s
where s.stu_class = c.class_id having count(stu_id) >
(select avg(count(stu_id)) from STUDENT group by STU_CLASS)
group by CLASS_ID, CLASS_NAME;