oracle/sql.

3 table S(student), C(course), SC(StudentCourse):

s(sno,sname)

c(cno,cname,cteacher)

sc(sno, cno, scgrade)

Q:

  1. 找出没选过“liming”老师的所有学生姓名.
  2. 列出2门以上(含2门)不及格学生姓名及平均成绩.
  3. 既学过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

oracle/sql.,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。