1. 找出沒有選修過“李明”老師講授課程的所有學(xué)生姓名
--實現(xiàn)代碼：
select sname from s
where not exists(
    select * from sc,c
    where sc.cno=c.cno
         and c.cteacher='李明'
          and sc.sno=s.sno)

2. 列出有二門以上（含兩門）不及格課程的學(xué)生姓名及其平均成績
--實現(xiàn)代碼：
select s.sno,s.sname,avg_scgrade=avg(sc.scgrade)
from s,sc,(
    select sno
    from sc
    where scgrade<60
    group by sno
    having count(distinct cno)>=2
)a where s.sno=a.sno and sc.sno=a.sno
group by s.sno,s.sname

3. 列出既學(xué)過“1”號課程，又學(xué)過“2”號課程的所有學(xué)生姓名
--實現(xiàn)代碼：
select s.sno,s.sname
from s,(
    select sc.sno
    from sc,c
    where sc.cno=c.cno
        and c.cname in('1','2')
    group by sno
    having count(distinct cno)=2
)sc where s.sno=sc.sno

4. 列出“1”號課成績比“2”號同學(xué)該門課成績高的所有學(xué)生的學(xué)號
--實現(xiàn)代碼：
select s.sno,s.sname
from s,sc sc1,sc sc2
    where sc1.cno='1'
        and sc2.sno='2'
        and sc1.cno=s.cno
        and sc1.scgrade>sc2.scgrade

5. 列出“1”號課成績比“2”號課成績高的所有學(xué)生的學(xué)號及其“1”號課和“2”號課的成績
--實現(xiàn)代碼：
select sc1.sno,[1號課成績]=sc1.scgrade,[2號課成績]=sc2.scgrade
from sc sc1,sc sc2
where sc1.cno='1'
        and sc2.cno='2'
        and sc1.sno=sc2.sno
        and sc1.scgrade>sc2.scgrade

trackback: http://tb.blog.csdn.net/trackback.aspx?postid=384993

[點擊此處收藏本文]   發(fā)表于 2005年05月31日 17:31:00

 十年等待 發(fā)表于2005-06-06 12:04 pm  ip: 61.186.252.*
你好：

1． 找出沒有選修過“李明”老師講授課程的所有學(xué)生姓名

select sname from s
where not exists(
select * from sc,c <=這里是否要加上s
where sc.cno=c.cno
and cname='李明' <=應(yīng)該是cteacher = '李明' 吧
and sc.sno=s.sno)

 
 十年等待 發(fā)表于2005-06-06 12:47 pm  ip: 61.186.252.*
列出“1”號課成績比“2”號同學(xué)該門課成績高的所有學(xué)生的學(xué)號
select s.sno,s.sname
from s,(
select sc1.sno
from sc sc1,c c1,sc sc2,c c2
where sc1.cno=c1.cno and c1.name='1'
and sc2.cno=c2.cno and c2.name='2'
<=這里好像牛頭不對馬嘴吧？？
<=是and sc2.cno=c2.cno and sc2.sno='2'才對吧
and sc1.scgrade>sc2.scgrade
)sc where s.sno=sc.sno

而且“1”號課，就我的理解是course的id,也就是cno,怎么會是cname,而且這里你還寫成了c1.name，這種態(tài)度要不得阿

我是新手，一般都是上網(wǎng)查資料的，還好以前學(xué)了一點點，不然對于那些一點都不會的人，不是被樓主害死了，要么不說，要么就要有認(rèn)真的態(tài)度，直言所至，請樓主諒解

 ghb 發(fā)表于2005-11-14 12:04 pm  ip: 61.236.10.*
/*
問題描述：
已知關(guān)系模式：
s (sno,sname） 學(xué)生關(guān)系。sno 為學(xué)號，sname 為姓名
c (cno,cname,cteacher) 課程關(guān)系。cno 為課程號，cname 為課程名，cteacher 為任課教師
sc(sno,cno,scgrade) 選課關(guān)系。scgrade 為成績

要求實現(xiàn)如下5個處理：
1． 找出沒有選修過“李明”老師講授課程的所有學(xué)生姓名
2． 列出有二門以上（含兩門）不及格課程的學(xué)生姓名及其平均成績
3． 列出既學(xué)過“1”號課程，又學(xué)過“2”號課程的所有學(xué)生姓名
4． 列出“1”號課成績比“2”號同學(xué)該門課成績高的所有學(xué)生的學(xué)號
5． 列出“1”號課成績比“2”號課成績高的所有學(xué)生的學(xué)號及其“1”號課和“2”號課的成績
*/

--create table s(sno varchar(10),sname varchar(20))
--create table c(cno varchar(10),cname varchar(20),cteacher varchar(20))
--create table sc(sno varchar(10),cno varchar(20),scgrade integer)

insert into s
select '1','ghb'
union all select '2','tw'
union all select '3','wkp'

insert into c
select '1','語文','李明'
union all select '2','數(shù)學(xué)','王了'
union all select '3','英語','其它'

insert into sc
select '1','2',50
union all select '1','3',52
union all select '2','1',80
union all select '2','2',90
union all select '2','3',59
union all select '3','1',100
union all select '3','2',59
union all select '3','3',70

--delete from sc where sno = '1' and cno = '1'
select *
from s
select *
from c
select *
from sc
-- 1． 找出沒有選修過“李明”老師講授課程的所有學(xué)生姓名
select sname
from s
where s.sno not in (select sc.sno from sc,c where sc.cno = c.cno and c.cteacher = '李明')

select sname from s
where not exists(
select * from sc,c
where sc.cno=c.cno
and c.cteacher='李明'
and sc.sno=s.sno)

-- 2． 列出有二門以上（含兩門）不及格課程的學(xué)生姓名及其平均成績
select s.sname,avg(sc.scgrade) as avgsc
from s,sc
where s.sno = sc.sno and s.sno in (select sno from sc where scgrade < 60 group by sno having count(sno) >= 2)
group by s.sname

select s.sname,avg(sc.scgrade) from s,sc where sc.scgrade<60 and s.sno=sc.sno group by s.sname having count(sc.scgrade)>=2

select s.sno,s.sname,avg_scgrade=avg(sc.scgrade)
from s,sc,(
select sno
from sc
where scgrade<60
group by sno
having count(distinct cno)>=2
)a where s.sno=a.sno and sc.sno=a.sno
group by s.sno,s.sname

-- 3． 列出既學(xué)過“1”號課程，又學(xué)過“2”號課程的所有學(xué)生姓名
select tem.sname
from
(
select s.sname
from s
where s.sno in(select sno from sc where cno = '1')
union all
select s.sname
from s
where s.sno in(select sno from sc where cno = '2')
)tem
group by tem.sname
having count(tem.sname) > 1

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

select s.sname from s,sc where s.sno=sc.sno and sc.cno=1 and exists (select b.sno from sc b where b.sno=sc.sno and b.cno=2)

select s.sno,s.sname
from s,(
select sc.sno
from sc,c
where sc.cno=c.cno
and c.cno in('1','2')
group by sno
having count(distinct c.cno)=2
)sc where s.sno=sc.sno

-- 4． 列出“1”號課成績比“2”號同學(xué)該門課成績高的所有學(xué)生的學(xué)號
select sc.sno
from sc
where cno = '1' and scgrade > (select scgrade from sc where sno = '2' and cno = '1') and sno <> '2'

select sc.sno from sc where sc.cno = '1' and exists (select * from sc b where b.sno = sc.sno and sc.scgrade > b.scgrade and b.sno = '2')

select s.sno from s,sc where s.sno=sc.sno and sc.cno=1 and sc.scgrade>(select b.scgrade from sc b where b.sno=sc.sno and b.cno=2)

select s.sno,s.sname
from s,sc sc1,sc sc2
where sc1.cno='1'
and sc2.sno='2'
and sc1.cno=s.cno
and sc1.scgrade>sc2.scgrade

select sc1.sno
from sc sc1,sc sc2
where sc1.cno = '1' and sc1.sno <> '2' and sc2.cno = '1' and sc2.sno = '2' and sc1.scgrade > sc2.scgrade and sc1.cno = sc2.cno

-- 5． 列出“1”號課成績比“2”號課成績高的所有學(xué)生的學(xué)號及其“1”號課和“2”號課的成績
select sc1.sno,sc1.scgrade,sc2.scgrade
from sc sc1,sc sc2
where sc1.sno = sc2.sno and sc1.cno = '1' and sc2.cno = '2' and sc1.scgrade > sc2.scgrade

select a.sno,a.scgrade from sc a where (a.cno=1 or a.cno=2)
and a.sno in
(select s.sno from s,sc where s.sno=sc.sno and sc.cno=1 and sc.scgrade>(select b.scgrade from sc b where b.sno=sc.sno and b.cno=2))

select sc1.sno,[1號課成績]=sc1.scgrade,[2號課成績]=sc2.scgrade
from sc sc1,sc sc2
where sc1.cno='1'
and sc2.cno='2'
and sc1.sno=sc2.sno
and sc1.scgrade>sc2.scgrade

drop table s
drop table c
drop table sc

 shenjane 發(fā)表于2006-02-07 3:13 pm  ip: 210.22.152.*
第四、第五題都有一些錯誤
select s.sn,sc1.scgrade as 課程1,sc2.scgrade as 課程2 from s,sc sc1,sc sc2 where
s.sno = sc1.sno and sc1.cno='0001'and sc2.cno='0002'
and sc1.sno=sc2.sno and sc1.scgrade<sc2.scgrade