关于数据库原理与应用-基于大规模实践的数据思维与能力训练第8期实验的自我训练（1）

本文链接：https://blog.youkuaiyun.com/2301_76934422/article/details/145438578

目录

一、数据库原理

1-1数据库系统概述

第1关：数据管理技术的发展

第2关：数据库系统概论

第3关：数据库系统的结构

1-2数据模型

第1关：关系模型

第2关：层次模型

第3关：网状模型

1-3关系模型

第1关：关系模型的基本概念

第2关：关系模式

第3关：关系代数

第4关：扩展关系代数

第5关：关系演算

第6关：关系系统

二、国产数据库实战——以OpenGauss为例

2-1OpenGauss数据库 - 初识OpenGauss

第1关：创建数据库

第2关：创建表

第3关：使用主键约束

第4关：添加常用约束

2-2OpenGauss数据库 -修改2-4数据库

第1关：查看表结构与修改表名

第2关：修改字段数据类型

第3关：添加与删除字段

2-3OpenGauss数据库 - 插入、更新、删除数据

第1关：插入数据

第2关：更新数据

第3关：删除数据

2-4OpenGauss数据库 - 单表查询

第1关：基本查询语句

第2关：带 IN 关键字的查询

第3关：带 BETWEEN AND 的范围查询

第4关：带 LIKE 的字符匹配查询

第5关：查询空值与去除重复结果

第6关：带 AND 与 OR 的多条件查询

第7关：使用 LIMIT 限制查询结果的数量

2-5OpenGauss数据库 - 连接查询和子查询

第1关：内连接查询

第2关：外连接查询

第3关：关键字子查询

第4关：分页子查询

2-6OpenGauss数据库 - 使用聚合函数查询

第1关：COUNT( )函数

第2关：SUM( )函数

第3关：AVG( )函数

第4关：MAX( )函数

第5关：MIN( )函数

2-7OpenGauss数据库 - 其他函数的使用

第1关：字符函数

第2关：数学函数

第3关：日期时间函数和流程控制类函数

2-8OpenGauss数据库 - 分组选择数据

第1关：对查询结果进行排序

第2关：分组查询

第3关：GROUP BY 与聚合函数

第4关：使用 HAVING 与 ORDER BY

2-9OpenGauss数据库 - 复杂查询

第1关：分数排名

第2关：体育馆的人流量

第3关：统计总成绩

第4关：查询学生平均分

第5关：查询修课相同学生信息

第6关：查询各科成绩并排序

第7关：查询张老师课程成绩最高的学生信息

第8关：查询两门课程不及格同学信息

第9关：交换工资

第10关：换座位

注意：本文为个人做题的答案，仅供参考！！！

一、数据库原理

1-1数据库系统概述

第1关：数据管理技术的发展

1、下面列出的数据库管理技术发展的三个阶段中，没有专门的软件对数据进行管理的是（A）。
A、人工管理阶段
B、文件系统阶段
C、数据库阶段
2、下面关于数据库系统叙述正确的是（B）。
A、数据库系统避免了一切冗余
B、数据库系统减少了数据冗余
C、数据库系统文件能管理更多的数据
D、数据库系统中数据的一致性是指数据类型的一致
3、下列叙述中，错误的是（C）。

A、数据库技术的根本目标是要解决数据共享的问题

B、数据库设计是指设计一个能满足用户要求，性能良好的数据库

C、数据库系统中，数据的物理结构必须与逻辑结构一致

D、数据库系统是一个独立的系统，但是需要操作系统的支持

第2关：数据库系统概论

1、数据库系统是采用了数据库技术的计算机系统，数据库系统由数据库、数据库管理系统、应用系统和（C）。
A、系统分析员
B、程序员
C、数据库管理员
D、操作员
2、数据库（DB），数据库系统（DBS）和数据库管理系统（DBMS）之间的关系是（A）。
A、DBS包括DB和DBMS
B、DBMS包括DB和DBS
C、DB包括DBS和DBMS
D、DBS就是DB，也就是DBMS
3、下面四项中，不属于数据库系统特点的是（C）。
A、数据共享
B、数据完整性
C、数据冗余度高
D、数据独立性高
4、下面（B）不是 DBA 数据库管理员的职责。

A、完整性约束说明
B、定义数据库模式
C、数据库安全
D、数据库管理系统设计

第3关：数据库系统的结构

1、数据库系统是采用了数据库技术的计算机系统，数据库系统由数据库、数据库管理系统、应用系统和（A）。
A、系统分析员
B、程序员
C、数据库管理员
D、操作员
2、数据库（DB），数据库系统（DBS）和数据库管理系统（DBMS）之间的关系是（C）。
A、DBS包括DB和DBMS
B、DBMS包括DB和DBS
C、DB包括DBS和DBMS
D、DBS就是DB，也就是DBMS
3、下面四项中，不属于数据库系统特点的是（C）。
A、数据共享
B、数据完整性
C、数据冗余度高
D、数据独立性高
4、下面（B）不是 DBA 数据库管理员的职责。
A、完整性约束说明
B、定义数据库模式
C、数据库安全
D、数据库管理系统设计

1-2数据模型

第1关：关系模型

#请在此添加实现代码
########## Begin ##########
#在mydb库中创建表
create table t_player(
    p_id int(11) NOT NULL,
    p_name varchar(32) NOT NULL,
    p_sex varchar(32) NOT NULL,
    p_num int(11) NOT NULL,
    p_email varchar(32) NOT NULL,
    PRIMARY KEY (p_id)
);
create table t_school(
    s_name varchar(32) NOT NULL,
    s_address varchar(32) NOT NULL,
    s_telephone int(11) NOT NULL,
    s_email varchar(32) NOT NULL,
    PRIMARY KEY (s_name)
);
create table t_product(
    pr_id int(11) NOT NULL,
    pr_name varchar(32) NOT NULL,
    pr_kind varchar(32) NOT NULL,
    pr_link varchar(32) NOT NULL,
    pr_size int(11) NOT NULL,
    PRIMARY KEY (pr_id)
);
create table t_player_product(
    p_id int(11) NOT NULL,
    pr_id int(11) NOT NULL,
    pr_grade int(11) NOT NULL,
    grade_rates varchar(11) NOT NULL,
    CONSTRAINT fk_player_product1 
    FOREIGN KEY t_player_product(p_id) 
    REFERENCES t_player(p_id),
    CONSTRAINT fk_player_product2 
    FOREIGN KEY t_player_product(pr_id) 
    REFERENCES t_product(pr_id)
);
create table t_school_player(
    s_name varchar(32) NOT NULL,
    p_id int(11) NOT NULL,
    CONSTRAINT fk_school_stu1
    FOREIGN KEY t_school_player(s_name) 
    REFERENCES t_school(s_name),
    CONSTRAINT fk_school_stu2 
    FOREIGN KEY t_school_player(p_id) 
    REFERENCES t_player(p_id)
);
########## End ##########

第2关：层次模型

#请在此添加实现代码
########## Begin ##########
#在mydb库中创建表并插入数据
-- Begin
-- 创建院系表 dept
CREATE TABLE dept (
    dept_id VARCHAR(32) PRIMARY KEY,
    dept_name VARCHAR(32),
    addr VARCHAR(32)
);
-- 创建教研室表 edu
CREATE TABLE edu (
    edu_id VARCHAR(32) PRIMARY KEY,
    edu_name VARCHAR(32),
    dept_id VARCHAR(32),
    FOREIGN KEY (dept_id) REFERENCES dept(dept_id)
);
-- 创建学生表 student
CREATE TABLE student (
    student_id VARCHAR(32) PRIMARY KEY,
    student_name VARCHAR(32),
    level_class VARCHAR(32),
    dept_id VARCHAR(32),
    FOREIGN KEY (dept_id) REFERENCES dept(dept_id)
);
-- 创建职工表 emp
CREATE TABLE emp (
    emp_id VARCHAR(32) PRIMARY KEY,
    emp_name VARCHAR(32),
    title VARCHAR(32),
    edu_id VARCHAR(32),
    dept_id VARCHAR(32),
    FOREIGN KEY (edu_id) REFERENCES edu(edu_id),
    FOREIGN KEY (dept_id) REFERENCES dept(dept_id)
);
-- 插入院系表数据
INSERT INTO dept (dept_id, dept_name, addr) VALUES
('D02', '计算机', 'R1101');
-- 插入教研室表数据
INSERT INTO edu (edu_id, edu_name, dept_id) VALUES
('R01', '数据库', 'D02'),
('R02', '网络', 'D02'),
('R03', '人工智能', 'D02');
-- 插入学生表数据
INSERT INTO student (student_id, student_name, level_class, dept_id) VALUES
('S0012', '王明', 'G1', 'D02'),
('S0020', '郑直', 'G2', 'D02'),
('S0199', '周密', 'G3', 'D02');
-- 插入职工表数据
INSERT INTO emp (emp_id, emp_name, title, edu_id, dept_id) VALUES
('E1101', '何璧', '教授', 'R01', 'D02'),
('E1234', '王思', '教授', 'R03', 'D02'),
('E3721', '刘新', '讲师', 'R01', 'D02'),
('E3722', '付弈', '助教', 'R03', 'D02');
-- End
########## End ##########

第3关：网状模型

########## Begin ##########
-- 创建学生表 t_student
create table t_student(
    s_id varchar(32)  PRIMARY KEY comment '学号',
    s_name varchar(32) comment '学生姓名',
    s_class varchar(32) comment '年级'     
);
-- 创建课程表 t_course
create table  t_course(
    c_id varchar(32)   PRIMARY KEY comment '课程号' ,
    c_name varchar(32) comment '课程名' , 
    c_creadit int(3) comment '学分' 
);
-- 创建关系表 t_relation
create table  t_relation(
    s_id varchar(32),
    c_id varchar(32),
    grade varchar(32) comment '成绩' , 
    FOREIGN KEY (s_id) REFERENCES t_student(s_id),
    FOREIGN KEY (c_id) REFERENCES t_course(c_id)
);
-- 向学生表 t_student 插入数据
INSERT INTO t_student (s_id, s_name, s_class) VALUES
('S1', '张乐', '大一'),
('S2', '王冲', '大二'),
('S3', '翠花', '大一');
-- 向课程表 t_course 插入数据
INSERT INTO t_course (c_id, c_name, c_creadit) VALUES
('C1', '数据库', 45),
('C2', 'python', 30);
-- 向关系表 t_relation 插入数据
INSERT INTO t_relation (s_id, c_id, grade) VALUES
('S1', 'C1', 'A'),
('S1', 'C2', 'A'),
('S2', 'C1', 'B'),
('S2', 'C2', 'A-'),
('S3', 'C1', 'C');
########## End ##########

1-3关系模型

第1关：关系模型的基本概念

1、数据库技术的奠基人之一 E.F.Codd 从 1970 年起发表过多篇论文，主要论述的是（C）。
A、层次数据模型
B、网状数据模型
C、关系数据模型
D、面向对象数据模型
2、在关系数据库设计中用（C）来表示实体及实体之间的联系。
A、树结构
B、封装结构
C、二维表结构
D、图结构
3、下面的选项不是关系数据库基本特征的是（A）。
A、不同的列应有不同的数据类型
B、不同的列应有不同的列名
C、与行的次序无关
D、与列的次序无关
4、关系模型中，一个码是（C）。
A、可以由多个任意属性组成
B、至多由一个属性组成
C、由一个或多个属性组成，其值能够惟一标识关系中一个元组
D、以上都不是
5、下列叙述中，哪一条是不正确的（A）。
A、一个二维表就是一个关系，二维表的名就是关系的名
B、关系中的列称为属性，属性的个数称为关系的元或度
C、关系中的行称为元组，对关系的描述称为关系模式
D、属性的聚会范围称为值域，元组中的一个属性值称为分量

第2关：关系模式

1、现有如下关系：患者（患者编号，患者姓名，性别，出生日期，所在单位）医疗（患者编号，医生编号，医生姓名，诊断日期，诊断结果）其中，医疗关系中的外码是（A）。
A、患者编号
B、患者姓名
C、患者编号和患者姓名
D、医生编号和患者编号
2、设有属性A，B，C，D，以下表达中不是关系的是（C）。
A、R(A)
B、R(A,B,C,D)
C、R(A×B×C×D)
D、R(A,B)
3、关系数据模型（D）。
A、只能表示实体间的1：1联系
B、只能表示实体间的1：n联系
C、只能表示实体间的m：n联系
D、可以表示实体间的上述三种联系
4、在通常情况下，下面的关系中不可以作为关系数据库的关系的是（D）。
A、R1(学生号，学生名，性别)
B、R2(学生号，学生名，班级号)
C、R3(学生号，班级号，宿舍号)
D、R1(学生号，学生名，简历)
5、设有表示学生选课的 3 张表，“学生”表 S(学号，姓名，性别，年龄，身份证号)，“课程”表 C(课号，课名)，“选课”表 SC(学号，课号，成绩)，则表 SC 的关键字(键或码)为（C）。
A、课号，成绩
B、学号，成绩
C、学号，课号
D、学号，姓名，成绩

第3关：关系代数

1、关系代数运算是以（C）为基础的运算。
A、关系运算
B、谓词演算
C、集合运算
D、代数运算
2、有两个关系 R 和 S，分别包含15个和10个元组，那么在R∪S、R-S、R∩S，中不可能出现的元组数目情况是（B）。
A、15，5，10
B、18，7，7
C、21，11，4
D、25，15，0
3、参加差运算的两个关系（B）。
A、属性个数可以不相同
B、属性个数必须相同
C、一个关系包含另一个关系的属性
D、属性名必须相同
4、若D1={a1,a2,a3}，D2={1,2,3}，则 D1×D2 集合中共有元组（C）个。
A、6
B、8
C、9
D、12
5、设有关系 R，S 和 T 如下图所示。关系 T 是由关系 R 和 S 经过哪种操作得到的（A）。

A、交

B、并

C、差

D、笛卡尔积

第4关：扩展关系代数

1、关系代数中的连接操作是由（B）操作组合而成。
A、选择和投影
B、选择和笛卡尔积
C、投影、选择、笛卡尔积
D、投影和笛卡尔积
2、从一个数据库文件中取出满足某个条件的所有记录形成一个新的数据库文件的操作是（C）操作。
A、投影
B、连接
C、选择
D、复制
3、一般情况下，当对关系 R 和 S 进行自然连接时，要求 R 和 S 含有一个或者多个共有的（C）。
A、记录
B、行
C、属性
D、元组
4、取出关系中的某些列，并消去重复元组的关系代数运算称为（B）。
A、取列运算
B、投影运算
C、连接运算
D、选择运算
5、在学生表中要查找所有年龄大于30岁姓王的男同学，应该采用的关系运算是（A）。
A、选择
B、投影
C、连接
D、自然连接

第5关：关系演算

1、设关系 R 和关系 S 具有相同的属性个数，且相应的属性取自同一个域，则{t∣t∈R⋀¬t∈S} t 是元组变量，其结果关系是（B）。
A、R∪S
B、R-S
C、R∩S
D、R-(R-S)
2、在关系演算中，元组变量的变化范围是（B）。
A、某一命名的关系
B、数据库中的所有关系
C、某一个域
D、数据库中的所有域
3、关系演算的基础是（C）。
A、形式逻辑中的逻辑演算
B、形式逻辑中的关系演算
C、数理逻辑中的谓词演算
D、数理逻辑中的形式演算

第6关：关系系统

1、数据库系统的数据独立性体现在（B）。
A、不会因为数据的变化而影响到应用程序
B、不会因为数据存储结构与数据逻辑结构的变化而影响应用程序
C、不会因为存储策略的变化而影响存储结构
D、不会因为某些存储结构的变化而影响其他的存储结构
2、下列关于关系的叙述中，正确的是（A）。
A、关系是一个由行与列组成的、能够表达数据及数据之间联系的二维表
B、表中某一列的数据类型既可以是字符串，也可以是数字
C、表种某一列的值可以取空值null，所谓空值是指安全可靠或零
D、表中必须有一列作为主关键字，用来唯一标识一行

二、国产数据库实战——以OpenGauss为例

2-1OpenGauss数据库 - 初识OpenGauss

第1关：创建数据库

gsql -d postgres -U gaussdb -W'passwd123@123';
create database mydb;

第2关：创建表

gsql -d postgres -U gaussdb -W'passwd123@123';
create database testdb;
\c testdb;
passwd123@123
create table t_emp(id int,name varchar(32),deptid int,salary float);

第3关：使用主键约束

gsql -d postgres -U gaussdb -W'passwd123@123';
create database MyDb;
\c MyDb;
passwd123@123
create table t_user1(userid int primary key,name varchar(32),password varchar(11),phone varchar(11),email varchar(32));
create table t_user2(name varchar(32),phone varchar(11),email varchar(32),primary key(name,phone));

第4关：添加常用约束

gsql -d postgres -U gaussdb -W'passwd123@123';
create database mydb;
\c mydb;
passwd123@123
create table t_user(id serial primary key,username varchar(32) unique not null,sex varchar(4) default '男');

2-2OpenGauss数据库 -修改2-4数据库

第1关：查看表结构与修改表名

--请在此处添加实现代码
--########## Begin ##########
ALTER TABLE tb_emp RENAME TO jd_emp;
\d
--########## End ##########

第2关：修改字段数据类型

--请在此处添加实现代码
--########## Begin ##########
--########## update the value ##########
ALTER TABLE  tb_emp  ALTER name TYPE varchar(30);
--########## End ##########
\d+ tb_emp

第3关：添加与删除字段

--#请在此处添加实现代码
--########## Begin ##########
 alter table  tb_emp ADD COLUMN  country varchar(20);
 ALTER TABLE tb_emp DROP salary  ;
--########## End ##########
\d+ tb_emp

2-3OpenGauss数据库 - 插入、更新、删除数据

第1关：插入数据

--#请在此处添加实现代码
--########## Begin ##########
INSERT INTO tb_emp(Id,Name,DeptId,Salary) 
values
    (1,'Nancy',301,2300.00),
    (2,'Tod',303,5600.00),
    (3,'Carly',301,3200.00);
--########## End ##########
SELECT * FROM tb_emp;

第2关：更新数据

--#请在此处添加实现代码
--########## Begin ##########
UPDATE tb_emp
SET
 Name = 'tracy',
 DeptId = 302,
 Salary = 4300.00
WHERE Name = 'carly';
--########## End ##########
SELECT * FROM tb_emp;

第3关：删除数据

--#请在此处添加实现代码
--########## Begin ##########
DELETE FROM tb_emp WHERE salary>3000 ; 
--########## End ##########
SELECT * FROM tb_emp;

2-4OpenGauss数据库 - 单表查询

第1关：基本查询语句

--#请在此处添加实现代码
--########## Begin ##########
SELECT  name,salary from tb_emp;
SELECT * from tb_emp;
--########## End ##########

第2关：带 IN 关键字的查询

--#请在此处添加实现代码
--########## Begin ##########
SELECT name, salary
FROM tb_emp
WHERE id != 1;
--########## End ##########

第3关：带 BETWEEN AND 的范围查询

--#请在此处添加实现代码
--########## Begin ##########
-- Begin
SELECT name, salary
FROM tb_emp
WHERE salary BETWEEN 3000 AND 5000;
-- End
--########## End ##########

第4关：带 LIKE 的字符匹配查询

--######### Begin #########
SELECT name, salary
FROM tb_emp
WHERE name LIKE 'C%';
--######### End #########

第5关：查询空值与去除重复结果

--#请在此处添加实现代码
--########## Begin ##########
-- Begin
SELECT name, salary
FROM tb_emp
WHERE salary BETWEEN 3000 AND 5000;
-- End
--########## End ##########

第6关：带 AND 与 OR 的多条件查询

--######### Begin #########
SELECT * FROM tb_emp WHERE deptid=301 AND salary>3000; 
--######### End #########
--######### Begin #########
SELECT * FROM tb_emp WHERE deptid=301 OR deptid=303; 
--######### End #########

第7关：使用 LIMIT 限制查询结果的数量

--#请在此处添加实现代码
--########## Begin ##########
--########## 查询班级中第2名到第5名的学生信息 ##########
SELECT id, name, score
FROM (
    SELECT 
        id, 
        name, 
        score,
        ROW_NUMBER() OVER (ORDER BY score DESC) AS ranking
    FROM tb_score
) ranked_students
WHERE ranking BETWEEN 2 AND 5
ORDER BY score DESC;
--########## End ##########

2-5OpenGauss数据库 - 连接查询和子查询

第1关：内连接查询

--########## 查询数据表中学生姓名和对应的班级 ##########
--#请在此处添加实现代码
--########## Begin ##########
SELECT 
    tb_student.name as studentName,
    tb_class.name AS className
FROM 
    tb_class
JOIN 
    tb_student 
ON 
    tb_student.class_id = tb_class.id;
--########## End ##########
select 
emp.name as empname,
dept.name as deptname 
from department as dept 
join employee as emp 
on dept.id=emp.dept_id;

第2关：外连接查询

--########## 使用左外连接查询所有学生姓名和对应的班级 ##########
--#请在此处添加实现代码
--########## Begin ##########
SELECT 
student.name AS studentName,
class.name AS className
FROM
tb_student AS student
left join
tb_class AS class
on student.class_id=class.id;
--########## End ##########
--########## 使用右外连接查询所有学生姓名和对应的班级 ##########
--#请在此处添加实现代码
--########## Begin ##########
SELECT 
student.name AS studentName,
class.name AS className
FROM
tb_student AS student
right join
tb_class AS class
on student.class_id=class.id;
--########## End ##########

第3关：关键字子查询

--#请在此处添加实现代码
--########## Begin ##########

--#1.使用 ALL 关键字进行查询
SELECT 
    position, salary 
FROM 
    tb_salary 
WHERE  
    salary > ALL (SELECT salary FROM tb_salary WHERE position = 'Java');
--#2.使用 ANY 关键字进行查询
SELECT 
    position, salary 
FROM 
    tb_salary 
WHERE  
    salary > ANY (SELECT salary FROM tb_salary WHERE position = 'Java');
--#3.使用 IN 关键字进行查询
SELECT position, salary
FROM tb_salary
WHERE position IN ('Java');
--########## End ##########

第4关：分页子查询

--#请在此处添加实现代码
--########## Begin ##########
--#1.分页查询
select prod_id from products limit 5,5;
--#2.用子查询优化分页查询语句
select prod_id from products where prod_id>=(select prod_id from products limit 10,1) limit 5
--########## End ##########

2-6OpenGauss数据库 - 使用聚合函数查询

第1关：COUNT( )函数

--#请在此处添加实现代码
--########## Begin ##########
--########## 查询该表中一共有多少条数据 ##########
SELECT count(*) from tb_class;
--########## 查询此表中367班有多少位学生 ##########
SELECT classid, COUNT(*) AS count
FROM tb_class
WHERE classid = 367
GROUP BY classid;
--########## End ##########

第2关：SUM( )函数

--#请在此处添加实现代码
--########## Begin ##########
--########## 查询所有学生总分数 ##########
SELECT SUM(score) FROM tb_class;
--########## 查询学生语文科目的总分数 ##########
SELECT course, SUM(score) 
FROM tb_class
WHERE course = '语文'
GROUP BY course;
--########## End ##########

第3关：AVG( )函数

--#请在此处添加实现代码
--########## Begin ##########
--########## 查询学生语文科目的平均分数 ##########
SELECT course,AVG(score)  
FROM tb_class 
WHERE course='语文' 
GROUP BY course;
--########## 查询学生英语科目的平均分数 ##########
SELECT course ,AVG(score)  
FROM tb_class 
WHERE course='英语' 
GROUP BY course;
--########## End ##########

第4关：MAX( )函数

--#请在此处添加实现代码
--########## Begin ##########
-- 查询语文课程中的最高分数
SELECT course, MAX(score) AS max
FROM tb_class
WHERE course = '语文'
GROUP BY course;
-- 查询英语课程中的最高分数
SELECT course, MAX(score) AS max
FROM tb_class
WHERE course = '英语'
GROUP BY course;
--########## End ##########

第5关：MIN( )函数

--#请在此处添加实现代码
--########## Begin ##########
--########## 查询语文课程中的最低分数 ##########
SELECT course, MIN(score) AS min
FROM tb_class
WHERE course = '语文'
GROUP BY course;
--########## 查询英语课程中的最低分数 ##########
SELECT course, MIN(score) AS min
FROM tb_class
WHERE course = '英语'
GROUP BY course;
--########## End ##########

2-7OpenGauss数据库 - 其他函数的使用

第1关：字符函数

--#请在此添加实现代码
--########## Begin ##########
SELECT 
    CONCAT(UPPER(LEFT(Name, 1)), LOWER(SUBSTRING(Name, 2))) AS Name
FROM 
    employee;
--########## End ##########

第2关：数学函数

--#请在此添加实现代码
--########## Begin ##########
UPDATE Score
SET s_score =  ROUND(s_score - SQRT((POWER(4, 4) - POWER(3, 3)) / POWER(2, 2)), 2);
--########## End ##########

第3关：日期时间函数和流程控制类函数

--#请在此添加实现代码
--########## Begin ##########

--##########  查询学生出生年份及年龄 ##########
SELECT 
    CONCAT(
        EXTRACT(YEAR FROM age(timestamp '2019-01-01', s_birth)), 
        CASE 
            WHEN EXTRACT(YEAR FROM age(timestamp '2019-01-01', s_birth)) = 1 THEN ' year'
            ELSE ' years'
        END,
        CASE 
            WHEN EXTRACT(MONTH FROM age(timestamp '2019-01-01', s_birth)) > 0 THEN 
                ' ' || EXTRACT(MONTH FROM age(timestamp '2019-01-01', s_birth))::text || 
                CASE 
                    WHEN EXTRACT(MONTH FROM age(timestamp '2019-01-01', s_birth)) = 1 THEN ' mon'
                    ELSE ' mons'
                END
            ELSE ''
        END,
        CASE 
            WHEN EXTRACT(DAY FROM age(timestamp '2019-01-01', s_birth)) > 0 THEN 
                ' ' || EXTRACT(DAY FROM age(timestamp '2019-01-01', s_birth))::text || 
                CASE 
                    WHEN EXTRACT(DAY FROM age(timestamp '2019-01-01', s_birth)) = 1 THEN ' day'
                    ELSE ' days'
                END
            ELSE ''
        END
    ) AS age
FROM 
    Student;

--##########  查询课程的最高分、最低分、平均分和及格率 #########
SELECT 
    c.c_id AS 课程id,
    c.c_name AS 课程名,
    MAX(s.s_score) AS 最高分,
    MIN(s.s_score) AS 最低分,
    ROUND(AVG(s.s_score), 2) AS 平均分,
    ROUND(SUM(CASE WHEN s.s_score >= 60 THEN 1 ELSE 0 END)::numeric / COUNT(s.s_score)::numeric * 100, 2) AS 及格率
FROM 
    Course c
LEFT JOIN 
    Score s ON c.c_id = s.c_id
GROUP BY 
    c.c_id, c.c_name
ORDER BY 
    c.c_id ASC;

--########## End ##########

2-8OpenGauss数据库 - 分组选择数据

第1关：对查询结果进行排序

--#请在此处添加实现代码
--########## Begin ##########
--########## 查询1班同学的所有信息以成绩降序的方式显示结果 ##########
SELECT * FROM tb_score WHERE class_id=1 ORDER BY score DESC;
--########## End ##########

第2关：分组查询

--#请在此处添加实现代码
--########## Begin ##########
--########## 对班级名称进行分组查询 ##########
SELECT class_id
FROM tb_class
GROUP BY class_id;
--########## End ##########

第3关：GROUP BY 与聚合函数

--#请在此处添加实现代码
--########## Begin ##########
--#1.查询表中2,3,4年级中分别男女的总人数
SELECT 
    gradeid,
    sex,
    COUNT(stuId) AS count
FROM student 
WHERE gradeId IN (2, 3, 4)
GROUP BY gradeid, sex;
--########## End ##########

第4关：使用 HAVING 与 ORDER BY

--#请在此处添加实现代码
--########## Begin ##########

-- 列出至少有两门课程在90分以上（包括90分）的学生的学号及其90分以上科目总数
SELECT 
    sno,
    COUNT(*) AS count
FROM 
    tb_grade
WHERE 
    score >= 90
GROUP BY 
    sno
HAVING 
    COUNT(*) >= 2;

-- 列出平均成绩大于90分（包括90分）且语文课必须在95分以上（包括95分）的学生的学号及其科目的平均分
SELECT 
    tg.sno,
    ROUND(AVG(tg.score), 16) AS avg
FROM 
    tb_grade tg
JOIN (
    -- 子查询筛选出语文成绩在95分以上（包括95分）的学生学号
    SELECT 
        sno
    FROM 
        tb_grade
    WHERE 
        pno = '语文' AND score >= 95
) AS chinese_high_score ON tg.sno = chinese_high_score.sno
GROUP BY 
    tg.sno
HAVING 
    AVG(tg.score) >= 90 
ORDER BY 
    tg.sno ASC;
--########## End ##########

2-9OpenGauss数据库 - 复杂查询

第1关：分数排名

--#请在此添加实现代码
--########## Begin ##########
-- 情况一：平分后的下一个名次是下一个连续的整数值，名次之间无“间隔”
SELECT 
    Score,
    DENSE_RANK() OVER (ORDER BY Score DESC) AS "rank"
FROM 
    score
ORDER BY 
    Score DESC;
-- 情况二：排名是非连续的
SELECT 
    Score,
    RANK() OVER (ORDER BY Score DESC) AS "rank"
FROM 
    score
ORDER BY 
    Score DESC;
--########## End ##########

第2关：体育馆的人流量

--#请在此添加实现代码
--########## Begin ##########
SELECT DISTINCT g1.*
FROM gymnasium g1
JOIN gymnasium g2 ON g1.id + 1 = g2.id
JOIN gymnasium g3 ON g1.id + 2 = g3.id
WHERE g1.visitors_flow >= 100 AND g2.visitors_flow >= 100 AND g3.visitors_flow >= 100
UNION
SELECT DISTINCT g2.*
FROM gymnasium g1
JOIN gymnasium g2 ON g1.id + 1 = g2.id
JOIN gymnasium g3 ON g1.id + 2 = g3.id
WHERE g1.visitors_flow >= 100 AND g2.visitors_flow >= 100 AND g3.visitors_flow >= 100
UNION
SELECT DISTINCT g3.*
FROM gymnasium g1
JOIN gymnasium g2 ON g1.id + 1 = g2.id
JOIN gymnasium g3 ON g1.id + 2 = g3.id
WHERE g1.visitors_flow >= 100 AND g2.visitors_flow >= 100 AND g3.visitors_flow >= 100
ORDER BY id;
--########## End ##########

第3关：统计总成绩

--#请在此添加实现代码
--########## Begin ##########
-- 选择班级名称、语文总成绩和数学总成绩
SELECT 
    tc.classname,
    SUM(CASE WHEN ts.chinese >= 60 THEN ts.chinese ELSE 0 END) AS chinese,
    SUM(CASE WHEN ts.maths >= 60 THEN ts.maths ELSE 0 END) AS maths
FROM 
    tb_score ts
JOIN 
    tb_class tc ON ts.name = tc.stuname
GROUP BY 
    tc.classname;
--########## End ##########

第4关：查询学生平均分

--#请在此添加实现代码
--########## Begin ##########
-- 选择学生编号、学生姓名和平均成绩
SELECT 
    s.s_id,
    s.s_name,
    -- 使用 ROUND 函数将平均成绩保留 2 位小数
    CASE 
    WHEN COALESCE(AVG(sc.s_score), 0) = 0 THEN 0
    ELSE ROUND(COALESCE(AVG(sc.s_score), 0), 2) 
    END AS avg_score
FROM 
    student s
-- 使用 LEFT JOIN 确保包含无成绩的学生
LEFT JOIN 
    score sc ON s.s_id = sc.s_id
GROUP BY 
    s.s_id, s.s_name
-- 筛选出平均成绩小于 60 分的学生
HAVING 
    ROUND(COALESCE(AVG(sc.s_score), 0), 2) < 60;
--########## End ##########

第5关：查询修课相同学生信息

--#请在此添加实现代码
--########## Begin ##########
-- 查询与 s_id = 01 号同学学习的课程完全相同的其他同学的信息
SELECT s.s_id, s.s_name, s.s_sex
FROM student s
JOIN score sc ON s.s_id = sc.s_id
WHERE s.s_id != '01'
GROUP BY s.s_id, s.s_name, s.s_sex
HAVING COUNT(DISTINCT sc.c_id) = (
    -- 计算 01 号同学所选课程的数量
    SELECT COUNT(DISTINCT c_id) 
    FROM score 
    WHERE s_id = '01'
)
AND SUM(CASE WHEN sc.c_id NOT IN (SELECT c_id FROM score WHERE s_id = '01') THEN 1 ELSE 0 END) = 0
AND SUM(CASE WHEN c_id NOT IN (SELECT c_id FROM score WHERE s.s_id = sc.s_id) THEN 1 ELSE 0 END) = 0
ORDER BY s.s_id DESC; 
--########## End ##########

第6关：查询各科成绩并排序

--#请在此添加实现代码
--########## Begin ##########
SELECT
    s.s_id,
    c.c_id,
    sc.s_score,
    RANK() OVER (PARTITION BY sc.c_id ORDER BY sc.s_score DESC) AS rank
FROM
    student s
JOIN 
    score sc ON s.s_id = sc.s_id
JOIN
    course c ON sc.c_id = c.c_id;
--########## End ##########

第7关：查询张老师课程成绩最高的学生信息

--#请在此添加实现代码
--########## Begin ##########
-- 查询选修“张三”老师所授课程且成绩最高的学生信息及其成绩
SELECT 
    s.s_id,
    s.s_name,
    s.s_sex,
    sc.s_score,
    c.c_id,
    c.c_name
FROM 
    student s
JOIN 
    score sc ON s.s_id = sc.s_id
JOIN 
    course c ON sc.c_id = c.c_id
JOIN 
    teacher t ON c.t_id = t.t_id
WHERE 
    t.t_name = '张三'
    AND sc.s_score = (
        SELECT 
            MAX(s_score)
        FROM 
            score sc2
        JOIN 
            course c2 ON sc2.c_id = c2.c_id
        JOIN 
            teacher t2 ON c2.t_id = t2.t_id
        WHERE 
            t2.t_name = '张三'
    );
--########## End ##########

第8关：查询两门课程不及格同学信息

--#请在此添加实现代码
--########## Begin ##########
-- 查询两门及其以上不及格课程的同学的学号、姓名及其平均成绩
SELECT 
    s.s_id,
    s.s_name,
    ROUND(AVG(sc.s_score)) AS avg_score
FROM 
    student s
JOIN 
    score sc ON s.s_id = sc.s_id
GROUP BY 
    s.s_id, s.s_name
HAVING 
    SUM(CASE WHEN sc.s_score < 60 THEN 1 ELSE 0 END) >= 2;
--########## End ##########

第9关：交换工资

--#请在此添加实现代码
--########## Begin ##########
UPDATE tb_Salary
SET sex =
case
    when sex='f' then 'm'
    ELSE 'f'
    END;
--########## End ##########

第10关：换座位


--#请在此添加实现代码
--########## Begin ##########
SELECT 
    -- 对于座位号为偶数的学生，将其座位号减 1
    CASE 
        WHEN id % 2 = 0 THEN id - 1
        -- 对于座位号为奇数且不是最后一个座位的学生，将其座位号加 1
        WHEN id % 2 = 1 AND id < (SELECT MAX(id) FROM tb_Seat) THEN id + 1
        -- 对于座位号为奇数且是最后一个座位的学生，保持座位号不变
        ELSE id
    END AS id,
    name
FROM 
    tb_Seat
ORDER BY 
    id;
--########## End ##########