平常加班不多,为了提高自己的SQL能力,找了网上流传的比较广泛的SQL50题,先把题目拿过来,计划抽空作为练习用HiveSQL完成。

开始日期:2020-03-18

完成日期:2020-03-21

建表语句

1
2
3
4
5
6
7
create table student(s_id string,s_name string,s_birth string,s_sex string) row format delimited fields terminated by '\t';

create table course(c_id string,c_name string,t_id string) row format delimited fields terminated by '\t';

create table teacher(t_id string,t_name string) row format delimited fields terminated by '\t';

create table score(s_id string,c_id string,s_score int) row format delimited fields terminated by '\t';

数据:

student.txt

1
2
3
4
5
6
7
8
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 女

teacher.txt

1
2
3
01	张三
02 李四
03 王五

course.txt

1
2
3
01	语文	02
02 数学 01
03 英语 03

score.txt

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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
2
3
4
5
6
7
load data local inpath '~/student.txt' into table student;

load data local inpath '~/course.txt' into table course;

load data local inpath '~/teacher.txt' into table teacher;

load data local inpath '~/score.txt' into table score;

表之间的关系如图:

表关系

  • 1、查询”01”课程比”02”课程成绩高的学生的信息及课程分数:
1
2
3
4
5
6
7
8
9
10
11
SELECT 
T0.*,
T1.s_score AS c01,
T2.s_score as c02
FROM
student AS T0,
(SELECT s_id,s_score FROM score WHERE c_id='01') AS T1,
(SELECT s_id,s_score FROM score WHERE c_id='02') AS T2
WHERE T0.s_id=T1.s_id
AND T0.s_id=T2.s_id
AND T1.s_score>T2.s_score;

运行结果:

image-20200318221755774

  • 2、查询同时存在” 01 “课程和” 02 “课程的情况:
1
2
3
4
5
6
7
8
9
10
SELECT
t3.*,
t1.s_score as c01,
t2.s_score as c02
FROM
(SELECT s_id,s_score FROM score WHERE c_id='01') as t1, -- 选修01课程的同学
(SELECT s_id,s_score FROM score WHERE c_id='02') as t2, -- 选修01课程的同学
student as t3
WHERE t1.s_id=t2.s_id
AND t2.s_id=t3.s_id;

运行结果:

image-20200318224041101

  • 3、查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null )
1
2
3
4
5
6
7
8
9
10
SELECT
t2.*,
t1.s_score AS c01,
t3.s_score AS c02
FROM
(SELECT s_id,s_score FROM score WHERE c_id='01') AS t1, -- 选修01课程的同学
student AS t2
LEFT JOIN (SELECT s_id,s_score FROM score WHERE c_id='02') AS t3 -- 选修01课程的同学
ON t2.s_id = t3.s_id
WHERE t1.s_id=t2.s_id;

运行结果:
image-20200318225024279

  • 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩:
    (包括有成绩的和无成绩的)
1
2
3
4
5
6
7
8
9
10
11
12
SELECT
T1.s_id,
T1.s_name,
T2.avg_sc
FROM student AS T1
JOIN(SELECT
s_id,
avg(s_score) AS avg_sc
FROM score
GROUP BY s_id
having avg_sc<60) AS T2
ON T1.s_id=T2.s_id;

运行结果:

image-20200318225822830

  • 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩:
1
2
3
4
5
6
7
8
9
10
SELECT
T1.s_id,
T1.s_name,
T2.co_cnt,
T3.sum_score
FROM student AS T1
LEFT JOIN (SELECT s_id,count(1) AS co_cnt FROM score GROUP BY s_id) AS T2
ON T1.s_id=T2.s_id
LEFT JOIN (SELECT s_id,sum(s_score) AS sum_score FROM score GROUP BY s_id) AS T3
ON T1.s_id=T3.s_id;

运行结果:

image-20200318231242423

  • 6、查询”李”姓老师的数量:
1
SELECT COUNT(1) FROM teacher WHERE t_name LIKE "李%";

image-20200319073738350

  • 7、查询学过”张三”老师授课的同学的信息:
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
T1.*,
T3.c_name,
T4.t_name
FROM
student AS T1,
score AS T2,
course AS T3,
teacher AS T4
WHERE T1.s_id=T2.s_id
AND T2.c_id=T3.c_id
AND T3.t_id=T4.t_id
AND T4.t_name="张三";

运行结果:

image-20200319074432715

  • 8、查询没学过”张三”老师授课的同学的信息:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT A1.* FROM student AS A1
LEFT JOIN(SELECT
T1.s_id
FROM
student AS T1,
score AS T2,
course AS T3,
teacher AS T4
WHERE T1.s_id=T2.s_id
AND T2.c_id=T3.c_id
AND T3.t_id=T4.t_id
AND T4.t_name="张三") AS A2
ON A1.s_id=A2.s_id
WHERE A2.s_id IS NULL;

运行结果:

image-20200319075846289

  • 9、查询学过编号为”01”并且也学过编号为”02”的课程的同学的信息:
1
2
3
4
5
6
7
8
SELECT A.* 
FROM student AS A
JOIN (SELECT
T1.s_id
FROM (SELECT s_id FROM score WHERE c_id='01') AS T1
JOIN (SELECT s_id FROM score WHERE c_id='02') AS T2
ON T1.s_id=T2.s_id) AS B
ON A.s_id=B.s_id;

运行结果:

image-20200319080655329

  • 10、查询学过编号为”01”但是没有学过编号为”02”的课程的同学的信息:
1
2
3
4
5
6
7
8
9
SELECT A.* 
FROM student AS A
JOIN (SELECT
T1.s_id
FROM (SELECT s_id FROM score WHERE c_id='01') AS T1
LEFT JOIN (SELECT s_id FROM score WHERE c_id='02') AS T2
ON T1.s_id=T2.s_id
WHERE T2.s_id IS NULL) AS B
ON A.s_id=B.s_id;

运行结果:

image-20200319080957865

  • 11、查询没有学全所有课程的同学的信息:
1
2
3
4
5
6
7
SELECT
T1.*,
T2.CNT_C
FROM student AS T1
LEFT JOIN (SELECT s_id,count(1) AS CNT_C FROM score GROUP BY s_id) AS T2
ON T1.s_id=T2.s_id
WHERE T2.CNT_C<3;

image-20200319221856619

  • 12、查询至少有一门课与学号为”01”的同学所学相同的同学的信息:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT 
t3.*,
t4.cnt
FROM student AS t3
JOIN(
SELECT s_id,
COUNT(1) AS cnt
FROM score AS t1
JOIN (SELECT c_id
FROM score
WHERE s_id ="01") AS t2
ON t1.c_id = t2.c_id
GROUP BY s_id
HAVING cnt>0) AS t4
ON t3.s_id=t4.s_id;

image-20200319223531461

  • 13、查询和”01”号的同学学习的课程完全相同的其他同学的信息:
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
A1.*,
A2.CNT_C
FROM student AS A1
JOIN (SELECT T1.s_id,count(1) AS CNT_C
FROM score AS T1
JOIN (SELECT c_id FROM score WHERE s_id='01') AS T2
ON T1.c_id=T2.c_id
GROUP BY T1.s_id) AS A2
ON A1.s_id=A2.s_id
AND A1.S_id<>'01'
JOIN (SELECT count(1) AS CNT_C FROM score where s_id='01') AS A3
ON A2.CNT_C=A3.CNT_C;

image-20200319230518263

  • 14、查询没学过”张三”老师讲授的任一门课程的学生姓名:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT S1.s_name 
FROM student AS S1
LEFT JOIN (SELECT
A1.s_id
FROM score AS A1
JOIN (SELECT T2.c_id
FROM teacher AS T1
JOIN course AS T2
ON T1.t_id=T2.t_id
WHERE T1.t_name='张三') AS A2
ON A1.c_id=A2.c_id
GROUP BY A1.s_id) AS S2
ON S1.s_id=S2.s_id
WHERE S2.s_id IS NULL;

image-20200319232211741

  • 15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT
T1.s_id,
T1.s_name,
T3.AVG_C
FROM student AS T1
JOIN (SELECT
s_id,
count(1) AS CNT_C
FROM score
WHERE s_score<60
GROUP BY s_id
having CNT_C>=2) AS T2
ON T1.s_id=T2.s_id
JOIN (SELECT
s_id,
AVG(s_score) AS AVG_C
FROM score
GROUP BY s_id) AS T3
ON T1.s_id=T3.s_id;

image-20200320080055175

  • 16、检索”01”课程分数小于60,按分数降序排列的学生信息:
1
2
3
4
5
6
7
8
9
10
11
12
SELECT
T1.*,
T2.s_score
FROM student AS T1
JOIN (SELECT
s_id,
s_score
FROM score
WHERE c_id='01'
AND s_score<60) AS T2
ON T1.S_id=T2.s_id
ORDER BY T2.s_score DESC;

运行结果:

image-20200320080551819

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

尝试用WITH的写法,相对常规写法逻辑还是比较清晰的。

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
WITH T1 AS (
-- 平均分
SELECT
s_id,
AVG(s_score) AS AVG_S
FROM score
GROUP BY s_id
), T2 AS(
-- 语文分数
SELECT
s_id,
s_score
FROM score
WHERE c_id='01'
)
, T3 AS(
-- 数学分数
SELECT
s_id,
s_score
FROM score
WHERE c_id='02'
), T4 AS(
-- 英语分数
SELECT
s_id,
s_score
FROM score
WHERE c_id='03'
)
SELECT
T0.s_id,
T0.s_name,
(CASE WHEN T1.AVG_S IS NULL THEN '0' ELSE round(T1.AVG_S,2) END) AS AVG_S,
(CASE WHEN T2.s_score IS NULL THEN '0' ELSE T2.s_score END) AS CHINESE,
(CASE WHEN T3.s_score IS NULL THEN '0' ELSE T3.s_score END) AS MATH,
(CASE WHEN T4.s_score IS NULL THEN '0' ELSE T4.s_score END) AS ENGLISH
FROM student AS T0
LEFT JOIN T1
ON T1.s_id=T0.s_id
LEFT JOIN T2
ON T2.s_id=T0.s_id
LEFT JOIN T3
ON T3.s_id=T0.s_id
LEFT JOIN T4
ON T4.s_id=T0.s_id
ORDER BY AVG_S DESC;

image-20200321105038801

  • 18.查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分, ,平均分,及格率,中等率,优良率,优秀率: (及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90)
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
WITH A1 AS(
SELECT
c_id,
MAX(s_score) AS max_c,
MIN(s_score) AS min_c,
ROUND(AVG(s_score),2) AS avg_c,
ROUND(SUM(CASE WHEN s_score>=60 THEN 1 ELSE 0 END)/count(s_score),2) AS L1,
ROUND(SUM(CASE WHEN s_score BETWEEN 70 AND 79 THEN 1 ELSE 0 END)/count(s_score),2) AS L2,
ROUND(SUM(CASE WHEN s_score BETWEEN 80 AND 89 THEN 1 ELSE 0 END)/count(s_score),2) AS L3,
ROUND(SUM(CASE WHEN s_score>=90 THEN 1 ELSE 0 END)/count(s_score),2) AS L4
FROM score GROUP BY c_id
)
SELECT
A0.c_id,
A0.c_name,
A1.max_c,
A1.min_c,
A1.avg_c,
A1.L1,
A1.L2,
A1.L3,
A1.L4
FROM course AS A0
JOIN A1
ON A0.c_id=A1.c_id;

image-20200321153421319

  • 19、按各科成绩进行排序,并显示排名:
1
2
3
4
5
6
7
8
9
10
11
12
SELECT
T1.s_id,
T3.s_name,
T1.c_id,
T2.c_name,
T1.s_score,
RANK() OVER (PARTITION BY T1.c_id ORDER BY T1.s_score DESC)
FROM score AS T1
JOIN course AS T2
ON T1.c_id=T2.c_id
JOIN student AS T3
ON T1.s_id=T3.s_id;
  • 20、查询学生的总成绩并进行排名:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    SELECT
    T1.s_id,
    T3.s_name,
    sum(T1.s_score) AS SUM_C,
    RANK() OVER(ORDER BY sum(T1.s_score) DESC)
    FROM score AS T1
    JOIN student AS T3
    ON T1.s_id=T3.s_id
    GROUP BY T1.s_id,T3.s_name;

image-20200321181506578

  • 21、查询不同老师所教不同课程平均分从高到低显示:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT
    T1.c_id,
    T3.t_name,
    ROUND(AVG(T1.s_score),2),
    RANK() OVER(ORDER BY AVG(T1.s_score) DESC)
    FROM score AS T1
    JOIN course AS T2
    ON T1.c_id=T2.c_id
    JOIN teacher AS T3
    ON T2.t_id=T3.t_id
    GROUP BY T1.c_id,T3.t_name;

image-20200321181609080

  • 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    WITH A1 AS(
    SELECT
    T1.s_id,
    T3.s_name,
    T1.c_id,
    T2.c_name,
    T1.s_score,
    RANK() OVER (PARTITION BY T1.c_id ORDER BY T1.s_score DESC) AS rk
    FROM score AS T1
    JOIN course AS T2
    ON T1.c_id=T2.c_id
    JOIN student AS T3
    ON T1.s_id=T3.s_id
    )
    SELECT A1.*
    FROM A1
    WHERE A1.rk=2 OR A1.rk=3;

image-20200321181701546

  • 23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
    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
    WITH A1 AS(
    SELECT
    c_id,
    SUM(CASE WHEN s_score BETWEEN 0 AND 59 THEN 1 ELSE 0 END) AS L0, -- [0-59分的人数]
    ROUND(SUM(CASE WHEN s_score BETWEEN 0 AND 59 THEN 1 ELSE 0 END)/COUNT(1),2) AS H0, -- [0-59分的人数占比]
    SUM(CASE WHEN s_score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS L1, -- [60-69分的人数]
    ROUND(SUM(CASE WHEN s_score BETWEEN 60 AND 69 THEN 1 ELSE 0 END)/COUNT(1),2) AS H1, -- [60-69分的人数占比]
    SUM(CASE WHEN s_score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS L2, -- [70-85分的人数]
    ROUND(SUM(CASE WHEN s_score BETWEEN 70 AND 85 THEN 1 ELSE 0 END)/COUNT(1),2) AS H2, -- [70-85分的人数占比]
    SUM(CASE WHEN s_score BETWEEN 86 AND 100 THEN 1 ELSE 0 END) AS L3, -- [86-100分的人数]
    ROUND(SUM(CASE WHEN s_score BETWEEN 86 AND 100 THEN 1 ELSE 0 END)/COUNT(1),2) AS H3 -- [86-100分的人数占比]
    FROM score GROUP BY c_id
    )
    SELECT
    A0.c_id,
    A0.c_name,
    A1.L3,
    A1.H3,
    A1.L2,
    A1.H2,
    A1.L1,
    A1.H1,
    A1.L0,
    A1.H0
    FROM course AS A0
    JOIN A1
    ON A0.c_id=A1.c_id;

image-20200321181822411

  • 24、查询学生平均成绩及其名次:
1
2
3
4
5
6
7
8
9
SELECT
T1.s_id,
T3.s_name,
ROUND(AVG(T1.s_score),2) AS AVG_C,
RANK() OVER(ORDER BY sum(T1.s_score) DESC)
FROM score AS T1
JOIN student AS T3
ON T1.s_id=T3.s_id
GROUP BY T1.s_id,T3.s_name;

image-20200321181938010

  • 25、查询各科成绩前三名的记录
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    WITH A1 AS(
    SELECT
    T1.s_id,
    T3.s_name,
    T1.c_id,
    T2.c_name,
    T1.s_score,
    RANK() OVER (PARTITION BY T1.c_id ORDER BY T1.s_score DESC) AS rk
    FROM score AS T1
    JOIN course AS T2
    ON T1.c_id=T2.c_id
    JOIN student AS T3
    ON T1.s_id=T3.s_id
    )
    SELECT A1.*
    FROM A1
    WHERE A1.rk<=3;

image-20200321182026689

  • 26、查询每门课程被选修的学生数:
    1
    2
    3
    4
    5
    6
    7
    8
    SELECT
    T1.c_id,
    T2.c_name,
    COUNT(1)
    FROM score AS T1
    JOIN course AS T2
    ON T1.c_id=T2.c_id
    GROUP BY T1.c_id,T2.c_name

image-20200321182103466

  • 27、查询出只有两门课程的全部学生的学号和姓名:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    WITH A1 AS(
    SELECT
    T1.s_id,
    COUNT(1) AS CNT
    FROM score AS T1
    GROUP BY T1.s_id
    HAVING CNT=2
    )
    SELECT
    A2.s_id,
    A2.s_name,
    A1.CNT
    FROM student AS A2
    JOIN A1
    ON A1.s_id=A2.s_id;

image-20200321182151875

  • 28、查询男生、女生人数:
    1
    2
    3
    4
    5
    SELECT
    T1.s_sex,
    COUNT(1)
    FROM student AS T1
    GROUP BY s_sex;

image-20200321182221250

  • 29、查询名字中含有”风”字的学生信息:
    1
    2
    3
    4
    SELECT
    T1.*
    FROM student AS T1
    WHERE T1.s_name LIKE "%风%"

image-20200321182246186

  • 30、查询同名同性学生名单,并统计同名人数:

数据中发现没有同名同性的学生,先插入几条

1
2
3
4
5
6
insert into student values
('09' , '张三' , '2017-12-20' , '女'),
('10' , '李四' , '2017-12-25' , '女'),
('11' , '李四' , '2012-06-06' , '女'),
('12' , '赵六' , '2013-06-13' , '女'),
('13' , '孙七' , '2014-06-01' , '女');

然后找同名同性的学生

1
2
3
4
5
6
7
SELECT
s_name,
s_sex,
COUNT(1) AS CNT_N
FROM student
GROUP BY s_name,s_sex
HAVING CNT_N>1;

image-20200321182748601

  • 31、查询1990年出生的学生名单:
1
SELECT * FROM student WHERE s_birth BETWEEN '1990-01-01' AND '1990-12-31';

image-20200321182943315

  • 32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH T1 AS (
SELECT
c_id,
ROUND(AVG(s_score),2) AS AVG_C
FROM score
GROUP BY c_id
)
SELECT
T1.c_id,
T0.c_name,
T1.AVG_C
FROM course AS T0
JOIN T1
ON T0.c_id=T1.c_id
ORDER BY T1.AVG_C DESC,T1.c_id;

image-20200321183807915

  • 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH T1 AS (
SELECT
s_id,
AVG(s_score) AS AVG_S
FROM score
GROUP BY s_id
HAVING AVG_S>=85
)
SELECT
T0.s_id,
T0.s_name,
T1.AVG_S
FROM student as T0
JOIN T1
ON T0.s_id=T1.s_id;
  • 34、查询课程名称为”数学”,且分数低于60的学生姓名和分数:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH T1 AS (
SELECT
s_id,
AVG(s_score) AS AVG_S
FROM score
GROUP BY s_id
HAVING AVG_S>=85
)
SELECT
T0.s_id,
T0.s_name,
ROUND(T1.AVG_S,2) AS AVG_S
FROM student as T0
JOIN T1
ON T0.s_id=T1.s_id;

image-20200321194621260

  • 35、查询所有学生的课程及分数情况:
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
WITH T1 AS (
SELECT
s_id,
s_score
FROM score
WHERE c_id='01'
), T2 AS (
SELECT
s_id,
s_score
FROM score
WHERE c_id='02'
), T3 AS (
SELECT
s_id,
s_score
FROM score
WHERE c_id='03'
)
SELECT
T0.s_id,
T0.s_name,
(CASE WHEN T1.s_score IS NULL THEN "未选修" ELSE T1.s_score END) AS CHINESE,
(CASE WHEN T2.s_score IS NULL THEN "未选修" ELSE T2.s_score END) AS MATH,
(CASE WHEN T3.s_score IS NULL THEN "未选修" ELSE T3.s_score END) AS ENGLISH
FROM student AS T0
LEFT JOIN T1
ON T0.s_id=T1.s_id
LEFT JOIN T2
ON T0.s_id=T2.s_id
LEFT JOIN T3
ON T0.s_id=T3.s_id;

image-20200321195553301

  • 36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数:
1
2
3
4
5
6
7
8
9
10
11
12
SELECT
-- T0.s_id,
T1.s_name,
-- T0.c_id,
T2.c_name,
T0.s_score
FROM score AS T0
LEFT JOIN student AS T1
ON T0.s_id=T1.s_id
LEFT JOIN course AS T2
ON T0.c_id=T2.c_id
WHERE T0.s_score>70;

image-20200321200351423

  • 37、查询课程不及格的学生:
1
2
3
4
5
6
7
8
9
10
11
12
SELECT
-- T0.s_id,
T1.s_name,
-- T0.c_id,
T2.c_name,
T0.s_score
FROM score AS T0
LEFT JOIN student AS T1
ON T0.s_id=T1.s_id
LEFT JOIN course AS T2
ON T0.c_id=T2.c_id
WHERE T0.s_score<60;

image-20200321200449627

  • 38、查询课程编号为01且课程成绩在80分及以上的学生的学号和姓名:
1
2
3
4
5
6
7
8
SELECT
T1.s_id,
T2.s_name
FROM score AS T1
JOIN student AS T2
ON T1.s_id=T2.s_id
WHERE T1.c_id='01'
AND T1.s_score>=80;

image-20200321201135781

  • 39、求每门课程的学生人数:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH T1 AS (
SELECT
c_id,
COUNT(1) AS CNT_S
FROM score
GROUP BY c_id
)
SELECT
T2.c_id,
T2.c_name,
T1.CNT_S
FROM course AS T2
JOIN T1
ON T1.c_id=T2.c_id;

image-20200321201506854

  • 40、查询选修”张三”老师所授课程的学生中,成绩最高的学生信息及其成绩:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
WITH T1 AS(
SELECT
A4.*,
A3.s_score,
RANK() OVER(ORDER BY A3.s_score DESC) AS RK
FROM teacher AS A1
JOIN course AS A2
ON A1.t_id=A2.t_id
JOIN score AS A3
ON A3.c_id=A2.c_id
JOIN student AS A4
ON A3.s_id=A4.s_id
WHERE A1.t_name='张三'
)
SELECT
T1.*
FROM T1
WHERE RK=1;

image-20200321203900937

  • 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩:
1
2
3
4
5
6
7
8
9
SELECT 
DISTINCT T1.s_id,
T1.c_id,
T3.c_name,
T1.s_score
FROM score AS T1,score AS T2,course AS T3
WHERE T1.c_id <> T2.c_id
AND T1.s_score=T2.s_score
AND T1.c_id=T3.c_id;

image-20200321220508059

  • 42、查询每门课程成绩最好的前三名:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH A1 AS(
SELECT
T1.s_id,
T1.c_id,
T1.s_score,
RANK() OVER(PARTITION BY T1.c_id ORDER BY T1.s_score DESC) AS RK
FROM score AS T1
)
SELECT
A1.s_id,
A3.s_name,
A2.c_name,
A1.s_score,
A1.RK
FROM A1
JOIN course AS A2
ON A1.c_id=A2.c_id
JOIN student AS A3
ON A1.s_id=A3.s_id
WHERE RK<=3;

image-20200321205236979

  • 43、统计每门课程的学生选修人数(超过5人的课程才统计):要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
1
2
3
4
5
6
7
SELECT
c_id,
COUNT(1) AS CNT_C
FROM score
GROUP BY c_id
HAVING CNT_C>=5
ORDER BY CNT_C DESC,c_id;

image-20200321205619210

  • 44、检索至少选修两门课程的学生学号:
1
2
3
4
5
6
SELECT
s_id,
COUNT(1) AS CNT_S
FROM score
GROUP BY s_id
HAVING CNT_S>=2

image-20200321205759015

  • 45、查询选修了全部课程的学生信息:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH A1 AS(
SELECT
T1.s_id,
COUNT(1) AS CNT_S
FROM score AS T1
GROUP BY T1.s_id
HAVING CNT_S=3
)
SELECT
A0.*
FROM student A0
JOIN A1
ON A0.s_id=A1.s_id
WHERE A1.CNT_S=3;

image-20200321210907315

  • 46、查询各学生的年龄(周岁):
1
2
3
4
5
6
7
8
9
10
SELECT
s_name,
s_birth,
(YEAR(current_date())-YEAR(s_birth)-
(CASE WHEN MONTH(current_date())>MONTH(s_birth) THEN 0
WHEN MONTH(current_date())=MONTH(s_birth) THEN
(CASE WHEN DAY(current_date())>=DAY(s_birth) THEN 0 ELSE 1 END)
ELSE 1 END)
) AS AGE
FROM student;

image-20200321213703448

  • 47、查询本周过生日的学生:
1
2
3
4
5
SELECT
s_name,
s_birth
FROM student
WHERE s_birth BETWEEN DATE_ADD(NEXT_DAY(current_date(), 'MON'),-1) AND DATE_ADD(NEXT_DAY(current_date(), 'MON'),-7);

今天是3月21号,暂时没有本周过生日的同学,不过本题的思想是找到本周第一天和本周最后一天,HQL实现如下:

1
2
3
SELECT
DATE_ADD(NEXT_DAY(current_date(), 'MON'),-1) AS SUNDAY,
DATE_ADD(NEXT_DAY(current_date(), 'MON'),-7) AS MONDAY

image-20200321215322208

  • 48、查询下周过生日的学生:
1
2
3
4
5
SELECT
s_name,
s_birth
FROM student
WHERE s_birth BETWEEN NEXT_DAY(current_date(), 'MON') AND DATE_ADD(NEXT_DAY(current_date(), 'MON'),6);

同样没有下周过生日的同学,下周一到下周日的HQL如下:

1
SELECT NEXT_DAY(current_date(), 'MON') , DATE_ADD(NEXT_DAY(current_date(), 'MON'),6)

image-20200321215737713

  • 49、查询本月过生日的学生:
1
2
3
4
5
SELECT
s_name,
s_birth
FROM student
WHERE MONTH(s_birth)=MONTH(current_date());

image-20200321215852433

  • 50、查询12月份过生日的学生:
1
2
3
4
5
SELECT
s_name,
s_birth
FROM student
WHERE MONTH(s_birth)=12;

image-20200321215929799