西电数据库openGauss平台练习

仅记录平台标注中等难度及以上的题目

试卷编号:41
试卷名称:gongsi

条件:
公司数据库有如下关系模式： 员工employee（员工号eno，员工姓名ename，员工年龄eage，所在部门号dno，工资salary，工作日期work）； 部门depart(部门号dno，部门名称dname，部门经理员工号dman)。

题目3：查询每个部门的部门号、员工平均工资。

1
2
select dno, AVG(salary)
from employee group by dno

题目4：查询员工的中位数工资。

1
2
3
4
select distinct salary from employee a 
where (select count(*) from employee b where b.salary>=a.salary) -
(select count(*) from employee b where b.salary<=a.salary)
between -1 and 1

这道题比较容易踩坑的地方是题目没有说明它对中位数的定义,这里是将多个中位数返回回去,而不用求平均,而网上大多数类似的需求都是只能返回一个中位数,所以有多个中位数是都进行了求平均操作,但这里是没有的,所以需要特别注意

题目5：查询每个部门的部门号，员工中位数工资。

1
2
3
4
select distinct dno,salary from employee a 
where (select count(*) from employee b where b.dno=a.dno and b.salary>=a.salary) -
(select count(*) from employee b where b.dno=a.dno and b.salary<=a.salary)
between -1 and 1

题目6：员工表设计不符合1NF

工作日期包含多个日期，中间用逗号,分隔，前后可能有多个空格，如2022-11-11,2022-11-12,2018-08-01，查询每个员工的员工号，姓名和工作日期，其中工作日期要求将原有日期拆分为单个日期。

1
select eno,ename,trim(regexp_split_to_table(work,',')) as work from employee;

这道题个人觉得是这套练习里最难的一道了,但openGauss的regexp_split_to_table函数已经帮我们实现了行转列这个最难的点,然后trim函数去一下空格,就出来了.当然别的方法也可以做,网上一搜都有,但函数用起来就是爽🤪

题目12：创建函数get_age_count(s,e)，返回年龄在[s,e]范围内的员工的人数。

1
2
3
4
5
6
7
8
9
CREATE OR REPLACE FUNCTION get_age_count(s integer, e integer)  
RETURNS integer AS $total$  
declare  
    total integer;  
BEGIN  
   SELECT count(*) INTO total FROM employee WHERE eage>=s AND eage<=e;  
   RETURN total;  
END;  
$total$ LANGUAGE plpgsql;

这道题就要仔细看下openGauss的手册,我一开始按MySQL函数的格式写就错了,后来仔细看了下手册才发现openGauss自定义函数是PostgreSQL格式的,这点要注意.