# =======================================

(Select ……);//查询语句

1. 查询各个系学生选修 1001 课程的平均成绩。(同作业三 T3_2_1)

create view T4_1 as
from student,sc
where student.sno=sc.sno
and cno='1001'
group by sdno 

create view T4_1_1 as
from student,sc
where student.sno=sc.sno
and cno='1001'
group by sdno
order by sdno;

2. 查询各个系学生选修课程的总门数。(同作业三 T3_2_2)

create view T4_2 as
select sdno,count(distinct cno) count
from student,sc
where student.sno=sc.sno
group by sdno;

3. 查询‘cs’系且选修课程的最低成绩大于等于 60 分的学生信息。(同作业三 T3_2_3)

create view T4_3 as
select student.sno,sname,ssex,sage,student.sdno
from student,sc,dept
where student.sno=sc.sno
and student.sdno=dept.dno
and dname='cs'
group by student.sno,sname,ssex,sage,student.sdno
having min(grade)>=60;

create view T4_3_1 as
select student.sno,sname,ssex,sage,student.sdno
from student,dept
where student.sdno=dept.dno
and dname='cs'
and sno in( select sno
from sc
group by sno
);

create view T4_3_2 as
select sno,sname,ssex,sage,sdno
from student
where sdno=( select dno
from dept
where dname='cs'
)
and sno not in( select sno
from sc
);

4. 列出学校开设的课程总数。(同作业三 T3_2_4)

create view T4_4 as
select count(cno) count
from course;

5. 列出每门课程的平均分。(同作业三 T3_2_6)

create view T4_5 as
from sc
group by cno
order by cno;

create view T4_5_1 as
from sc
group by cno
order by cno;

6. 查询各个系学生选修每门课程的平均成绩。

create view T4_6 as
from student,sc
where student.sno=sc.sno
group by sdno,cno
order by sdno,cno;

7. 列出成绩高于课程平均成绩的所有学生信息，并列出相关详细信息。

create view T4_7 as
select student.sno,sname,ssex,sage,sdno
from student
where student.sno in( select c1.sno
from sc c1
from sc c2
where c1.cno=c2.cno
group by cno
)
);

create view T4_7_1 as
select distinct student.sno,sname,ssex,sage,sdno
from student,sc c1
where student.sno=c1.sno
from sc c2
where c1.cno=c2.cno
group by c2.cno
);


create view T4_7_2 as
from student,sc,course,( select cno,avg(grade) avg from sc group by cno ) t1
where student.sno=sc.sno
and sc.cno=course.cno
and sc.cno=t1.cno
and grade>t1.avg;

8. 列出每个系学生的详细信息。

create view T4_8 as
select student.sdno,dname,sno,sname,ssex,sage
from student,dept
where student.sdno=dept.dno
order by sdno;

9. 列出每门课程的详细授课信息。

create view T4_9 as
select cno,cname,ccredit,tno,tname,tsex,prof,tdno
from course,teacher
where teacher.tno=course.ctno;

10. 列出每门课程的最低成绩和最高成绩。

create view T4_10 as
from sc
group by cno;

create view T4_10_1 as
from course,sc
where course.cno=sc.cno
group by sc.cno,cname
order by sc.cno;

11. 列出所有学生选课的详细信息，并按每位学生选课成绩降序显示。

create view T4_11 as
from sc
order by sno,grade desc;

create view T4_11_1 as
from student,sc,course
where student.sno=sc.sno(+)
and sc.cno=course.cno(+)
order by sno,grade desc;

12. 列出同一系中选修相同课程的学生的详细信息。

create view T4_12 as
select distinct student.sdno,sc.cno,student.sno,sname,ssex,sage
from student,sc,(select sdno,cno,student.sno from student,sc where student.sno=sc.sno) t1
where student.sno=sc.sno
and student.sdno=t1.sdno
and sc.cno=t1.cno
and student.sno!=t1.sno;

create view T4_12_1 as
select *
from student s1
where exists( select *
from student s2
where s1.sno!=s2.sno
and s1.sdno=s2.sdno
and exists( select *
from sc c1
where s1.sno=c1.sno
and exists( select *
from sc c2
where c1.sno!=c2.sno
and s2.sno=c2.sno
and c1.cno=c2.cno
)
)
);

create view T4_12_2 as
select *
from student s1
where exists( select *
from sc c1
where s1.sno=c1.sno
and exists( select *
from student s2
where s1.sno!=s2.sno
and s1.sdno=s2.sdno
and exists( select *
from sc c2
where s2.sno=c2.sno
and c1.cno=c2.cno
)
)
);

13. 列出学生选修课程人数最多的三门课程。

create view T4_13 as
select cno,count
from (select cno,count(sno) count from sc group by cno order by count desc)
where rownum<=3;

create view T4_13_1 as
select t1.cno,t1.cname,t1.count
from ( select sc.cno,cname,count(sno) count
from course,sc
where course.cno=sc.cno
group by sc.cno,cname
order by count desc
)t1
where rownum<=3;

14. 统计每个学生的学分，列出学号、姓名、学生。（要求：不及格课程无学分）

create view T4_14 as
select student.sno,sname,sum(ccredit) sum
from student,sc,course
where student.sno=sc.sno
and sc.cno=course.cno
group by student.sno,sname
order by student.sno;

15. 某高校学士学位授予条件为：每门课程必须及格且平均成绩达 75 分以上。请查询 Cs 系可授予学士学位的学生名单。

create view T4_15 as
select student.sdno,dname,student.sno,sname
from dept,student,sc
where dept.dno=student.sdno
and student.sno=sc.sno
and dname='cs'
group by student.sno,sname,student.sdno,dname
having ( min(grade)>60 and avg(grade)>75 );

create view T4_15_1 as
from student,sc,dept,(select sno,avg(grade) avg from sc group by sno having avg(grade)>75) t1
where student.sno=sc.sno
and student.sdno=dept.dno
and student.sno=t1.sno
and dname='cs'
group by student.sdno,dname,student.sno,sname,t1.avg
order by student.sdno,student.sno;


16. 建立一张统计信息表 DEPTage(sdept,avage)，要求存放每个系学生的平均年龄。 (注意是建表，而不是建立视图)

create table DEPTage(sdept,avage) as
select sdno,avg(sage)
from student
group by sdno
order by sdno;

17. 查询只选 1 门课且成绩不及格学生的选课记录。

create view T4_17 as
from sc
and sno in( select sno
from sc
group by sno
having count(cno)=1
);

18. 查询 cs 系只选 1 门课且成绩不及格学生的选课记录。

create view T4_18 as
from student,sc,dept
where student.sno=sc.sno
and student.sdno=dept.dno
and dname='cs'
and sc.sno in ( select sno
from sc
group by sno
having count(cno)=1
);

# 作业一         作业二          作业三           作业四           作业五

07-10 478
09-27
10-21 5703
05-01 4344
11-27
04-10 4736
06-17 3288
07-08 1012

### “相关推荐”对你有帮助么？

• 非常没帮助
• 没帮助
• 一般
• 有帮助
• 非常有帮助

AN_drew

¥2 ¥4 ¥6 ¥10 ¥20

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、C币套餐、付费专栏及课程。