已知以下数据库:S表 Sid,Sname 为学生表 --- C表 Cid,Cname,Ctracher为课程表 --- SC表 Sid,Cid,Scgrade为选课关系及成绩表。其中,Sid为学号,Sname为姓名,Cid为课程号,Cname为课程名,Ctracher为任课教师,Scgrade为学生成绩。请用SQL语言实现:列出有二门以上(含两门)不几个课程的学生姓名及其平均成绩?
调试欢乐多
首先二门以上:SC表查询出不及格的,group by Sid,COUNT(Cid)>=2
后面就是三表查询了,只要上面查询出来的结果和S表连接下就能查询出来建议不要一句语句,因为涉及到IN,GROUP BY,查询速度不快,写个包来实现
select
(select ts.sname from s ts where ts.sid=b.sid) as sname,
(select avg(tsc.scgrade) as arg from sc tsc where tsc.sid=b.sid group by tsc.sid)
from(
select t.sid,count(*) as num from sc t where t.scgrade<60 group by t.sid )b where num>=2
CREATE TABLE Student
(
SID VARCHAR2(20),
SNAME VARCHAR2(20)
);INSERT INTO Student VALUES('001', '张三');
INSERT INTO Student VALUES('002', '李四');
INSERT INTO Student VALUES('003', '王五');
INSERT INTO Student VALUES('004', '菜六');DROP TABLE Course;
CREATE TABLE Course
(
CID VARCHAR2(20),
CNAME 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');DROP TABLE StudentCourse;
CREATE TABLE StudentCourse
(
SID VARCHAR2(20),
CID VARCHAR2(20),
SCGrade 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);
测试结果: