SQL Server練習題2

server

題目2

問題描述：
已知關係模式：
S (SNO,SNAME）                       學生關係。SNO 為學號，SNAME 為姓名
C (CNO,CNAME,CTEACHER)  課程關係。CNO 為課程號，CNAME 為課程名，CTEACHER 為任課教師
SC(SNO,CNO,SCGRADE)        選課關係。SCGRADE 為成績

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

1. 找出沒有選修過“李明”老師講授課程的所有學生姓名
--實現代碼：
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. 列出有二門以上（含兩門）不及格課程的學生姓名及其平均成績
--實現代碼：
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. 列出既學過“1”號課程，又學過“2”號課程的所有學生姓名
--實現代碼：
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”號同學該門課成績高的所有學生的學號
--實現代碼：
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”號課成績高的所有學生的學號及其“1”號課和“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

Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=384993

[點擊此處收藏本文]   發表於 2005年05月31日 17:31:00

 十年等待 發表於2005-06-06 12:04 PM  IP: 61.186.252.*
你好：

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

SELECT SNAME FROM S
WHERE NOT EXISTS(
SELECT * FROM SC,C <=這裡是否要加上S
WHERE SC.CNO=C.CNO
AND CNAME='李明' <=應該是CTEACHER = '李明' 吧
AND SC.SNO=S.SNO)

 
 十年等待 發表於2005-06-06 12:47 PM  IP: 61.186.252.*
列出“1”號課成績比“2”號同學該門課成績高的所有學生的學號
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，這種態度要不得阿

我是新手，一般都是上網查資料的，還好以前學了一點點，不然對於那些一點都不會的人，不是被樓主害死了，要麼不說，要麼就要有認真的態度，直言所至，請樓主諒解

 ghb 發表於2005-11-14 12:04 PM  IP: 61.236.10.*
/*
問題描述：
已知關係模式：
S (SNO,SNAME） 學生關係。SNO 為學號，SNAME 為姓名
C (CNO,CNAME,CTEACHER) 課程關係。CNO 為課程號，CNAME 為課程名，CTEACHER 為任課教師
SC(SNO,CNO,SCGRADE) 選課關係。SCGRADE 為成績

要求實現如下5個處理：
1． 找出沒有選修過“李明”老師講授課程的所有學生姓名
2． 列出有二門以上（含兩門）不及格課程的學生姓名及其平均成績
3． 列出既學過“1”號課程，又學過“2”號課程的所有學生姓名
4． 列出“1”號課成績比“2”號同學該門課成績高的所有學生的學號
5． 列出“1”號課成績比“2”號課成績高的所有學生的學號及其“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','數學','王了'
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． 找出沒有選修過“李明”老師講授課程的所有學生姓名
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． 列出有二門以上（含兩門）不及格課程的學生姓名及其平均成績
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． 列出既學過“1”號課程，又學過“2”號課程的所有學生姓名
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”號同學該門課成績高的所有學生的學號
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”號課成績高的所有學生的學號及其“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 發表於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