SQL经典题目

涉及的表

drop database exercise;
create database exercise default charset utf8;
use exercise;

create table student(
s_id varchar(10),
s_name varchar(20),
s_birth date,
s_sex varchar(10)
) default charset=utf8;

create table course(
c_id varchar(10),
c_name varchar(20),
t_id varchar(10)
) default charset=utf8;

create table teacher(
t_id varchar(10),
t_name varchar(20)
) default charset=utf8 ;

create table score(
s_id varchar(10),
c_id varchar(10),
c_score varchar(20)
) default charset=utf8;

填充数据

insert into student (s_id, s_name, s_birth, s_sex)
values  ('01' , '赵雷' , '1990-01-01' , '男'),
        ('02' , '钱电' , '1990-12-21' , '男'),
        ('03' , '孙风' , '1990-05-20' , '男'),
        ('04' , '李云' , '1990-08-06' , '男'),
        ('05' , '周梅' , '1991-12-01' , '女'),
        ('06' , '吴兰' , '1992-03-01' , '女'),
        ('07' , '郑竹' , '1989-07-01' , '女'),
        ('08' , '王菊' , '1990-01-20' , '女');

insert into course (c_id, c_name, t_id)
values  ('01' , '语文' , '02'),
        ('02' , '数学' , '01'),
        ('03' , '英语' , '03');

insert into teacher (t_id, t_name)
values  ('01' , '张三'),
        ('02' , '李四'),
        ('03' , '王五');

insert into score (s_id, c_id, c_score)
values  ('01' , '01' , 80),
        ('01' , '02' , 90),
        ('01' , '03' , 99),
        ('02' , '01' , 70),
        ('02' , '02' , 60),
        ('02' , '03' , 80),
        ('03' , '01' , 80),
        ('03' , '02' , 80),
        ('03' , '03' , 80),
        ('04' , '01' , 50),
        ('04' , '02' , 30),
        ('04' , '03' , 20),
        ('05' , '01' , 76),
        ('05' , '02' , 87),
        ('06' , '01' , 31),
        ('06' , '03' , 34),
        ('07' , '02' , 89),
        ('07' , '03' , 98);

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数 

SELECT
    s.*,
    a.c_score '01课程分数',
    b.c_score '02课程分数' 
FROM
    student s,
    score a,
    score b 
WHERE
  a.s_id = s.s_id 
    AND a.s_id = b.s_id 
    AND a.c_id = '01' 
    AND b.c_id = '02' 
    AND a.c_score > b.c_score

select s.*,
s1.c_score '01课程分数',
s2.c_score '02课程分数'
from student s
left join score s1 on s.s_id=s1.s_id and s1.c_id='01'
left join score s2 on s.s_id=s2.s_id and s2.c_id='02'
where s1.c_score>s2.c_score

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

SELECT
    stu.s_id,
    stu.s_name,
    round( avg( ifnull( s.c_score, 0 ) ), 2 ) avg_score 
FROM
    score s
    LEFT JOIN student stu ON s.s_id = stu.s_id 
GROUP BY
    s_id 
HAVING
    avg_score >= 60

    3、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩（含无成级的）

SELECT
    stu.s_id,
    stu.s_name,
    round( avg( ifnull( s.c_score, 0 ) ), 2 ) avg_score 
FROM
    student stu
    LEFT JOIN score s ON s.s_id = stu.s_id 
GROUP BY
    s_id 
HAVING
    avg_score < 60 
    OR avg_score IS NULL

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

SELECT
    s.s_id,
    s.s_name,
    count( sc.c_id ) '选课总数',
    sum( ifnull( sc.c_score, 0 ) ) '总成绩'
FROM
    student s
    LEFT JOIN score sc ON s.s_id = sc.s_id 
GROUP BY
    s.s_id

5、查询学过"张三"老师授课的同学的信息 

SELECT
    s.* 
FROM
    teacher t
    LEFT JOIN course cs ON t.t_id = cs.t_id
    LEFT JOIN score sc ON cs.c_id = sc.c_id
    LEFT JOIN student s ON sc.s_id = s.s_id 
WHERE
    t.t_name = '张三'

6、查询没学过"张三"老师授课的同学的信息 ：

select * 
from student s
where s.s_id not in 
(select distinct sc.s_id 
  from score sc 
  join course cs on sc.c_id=cs.c_id
    JOIN teacher t on t.t_id=cs.t_id
    where t.t_name='张三'
)

7、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

SELECT
    s.* 
FROM
    student s
    JOIN score sc1 ON s.s_id = sc1.s_id 
    JOIN score sc2 ON s.s_id = sc2.s_id 
WHERE
    sc1.c_id='01' and sc2.c_id='02'

8、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

select s.*
from student s
where s.s_id in (select distinct s_id from score where c_id='01')
and s.s_id not in (select distinct s_id from score where c_id='02')

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

SELECT
    s.* 
FROM
    student s 
WHERE
    s.s_id not IN ( SELECT s_id FROM score GROUP BY s_id HAVING count( c_id ) = ( SELECT count( 1 ) FROM course ) )
    
select s.*
from student s
left join score sc on s.s_id=sc.s_id
group by s.s_id
having count(sc.c_id) < ( SELECT count( 1 ) FROM course )
 

10、查询和01同学学习的课程完全相同的同学的信息

SELECT
    s.* 
FROM
    student s
    JOIN score sc ON s.s_id = sc.s_id 
    where s.s_id!='01'
GROUP BY
    s_id 
HAVING
    GROUP_CONCAT( sc.c_id ORDER BY c_id ) = ( SELECT GROUP_CONCAT( c_id ORDER BY c_id ) FROM score WHERE s_id = '01' GROUP BY s_id )

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

SELECT
    s.* 
FROM
    student s 
WHERE
    s.s_id NOT IN (
SELECT
    sc.s_id 
FROM
    score sc 
WHERE
    sc.c_id IN ( SELECT cs.c_id FROM course cs JOIN teacher t ON cs.t_id = t.t_id WHERE t.t_name = '张三' ) 
    )

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

select s.s_id,s.s_name,t.avg_score
from student s
join (
  select sc.s_id,round( AVG( ifnull( sc.c_score, 0 ) ), 2) avg_score
    from score sc
    where sc.c_score <60
    group by sc.s_id
    having count(1)>=2
) t on s.s_id=t.s_id

SELECT
    s.s_id,
    s.s_name,
    round( AVG( ifnull( sc.c_score, 0 ) ), 2 ) avg_score 
FROM
    student s
    JOIN score sc ON s.s_id = sc.s_id 
WHERE
    sc.c_score < 60 GROUP BY s.s_id, s.s_name HAVING count( 1 ) >=2

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

select stu.*
from student stu
join score s on stu.s_id=s.s_id
where s.c_id='01' and s.c_score<60
order by s.c_score desc

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

SELECT
    sc.s_id,sc.c_id,
    ifnull(sc.c_score,0) score,
    ifnull(t.avg_score,0)  avg_score
FROM
  score sc
    left JOIN (
SELECT
    s_id,
    round( avg( ifnull( s.c_score, 0 ) ), 2 ) avg_score 
FROM
    score s 
GROUP BY
    s_id 
    ) t ON sc.s_id = t.s_id 
ORDER BY
    t.avg_score desc

15、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

select cs.c_id,cs.c_name,
round(max(s.c_score),2) '最高分',
round(min(s.c_score),2) '最低分',
round(avg(s.c_score),2) '平均分',
sum(CASE WHEN s.c_score>=60 then 1 else 0 end) /count(1) '及格率',
sum(CASE WHEN s.c_score>=70 and s.c_score<80 then 1 else 0 end) /count(1) '中等率',
sum(CASE WHEN s.c_score>=80 and s.c_score<90 then 1 else 0 end) /count(1) '优良率'
from course cs 
left join score s  on s.c_id=cs.c_id
GROUP BY s.c_id

16、按各科成绩进行排序，并显示排名

SELECT
    s1.c_id,
    s1.c_score,
    ( SELECT count(1) FROM score s2 WHERE s1.c_id = s2.c_id AND s1.c_score < s2.c_score ) + 1 rank 
FROM
    score s1 
ORDER BY
    s1.c_id,
    s1.c_score DESC

 17、查询学生的总成绩并进行排名

select
    t1.s_id ,t1.s_name, t1.score
    ,(select count(t2.score)
    from(select s.s_id, s.s_name, sum(sc.c_score) score
         from Student s
         join Score sc
         on s.s_id = sc.s_id
         group by s.s_id
         order by score desc)t2  
    where t2.score > t1.score) + 1 as rank
from(
  select s.s_id ,s.s_name ,sum(sc.c_score) score
  from Student s
  join Score sc
  on s.s_id = sc.s_id
  group by s.s_id
  order by score desc)t1  
order by t1.score desc


SELECT
    t.s_id,
    rank() over (ORDER BY t.score DESC) rank 
FROM
    ( SELECT s.s_id, sum( s.c_score ) score FROM score s GROUP BY s.s_id ORDER BY score DESC ) t

 18、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

SELECT
    s.s_id,
    s.s_name,
    t.c_score 
FROM
    student s
    JOIN ( SELECT s.s_id, s.c_score FROM score s ORDER BY s.c_score DESC LIMIT 1, 2 ) t ON s.s_id = t.s_id

19、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

select c.c_id,c.c_name,
sum(case when s.c_score<60 then 1 else 0 end) '[0,60)',
round(sum(case when s.c_score<60 then 1 else 0 end)/count(1),2) '[0,60)占比',
sum(case when s.c_score>=60 and s.c_score<70 then 1 else 0 end) '[60,70)',
round(sum(case when s.c_score>=60 and s.c_score<70 then 1 else 0 end)/count(1),2) '[60,70)占比',
sum(case when s.c_score>=70 and s.c_score<85 then 1 else 0 end) '[70,85)',
round(sum(case when s.c_score>=70 and s.c_score<85 then 1 else 0 end)/count(1),2) '[70,85)占比',
sum(case when s.c_score>=85 and s.c_score<100 then 1 else 0 end) '[85,100]',
round(sum(case when s.c_score>=85 and s.c_score<100 then 1 else 0 end)/count(1),2) '[85,100]占比'
from course c
left join score s
on c.c_id=s.c_id
group by c.c_id

20、查询学生平均成绩及其名次

SELECT
    t.s_id,
    t.avg_score,
    (SELECT count( distinct t1.avg_score) FROM
    (SELECT sc.s_id,round( AVG( sc.c_score ), 2 ) avg_score 
  FROM score sc GROUP BY sc.s_id ORDER BY avg_score DESC ) t1 WHERE t1.avg_score >= t.avg_score ) rank 
FROM
    (
    SELECT
        sc.s_id,
        round( AVG( sc.c_score ), 2 ) avg_score 
    FROM
        score sc 
    GROUP BY
        sc.s_id 
    ORDER BY
    avg_score DESC
    ) t

 21、查询各科成绩前三名的记录

SELECT
    s.* 
FROM
    score s 
WHERE
    ( SELECT count( 1 ) FROM score sc WHERE s.c_id = sc.c_id AND s.c_score < sc.c_score ) < 3 
ORDER BY
    s.c_id,
    s.c_score DESC


select s.s_id,s.c_id,s.c_score
from score s
left join (select c_id,c_score from score) t
on s.c_id=t.c_id and  s.c_score<t.c_score
group by s.s_id,s.c_id
having count(1)<3
order by s.c_id,s.c_score desc

---并列的
select s.s_id,s.c_id,s.c_score
from score s
left join (select DISTINCT c_id,c_score from score) t
on s.c_id=t.c_id and  s.c_score<t.c_score
group by s.s_id,s.c_id
having count(1)<3
order by s.c_id,s.c_score desc

 22、查询出只有两门课程的全部学生的学号和姓名

select s.s_id,s.s_name
from student s
join (
select s_id
from score 
GROUP BY s_id
having count(1)=2) t on s.s_id=t.s_id

23、查询所有学生的课程及分数情况

select s.s_id,s.s_name,
sum(case when cs.c_name='语文' then sc.c_score else 0 end) '语文',
sum(case when cs.c_name='数学' then sc.c_score else 0 end) '数学',
sum(case when cs.c_name='英语' then sc.c_score else 0 end) '英语',
sum(ifnull(sc.c_score,0)) '总分'
from student s
left join score sc on s.s_id=sc.s_id
left join course cs on sc.c_id=cs.c_id
group by s.s_id,s.s_name