我学校的题,请教高手,
设有关系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)求选修数据库原理课程,并且成绩高于王清同学的学生学号和成绩。
select s.sname, s.age from s, c, sc where s.sno = sc.sno and c.cno = sc.cno and c.tname = '数据库原理'
select sc.sno, sc.cno from sc, s where sc.sno = c.sno and s.sno not in (select distinct sc.sno from sc )
select c.cno, count(sc.sno) from c, sc where c.cno = sc.cno group by c.cno
select c.cno, count(sc.sno), avg(sc.gr) from c, sc where c.cno = sc.cno group by c.cno
select s.sno, avg(sc.gr) as a from s, c, sc where s.sno = sc.sno and c.cno = sc.cno and count(sc.cno) > 5 order by a
select s.sno, sc.gr from s, c, sc where s.sno = sc.sno and c.cno = sc.cno and c.tname = '数据库原理' and sc.gr > (select sc.gr from s, sc where s.sno = sc.sno and c.sname = '王清')
2. select sname,age from s,c,sc where s.sno=sc.sno and sc.cno=c.cno and c.cname='數據庫原理'
3. select sno,cno from s,c,sc where s.sno=sc.sno and sc.cno=c.cno and gr is null
4. select cno,count(*) rs from sc group by cno
5. select cno,count(*) rs,avg(gr) gr from sc
6. select sno,b.avggr from s ,(select sno,avg(gr) avggr from sc where gr>=60 group by sno) b where s.sno=b.sno order by b.avggr desc
7. select sname,age from s,c,sc where s.sno=sc.sno and sc.cno=c.cno and c.cname='數據庫原理' and gr>(select gr from s,c,sc where s.sno=sc.sno and sc.cno=c.cno and c.cname='數據庫原理' and s.name='王清')