我学校的题,请教高手,
设有关系S(SNO,SNAME,AGE,SEX) 学生号,学生名字,年龄,性别
C(CNO,CNAME,TNAME) 课程号,课程名称,授课老师
SC(SNO,CNO,GR) 学生号,课程号,课程成绩(1)用关系代数和SQL语句检索年龄小于19的姓名和年龄;
(2)用关系代数和SQL语句检索选修了'数据库原理'的学生名和年龄;
(3)查缺少成绩的学生的学号和相应的课程号;
(4)查询各个课程号与响应的选课人数;
(5)求选修各门课程的认输及平局成绩(输出课程号、人数、平均成绩);
(6)求选修课程5门以上且都及格的学生号及总平均分,结果按平均分成绩降序;
(7)求选修数据库原理课程,并且成绩高于王清同学的学生学号和成绩。
设有关系S(SNO,SNAME,AGE,SEX) 学生号,学生名字,年龄,性别
C(CNO,CNAME,TNAME) 课程号,课程名称,授课老师
SC(SNO,CNO,GR) 学生号,课程号,课程成绩(1)用关系代数和SQL语句检索年龄小于19的姓名和年龄;
(2)用关系代数和SQL语句检索选修了'数据库原理'的学生名和年龄;
(3)查缺少成绩的学生的学号和相应的课程号;
(4)查询各个课程号与响应的选课人数;
(5)求选修各门课程的认输及平局成绩(输出课程号、人数、平均成绩);
(6)求选修课程5门以上且都及格的学生号及总平均分,结果按平均分成绩降序;
(7)求选修数据库原理课程,并且成绩高于王清同学的学生学号和成绩。
where x.学生名=y.学生名 and y.课程号=z.课程号 and z.课程名称='数据库原理'
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='数据库原理'
缺少成绩是指该学生没有上这个课还是指上了课并且成功为null值的?
2、select s.SNAME,s.AGE from s left jion C on s.SNO=C.CNO
where S.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
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='数据库原理')
select sno,sum(gr)/count(*) as '总平均分'
from sc
where gr>=60
group by sno
having count(*)>5
order by sum(gr)/count(*) desc
(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
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='数据库原理')
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='数据库原理'
每条语句写法太多,不一一写了.
随手打的,可能有手误.
感谢,fcuandy(长翅膀的不一定是天使,也可能是鸟人) 但我已经给完分了,不好意思,