已知以下数据库:S表 Sid,Sname 为学生表 --- C表 Cid,Cname,Ctracher为课程表 --- SC表 Sid,Cid,Scgrade为选课关系及成绩表。其中,Sid为学号,Sname为姓名,Cid为课程号,Cname为课程名,Ctracher为任课教师,Scgrade为学生成绩。请用SQL语言实现:列出有二门以上(含两门)不几个课程的学生姓名及其平均成绩?
调试欢乐多
(
SID VARCHAR2(20),
NAME VARCHAR2(20)
);INSERT INTO Student VALUES('001', '张三');
INSERT INTO Student VALUES('002', '李四');
INSERT INTO Student VALUES('003', '王五');
INSERT INTO Student VALUES('004', '菜六');CREATE TABLE Course
(
CID VARCHAR2(20),
NAME VARCHAR2(20),
CTeacher VARCHAR2(20)
);INSERT INTO Course VALUES('0001', '语文', '教师1');
INSERT INTO Course VALUES('0002', '数学', '教师2');
INSERT INTO Course VALUES('0003', '英文', '教师3');
INSERT INTO Course VALUES('0004', '物理', '教师4');
INSERT INTO Course VALUES('0005', '化学', '教师5');
INSERT INTO Course VALUES('0006', '政治', '教师6');
CREATE TABLE StudentCourse
(
SID VARCHAR2(20),
CID VARCHAR2(20),
Grade NUMBER(4)
);INSERT INTO StudentCourse VALUES('001', '0001', 60);
INSERT INTO StudentCourse VALUES('001', '0002', 70);
INSERT INTO StudentCourse VALUES('001', '0003', 80);
INSERT INTO StudentCourse VALUES('001', '0004', 90);
INSERT INTO StudentCourse VALUES('001', '0005', 60);
INSERT INTO StudentCourse VALUES('001', '0006', 70);INSERT INTO StudentCourse VALUES('002', '0001', 40);
INSERT INTO StudentCourse VALUES('002', '0002', 50);
INSERT INTO StudentCourse VALUES('002', '0003', 70);
INSERT INTO StudentCourse VALUES('002', '0004', 80);
INSERT INTO StudentCourse VALUES('002', '0005', 90);
INSERT INTO StudentCourse VALUES('002', '0006', 70);INSERT INTO StudentCourse VALUES('003', '0001', 60);
INSERT INTO StudentCourse VALUES('003', '0002', 50);
INSERT INTO StudentCourse VALUES('003', '0003', 50);
INSERT INTO StudentCourse VALUES('003', '0004', 60);
INSERT INTO StudentCourse VALUES('003', '0005', 80);
INSERT INTO StudentCourse VALUES('003', '0006', 90);INSERT INTO StudentCourse VALUES('004', '0001', 60);
INSERT INTO StudentCourse VALUES('004', '0002', 70);
INSERT INTO StudentCourse VALUES('004', '0003', 80);
INSERT INTO StudentCourse VALUES('004', '0004', 90);
INSERT INTO StudentCourse VALUES('004', '0005', 80);
INSERT INTO StudentCourse VALUES('004', '0006', 90);
查询:
(
select sid,sname,Round(avg(scgrade),2) avgScore
from
(
select aa.sid,aa.sname,cc.cid,cc.cname,bb.scgrade
from s aa
left join sc bb on aa.sid=bb.sid
left join c cc on bb.cid=cc.cid
)group by sid,sname
order by sid) mm,
(
select count(1) notCounts,aa.sid,aa.sname
from s aa
left join sc bb on aa.sid=bb.sid
left join c cc on bb.cid=cc.cid
where scgrade<60
group by aa.sid,aa.sname
order by sid
)kk
where mm.sid=kk.sid and kk.notCounts>=2;