急，SQL语句高手请进，在线等。

[vbnetstudent]

我学校的题,请教高手,
设有关系S(SNO,SNAME,AGE,SEX) 学生号,学生名字,年龄,性别
C(CNO,CNAME,TNAME) 课程号,课程名称,授课老师
SC(SNO,CNO,GR) 学生号,课程号,课程成绩

(1)用关系代数和SQL语句检索年龄小于19的姓名和年龄;
(2)用关系代数和SQL语句检索选修了'数据库原理'的学生名和年龄;
(3)查缺少成绩的学生的学号和相应的课程号;
(4)查询各个课程号与响应的选课人数;
(5)求选修各门课程的认输及平局成绩(输出课程号、人数、平均成绩)；
（6）求选修课程5门以上且都及格的学生号及总平均分，结果按平均分成绩降序；
（7）求选修数据库原理课程，并且成绩高于王清同学的学生学号和成绩。

[splory]

1.select 姓名,年龄 from S where <年龄<19

[splory]

1.select 姓名,年龄 from S where 年龄<19

[splory]

2.select 学生名字,年龄 from S x,sc y,C z
where x.学生名=y.学生名 and y.课程号=z.课程号 and z.课程名称='数据库原理'

[gahade]

1.
select * from s where age<19
2.
select sname,age
from s
inner join sc on s.sno=sc.sno
inner join c on sc.cno=c.cno
where c.cname='数据库原理'

[gahade]

3没看明白.
缺少成绩是指该学生没有上这个课还是指上了课并且成功为null值的?

[allright_flash]

1、select SNAME,AGE from s where AGE<19
2、select s.SNAME,s.AGE from s left jion C on s.SNO=C.CNO
where S.AGE<19

[playwarcraft]

我喜歡等別人做好了,然後拿來抄一下...^^

[vbnetstudent]

谢谢各位，小弟急用，请各位高手帮忙。

[allright_flash]

1、select SNAME,AGE from s where AGE<19
2、select s.SNAME,s.AGE from s left jion C on s.SNO=C.CNO
where C.CNAME='数据库原理'
3、select SNO,CNO from SC
where GR='' or GR is null
4、select CNO,count(SNO) fron SC group by CNO
5、select SNO,count(CNO),avg(GR) from SC

[gahade]

4.
select cno,count(*) as '人数'
from sc
group by cno
5.
select cno,count(*) as '人数',avg(gr) as '平均成绩'
from sc
group by cno
6.
select sno,sum(gr)/count(*) as '总平均分'
from sc
where gr>=60
group by sno
having count(*)>5
order by sum(gr)/count(*)
7.
select sc.sno,sc.gr
from sc
inner join c on sc.cno=c.cno
where c.cname='数据库原理'
and sc.gr>(select gr from sc
inner join c on sc.cno=c.cno
inner join s on sc.sno=s.sno
where s.sname='王清'
and c.cname='数据库原理')

[gahade]

6.
select sno,sum(gr)/count(*) as '总平均分'
from sc
where gr>=60
group by sno
having count(*)>5
order by sum(gr)/count(*) desc

[dawugui]

1、select sname,age from s where age < 19

2、select sname,age from s where sno in (select sc.cno from sc,c where sc.cno = c.cno and c.cname = '数据库原理')

3、既然是选修，又不是全修，何来缺少不缺少，此题有误。

4、select cno , count(*) from sc group by cno

5、select cno,count(*),avg(gr) from sc group by cno

6、select sno,count(*),avg(gr) as gr from sc group by sno having count(*) >=5 order by gr desc (此处要每门课>=0.6*科目总分,有难度,我不会)

7、select sno,gr from sc , c ,
(select sno,gr from sc,c,s where s.sname = '王清' and s.sno=sc.sno and sc.cno=c.cno and c.cname = '数据库原理') wq
where sc.cno = c.cno and c.cname = '数据库原理' and sc.gr > wq.gr

[allright_flash]

1、select SNAME,AGE from s where AGE<19
2、select s.SNAME,s.AGE from s left jion C on s.SNO=C.CNO
where C.CNAME='数据库原理'
3、select SNO,CNO from SC
where GR='' or GR is null
4、select CNO,count(SNO) fron SC group by CNO
5、select cno,count(*) ,avg(gr)
from sc
group by cno
6、select sno,sum(gr)/count(*) as sum_ccount
from sc
where gr>=60
group by sno
having count(*)>5
order by sum_ccount
7、select sc.sno,sc.gr
from sc,c
where sc.cno=c.cno
and c.cname='数据库原理'
and sc.gr>(
select sc.gr from sc,c,s where
sc.cno=c.cno and sc.sno=s.sno
and s.sname='王清' and c.cname='数据库原理')

[fcuandy]

1.
select * from s where age<19
2.
select s.*
from s
inner join sc
on sc.sno=s.sno
inner join c
on sc.cno=c.cno
where c.cname='数据库原理'
3
select distinct s.sno,c.cno from s
cross join c
left join sc
on sc.sno=s.sno and sc.cno=c.cno
where sc.sno is null or sc.cno is null
4
select c.cno,b.cnt
from c
inner join
(select cno,count(*) cnt from sc group by cno) b
on c.cno=b.cno
5
select c.cno,b.cnt,b.agr
from c
inner join
(select cno,count(*) cnt,avg(gr) agr from sc group by cno) b
on c.cno=b.cno
6
select s.sno,b.agr
(select sno,avg(gr) agr from sc group by sno having count(*)>=5 and count(*)=count(case when gr>=60 then 1 else null end)) b
order by b.agr
7
select s.* ,sc.*
from s
inner join sc
on sc.sno=s.sno
inner join c
on sc.cno=c.cno
inner join sc d
on d.gr<sc.dr
inner join s x
on x.sno=d.sno
inner join c y
on y.cno=d.cno
where c.cname='数据库原理' and x.sname='王清' and y.cname='数据库原理'


每条语句写法太多,不一一写了.
随手打的,可能有手误.

[vbnetstudent]

非常感谢，
感谢，fcuandy(长翅膀的不一定是天使，也可能是鸟人) 但我已经给完分了，不好意思，

[fcuandy]

呵呵,0分的贴子又不是没回过.回了没分这也不是头一回.