--8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名; Select S#, Sname from (select Student.S#, Student.Sname, score, (select score from SC SC_2 where SC_2.S# = Student.S#and SC_2.C# = '002') score2 from Student, SC where Student.S# = SC.S# andC# ...
select c.sno "学号",avg(c.score) "平均成绩" from sc c group by c.sno having avg(c.score) > 60; 3、查询所有同学的学号、姓名、选课数、总成绩; select * from sc; select *from student; select a.*,s.sname from (select sno,sum(score),count(cno) from sc group by sno) a ,student...
--查询所有同学的学号、姓名、选课数、总成绩; [b]外连接[/b] 包括没有分数的新人 select t1.sno, t1.sname, count(t3.sno), sum(t3.grade) from student t1 ,course t2, sc t3 where t1.sno=t3.sno(+) --and t2.cno = t3.cno ...
--- Select t2.sname,t2.s,t3.e from student t2,(select t1.s,t1.e from (select s,avg(score) e from sc group by s) t1 Where t1.e<60 ) t3 Where t2.s=t3.s --5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩--- Select t.s,t.sname, count(c),sum(score) Fr...
1、查询“c001”课程比“c002”课程成绩高的所有学生的学号; 2、查询平均成绩大于60 分的同学的学号和平均成绩; 3、查询所有同学的学号、姓名、选课数、总成绩; 4、查询姓“刘”的老师的个数; 5、查询没学过“谌燕”老师课的同学的学号、姓名; 6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、...
6、查询所有同学的学号、姓名、选课数、总成绩；SELECT stu.Sno, stu.Sname, COUNT(Cno), SUM(score)FROM Student stu, SC WHERE stu.Sno = Sc.sno GROUP BY stu.sno, stu.Sname 7、查询没有学全所有课的同学的学号、姓名；SELECT Sno, Sname FROM SC WHERE Sno NOT IN (SELECT Sno FROM ...
查询所有同学的学号、姓名、选课数、总成绩;Sc表和student表有共同的字段cno,而需要查询该字段数据的条数,为避免冲突,先查询Student表除了字段cno的所有数据并其个别名(select sname,sno from student) b 再和sc表连接查询字段cno数据的条数 selecta.sno,b.sname,count(cno), sum(score)from sc a,(select ...
3、查询所有同学的学号、姓名、选课数、总成绩; select a.*,s.sname from (select sno,sum(score),count(cno) from sc group by sno) a ,student s where a.sno=s.sno 4、查询姓“刘”的老师的个数; select count(*) from teacher where tname like '刘%';已...
5、 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩5.1、 查询所有成绩的(不含缺考的)。select s.s no ,s.s name,c.co unt_cno ,c.sum_scorefrom stude nt s,(select sno ,co un t(c no count_cno ,sum(score sum_score from sc group by sno order by sno c ...
2、在使用视图进行查询时，是通过View 中的SQL 查询原表的数据，不能提高查询性能。在通过物化视图进行查询时，直接读取物化视图的数据，提高查询性能。3、对普通视图应用更新、删除操作会影响原表的数据。只读物化视图不能进行DML 操作。可更新物化视图可进行DML 操作，并且在刷新时操作会被推送回原表。可写物化视图...