数据分析校招sql50题-1

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

第一步 先在分数表中把课程1和课程2的数据分别筛选出之后对比课程1比课程2高的学生id，

第二步 结果再与学生表关联

结果：

ps：

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

'''

select sname, student.sid , r.avg_score from student right join

(select sid, avg(score) as avg_score from sc group by sid having avg(score) >60) r

on student.sid = r.sid

PS：

第一步 从sc表中将sid去重查出后与student表关联

第二部 将符合第一步结果的数据从student表中展示出来

结果：

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

第一步 将sc表中学生按sid分组，并聚合计算数量、总分。同时注意子查询中聚合函数要有别名( as cnum、as sum_score)

第二步 将第一步中结果与student表关联

结果：

第一步 从teacher表中找到张三老师tid

第二步 用tid关联course表找到张三老师教授课程的cid

第三步 用cid关联score表找到上过张三老师课程的学生的sid

第四步 用第三步的sid关联student表查询出具体符合条件的学生的详细信息

太笨了，多表联合查询：

结果：

排除学全所有课程之外的学生id（not in语法），再与student表关联

结果：

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

第一步 将学号为01的学生所学课程id从sc表中筛选出来

第二步 查询sc表中所学课程id在上面得到的结果中的学生id（in语法），去重

第三步 查询student表中学生id在上面得到结果中的学生信息（in语法）

mysql有group_concat函数 参考 https://blog.csdn.net/qq_35531549/article/details/90383022

postgresql稍微麻烦点。

---ARRAY_AGG()函数是一个聚合函数，它接受一组值并返回一个数组，其中将输入集中的每个值分配给该数组的元素。

----unnest(anyarray)

返回值:setof anyelement(可以理解为一个(临时)表)

说明:unnest函数将输入的数组转换成一个表,这个表的每一列都代表相应的一个数组中的元素。如果unnest与其他字段一起出现在select中，就相当于其他字段进行了一次join。

----array_to_string("数组",",") 即把数组转化为字符串，并用“,”连接(使用提供的分隔符连接数组元素)

结果:

第一步 查出张三老师教授的课程id

第二步 在sc表中查出上过张三老师课程的学生id

第三步 将结果的非（not in）去筛选student表

CREATE TABLE TestTitle (

  name   VARCHAR(10),

  title  VARCHAR(20)

)

INSERT INTO TestTitle VALUES ('张三', '程序员')

INSERT INTO TestTitle VALUES ('张三', '系统管理员')

INSERT INTO TestTitle VALUES ('张三', '网络管理员')

INSERT INTO TestTitle VALUES ('李四', '项目经理')

INSERT INTO TestTitle VALUES ('李四', '系统分析员')

Oracle  数据库的话

SELECT

  2    name,

  3    WMSYS.WM_CONCAT(title) AS allTitle

  4  FROM

  5    TestTitle

  6  GROUP BY

  7    name

NAME

ALLTITLE

李四

项目经理,系统分析员

张三

程序员,系统管理员,网络管理员

SQL Server 的话

SELECT

  name,

  STUFF(

    (

    SELECT

      ',' + title

    FROM

      TestTitle subTitle

    WHERE

      name = TestTitle.name

    FOR XML PATH('')

    ),

    1, 1, '') AS allTitle

FROM

  TestTitle

GROUP BY

  name

name       allTitle

---------- --------------------------------

李四         项目经理,系统分析员

张三         程序员,系统管理员,网络管理员

MySQL 的话

mysql> SELECT

    ->   name,

    ->   GROUP_CONCAT(title) AS allTitle

    -> FROM

    ->   TestTitle

    -> GROUP BY

    ->   name

+------+------------------------------+

| name | allTitle                     |

+------+------------------------------+

| 李四 | 项目经理,系统分析员          |

| 张三 | 程序员,系统管理员,网络管理员 |

+------+------------------------------+

2 rows in set (0.00 sec)

PostgreSQL 的话

SELECT

  name,

  array_to_string(ARRAY(SELECT unnest(array_agg(title))),',') AS allTitle

FROM

  TestTitle

GROUP BY

  name

 name |           alltitle

------+------------------------------

 李四 | 项目经理,系统分析员

 张三 | 程序员,系统管理员,网络管理员

(2 行记录)

---