SQL基础《学生成绩表》项目让你从入门到精通-50个sql语句.docVIP

————————————————————————————————————————————————————— SQL基础《学生成绩表》项目让你从入门到精通-50个sql语句 Student(Sno,Sname,Sage,Ssex) 学生表 S Course(Cno,Cname,Tno) 课程表 C SC(Sno,Cno,score) 成绩表 Teacher(Tno,Tname) 教师表 T 问题： 1、查询“001”课程比“002”课程成绩高的所有学生的学号； select a.Sno from (select sno,score from SC where Cno=#39;001#39;) a , (select sno,score from SC where Cno=#39;002#39;) b where a.score gt; b.score and a.sno = b.sno; //2张表比较，1张是001课程表，1张是002课程表 2、查询平均成绩大于60分的同学的学号和平均成绩； select Sno , avg(score) from sc group by Sno having avg(score) gt;60; Student(Sno,Sname,Sage,Ssex) 学生表 S Course(Cno,Cname,Tno) 课程表 C SC(Sno,Cno,score) 成绩表 Teacher(Tno,Tname) 教师表 T 3、查询所有同学的学号、姓名、选课数、总成绩； select S.Sno , S.Sname , count(SC.Cno) , sum(score) from S left Outer join SC on S.Sno = SC.Sno group by S.Sno , Sname 4、查询姓“李”的老师的个数； select count(distinct(Tname)) from T where Tname like #39;李%#39;; 5、查询没学过“叶平”老师课的同学的学号、姓名； 1找出叶平老师的课程号 select S.Sno , S.Sname from S where Sno not in (select distinct( SC.Sno) from SC , C , T where SC.Cno = C.Cno and T.Tno = C.Tno and T.Tname = #39;叶平#39; ); 6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； select S.Sno , S.Sname from S , SC where S.Sno=SC.Sno and SC.Cno=#39;001#39; and exists( Select * from SC as SC_2 where SC_2.Sno=SC.Sno and SC_2.Cno=#39;002#39;); 7、查询学过“叶平”老师所教的所有课的同学的学号、姓名； select Sno,Sname from Student where Sno in (select Sno from SC ,Course ,Teacher where SC.Cno=Course.Cno and Teacher.Tno=Course.Tno and Teacher.Tname=#39;叶平#39; group by Sno having count(SC.Cno) = ); 8、查询课程编号“002”的成绩比课程编号“001??课程低的所有同学的学号、姓名； Select Sno,Sname from (select Student.Sno,Student.Sname,score ,(select score from SC SC_2 where SC_2.Sno=Student.Sno and SC_2.Cno=#39;002#39;) score2 from Student,SC where Student.Sno=SC.Sno and