sql练习题

题目采自网络，解答来自本人/网络，有些题重点在于思路，很简单的关联的字段查询没处理

表

–1.学生表
Student(s_id,s_name,s_birth,s_sex) --学生编号,学生姓名, 出生年月,学生性别
–2.课程表
Course(c_id,c_name,t_id) – --课程编号, 课程名称, 教师编号
–3.教师表
Teacher(t_id,t_name) --教师编号,教师姓名
–4.成绩表
Score(s_id,c_id,s_score) --学生编号,课程编号,分数

测试数据

--建表
--学生表
CREATE TABLE `Student`(
    `s_id` VARCHAR(20),
    `s_name` VARCHAR(20) NOT NULL DEFAULT '',
    `s_birth` VARCHAR(20) NOT NULL DEFAULT '',
    `s_sex` VARCHAR(10) NOT NULL DEFAULT '',
    PRIMARY KEY(`s_id`)
);
--课程表
CREATE TABLE `Course`(
    `c_id`  VARCHAR(20),
    `c_name` VARCHAR(20) NOT NULL DEFAULT '',
    `t_id` VARCHAR(20) NOT NULL,
    PRIMARY KEY(`c_id`)
);
--教师表
CREATE TABLE `Teacher`(
    `t_id` VARCHAR(20),
    `t_name` VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY(`t_id`)
);
--成绩表
CREATE TABLE `Score`(
    `s_id` VARCHAR(20),
    `c_id`  VARCHAR(20),
    `s_score` INT(3),
    PRIMARY KEY(`s_id`,`c_id`)
);
--插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
--课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

--教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

题目与作答

      -- 1.count(1)与count(*)得到的结果一致，包含null值,*包括了id字段，每行必有一个不空的字段。
      -- 2.count(字段)不计算null值
      -- 3.count(null)结果恒为0  

-- 1 查询"01"课程比"02"课程成绩高的学生的信息及课程分数  要点：必须2个都有成绩，否则01成绩有的学生有，02这个学生没有就会直接忽略
select * from score a inner join score b on a.s_id=b.s_id where a.c_id ='01' and b.c_id='02' and a.s_score>b.s_score

-- 第二种写法，不算where部分的话也是笛卡尔积
SELECT
    * 
FROM
    ( SELECT s_id, s_score FROM score WHERE c_id = '01' ) t1,
    ( SELECT s_id, s_score FROM score WHERE c_id = '02' ) t2 
WHERE
    t1.s_id = t2.s_id 
    AND t1.s_score > t2.s_score
    
-- 1.1 查询不存在01课程但是存在02课程的情况
select s_id from score where c_id='02' and s_id not in(select s_id from score where c_id='01')

-- 2 查询几门课平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩,round保留一位小数，会四舍五入
select s_id,round(avg(s_score),1) avg_score from score group by s_id having avg_score>=60

-- 3 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的) ,先group by，然后ifnull判断为0处理0结果，最后再having排除
select a.s_id,round(avg(IFNULL(b.s_score,0)),1)as avgScore from student a left join score b on a.s_id=b.s_id group by a.s_id having avgScore<60

-- 4 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩，包括没有课程的   注意：必须是全部学生表的学生id字段，成绩表的id字段缺8号学生
select a.s_id,count(b.c_id),sum(IFNULL(b.s_score,0)) from student a left join score b on a.s_id =b.s_id group by a.s_id;

-- 易错点：b表score表中无8号学生数据，如果用student表和score表连接，直接获取score表的学生id将遗漏
select * from student a left join score b on a.s_id=b.s_id;

select b.s_id,count(b.c_id),sum(b.s_score) from student a left join score b on a.s_id =b.s_id group by b.s_id  -- (3)
-- 第一个语句，最理想化的模型
-- 第二个语句，因为左连接导致将8号学生在group by 之前大表左边算上了，但是group by的时候a表的8号学生有，b表的8号学生没有,(3)的SQL执行结果务必注意

-- 5 查询学过"张三"老师授课的同学的信息
select c.s_id from teacher a inner join course b on a.t_id=b.t_id left join score c on b.c_id=c.c_id where a.t_name='张三' group by c.s_id
SELECT s_id FROM score WHERE c_id =( SELECT c_id FROM teacher a INNER JOIN course b ON a.t_id = b.t_id WHERE a.t_name = '张三' )

-- 6 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select a.s_id from score a inner join score b on a.s_id=b.s_id where a.c_id='01' and b.c_id='02'
select a.s_id from score a,score b where a.s_id=b.s_id and a.c_id='01' and b.c_id='02'

-- 7 查询没有学全所有课程的同学的信息
SELECT * FROM student WHERE s_id NOT IN ( SELECT s_id FROM score GROUP BY s_id HAVING count( c_id ) = ( SELECT count( c_id ) FROM course ) )

-- 8 查询和"01"号的同学学习的课程完全相同的其他同学的信息   ***********
-- 用这个group_concat结果去左连接
SELECT s_id FROM
    ( SELECT GROUP_CONCAT( c_id ) subjects FROM score WHERE s_id = 01 GROUP BY s_id ) a
    INNER JOIN ( SELECT s_id, GROUP_CONCAT( c_id ) subjects FROM score GROUP BY s_id ) b ON a.subjects = b.subjects 
WHERE
    s_id != '01'
    
-- 9 查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT s_name FROM student WHERE s_id NOT IN (SELECT s_id FROM score WHERE c_id IN ( SELECT c_id FROM course WHERE t_id = ( SELECT t_id FROM teacher WHERE t_name = '张三' )))

-- 10 查询不及格课程有两门及两门以上的同学的学号，姓名及其平均成绩
SELECT
    a.s_id,
    ( SELECT s_name FROM student WHERE s_id = a.s_id ),
    round( avg( a.s_score ), 1 ) 
FROM
    score a
    INNER JOIN (
    SELECT
        s_id,
        sum( CASE WHEN s_score < 60 THEN 1 ELSE 0 END ) counts 
    FROM
        score 
    GROUP BY
        s_id 
    HAVING
        counts >= 2 
    ) b ON a.s_id = b.s_id 
GROUP BY
    a.s_id
    
-- 11 按照平均成绩从高到低显示【所有学生】的所有课程的成绩以及平均成绩,直接用avg解法有误,avg默认会不包括null，即null，89，null的品均分为89大于80,80,80的平均分为80不合理
SELECT
    a.s_id,
    ( SELECT s_score FROM score WHERE s_id = a.s_id AND c_id = '01' )语文,
    ( SELECT s_score FROM score WHERE s_id = a.s_id AND c_id = '02' )数学,
    ( SELECT s_score FROM score WHERE s_id = a.s_id AND c_id = '03' )英语,
    round(sum(s_score)/3,2) avgScore     -- 这里如果理解为选修几门课程的话，而不是必选的话，这里用avg函数是合理的 avg(s_score)
FROM
    student a
    LEFT JOIN score b ON a.s_id = b.s_id 
GROUP BY
    a.s_id 
ORDER BY
    avgScore DESC;
    
-- 12 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 
-- 注意：该科目的成绩可能某一栏位是空的，空的不能算！！！因此不能直接除以count(*)，而应该用case when或者ifnull
SELECT
    a.c_id,
    ( SELECT c_name FROM course WHERE c_id = a.c_id ) 课程名称,
    max( s_score ) 最高分,
    min( s_score ) 最低分,
    round( avg( s_score ), 1 ) 平均分,
    round( sum( CASE WHEN s_score >= 60 THEN 1 ELSE 0 END ) / sum(case when s_score is not null then 1 else 0 end), 2 ) 及格率,
    round( sum( CASE WHEN s_score >= 70 AND s_score < 80 THEN 1 ELSE 0 END ) / sum(case when s_score is not null then 1 else 0 end), 2 ) 中等率,
    round( sum( CASE WHEN s_score >= 80 AND s_score < 90 THEN 1 ELSE 0 END ) / sum(case when s_score is not null then 1 else 0 end), 2 ) 优良率,
    round( sum( CASE WHEN s_score >= 90 THEN 1 ELSE 0 END ) / sum(case when s_score is not null then 1 else 0 end), 2 ) 优秀率
FROM
    score a 
GROUP BY
    c_id;
    
-- 13 按各科成绩进行排序，并显示排名，mysql没有rank函数  
            -- 声明变量@方式API DOC https://blog.csdn.net/justry_deng/article/details/80597916
            -- 排名跳级版本：age升序排列(score相同时，排名相同；但是到下一个score不同时，排名跳级+n)
SELECT c_id,s_id,s_score,
    @inRank := @inRank + 1,
    @curRank :=IF( @prevScore = s_score, @curRank, @inRank ) AS rank,   -- 写法2 ： @curRank:=case when @prevScore=s_score then @curRank else @inRank end
    @prevScore := s_score
FROM
    score a,( SELECT @curRank := 0, @prevScore := NULL, @inRank := 0 ) b 
ORDER BY c_id ASC,s_score DESC
    
    -- 每个科目分开排名版本
    SELECT c_id,s_id,s_score,
    @inRank := if(@prevSubject=c_id,@inRank+1,1),
    @curRank :=IF( @prevScore = s_score, @curRank, @inRank ) AS rank,   -- 这一步保证跳的时候，正确赋值给curBank
    @prevScore := s_score ,
    @prevSubject:=c_id
FROM
    score a,( SELECT @curRank := 0, @prevScore := NULL,@prevSubject:=null, @inRank := 0 ) b 
ORDER BY c_id ASC,s_score DESC

-- 解法2 自交  -- 首先左边所有记录挨个对应一遍右边第一列，然后左边所有记录挨个对应一遍右边第二列...左边6列*右边每一列对应6次=36条
select * from score a left join score b on a.c_id=b.c_id where a.c_id='01';
-- 丢弃左边大于等于右边的记录，右边从1到6条记录，没跳对应左边的都小于右边
select * from score a left join score b on a.c_id=b.c_id where a.c_id='01' and a.s_score<b.s_score;
-- 应该group by 的是a表，group by b表没有意义，因为要对每一条记录进行排名，是a的记录,算的是group by以后比a大的记录个数
SELECT
    a.c_id,
    a.s_id,
    a.s_score,
    count( b.s_score )+1 AS bigCountPlus1 
FROM
    score a
    LEFT JOIN score b ON a.c_id = b.c_id and a.s_score < b.s_score 
GROUP BY
    a.c_id,
    a.s_id,
    a.s_score 
ORDER BY
    a.c_id,
    bigCountPlus1   
    
-- 14 查询学生的总成绩并进行排名--成绩相同需要跳参考上面，这边不再处理
select a.*,@inRank:=@inRank+1,@rank:=if(@preScore=sumScore,@rank,@inRank),@preScore:=sumScore as rank from (
    select s_id,sum(s_score) sumScore from score group by s_id order by sumScore desc
) a,(select @rank:=0,@inRank:=0,@preScore:=null)b

-- 15 查询不同老师所教不同课程平均分从高到低显示 
select a.t_id,a.t_name,b.c_id,b.c_name,avg(c.s_score) avgScore from teacher a inner join course b on a.t_id=b.t_id left join score c on b.c_id=c.c_id group by t_id,t_name,c_id,c_name order by avgScore desc

-- 16 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩  课程1前两名并列第一，第三名就是第三名，不存在第二名
select c.s_id,c.s_score,c.c_id,c.rank from(
select a.s_id,a.s_score,a.c_id,@inRank:=if(@prevSubject=a.c_id,@inRank+1,1),@rank:=if(@preScore=a.s_score,@rank,@inRank) as rank,@preScore:=a.s_score,@prevSubject:=a.c_id from score a,(select @rank:=0,@inRank:=0,@preScore:=null,@prevSubject:=null)b order by a.c_id,a.s_score desc
)c where c.rank in(2,3) order by c.c_id ;

-- 17 统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
select main.*,
    round(main.`85-100`/(select count(*) from score where c_id=main.c_id),2)as "85-100占比",
    round(main.`70-85`/(select count(*) from score where c_id=main.c_id),2)as "70-85占比",
    round(main.`60-70`/(select count(*) from score where c_id=main.c_id),2)as "60-70占比",
    round(main.`0-60`/(select count(*) from score where c_id=main.c_id),2)as "0-60占比"
from (
    select c_id,
    sum(if(s_score>85 and s_score<=100,1,0)) as "85-100",
    sum(if(s_score>70 and s_score<=85,1,0)) as "70-85",
    sum(if(s_score>60 and s_score<=70,1,0)) as "60-70",
    sum(if(s_score>=0 and s_score<=60,1,0)) as "0-60"
    from score group by c_id
)main

-- 18 获取各科成绩前3名的记录 
    -- 参照第13题作答

-- 19 查询所有学生的课程及分数情况
select a.s_id,
    sum(case when c_name='语文' then s_score else 0 end)as '语文',
    sum(case when c_name='数学' then s_score else 0 end)as '数学',
    sum(case when c_name='英语' then s_score else 0 end)as '英语',
    sum(s_score) as '总分'
from score a left join course b on a.c_id=b.c_id group by a.s_id

-- 20 查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩  考虑并列第一的情况
SELECT b.* FROM score a left join student b on a.s_id=b.s_id
WHERE a.s_score = (SELECT max( s_score ) AS topScore FROM score WHERE c_id IN ( SELECT c_id FROM course WHERE t_id IN 
( SELECT t_id FROM teacher WHERE t_name = '张三' ) ) ) 
AND a.c_id = ( SELECT c_id FROM course WHERE t_id IN ( SELECT t_id FROM teacher WHERE t_name = '张三' ) )

-- 21 查询不同课程成绩相同的学生编号，课程编号，学生成绩
select DISTINCT b.s_id,b.c_id,b.s_score from score a,score b where a.c_id != b.c_id and a.s_score = b.s_score

-- 22 查询每门课成绩最好的前两名
    -- 解法1 参考13 解法2
    -- 解法2
select a.s_id,a.c_id,a.s_score from score a
        where (select COUNT(1) from score b where b.c_id=a.c_id and b.s_score>=a.s_score)<=2 ORDER BY a.c_id

-- 23 统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列  
select c_id,count(s_id) counts from score group by c_id having counts>5 order by counts desc,c_id asc

-- 24 查询各学生的年龄
SELECT TIMESTAMPDIFF(YEAR,s_birth,DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S')) from student
-- 解法2
            select s_birth,(DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(s_birth,'%Y') - 
                (case when DATE_FORMAT(NOW(),'%m%d')>DATE_FORMAT(s_birth,'%m%d') then 0 else 1 end)) as age
        from student;
        
-- 25 查询本周过生日的学生
select * from student where WEEK(DATE_FORMAT(NOW(),'%Y-%m-%d'))=WEEK(s_birth);

-- 26 查询下月过生日的学生
select * from student where month(date_format(now(),'%Y-%m-%d'))+1=month(s_birth)