1CREATE TABLE dp (
 2    dp_id      VARCHAR(20) not null PRIMARY KEY,      -- 部门编号
 3    dp_name    VARCHAR(100) NOT NULL,        -- 部门名
 4    floor      INT        -- 楼层
 5);
 6
 7-- 2) 员工表
 8CREATE TABLE employee (
 9    em_id       VARCHAR(20)  not null PRIMARY KEY,      -- 员工编号
10    em_name     VARCHAR(50) NOT NULL,         -- 员工姓名
11    em_salary   NUMERIC(10,2) NOT null,          -- 员工薪资
12    absence_days	INT not null DEFAULT 0, -- 员工缺勤天数
13    birth_day	DATE, -- 员工生日
14    if_married	BOOL, -- 员工婚姻情况
15    dp_id	VARCHAR(20) not null, 	-- 员工所属部门
16    CONSTRAINT chk_em_salary check (em_salary >= 2000 )
17);
18
19-- 加外键
20ALTER TABLE employee
21  ADD CONSTRAINT fk_dp_employee
22  FOREIGN KEY (dp_id) REFERENCES dp(dp_id);

 1-- dp 表数据
 2INSERT INTO dp (dp_id, dp_name, floor) VALUES
 3('DP007', '第一部门', 12),
 4('DP996', '第二部门', 8),
 5('DP110', '第四部门', 15);
 6
 7-- employee 表数据
 8INSERT INTO employee (em_id, em_name, em_salary, absence_days, birth_day, if_married, dp_id) VALUES
 9('412502', 'Dong Zi', '6000', '300', '2009-09-25', '0', 'DP007'),
10('112233', 'Xiao Zhang', '2500', '3', '2000-03-24', '0', 'DP996'),
11('904862', 'Old Shi', '3000', '0', '1996-01-26', '1', 'DP110'),
12('201001', 'Li Wei', '3200', '0', '1995-07-12', '1', 'DP007'),
13('201002', 'Wang Fang', '2800', '2', '1998-11-03', '0', 'DP007'),
14('305678', 'Chen Hao', '4500', '5', '1992-04-18', '1', 'DP996'),
15('408888', 'Liu Yang', '2100', '0', '2001-06-30', '0', 'DP996'),
16('556677', 'Zhao Min', '5500', '1', '1990-02-14', '1', 'DP110'),
17('667788', 'Sun Lei', '3800', '4', '1997-09-09', '0', 'DP110'),
18('778899', 'Zhou Jing', '2600', '0', '1999-12-01', '0', 'DP996');

 1-- Q3
 2SELECT em_salary, absence_days
 3FROM employee;
 4
 5-- Q4
 6SELECT COUNT(*) AS employee_count
 7FROM employee;
 8
 9-- Q5
10SELECT AVG(em_salary) AS avg_salary
11FROM employee;
12
13-- Q6
14SELECT dp_id,
15       MAX(em_salary) AS max_salary,
16       MIN(em_salary) AS min_salary
17FROM employee
18WHERE dp_id = 'DP007'
19GROUP BY dp_id;
20
21-- Q7
22SELECT em_name
23FROM employee
24WHERE absence_days > 3;
25
26-- Q9
27SELECT UPPER(em_name) AS em_name_upper
28FROM employee;
29
30-- Q10（openGauss）
31SELECT em_id,
32       em_name,
33       birth_day,
34       TO_CHAR(birth_day, 'YYYY/MM/DD')   AS birth_fmt_slash,
35       TO_CHAR(birth_day, 'YYYYMMDD')     AS birth_fmt_compact
36FROM employee;
37
38-- Q11（openGauss）选定员工：412502
39SELECT em_name,
40       SUBSTR(em_name, 1, POSITION(' ' IN em_name) - 1) AS part1,
41       SUBSTR(em_name, POSITION(' ' IN em_name) + 1)    AS part2
42FROM employee
43WHERE em_id = '412502';
44
45SELECT AGE(e1.birth_day, e2.birth_day) AS age_interval
46FROM employee e1, employee e2
47WHERE e1.em_id = '112233' AND e2.em_id = '904862';
48
49-- Q13（openGauss）
50SELECT em_id, em_name, birth_day
51FROM employee
52WHERE birth_day > TO_DATE('2000-01-01', 'YYYY-MM-DD');