SQL经典练习题（openGauss数据库）上

引言：

SQL 对于现在的互联网公司生产研发等岗位几乎是一个必备技能，你可以把 SQL 当做是一个工具，利用它可以帮助你完成相应的工作，创造价值。当然啦， 学习sql 也是有规可循的

openGauss数据库是国产数据库的佼佼者之一，其支持的SQL标准默认包含SQL2、SQL3和SQL4的主要特性。如何在openGauss数据库中使用SQL语句？ 接下来，我们将准备一些SQL经典练习题进行实践与测试。

本次练习题将分为上下两部分。

openGauss基础涉及到的常用“数据类型”和“基础SQL语法”，可参见前面的文章《openGauss数据库之SQL介绍》 或者官方文档《开发者指南》 。

1、 初始化4张基础表

1、数据表介绍

--学生表：Student(SId,Sname,Sage,Ssex)
--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

--课程表：Course(CId,Cname,TeId)
--CId 课程编号,Cname 课程名称,TId 教师编号

--教师表Teacher(TeId,Tname)
--TId 教师编号,Tname 教师姓名

--成绩表：SC(SId,CId,score)
--SId 学生编号,CId 课程编号,score 分数

2、初始化（创建表并插入测试数据）

学生表Student

create table Student(SId varchar(10),Sname varchar(10),Sbirthday date,Ssex varchar(10));

insert into Student values('01' , '赵雷' , date '1990-01-01' , '男');

insert into Student values('02' , '钱电' , date '1990-12-21' , '男');

insert into Student values('03' , '孙风' , date '1990-12-20' , '男');

insert into Student values('04' , '李云' , date '1990-12-06' , '男');

insert into Student values('05' , '周梅' , date '1991-12-01' , '女');

insert into Student values('06' , '吴兰' , date '1992-01-01' , '女');

insert into Student values('07' , '郑竹' , date '1989-01-01' , '女');

insert into Student values('09' , '张三' , date '2017-12-20' , '女');

insert into Student values('10' , '李四' , date '2017-12-25' , '女');

insert into Student values('11' , '李四' , date '2012-06-06' , '女');

insert into Student values('12' , '赵六' , date '2013-06-13' , '女');

insert into Student values('13' , '孙七' , date '2014-06-01' , '女');

课程表 Course

create table Course(CId varchar(10),Cname varchar(10),TeId varchar(10));

insert into Course values('01' , '语文' , '02');

insert into Course values('02' , '数学' , '01');

insert into Course values('03' , '英语' , '03');

教师表 Teacher

create table Teacher(Teid varchar(10),Tname varchar(10));

insert into Teacher values('01' , '张老师');

insert into Teacher values('02' , '李老师');

insert into Teacher values('03' , '王老师');

成绩表 SC

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));

insert into SC values('01' , '01' , 80);

insert into SC values('01' , '02' , 90);

insert into SC values('01' , '03' , 99);

insert into SC values('02' , '01' , 70);

insert into SC values('02' , '02' , 60);

insert into SC values('02' , '03' , 80);

insert into SC values('03' , '01' , 80);

insert into SC values('03' , '02' , 80);

insert into SC values('03' , '03' , 80);

insert into SC values('04' , '01' , 50);

insert into SC values('04' , '02' , 30);

insert into SC values('04' , '03' , 20);

insert into SC values('05' , '01' , 76);

insert into SC values('05' , '02' , 87);

insert into SC values('06' , '01' , 31);

insert into SC values('06' , '03' , 34);

insert into SC values('07' , '02' , 89);

insert into SC values('07' , '03' , 98);

全表显示：

学生表Student

课程表 Course

教师表 Teacher

成绩表 SC

2、 SQL习题

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

以学生id为主键，查找课程01 比02分数高的结果信息作为主表，然后与学生表关联。

2、查询同时存在" 01 "课程和" 02 "课程的情况

以学生id为主键

3、查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

以01课程为主表

4、查询不存在" 01 "课程但存在" 02 "课程的情况

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

根据学生ID把成绩分组，对分组中的score求平均值，最后在选取结果中AVG大于60的即可。然后关联学生信息表

6、查询在 SC 表存在成绩的学生信息

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

8、查有成绩的学生信息（同问题6）

EXISTS的参数是一个任意的SELECT语句，或者说子查询。系统对子查询进行运算以判断它是否返回行。如果它至少返回一行，则EXISTS结果就为”真”；如果子查询没有返回任何行， EXISTS的结果是”假”。这个子查询通常只是运行到能判断它是否可以生成至少一行为止，而不是等到全部结束。

9、查询「李」姓老师的数量

10、查询学过「张老师」授课的同学的信息

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

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

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

查询所选课程数量和01学生一样的学生，去掉所选课程中选了01学生未选课程的人

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

另一种解法也可以将教师表、课程表、成绩表先关联，然后再学生表中找到对应的学生信息

15、查询两门及其以上不及格（小于60分）课程的同学的学号，姓名及其平均成绩

16、检索" 01 "课程分数小于 60，按分数降序排列的学生信息

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

18、查询各科成绩最高分、最低分和平均分

/*以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列*/

openGauss数据库支持中文名称（字段名、表名）， 但不建议，当然了根据具体场景决定。

19、按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

20、查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

3、 实验小结（FAQ）：

关于openGauss数据库在使用SQL过程中需要注意的事项，总结如下（以下有SQL通用的， 也存在数据库特性的，具体以实践经验为准）：

1、关键字“Tid”

关键字“Tid”在openGauss数据库中被认定为关键字，不能作为字段被定义；

2、数据（日期）类型“datetime”

openGauss数据库中不存在类型“datetime”；

3、round() 函数使用

round(x),描述：离输入参数最近的整数。返回值类型：与输入相同。

round(v numeric, s int),描述：保留小数点后s位，s后一位进行四舍五入。返回值类型：numeric .

4、group by 子句

GROUP BY语句和SELECT语句一起使用，用来对相同的数据进行分组。您可以对一列或者多列进行分组，但是被分组的列必须存在。

5、HAVING子句

HAVING子句可以让我们筛选分组后的各组数据。WHERE子句在所选列上设置条件，而HAVING子句则在由GROUP BY子句创建的分组上设置条件。

6、EXISTS 、 NOT EXISTS

EXISTS的参数是一个任意的SELECT语句，或者说子查询。系统对子查询进行运算以判断它是否返回行。如果它至少返回一行，则EXISTS结果就为”真”；如果子查询没有返回任何行， EXISTS的结果是”假”。

这个子查询通常只是运行到能判断它是否可以生成至少一行为止，而不是等到全部结束。

7、IN 、NOT IN

右边是一个圆括弧括起来的子查询，它必须只返回一个字段。左边表达式对子查询结果的每一行进行一次计算和比较。如果找到任何相等的子查询行，则IN结果为”真”。如果没有找到任何相等行，则结果为”假”（包括子查询没有返回任何行的情况）。

表达式或子查询行里的NULL遵照SQL处理布尔值和NULL组合时的规则。如果两个行对应的字段都相等且非空，则这两行相等；如果任意对应字段不等且非空，则这两行不等；否则结果是未知（NULL）。如果每一行的结果都是不等或NULL ，并且至少有一个NULL ，则IN的结果是NULL 。

8、比较操作符

9、分支语句

图1

图2

参数说明：

case_expression：变量或表达式。

when_expression：常量或者条件表达式。

statement：执行语句。

10、窗口函数rank(expression)和row_number(expression)

列存表目前只支持rank(expression)和row_number(expression)两个函数。

窗口函数与OVER语句一起使用。OVER语句用于对数据进行分组，并对组内元素进行排序。窗口函数用于给组内的值生成序号。

说明：
窗口函数中的order by后面必须跟字段名，若order by后面跟数字，该数字会被按照常量处理，因此对目标列没有起到排序的作用。

RANK() ，描述：RANK函数为各组内值生成跳跃排序序号，其中，相同的值具有相同序号。返回值类型：BIGINT

ROW_NUMBER()，描述：ROW_NUMBER函数为各组内值生成连续排序序号，其中，相同的值其序号也不相同。返回值类型：BIGINT

本期《SQL经典练习题（openGauss数据库）上》就到这里， 下期再见！

作者：酷哥，来源Gauss松鼠会