SQL 经典50题

已于 2023-08-29 22:56:49 修改
阅读量150 收藏
点赞数
文章标签： 数据库 mysql sql
于 2023-08-27 23:54:27 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_46023827/article/details/132529785
版权 
一、建表
# student 学生表
CREATE TABLE student
(
    s_id VARCHAR(20),
    s_name VARCHAR(20) NOT NULL,
    s_birth VARCHAR(20) NOT NULL,
    s_sex VARCHAR(10) NOT NULL,
    PRIMARY KEY(s_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 student VALUES('09', '张三', '1999-09-03', '男');
INSERT INTO student VALUES('10', '王五', '1990-01-02', '男');
INSERT INTO student VALUES('12', '陈玉林', '2000-09-13', '女');
INSERT INTO student VALUES('18', '刘林文', '2001-07-18', '男');
INSERT INTO student VALUES('100', '张三', '2000-03-05', '男');

# course 课程表
CREATE TABLE course
(
    c_id VARCHAR(20),
    c_name VARCHAR(20) NOT NULL,
    t_id VARCHAR(20) NOT NULL,
    PRIMARY KEY(c_id)
);

INSERT INTO Course VALUES('01', '语文', '02');
INSERT INTO Course VALUES('02', '数学', '01');
INSERT INTO Course VALUES('03', '英语', '03');

 
# teacher 教师表
CREATE TABLE teacher
(
    t_id VARCHAR(20),
    t_name VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY(t_id)
);

INSERT INTO teacher VALUES('01', '张三');
INSERT INTO teacher VALUES('02', '李四');
INSERT INTO teacher VALUES('03', '王五');


# score 分数表
CREATE TABLE score
(
    s_id VARCHAR(20),
    c_id VARCHAR(20),
    s_score INT(3),
    PRIMARY KEY(s_id, c_id)  # 注意这里是联合主键
);

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);
INSERT INTO score VALUES('08', '01', null);
INSERT INTO score VALUES('08', '02', 100);
INSERT INTO score VALUES('08', '03', 60);
INSERT INTO score VALUES('09', '04', 70);

二、50题答案

-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
    
select st.s_id,st.s_name,st.s_birth,st.s_sex,sc.score01,sc.score02 from student st
inner join (
    select s1.s_id,s1.s_score as score01, s2.s_score as score02
    from score s1
             join score s2 on s1.s_id = s2.s_id
        and s1.c_id = 01
        and s2.c_id = 02
        and s1.s_score > s2.s_score
) sc on st.s_id = sc.s_id;

-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

select st.s_id,st.s_name,st.s_birth,st.s_sex,sc.score01,sc.score02 from student st
inner join (
    select s1.s_id,s1.s_score as score01, s2.s_score as score02
    from score s1
             join score s2 on s1.s_id = s2.s_id
        and s1.c_id = 01
        and s2.c_id = 02
        and s1.s_score < s2.s_score
) sc on st.s_id = sc.s_id;

            

-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
    
select st.s_id ,st.s_name,r.avg_score from student st 
left join (
select s.s_id, avg(s.s_score) as avg_score from score s group by s_id 
) r on st.s_id = r.s_id
where r.avg_score >= 60 or r.avg_score is null


-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
        -- (包括有成绩的和无成绩的)
select st.s_id ,st.s_name,r.avg_score from student st 
left join (
select s.s_id, avg(s.s_score) as avg_score from score s group by s_id 
) r on st.s_id = r.s_id
where r.avg_score < 60 or r.avg_score is null

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

select
    st.s_id,
    st.s_name,
    count(sc.c_id) totalcourse,
    sum(sc.s_score) totalscore
from
    student st
    left join score sc on st.s_id = sc.s_id
group by st.s_id
            
-- 6、查询"李"姓老师的数量 
select count(t_id) from teacher where t_name like '李%'
    
-- 7、查询学过"张三"老师授课的同学的信息 
select
    s_id,
    s_name,
    s_birth,
    s_sex
from
    student s
where
    s.s_id in (
select
    score.s_id 
from
    course
    right join teacher on course.t_id = teacher.t_id
    left join score on course.c_id = score.c_id 
where
    teacher.t_name = '张三' 
    )


-- 8、查询没学过"张三"老师授课的同学的信息 
select
    s_id,
    s_name,
    s_birth,
    s_sex
from
    student s
where
    s.s_id not in (
select
    score.s_id 
from
    course
    right join teacher on course.t_id = teacher.t_id
    left join score on course.c_id = score.c_id 
where
    teacher.t_name = '张三' 
    )
    
    
-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select
    st.s_id,
    st.s_name,
    st.s_birth,
    st.s_sex 
from
    student st
where
    st.s_id in (
select
    s1.s_id 
from
    score s1
    join score s2 on s1.s_id = s2.s_id 
where
    s1.c_id = '01' 
    and s2.c_id = '02' 
    )

    
-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select
    st.s_id,
    st.s_name,
    st.s_birth,
    st.s_sex 
from
    student st
where
    st.s_id in (
select
    s1.s_id 
from
    score s1
where
    s1.c_id = '01' 
    and s1.s_id not in (select s2.s_id from score s2 where s2.c_id='02') 
    )


-- 11、查询没有学全所有课程的同学的信息 
    
select st.s_id,st.s_name,st.s_birth,st.s_sex from student st 
where st.s_id not in ( select s_id from score group by s_id having count(s_id) = 3 )

-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息 
select distinct st.s_id,st.s_name,st.s_birth,st.s_sex from student st
left join score on st.s_id = score.s_id 
where score.c_id in( select c_id from score where s_id = '01' ) and  st.s_id != '01'
            
-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 
-- 查找数量等于01学生的课程数量，再查询与01不相同的学生再取反
select distinct s_id from score where
s_id in ( select s_id from score where s_id != '01' group by s_id 
having COUNT( distinct c_id ) = ( select count( distinct c_id ) from score where s_id = '01' ) ) 
and s_id not in ( select distinct s_id from score where c_id not in ( select c_id from score where s_id = '01' ) )

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

select st.s_id,st.s_name from student st
where st.s_id not in (select s_id from score where  c_id in 
(select c_id from course c join teacher t on c.t_id=t.t_id where t.t_name='张三' ));
    
-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 
-- 方法1
select
    st.s_id,
    st.s_name,
    AVG( sc.s_score ) 
from
    student st
    join score sc on sc.s_id = st.s_id 
where
    sc.s_id in ( select s_id from score where s_score < 60 group by s_id having COUNT( s_score ) >= 2 ) 
group by
    st.s_id

-- 方法2
select st.s_id ,st.s_name,r.avg_score from student st  join 
(select st.s_id,avg(s.s_score) avg_score from student st, score s 
where st.s_id=s.s_id and s.s_score <60
group by s.s_id having count(c_id)>=2)r on st.s_id=r.s_id ;
    
-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息
select st.s_id ,st.s_name,st.s_birth,st.s_sex from student st  join (select s_id,s_score from score where s_score<60 and c_id='01')r
on st.s_id=r.s_id order by r.s_score desc;

select score.s_id,student.s_birth,student.s_sex,s_name from student
left join score on score.s_id = student.s_id where c_id = '01' and s_score < 60 order by s_score desc

-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select s.s_id,s.s_score,t.avgscore from score s
join ( select s_id, AVG( s_score ) avgscore from score group by s_id ) t on s.s_id = t.s_id 
order by t.avgscore desc


-- 19、按各科成绩进行排序，并显示排名
select s.s_id,s.c_id,s.s_score,dense_rank ( ) over ( partition by c_id order by s_score desc ) rk_score from score s;
-- dense_rank ( ) over 排名可以并列，连续的不间断

-- 20、查询学生的总成绩并进行排名
select s.s_id,s.sumscore,dense_rank () over ( order by sumscore desc)
from (select s_id, sum( s_score ) sumscore from score group by s_id ) s;


-- 21、查询不同老师所教不同课程平均分从高到低显示 
select teacher.t_name,course.c_name,t.avgscore from course
join ( select c_id, AVG( s_score ) avgscore from score group by c_id ) t on course.c_id = t.c_id
join teacher on course.t_id = teacher.t_id 
order by t.avgscore desc


-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select distinct
    stu.s_name,
    cor.c_name,
    t.s_score,
    t.rk 
from
    ( select c_id, s_id, s_score, dense_rank ( ) over ( partition by c_id order by s_score desc ) as rk from score ) t
    -- 按照c_id分组，再按照s_score降序排序，最终dense_rank ()可以降序排序的次序相同
    join student stu on t.s_id = stu.s_id
    join course cor on cor.c_id = t.c_id 
where
    t.rk in ( 2, 3 )            

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

select c.c_id,c.c_name,
(select COUNT(1) from score sc where sc.c_id=c.c_id and sc.s_score<=100 and sc.s_score>80) "100-85人数",
((select COUNT(1) from score sc where sc.c_id=c.c_id and sc.s_score<=100 and sc.s_score>80)/(select COUNT(1) from score sc where sc.c_id=c.c_id )) "100-85百分比",
 
 (select COUNT(1) from score sc where sc.c_id=c.c_id and sc.s_score<=85 and sc.s_score>70) "85-70人数",
((select COUNT(1) from score sc where sc.c_id=c.c_id and sc.s_score<=85 and sc.s_score>70)/(select COUNT(1) from score sc where sc.c_id=c.c_id )) "85-70百分比",

 (select COUNT(1) from score sc where sc.c_id=c.c_id and sc.s_score<=70 and sc.s_score>60) "70-60人数",
((select COUNT(1) from score sc where sc.c_id=c.c_id and sc.s_score<=70 and sc.s_score>60)/(select COUNT(1) from score sc where sc.c_id=c.c_id )) "70-60百分比",

 (select COUNT(1) from score sc where sc.c_id=c.c_id and sc.s_score<=60 and sc.s_score>0) "60-0人数",
((select COUNT(1) from score sc where sc.c_id=c.c_id and sc.s_score<=60 and sc.s_score>=0)/(select COUNT(1) from score sc where sc.c_id=c.c_id )) "60-0百分比"
from course c order by c.c_id

-- 24、查询学生平均成绩及其名次 
select t.s_id,t.avgscore,t.s_name,
    dense_rank ( ) over ( order by t.avgscore desc ) as rank 
from
    (
select
    st.s_id,
    st.s_name,
    AVG( s.s_score ) avgscore 
from
    student st
    join score s on st.s_id = s.s_id 
group by
    st.s_id 
    ) t

    

select  t.s_id,t.avgscore,t.s_name,dense_rank ( ) over ( order by t.avgscore desc ) rank 
from
    (
select
    stu.s_id,
    stu.s_name,
    AVG( sc.s_score ) avgscore 
from
    student stu
    join score sc on stu.s_id = sc.s_id 
group by
    stu.s_id 
    ) t
    
-- 25、查询各科成绩前三名的记录
            -- 1.选出b表比a表成绩大的所有组
            -- 2.选出比当前id成绩大的 小于三个的 

select a.s_id,a.c_id,a.s_score from score a 
left join score b on a.c_id = b.c_id and a.s_score < b.s_score
group by a.s_id,a.c_id,a.s_score 
having COUNT(b.s_id)<3
order by a.c_id,a.s_score desc

-- having COUNT(b.s_id)<3  
-- b.s_id的数量取值是0，1，2，那么取值为0时，即满足a.s_score < b.s_score成立的a.s_id有0个，此时a.s_id为c_id课程的第一名
-- 取值为1时，即满足a.s_score < b.s_score成立的a.s_id有1个，此时a.s_id为c_id课程的第二名
-- 取值为2时，即满足a.s_score < b.s_score成立的a.s_id有2个，此时a.s_id为c_id课程的第三名

-- 26、查询每门课程被选修的学生数 
select course.c_name,count( score.s_id ) as stucount from score
join course on score.c_id = course.c_id 
group by course.c_name

    
-- 27、查询出只有两门课程的全部学生的学号和姓名 
select student.s_id,student.s_name from student
join score on student.s_id = score.s_id 
group by student.s_id having count( c_id ) =2
    
-- 28、查询男生、女生人数 
    select
    s_sex,
    count( s_id ) 
from
    student 
group by
    s_sex


-- 29、查询名字中含有"风"字的学生信息
select st.s_id,st.s_name,st.s_birth,st.s_sex from student st where s_name like '%风%'


-- 30、查询同名同性学生名单，并统计同名人数 

        
select s_name,s_sex, count(*) from student st group by st.s_name,st.s_sex having count(*)>1


-- 31、查询1990年出生的学生名单
select st.s_id,st.s_name,st.s_birth,st.s_sex from student st where year(s_birth) = '1990';

select st.s_id,st.s_name,st.s_birth,st.s_sex  from student st where s_birth LIKE '%1990%';

-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 
select s.c_id,avg(s.s_score) as avg_score from score as s  
group by s.c_id 
order by avg_score asc,s.c_id desc ;


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

select st.s_name,st.s_id,r.avg_score from student as st
left join (select s_id,avg(s.s_score) as  avg_score from score as s  group by s_id having avg_score>=85) r on r.s_id = st.s_id
where r.avg_score is not null;


-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数 
select 
    stu.s_name,
    t.s_score
from
    student stu
        join ( select s_id, s_score from score where c_id = ( select c_id from course where c_name = '数学' ) 
and s_score < 60 ) t on stu.s_id = t.s_id;


-- 35、查询所有学生的课程及分数情况； 
select
    stu.s_name,
    c.c_name,
    s.s_score
from
    student stu
        left join score s on stu.s_id = s.s_id
        left join  course c on s.c_id = c.c_id;


-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数； 
select 
    stu.s_name,
    c.c_name,
    s.s_score
from
    student stu
        left join score s on stu.s_id = s.s_id
        left join  course c on s.c_id = c.c_id
where
        s.s_id in ( select s_id from score group by s_id having max( s_score ) > 70 );

-- 37、查询不及格的课程
select s.s_id,course.c_id,c_name ,s_score from course 
    left join (select s_id,s_score,c_id from score where s_score<60) s on s.c_id=course.c_id;

-- 38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名； 
select s_id,s_name from student where 
s_id in ( select s_id from score WHERE c_id = '01' and s_score > 80 );
                

-- 39、求每门课程的学生人数 
select  c.c_name,t.student_count from course c
 left join ( select c_id, count( s_id ) student_count from score group by c_id ) t on c.c_id = t.c_id;

-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

                
                -- 查询老师id        

                -- 查询最高分（可能有相同分数）

                -- 查询信息


select st.s_id,st.s_name,st.s_birth,st.s_sex,s.s_score,s.c_id from student st,teacher t ,course c ,score s 
where t.t_id =c.t_id and st.s_id=s.s_id and s.c_id=c.c_id and t.t_name='张三'
order by s.s_score desc limit 1;

-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 
-- 自连接
select 
    s1.s_id,
    s1.c_id,
    s1.s_score
from
    score as s1
        join score as s2 on s1.s_score = s2.s_score
        and s1.s_id != s2.s_id
        and s1.c_id != s2.c_id;
        

select distinct s1.s_id,s1.c_id,
    s1.s_score
from
    score as s1,score as s2
        where s1.s_score = s2.s_score
        and s1.s_id != s2.s_id
        and s1.c_id != s2.c_id;

-- 42、查询每门课程成绩最好的前两名 
select t.c_id,t.s_id,t.score_rank from
    ( select c_id, s_id, row_number () over (partition by  c_id order by s_score desc) as score_rank from score ) t
where t.score_rank < 3;
-- 在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by，但不晚于 order by 的执行。

-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列  
select  course.c_name,t.c_id,t.count_studnet from
 (select c_id, count( s_id ) count_studnet from score group by c_id having count_studnet > 5 ) t
join course on course.c_id = t.c_id group by t.c_id;
        
        
        
-- 44、检索至少选修两门课程的学生学号 
select s_id,count( c_id ) as course_count from score
    group by s_id having course_count >=2;

select s_id,count( c_id )  from score
    group by s_id having count( c_id ) >=2;
   
-- 45、查询选修了全部课程的学生信息 
select s_id,count(c_id) as course_count from score group by s_id 
having course_count=(select count(distinct c_id) from score);

select s_id,count(c_id)  from score group by s_id 
having count(c_id)=(select count(distinct c_id) from score);

-- 46、查询各学生的年龄
        -- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select s_name, if (
    week(current_date)>=week(s_birth),
    year(current_date)-year(s_birth),
    year(current_date)-year(s_birth)-1
    ) s_age
from student;

# if 语句形式包含以下几种：
# IF … THEN … END IF
# IF … THEN … ELSE … END IF
# IF … THEN … ELSIF … THEN … ELSE … END IF


-- 47、查询本周过生日的学生
select s_name from student where week(s_birth)=(select week (current_date));

-- 48、查询下周过生日的学生
select s_name from student where week(s_birth)=(select week (current_date + interval 1 week));        

-- 49、查询本月过生日的学生
select s_name from student where month(s_birth)=(select month (current_date));

-- 50、查询下月过生日的学生
-- 用interval来解决期末与期初的问题
select s_name from student where month(s_birth)=(select month(current_date + interval 1 month ))

--46、查询各学生的年龄
select s_id,s_name,extract (year from AGE(current_date,s_birth::date))as s_year from student

-- age(timestamp, timestamp)：计算两个时间差
-- age(timestamp)：只输入一个参数，计算current_date与入参的时间间隔
-- date_part，extract(field FROM source)函数：field 表示取的时间对象，source 表示取的日期来源，类型为 timestamp、time 或 interval

--47、查询本周过生日的学生
select s_id,s_name from student where date_part('week',s_birth::date) = date_part('week',current_date)

--48、查询下周过生日的学生
select s_id,s_name from student where date_part('week',s_birth::date) = date_part('week',current_date)%52+1

--49、查询本月过生日的学生
select s_id,s_name from student where extract(month from s_birth::date) = extract(month from current_date)

--50、查询下月过生日的学生
select s_id,s_name from student where extract(month from s_birth::date) = (extract(month from current_date))%12+1

洛文泽
关注
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值