初学Oracle数据库,遇到要求写查询语句,有两句我想不明白,想请教路过的神仙哥哥姐姐们。谢谢。
这是个学生名单与学生选课成绩,及带课老师的三个表。下面两个查询语句我写不出来。
①: 查询选修某课程的同学人数多于2人的教师(我理解的题目要求是:如果某老师所教授的课程学生数多于2人则查询该教师)
②: 查询成绩比该课程平均成绩高的成绩表(我理解的题目要求是:如果某个学生的某门成绩高于该课程的平均成绩,查询该所有高于平均成绩的成绩表)我一直搞 不懂,望赐教!!!!!!!感激不尽!!!
建表的步骤如下:
drop table S;
create table S(SNo smallint primary key ,SName char(10),SAge smallint check(SAge >0),SSex char(2) check (SSex in ('男','女')));
desc S;insert into S values(1,'李强',19,'男');
insert into S values(2,'刘丽',18,'女');
insert into S values(5,'张平',20,'男');
insert into S values(3,'王丽',19,'女');
insert into S values(4,'李军',20,'男');select * from S;drop table C;
create table C (CNo char(2) primary key ,CName char(10),CTeacher Char(10));
desc C;
insert into C values('K1','C语言','王华');
insert into C values('K5','数据库原理','程军');
insert into C values('K8','编译原理','程军');select * from C;drop table SC;
create table SC (SNo smallint,CNo char(2),CGrade smallint check (CGrade > 0 and CGrade <= 100),primary key (SNo,CNo));
desc SC;insert into SC values(1,'K1',83);
insert into SC values(2,'K1',65);
insert into SC values(5,'K1',92);
insert into SC values(2,'K5',90);
insert into SC values(5,'K5',86);
insert into SC values(5,'K8',76);
insert into SC values(3,'K8',56);
insert into SC values(3,'K1',80);
insert into SC values(4,'K5',76);
insert into SC values(4,'K8',85);select * from SC;
2 from c, sc
3 where c.cno = sc.cno
4 group by c.cteacher
5 having count(sc.sno) > 2
6 ;CTEACHER
----------
程军
王华SQL> select a.sno, a.cno, a.cgrade
2 from sc a, (select cno, avg(cgrade) cgrade from sc group by cno) b
3 where a.cno = b.cno
4 and a.cgrade >= b.cgrade
5 order by sno
6 ; SNO CNO CGRADE
--------------------------------------- --- ---------------------------------------
1 K1 83
2 K5 90
3 K1 80
4 K8 85
5 K1 92
5 K5 86
5 K8 767 rows selected
having count(Distinct SC.SNo) > 2;
才正确。虽然结果一样。嘿嘿