经典学生-课程-成绩类SQL语句练习

Student(s_no,sname,age,sex) 学生表
Course(c_no,cname,t_no) 课程表
SC(s_no,c_no,score) 成绩表
Teacher(t_no,Tname) 教师表

 

 

1.查询“001”课程比“002”课程成绩高的所有学生的学号；
select a.s_no from SC a, SC b where a.c_no='001' and b.c_no='002 and a.s_no=b.s_no and a.score > b.score;

2.查询平均成绩大于60分的同学的学号和平均成绩；
select s_no,avg(score) from SC group by s_no having avg(score) > 60;

3.查询所有同学的学号、姓名、选课数、总成绩；
select a.s_no,a.sname,count(c_no) as CourseNum,sum(score) as ScoreSum from Student a left join SC b on a.s_no=b.s_no group by a.s_no;

4.查询姓“李”的老师的个数；
select count(Tname) from Teacher group by Tname having Tname like '李%';

5.查询没学过“叶平”老师课的同学的学号、姓名；
select s_no,sname from Student where s_no not in (select s_no from SC where c_no in (select c_no from Course where t_no in (select t_no from Teacher where Tname='叶平')));
select s_no,sname from Student where s_no not in (select distinct S_no from SC sc,Course c,Teacher t where sc.c_no=c.c_no and c.t_no=t.t_no and t.Tname='叶平');

6.查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
6. select s_no,sname from Student where s_no in (select a.s_no from SC a,SC b where a.c_no='001' and b.c_no='002' and a.s_no=b.s_no);

7.查询不同老师所教不同课程平均分从高到低显示
select avg(s.Score) as avgScore,c.Cno,c.Cname,t.Tno,t.Tname from Sc s,Course c,Teacher t where s.Cno=c.Cno and c.Tno=t.Tno group by c.Cno,c.Cname,t.Tname,t.Tname order by avgScore;

8.查询各科成绩前三名的记录:(不考虑成绩并列情况
8. select a.* from SC a where 3> (select count(*) from SC where a.c_no=c_no and score > a.score) order by a.c_no,a.score;
select a.* from Sc a where (select count(*) from Sc where a.cno = Sc.cno and a.score > Sc.score) < 3 order by a.Cno ;

9.查询男生人数
9. select count(*) from Student where sex='男';

10.查询同名同性学生名单，并统计同名人数
10.select distinct a.* from Student a,Student b where a.sname=b.sname and a.s_no != b.s_no;
select count(*) from (select distinct a.* from Student a,Student b where a.sname=b.sname and a.s_no != b.s_no) as c

11.学过叶平老师所有课的同学的学号，姓名

叶平老师教过的所有课：
select Cno from Course c,Teacher t where c.Tno=t.Tno and t.Tname='叶平'
学过叶平老师课的同学：
select Sno from Sc where Cno in (叶平老师教过的所有课)
学过叶平老师所有课的同学的学号
select Sno from Sc where Cno in (叶平老师教过的所有课) group by Sno having count(Cno)=(select count(叶平老师教过的所有课))
学过叶平老师所有课的同学的学号，姓名
select Sno,Sname from Student where Sno in (学过叶平老师所有课的同学的学号)

12.查询有课程成绩小于60分的同学的学号、姓名；

select Sno,Sname from Student where Sno in (select sc.Sno from Sc sc group by sc.Sno having MIN(sc.Score) < 60);

13.查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
select Sno,sname from Student where Sno in (select a.Sno from Sc a,Sc b where a.Cno='001' and b.Cno='002' and a.Sno=b.Sno and a.Score > b.Score);

查询没有学全所有课的同学的学号、姓名；
select Sno,Sname from Student where Sno in (select Sno from Sc group by Sno having count(Sc.Sno)<(select count(c.Cno) from Course c));

转载于:https://www.cnblogs.com/wangyi429111/p/8662589.html