sql常用查询‘

SQL查询练习

1. 查询id=1课程比id=2课程成绩高的学生的信息[学生id和学生的姓名]及课程分数

   select s.id,s.sname,sc1.score from student s join sc sc1 on s.id=sc1.sid join sc sc2 on sc1.sid=sc2.sid where sc1.cid=1 and sc2.cid=2 and sc1.score >sc2.score; 

2. 查询同时存在1 课程和2课程的情况

   select s.id,s.sname,sc1.score from student s join sc sc1 on s.id=sc1.sid join sc sc2 on sc1.sid=sc2.sid where  sc2.cid =2 and sc1.cid=1 ;

3. 查询存在1 课程但可能不存在2课程的情况

   select s.id,s.sname,sc1.score from student s join sc sc1 on s.id=sc1.sid  where  sc1.cid=1 ;

4. 查询不存在1课程但存在2课程的情况 (不存在时显示为 null)

   select sc2.sid ,sc2.cid ,sc1.score 课程1,sc2.score 课程2 from (select *from sc where cid=1 ) sc1  RIGHT join (select *from sc where cid=2 ) sc2 on sc1.sid=sc2.sid where sc1.cid is null and sc2.cid=2;

5. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

   select s.id,s.sname,avg(sc.score) from student s join sc sc on s.id=sc.sid group by s.id having avg(sc.score)>60 ; 

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

select distinct s.id,s.sname from student s join sc sc on s.id=sc.sid where exists(select 1 from sc where score is not null);

7. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

   select s.id ,s.sname,count(sc.cid),sum(sc.score) from student s join sc sc where sc.sid=s.id group by s.id;

8. 查询「李」姓老师的数量

   查询李老师的带过的学生的数量/授课的数量

   方法一.select (select count( c.id) '授课数' from teacher t join course c on c.tid=t.id join sc sc on sc.cid=c.id  where t.tname like '李%') 授课数,(select count( sc.sid) '学生数' from teacher t join course c on c.tid=t.id join sc sc on sc.cid=c.id  where t.tname like '李%')  带过的学生;select (select count(distinct c.id) '授课数' from teacher t join course c on c.tid=t.id join sc sc on sc.cid=c.id  where t.tname like '李%') 授课数；
   
   方法2. select t.tname 老师姓名, count(distinct s.id) 学生数量,count(sc.cid) 授课数量 from student s join sc sc on sc.sid=s.id join course c on c.id=sc.cid join teacher t on t.id=c.tid where t.tname like '李%' group by t.id;

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

方法一.select s.id,s.sname from student s join sc sc on sc.sid=s.id where sc.cid=
(select  c.id from teacher t left join  course c on c.tid=t.id where t.tname='张三')

 方法二.select  distinct s.*  from student s join sc sc on sc.sid=s.id join course c on c.id=sc.cid join teacher t on t.id=c.tid where t.tname='张三' ;

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

select s.id,s.sname from student s join sc sc on sc.sid=s.id  GROUP BY s.id HAVING count(*)!=(select count(*) from course);

11. 查询至少有一门课与学号为1的同学所学相同的同学的信息

select s.id,s.sname from student s join sc sc on sc.sid=s.id where sc.cid in (select sc1.cid from sc sc1 where sc1.sid=1);

12. 查询和id=1的同学学习的课程 完全相同的其他同学的信息（重要） - 非常重要,非常重要,非常重要,非常重要,非常重要!

select s.id,s.sname,s.age,s.sex from student s where s.id in(select sc.sid from sc where sc.sid<>1 and sc.cid in(select sc.cid from sc where sc.sid=1) group by sc.sid having count(*)=(select count(*) from sc where sc.sid=1));

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

    select distinct s.id ID ,s.sname 学生姓名 from sc sc2  right join student s on s.id= sc2.sid where s.id not in(select sc.sid from sc where sc.cid=(  select  c.id from teacher t left join  course c on c.tid=t.id where t.tname='张三'));

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

select s.sname 姓名,s.id 学号,avg(sc.score) 平均分 from student  s left join sc sc on s.id=sc.sid where sc.score<60 group by s.id having count(*)>=2 ;

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

select s.sname 姓名,s.id 学号,sc.score  from student  s left join sc sc on s.id=sc.sid where sc.score<60  and sc.cid='01' order by  3 desc;

16. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

    select s.id ID,s.sname 姓名,coalesce(sc.cid,'未选') 课程编号, coalesce(sc.score,0) 分数,coalesce((select avg(sc2.score) from sc sc2 where sc2.sid=s.id),0) 平均分 from student  s left join sc sc on s.id=sc.sid order by 5 desc; 

17. 查询各科成绩前三名的记录

    select  s.id id,s.sname 姓名,sc.cid 课程号, sc.score 分数 from student  s  join sc sc on s.id=sc.sid where exists (select 1 from sc sc1 where sc.cid=sc1.cid and sc.score<sc1.score  having count(*)<3) order by 4 desc, 3 desc; 

18. 查询每门课程被选修的学生数

    select c.id 课程号,c.cname 课程名,count(sc.sid) 学生数 from sc  right join course c on c.id=sc.cid group by sc.cid;

19. 查询出只选修两门课程的学生学号和姓名

    select s.id,s.sname from student s join sc sc on sc.sid=s.id group by s.id having count(sc.cid)=2;

20. 查询男生、女生人数

    select s.sex 性别,count(s.sex) 人数 from student s group by s.sex;

21. 查询名字中含有「风」字的学生信息

    select * from student s where s.sname like '%风%';

22. 查询同名同性学生名单，并统计同名同性人数

select * from student s where exists (select 1 from student s2 where s2.sname=s.sname having count(*)>=2);

23. 查询 1990 年出生的学生名单

select *from student s where year(s.age)=1990; 

24. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

    select c.cname 课程名,sc.cid 课程id,avg(sc.score) 平均成绩 from course c left join sc sc on sc.cid=c.id group by c.id order by  3 desc,2;

25. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

select s.id id,s.sname 姓名,coalesce(avg(sc.score),0) 平均成绩 from student s left join sc sc on s.id=sc.sid group by s.id having 平均成绩>85;

26. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

    select s.id id,s.sname 姓名,sc.score 分数 from student s join sc sc on s.id=sc.sid join course c on c.id=sc.cid where c.cname='数学' and sc.score<60;

27. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

    select s.id id,s.sname 姓名 ,sc.cid 课程名 ,sc.score 分数 from student s left join sc sc on s.id=sc.sid where sc.score>70;

28. 查询存在不及格的课程

    select sc.cid ,c.cname from sc sc join course c on c.id=sc.cid where sc.score<60 group by sc.cid ;

29. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

    select s.id,s.sname from student s join sc sc on sc.sid=s.id where sc.cid=1 and sc.score>80 group by s.id,s.sname;

30. 求每门课程的学生人数

    select sc.cid ,count(sc.sid) from sc group by sc.cid ;

31. 假设成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

    select s.id,s.sname,sc.score from student s join sc on sc.sid=s.id join course c on c.id=sc.cid join teacher t on t.id=c.tid and t.tname ='张三' where exists (select * from sc sc2 where sc.cid=sc2.cid and sc.score<sc2.score having count(*)<1) ;

32. 假设成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

select * from student s join sc on sc.sid=s.id join course c on c.id=sc.cid join teacher t on t.id=c.tid and t.tname ='张三' where exists (select * from sc sc2 where sc.cid=sc2.cid and sc.score>=sc2.score having count(*)=(select count(sc3.cid) from sc sc3 where sc3.cid=sc.cid)) ;

33. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

    select s.id,s.sname,sc.cid,sc.score from student s join sc sc on sc.sid=s.id join sc sc2 on sc.sid=sc2.sid where sc.score=sc2.score and
    sc.cid!=sc2.cid;

34. 统计每门课程的学生选修人数（超过 5 人的课程才统计）

select sc.cid,count(*) from sc group by sc.cid having count(*)>5

34. 检索至少选修两门课程的学生学号

select s.id from student s join sc on sc.sid=s.id  group by s.id having count(sc.cid)>=2;

36. 查询选修了全部课程的学生信息

select s.id from student s join sc on sc.sid=s.id  group by s.id having count(sc.cid)=(select count(c.id) from course c );