0


SQL经典练习题(上)

引言:

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

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

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

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

一、初始化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

二、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、查询学生的总成绩,并进行排名,总分重复时不保留名次空缺

三、实验小结(FAQ):

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

1、关键字“Tid”

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

2、数据(日期)类型“datetime

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

3round() 函数使用

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

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

4group by 子句

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

5HAVING子句

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

6EXISTS 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

openGauss: 一款高性能、高安全、高可靠的企业级开源关系型数据库。

🍒如果您觉得博主的文章还不错或者有帮助的话,请关注一下博主,如果三连点赞评论收藏就更好啦!谢谢各位大佬给予的支持!

标签: 数据库 sql 云原生

本文转载自: https://blog.csdn.net/GaussDB/article/details/126927594
版权归原作者 Gauss松鼠会 所有, 如有侵权,请联系我们删除。

“SQL经典练习题(上)”的评论:

还没有评论