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,

select sname from s join sc on(s.sno = sc.sno) join c on(sc.cno = c.cno)
where cteacher <> 'liming'

　　2.1,

select sname from s where sno in
(
select sno from sc where scgrade < 60 group by sno having count(*) >= 2
)

　　2.2,

select avg(scgrade) from sc join s on(sc.sno = s.sno)
where sname in
(
    select sname from s where sno in
    (
        select sno from sc where scgrade < 60 group by sno having count(*) >= 2
    )
) group by sname

　　3,

select sname from s join sc on(s.sno = sc.sno)
where sno in
(
    select sno from sc where cno = 1 and sno in 
    (select sno from sc where cno = 2)
)

 

 ref: oracle