SQL经典练习题

最新推荐文章于 2024-05-21 21:08:18 发布

骑蜗牛追梦者

最新推荐文章于 2024-05-21 21:08:18 发布

阅读量2.6k

点赞数 1

分类专栏：数据库文章标签： mysql 入门练习

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

表名和字段

–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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

练习题和sql语句

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

select a.* ,b.s_score as 01_score,c.s_score as 02_score from 
    student a 
    join score b on a.s_id=b.s_id and b.c_id='01'
    left join score c on a.s_id=c.s_id and c.c_id='02' or c.c_id = NULL where b.s_score>c.s_score


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

select a.* ,b.s_score as 01_score,c.s_score as 02_score from 
    student a left join score b on a.s_id=b.s_id and b.c_id='01' or b.c_id=NULL 
     join score c on a.s_id=c.s_id and c.c_id='02' where b.s_score<c.s_score


-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score from 
    student b 
    join score a on b.s_id = a.s_id
    GROUP BY b.s_id,b.s_name HAVING ROUND(AVG(a.s_score),2)>=60;


-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
        -- (包括有成绩的和无成绩的)

select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score from 
    student b 
    left join score a on b.s_id = a.s_id
    GROUP BY b.s_id,b.s_name HAVING ROUND(AVG(a.s_score),2)<60
    union
select a.s_id,a.s_name,0 as avg_score from 
    student a 
    where a.s_id not in (
                select distinct s_id from score);


-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select a.s_id,a.s_name,count(b.c_id) as sum_course,sum(b.s_score) as sum_score from 
    student a 
    left join score b on a.s_id=b.s_id
    GROUP BY a.s_id,a.s_name;


-- 6、查询"李"姓老师的数量 
select count(t_id) from teacher where t_name like '李%';

-- 7、查询学过"张三"老师授课的同学的信息 
select a.* from 
    student a 
    join score b on a.s_id=b.s_id where b.c_id in(
        select c_id from course where t_id =(
            select t_id from teacher where t_name = '张三'));

-- 8、查询没学过"张三"老师授课的同学的信息 
select * from 
    student c 
    where c.s_id not in(
        select a.s_id from student a join score b on a.s_id=b.s_id where b.c_id in(
            select c_id from course where t_id =(
                select t_id from teacher where t_name = '张三')));
-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

select a.* from 
    student a,score b,score c 
    where a.s_id = b.s_id  and a.s_id = c.s_id and b.c_id='01' and c.c_id='02';

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

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


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

select s.* from 
    student s where s.s_id in(
        select s_id from score where s_id not in(
            select a.s_id from score a 
                join score b on a.s_id = b.s_id and b.c_id='02'
                join score c on a.s_id = c.s_id and c.c_id='03'
            where a.c_id='01'))

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

select * from student where s_id in(
    select distinct a.s_id from score a where a.c_id in(select a.c_id from score a where a.s_id='01')
    );

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

select a.* from student a where a.s_id in(
    select distinct s_id from score where s_id!='01' and c_id in(select c_id from score where s_id='01')
    group by s_id 
    having count(1)=(select count(1) from score where s_id='01'));
-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名 
select a.s_name from student a where a.s_id not in (
    select s_id from score where c_id = 
                (select c_id from course where t_id =(
                    select t_id from teacher where t_name = '张三')) 
                group by s_id);

-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 
select a.s_id,a.s_name,ROUND(AVG(b.s_score)) from 
    student a 
    left join score b on a.s_id = b.s_id
    where a.s_id in(
            select s_id from score where s_score<60 GROUP BY  s_id having count(1)>=2)
    GROUP BY a.s_id,a.s_name

-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息
select a.*,b.c_id,b.s_score from 
    student a,score b 
    where a.s_id = b.s_id and b.c_id='01' and b.s_score<60 ORDER BY b.s_score DESC;

-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.s_id,(select s_score from score where s_id=a.s_id and c_id='01') as 语文,
                (select s_score from score where s_id=a.s_id and c_id='02') as 数学,
                (select s_score from score where s_id=a.s_id and c_id='03') as 英语,
            round(avg(s_score),2) as 平均分 from score a  GROUP BY a.s_id ORDER BY 平均分 DESC;

-- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select a.c_id,b.c_name,MAX(s_score),MIN(s_score),ROUND(AVG(s_score),2),
    ROUND(100*(SUM(case when a.s_score>=60 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 及格率,
    ROUND(100*(SUM(case when a.s_score>=70 and a.s_score<=80 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 中等率,
    ROUND(100*(SUM(case when a.s_score>=80 and a.s_score<=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 优良率,
    ROUND(100*(SUM(case when a.s_score>=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 优秀率
    from score a left join course b on a.c_id = b.c_id GROUP BY a.c_id,b.c_name

-- 19、按各科成绩进行排序，并显示排名(实现不完全)
-- mysql没有rank函数
    select a.s_id,a.c_id,
        @i:=@i +1 as i保留排名,
        @k:=(case when @score=a.s_score then @k else @i end) as rank不保留排名,
        @score:=a.s_score as score
    from (
        select s_id,c_id,s_score from score WHERE c_id='01' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC
)a,(select @k:=0,@i:=0,@score:=0)s
    union
    select a.s_id,a.c_id,
        @i:=@i +1 as i,
        @k:=(case when @score=a.s_score then @k else @i end) as rank,
        @score:=a.s_score as score
    from (
        select s_id,c_id,s_score from score WHERE c_id='02' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC
)a,(select @k:=0,@i:=0,@score:=0)s
    union
    select a.s_id,a.c_id,
        @i:=@i +1 as i,
        @k:=(case when @score=a.s_score then @k else @i end) as rank,
        @score:=a.s_score as score
    from (
        select s_id,c_id,s_score from score WHERE c_id='03' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC
)a,(select @k:=0,@i:=0,@score:=0)s

-- 20、查询学生的总成绩并进行排名
select a.s_id,
    @i:=@i+1 as i,
    @k:=(case when @score=a.sum_score then @k else @i end) as rank,
    @score:=a.sum_score as score
from (select s_id,SUM(s_score) as sum_score from score GROUP BY s_id ORDER BY sum_score DESC)a,
    (select @k:=0,@i:=0,@score:=0)s

-- 21、查询不同老师所教不同课程平均分从高到低显示 

<span class="hljs-keyword">select</span> a.t_id,c.t_name,a.c_id,ROUND(<span class="hljs-aggregate">avg</span>(s_score),<span class="hljs-number">2</span>) <span class="hljs-keyword">as</span> avg_score <span class="hljs-keyword">from</span> course a
    <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> score b <span class="hljs-keyword">on</span> a.c_id=b.c_id 
    <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> teacher c <span class="hljs-keyword">on</span> a.t_id=c.t_id
    <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> a.c_id,a.t_id,c.t_name <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> avg_score <span class="hljs-keyword">DESC</span>;</span>


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

        <span class="hljs-operator"><span class="hljs-keyword">select</span> d.*,c.排名,c.s_score,c.c_id <span class="hljs-keyword">from</span> (
            <span class="hljs-keyword">select</span> a.s_id,a.s_score,a.c_id,@i:=@i+<span class="hljs-number">1</span> <span class="hljs-keyword">as</span> 排名 <span class="hljs-keyword">from</span> score a,(<span class="hljs-keyword">select</span> @i:=<span class="hljs-number">0</span>)s <span class="hljs-keyword">where</span> a.c_id=<span class="hljs-string">'01'</span>    
        )c
        <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> student d <span class="hljs-keyword">on</span> c.s_id=d.s_id
        <span class="hljs-keyword">where</span> 排名 BETWEEN <span class="hljs-number">2</span> <span class="hljs-keyword">AND</span> <span class="hljs-number">3</span>
        <span class="hljs-keyword">UNION</span>
        <span class="hljs-keyword">select</span> d.*,c.排名,c.s_score,c.c_id <span class="hljs-keyword">from</span> (
            <span class="hljs-keyword">select</span> a.s_id,a.s_score,a.c_id,@j:=@j+<span class="hljs-number">1</span> <span class="hljs-keyword">as</span> 排名 <span class="hljs-keyword">from</span> score a,(<span class="hljs-keyword">select</span> @j:=<span class="hljs-number">0</span>)s <span class="hljs-keyword">where</span> a.c_id=<span class="hljs-string">'02'</span>    
        )c
        <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> student d <span class="hljs-keyword">on</span> c.s_id=d.s_id
        <span class="hljs-keyword">where</span> 排名 BETWEEN <span class="hljs-number">2</span> <span class="hljs-keyword">AND</span> <span class="hljs-number">3</span>
        <span class="hljs-keyword">UNION</span>
        <span class="hljs-keyword">select</span> d.*,c.排名,c.s_score,c.c_id <span class="hljs-keyword">from</span> (
            <span class="hljs-keyword">select</span> a.s_id,a.s_score,a.c_id,@k:=@k+<span class="hljs-number">1</span> <span class="hljs-keyword">as</span> 排名 <span class="hljs-keyword">from</span> score a,(<span class="hljs-keyword">select</span> @k:=<span class="hljs-number">0</span>)s <span class="hljs-keyword">where</span> a.c_id=<span class="hljs-string">'03'</span>    
        )c
        <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> student d <span class="hljs-keyword">on</span> c.s_id=d.s_id
        <span class="hljs-keyword">where</span> 排名 BETWEEN <span class="hljs-number">2</span> <span class="hljs-keyword">AND</span> <span class="hljs-number">3</span>;</span>


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

    <span class="hljs-operator"><span class="hljs-keyword">select</span> <span class="hljs-keyword">distinct</span> f.c_name,a.c_id,b.<span class="hljs-string">`85-100`</span>,b.百分比,c.<span class="hljs-string">`70-85`</span>,c.百分比,d.<span class="hljs-string">`60-70`</span>,d.百分比,e.<span class="hljs-string">`0-60`</span>,e.百分比 <span class="hljs-keyword">from</span> score a
            <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> (<span class="hljs-keyword">select</span> c_id,<span class="hljs-aggregate">SUM</span>(<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> s_score &gt;<span class="hljs-number">85</span> <span class="hljs-keyword">and</span> s_score &lt;=<span class="hljs-number">100</span> <span class="hljs-keyword">then</span> <span class="hljs-number">1</span> <span class="hljs-keyword">else</span> <span class="hljs-number">0</span> <span class="hljs-keyword">end</span>) <span class="hljs-keyword">as</span> <span class="hljs-string">`85-100`</span>,
                                        ROUND(<span class="hljs-number">100</span>*(<span class="hljs-aggregate">SUM</span>(<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> s_score &gt;<span class="hljs-number">85</span> <span class="hljs-keyword">and</span> s_score &lt;=<span class="hljs-number">100</span> <span class="hljs-keyword">then</span> <span class="hljs-number">1</span> <span class="hljs-keyword">else</span> <span class="hljs-number">0</span> <span class="hljs-keyword">end</span>)/<span class="hljs-aggregate">count</span>(*)),<span class="hljs-number">2</span>) <span class="hljs-keyword">as</span> 百分比
                            <span class="hljs-keyword">from</span> score <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> c_id)b <span class="hljs-keyword">on</span> a.c_id=b.c_id
            <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> (<span class="hljs-keyword">select</span> c_id,<span class="hljs-aggregate">SUM</span>(<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> s_score &gt;<span class="hljs-number">70</span> <span class="hljs-keyword">and</span> s_score &lt;=<span class="hljs-number">85</span> <span class="hljs-keyword">then</span> <span class="hljs-number">1</span> <span class="hljs-keyword">else</span> <span class="hljs-number">0</span> <span class="hljs-keyword">end</span>) <span class="hljs-keyword">as</span> <span class="hljs-string">`70-85`</span>,
                                        ROUND(<span class="hljs-number">100</span>*(<span class="hljs-aggregate">SUM</span>(<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> s_score &gt;<span class="hljs-number">70</span> <span class="hljs-keyword">and</span> s_score &lt;=<span class="hljs-number">85</span> <span class="hljs-keyword">then</span> <span class="hljs-number">1</span> <span class="hljs-keyword">else</span> <span class="hljs-number">0</span> <span class="hljs-keyword">end</span>)/<span class="hljs-aggregate">count</span>(*)),<span class="hljs-number">2</span>) <span class="hljs-keyword">as</span> 百分比
                            <span class="hljs-keyword">from</span> score <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> c_id)c <span class="hljs-keyword">on</span> a.c_id=c.c_id
            <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> (<span class="hljs-keyword">select</span> c_id,<span class="hljs-aggregate">SUM</span>(<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> s_score &gt;<span class="hljs-number">60</span> <span class="hljs-keyword">and</span> s_score &lt;=<span class="hljs-number">70</span> <span class="hljs-keyword">then</span> <span class="hljs-number">1</span> <span class="hljs-keyword">else</span> <span class="hljs-number">0</span> <span class="hljs-keyword">end</span>) <span class="hljs-keyword">as</span> <span class="hljs-string">`60-70`</span>,
                                        ROUND(<span class="hljs-number">100</span>*(<span class="hljs-aggregate">SUM</span>(<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> s_score &gt;<span class="hljs-number">60</span> <span class="hljs-keyword">and</span> s_score &lt;=<span class="hljs-number">70</span> <span class="hljs-keyword">then</span> <span class="hljs-number">1</span> <span class="hljs-keyword">else</span> <span class="hljs-number">0</span> <span class="hljs-keyword">end</span>)/<span class="hljs-aggregate">count</span>(*)),<span class="hljs-number">2</span>) <span class="hljs-keyword">as</span> 百分比
                            <span class="hljs-keyword">from</span> score <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> c_id)d <span class="hljs-keyword">on</span> a.c_id=d.c_id
            <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> (<span class="hljs-keyword">select</span> c_id,<span class="hljs-aggregate">SUM</span>(<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> s_score &gt;=<span class="hljs-number">0</span> <span class="hljs-keyword">and</span> s_score &lt;=<span class="hljs-number">60</span> <span class="hljs-keyword">then</span> <span class="hljs-number">1</span> <span class="hljs-keyword">else</span> <span class="hljs-number">0</span> <span class="hljs-keyword">end</span>) <span class="hljs-keyword">as</span> <span class="hljs-string">`0-60`</span>,
                                        ROUND(<span class="hljs-number">100</span>*(<span class="hljs-aggregate">SUM</span>(<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> s_score &gt;=<span class="hljs-number">0</span> <span class="hljs-keyword">and</span> s_score &lt;=<span class="hljs-number">60</span> <span class="hljs-keyword">then</span> <span class="hljs-number">1</span> <span class="hljs-keyword">else</span> <span class="hljs-number">0</span> <span class="hljs-keyword">end</span>)/<span class="hljs-aggregate">count</span>(*)),<span class="hljs-number">2</span>) <span class="hljs-keyword">as</span> 百分比
                            <span class="hljs-keyword">from</span> score <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> c_id)e <span class="hljs-keyword">on</span> a.c_id=e.c_id
            <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> course f <span class="hljs-keyword">on</span> a.c_id = f.c_id


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

    <span class="hljs-keyword">select</span> a.s_id,
            @i:=@i+<span class="hljs-number">1</span> <span class="hljs-keyword">as</span> <span class="hljs-string">'不保留空缺排名'</span>,
            @k:=(<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> @avg_score=a.avg_s <span class="hljs-keyword">then</span> @k <span class="hljs-keyword">else</span> @i <span class="hljs-keyword">end</span>) <span class="hljs-keyword">as</span> <span class="hljs-string">'保留空缺排名'</span>,
            @avg_score:=avg_s <span class="hljs-keyword">as</span> <span class="hljs-string">'平均分'</span>
    <span class="hljs-keyword">from</span> (<span class="hljs-keyword">select</span> s_id,ROUND(<span class="hljs-aggregate">AVG</span>(s_score),<span class="hljs-number">2</span>) <span class="hljs-keyword">as</span> avg_s <span class="hljs-keyword">from</span> score <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> s_id)a,(<span class="hljs-keyword">select</span> @avg_score:=<span class="hljs-number">0</span>,@i:=<span class="hljs-number">0</span>,@k:=<span class="hljs-number">0</span>)b;</span>


– 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

– 26、查询每门课程被选修的学生数 

    <span class="hljs-keyword">select</span> c_id,<span class="hljs-aggregate">count</span>(s_id) <span class="hljs-keyword">from</span> score a <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> c_id


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

        select s_id,s_name from student where s_id in( 

                select s_id from score GROUP BY s_id HAVING COUNT(c_id)=2);

– 28、查询男生、女生人数  

        select s_sex,COUNT(s_sex) as 人数  from student GROUP BY s_sex

– 29、查询名字中含有“风”字的学生信息

    <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> student <span class="hljs-keyword">where</span> s_name <span class="hljs-keyword">like</span> <span class="hljs-string">'%风%'</span>;</span>


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

    <span class="hljs-operator"><span class="hljs-keyword">select</span> a.s_name,a.s_sex,<span class="hljs-aggregate">count</span>(*) <span class="hljs-keyword">from</span> student a  <span class="hljs-keyword">JOIN</span> 
                student b <span class="hljs-keyword">on</span> a.s_id !=b.s_id <span class="hljs-keyword">and</span> a.s_name = b.s_name <span class="hljs-keyword">and</span> a.s_sex = b.s_sex
    <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> a.s_name,a.s_sex


– 31、查询1990年出生的学生名单

    <span class="hljs-keyword">select</span> s_name <span class="hljs-keyword">from</span> student <span class="hljs-keyword">where</span> s_birth <span class="hljs-keyword">like</span> <span class="hljs-string">'1990%'</span>


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

<span class="hljs-keyword">select</span> c_id,ROUND(<span class="hljs-aggregate">AVG</span>(s_score),<span class="hljs-number">2</span>) <span class="hljs-keyword">as</span> avg_score <span class="hljs-keyword">from</span> score <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> c_id <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> avg_score <span class="hljs-keyword">DESC</span>,c_id <span class="hljs-keyword">ASC</span>


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

<span class="hljs-keyword">select</span> a.s_id,b.s_name,ROUND(<span class="hljs-aggregate">avg</span>(a.s_score),<span class="hljs-number">2</span>) <span class="hljs-keyword">as</span> avg_score <span class="hljs-keyword">from</span> score a
    <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> student b <span class="hljs-keyword">on</span> a.s_id=b.s_id <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> s_id <span class="hljs-keyword">HAVING</span> avg_score&gt;=<span class="hljs-number">85</span>


– 34、查询课程名称为“数学”，且分数低于60的学生姓名和分数 

    <span class="hljs-keyword">select</span> a.s_name,b.s_score <span class="hljs-keyword">from</span> score b <span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span> student a <span class="hljs-keyword">on</span> a.s_id=b.s_id <span class="hljs-keyword">where</span> b.c_id=(
                <span class="hljs-keyword">select</span> c_id <span class="hljs-keyword">from</span> course <span class="hljs-keyword">where</span> c_name =<span class="hljs-string">'数学'</span>) <span class="hljs-keyword">and</span> b.s_score&lt;<span class="hljs-number">60</span>


– 35、查询所有学生的课程及分数情况； 

    <span class="hljs-keyword">select</span> a.s_id,a.s_name,
                <span class="hljs-aggregate">SUM</span>(<span class="hljs-keyword">case</span> c.c_name <span class="hljs-keyword">when</span> <span class="hljs-string">'语文'</span> <span class="hljs-keyword">then</span> b.s_score <span class="hljs-keyword">else</span> <span class="hljs-number">0</span> <span class="hljs-keyword">end</span>) <span class="hljs-keyword">as</span> <span class="hljs-string">'语文'</span>,
                <span class="hljs-aggregate">SUM</span>(<span class="hljs-keyword">case</span> c.c_name <span class="hljs-keyword">when</span> <span class="hljs-string">'数学'</span> <span class="hljs-keyword">then</span> b.s_score <span class="hljs-keyword">else</span> <span class="hljs-number">0</span> <span class="hljs-keyword">end</span>) <span class="hljs-keyword">as</span> <span class="hljs-string">'数学'</span>,
                <span class="hljs-aggregate">SUM</span>(<span class="hljs-keyword">case</span> c.c_name <span class="hljs-keyword">when</span> <span class="hljs-string">'英语'</span> <span class="hljs-keyword">then</span> b.s_score <span class="hljs-keyword">else</span> <span class="hljs-number">0</span> <span class="hljs-keyword">end</span>) <span class="hljs-keyword">as</span> <span class="hljs-string">'英语'</span>,
                <span class="hljs-aggregate">SUM</span>(b.s_score) <span class="hljs-keyword">as</span>  <span class="hljs-string">'总分'</span>
    <span class="hljs-keyword">from</span> student a <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> score b <span class="hljs-keyword">on</span> a.s_id = b.s_id 
    <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> course c <span class="hljs-keyword">on</span> b.c_id = c.c_id 
    <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> a.s_id,a.s_name


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

            select a.s_name,b.c_name,c.s_score from course b left join score c on b.c_id = c.c_id 

                left join student a on a.s_id=c.s_id where c.s_score>=70

– 37、查询不及格的课程 

        select a.s_id,a.c_id,b.c_name,a.s_score from score a left join course b on a.c_id = b.c_id 

            where a.s_score<60 

–38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；  

        select a.s_id,b.s_name from score a LEFT JOIN student b on a.s_id = b.s_id 

            where a.c_id = ‘01’ and a.s_score>80

– 39、求每门课程的学生人数  

        select count(*) from score GROUP BY c_id;

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

    <span class="hljs-comment">-- 查询老师id   </span>
    <span class="hljs-operator"><span class="hljs-keyword">select</span> c_id <span class="hljs-keyword">from</span> course c,teacher d <span class="hljs-keyword">where</span> c.t_id=d.t_id <span class="hljs-keyword">and</span> d.t_name=<span class="hljs-string">'张三'</span>
    -- 查询最高分（可能有相同分数）
    <span class="hljs-keyword">select</span> <span class="hljs-aggregate">MAX</span>(s_score) <span class="hljs-keyword">from</span> score <span class="hljs-keyword">where</span> c_id=<span class="hljs-string">'02'</span>
    -- 查询信息
    <span class="hljs-keyword">select</span> a.*,b.s_score,b.c_id,c.c_name <span class="hljs-keyword">from</span> student a
        <span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span> score b <span class="hljs-keyword">on</span> a.s_id = b.s_id
        <span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span> course c <span class="hljs-keyword">on</span> b.c_id=c.c_id
        <span class="hljs-keyword">where</span> b.c_id =(<span class="hljs-keyword">select</span> c_id <span class="hljs-keyword">from</span> course c,teacher d <span class="hljs-keyword">where</span> c.t_id=d.t_id <span class="hljs-keyword">and</span> d.t_name=<span class="hljs-string">'张三'</span>)
        <span class="hljs-keyword">and</span> b.s_score <span class="hljs-keyword">in</span> (<span class="hljs-keyword">select</span> <span class="hljs-aggregate">MAX</span>(s_score) <span class="hljs-keyword">from</span> score <span class="hljs-keyword">where</span> c_id=<span class="hljs-string">'02'</span>)


– 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩  

    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

– 42、查询每门功成绩最好的前两名  

        – 牛逼的写法 

    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

– 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列 

        select c_id,count(*) as total from score GROUP BY c_id HAVING total>5 ORDER BY total,c_id ASC

– 44、检索至少选修两门课程的学生学号  

        select s_id,count(*) as sel from score GROUP BY s_id HAVING sel>=2

– 45、查询选修了全部课程的学生信息  

        select * from student where s_id in( 

            select s_id from score GROUP BY s_id HAVING count()=(select count() from course))

–46、查询各学生的年龄 

    – 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

<span class="hljs-keyword">select</span> s_birth,(DATE_FORMAT(NOW(),<span class="hljs-string">'%Y'</span>)-DATE_FORMAT(s_birth,<span class="hljs-string">'%Y'</span>) - 
            (<span class="hljs-keyword">case</span> <span class="hljs-keyword">when</span> DATE_FORMAT(NOW(),<span class="hljs-string">'%m%d'</span>)&gt;DATE_FORMAT(s_birth,<span class="hljs-string">'%m%d'</span>) <span class="hljs-keyword">then</span> <span class="hljs-number">0</span> <span class="hljs-keyword">else</span> <span class="hljs-number">1</span> <span class="hljs-keyword">end</span>)) <span class="hljs-keyword">as</span> age
    <span class="hljs-keyword">from</span> student;</span>


– 47、查询本周过生日的学生 

    select * from student where WEEK(DATE_FORMAT(NOW(),‘%Y%m%d’))=WEEK(s_birth) 

    select * from student where YEARWEEK(s_birth)=YEARWEEK(DATE_FORMAT(NOW(),‘%Y%m%d’))

<span class="hljs-keyword">select</span> WEEK(DATE_FORMAT(NOW(),<span class="hljs-string">'%Y%m%d'</span>))


– 48、查询下周过生日的学生 

    select * from student where WEEK(DATE_FORMAT(NOW(),‘%Y%m%d’))+1 =WEEK(s_birth)

– 49、查询本月过生日的学生

<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> student <span class="hljs-keyword">where</span> <span class="hljs-keyword">MONTH</span>(DATE_FORMAT(NOW(),<span class="hljs-string">'%Y%m%d'</span>)) =<span class="hljs-keyword">MONTH</span>(s_birth)


– 50、查询下月过生日的学生 

    select * from student where MONTH(DATE_FORMAT(NOW(),‘%Y%m%d’))+1 =MONTH(s_birth) 


 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355

骑蜗牛追梦者

关注

1
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
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_sco...
复制链接

扫一扫