首页 > 代码库 > oracle/sql.
oracle/sql.
3 table S(student), C(course), SC(StudentCourse):
s(sno,sname)
c(cno,cname,cteacher)
sc(sno, cno, scgrade)
Q:
- 找出没选过“liming”老师的所有学生姓名.
- 列出2门以上(含2门)不及格学生姓名及平均成绩.
- 既学过1号课程又学过2号课所有学生的姓名.
A:
1,
1 select sname from s join sc on(s.sno = sc.sno) join c on(sc.cno = c.cno) 2 where cteacher <> ‘liming‘
2.1,
1 select sname from s where sno in 2 ( 3 select sno from sc where scgrade < 60 group by sno having count(*) >= 2 4 )
2.2,
1 select avg(scgrade) from sc join s on(sc.sno = s.sno) 2 where sname in 3 ( 4 select sname from s where sno in 5 ( 6 select sno from sc where scgrade < 60 group by sno having count(*) >= 2 7 ) 8 ) group by sname
3,
1 select sname from s join sc on(s.sno = sc.sno) 2 where sno in 3 ( 4 select sno from sc where cno = 1 and sno in 5 (select sno from sc where cno = 2) 6 )
ref: oracle
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。