50道SQL练习题及答案与详细分析

2024-07-14 12:29| 来源: 网络整理| 查看: 265

查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数。因为需要全部的学生信息，则需要在sc表中得到符合条件的SId后与student表进行join，可以left join 也可以 right join

select * from Student RIGHT JOIN ( select t1.SId, class1, class2 from (select SId, score as class1 from sc where sc.CId = '01')as t1, (select SId, score as class2 from sc where sc.CId = '02')as t2 where t1.SId = t2.SId AND t1.class1 > t2.class2 )r on Student.SId = r.SId; select * from ( select t1.SId, class1, class2 from (SELECT SId, score as class1 FROM sc WHERE sc.CId = '01') AS t1, (SELECT SId, score as class2 FROM sc WHERE sc.CId = '02') AS t2 where t1.SId = t2.SId and t1.class1 > t2.class2 ) r LEFT JOIN Student ON Student.SId = r.SId;

1.1 查询同时存在" 01 “课程和” 02 "课程的情况

select * from (select * from sc where sc.CId = '01') as t1, (select * from sc where sc.CId = '02') as t2 where t1.SId = t2.SId;

1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )

select * from (select * from sc where sc.CId = '01') as t1 left join (select * from sc where sc.CId = '02') as t2 on t1.SId = t2.SId; select * from (select * from sc where sc.CId = '02') as t2 right join (select * from sc where sc.CId = '01') as t1 on t1.SId = t2.SId;

1.3 查询不存在" 01 “课程但存在” 02 "课程的情况

select * from sc where sc.SId not in ( select SId from sc where sc.CId = '01' ) AND sc.CId= '02';

查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩。根据学生ID把成绩分组，对分组中的score求平均值，最后在选取结果中AVG大于60的即可

select student.SId,sname,ss from student,( select SId, AVG(score) as ss from sc GROUP BY SId HAVING AVG(score)> 60 )r where student.sid = r.sid; select Student.SId, Student.Sname, r.ss from Student right join( select SId, AVG(score) AS ss from sc GROUP BY SId HAVING AVG(score)> 60 )r on Student.SId = r.SId; select s.SId,ss,Sname from( select SId, AVG(score) as ss from sc GROUP BY SId HAVING AVG(score)> 60 )r left join (select Student.SId, Student.Sname from Student)s on s.SId = r.SId;

查询在 SC 表存在成绩的学生信息

select DISTINCT student.* from student,sc where student.SId=sc.SId

查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和。联合查询不显示没选课的学生

select student.sid, student.sname,r.coursenumber,r.scoresum from student, (select sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber from sc group by sc.sid)r where student.sid = r.sid;

显示没选课的学生

select s.sid, s.sname,r.coursenumber,r.scoresum from ( (select student.sid,student.sname from student )s left join (select sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber from sc group by sc.sid )r on s.sid = r.sid );

4.1 查有成绩的学生信息

select * from student where exists (select sc.sid from sc where student.sid = sc.sid); select * from student where student.sid in (select sc.sid from sc);

查询「李」姓老师的数量

select count(*) from teacher where tname like '李%';

查询学过「张三」老师授课的同学的信息

select student.* from student,teacher,course,sc where student.sid = sc.sid and course.cid=sc.cid and course.tid = teacher.tid and tname = '张三';

查询没有学全所有课程的同学的信息

select * from student where student.sid not in ( select sc.sid from sc group by sc.sid having count(sc.cid)= (select count(cid) from course) );

查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

select * from student where student.sid in ( select sc.sid from sc where sc.cid in( select sc.cid from sc where sc.sid = '01' ) );

查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

select student.* from student right join ( select b.sid from ( select sid from SC left join ( select cid from sc where sid='01' )a on sc.CId=a.CId )b group by b.SId having COUNT(b.sid)>2 and b.SId > '01' )c on Student.SId=c.SId

查询没学过"张三"老师讲授的任一门课程的学生姓名

select * from student where student.sid not in( select sc.sid from sc where sc.cid in( select course.cid from course where course.tid in( select teacher.tid from teacher where tname = "张三" ) ) ); select * from student where student.sid not in( select sc.sid from sc,course,teacher where sc.cid = course.cid and course.tid = teacher.tid and teacher.tname= "张三" );

查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select student.sid, student.sname, AVG(sc.score) from student,sc where student.sid = sc.sid and sc.score1;

检索" 01 "课程分数小于 60，按分数降序排列的学生信息

select student.*, sc.score from student, sc where student.sid = sc.sid and sc.score =60 then 1 else 0 end )/count(*)as 及格率, sum(case when sc.score>=70 and sc.score=80 and sc.score=90 then 1 else 0 end )/count(*)as 优秀率 from sc GROUP BY sc.CId ORDER BY count(*)DESC, sc.CId ASC

按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

select a.cid, a.sid, a.score, count(b.score)+1 as rank from sc as a left join sc as b on a.score

【本文地址】

公司简介

联系我们