SQL Server练习题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