基于OpenGauss的数据库上机实验

一、建立数据表

CREATE TABLE DEPT (
DEPTNO		INT,
DNAME		VARCHAR(14),
LOC			VARCHAR(13),
CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO)
);

CREATE TABLE BONUS (
ENAME		VARCHAR(10),
JOB			VARCHAR(9),
SAL			INT,
COMM		INT
);

CREATE TABLE SALGRADE (
GRADE		INT,
LOSAL		INT,
HISAL		INT
);

CREATE TABLE EMP (
EMPNO		INT,
ENAME		VARCHAR(10),
JOB			VARCHAR(9),
MGR			INT,
HIREDATE	DATE,
SAL			FLOAT,
COMM		FLOAT,
DEPTNO		INT,
CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
);

INSERT INTO DEPT VALUES
(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES
(20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES
(30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES
(40, 'OPERATIONS', 'BOSTON');

INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7566, to_date('1980-12-17','YYYY-MM-DD'), 800, NULL, 20);
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698, to_date('1981-02-20','YYYY-MM-DD'), 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698, to_date('1981-02-22','YYYY-MM-DD'), 1250, 500, 30);
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839, to_date('1981-04-02','YYYY-MM-DD'), 2975, NULL, 20);
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698, to_date('1981-09-28','YYYY-MM-DD'), 1250, 1400, 30);
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839, to_date('1981-05-01','YYYY-MM-DD'), 2850, NULL, 30);
INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839, to_date('1981-06-09','YYYY-MM-DD'), 2450, NULL, 10);
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566, to_date('1987-06-13','YYYY-MM-DD'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7839, 'KING', 'PRESIDENT', NULL, to_date('1981-11-17','YYYY-MM-DD'), 5000, NULL, 10);
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698, to_date('1981-09-08','YYYY-MM-DD'), 1500, 0, 30);
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788, to_date('1987-06-13','YYYY-MM-DD'), 1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698, to_date('1981-12-03','YYYY-MM-DD'), 950, NULL, 30);
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782, to_date('1982-01-23','YYYY-MM-DD'), 1300, NULL, 10);

INSERT INTO SALGRADE VALUES
(1, 700, 1200);
INSERT INTO SALGRADE VALUES
(2, 1201, 1400);
INSERT INTO SALGRADE VALUES
(3, 1401, 2000);
INSERT INTO SALGRADE VALUES
(4, 2001, 3000);
INSERT INTO SALGRADE VALUES
(5, 3001, 9999);

成功结果如下：

二、DML

5.1.2 实践题目1

查看EMP表中部门号为10的员工的姓名，职位，参加工作时间，工资。

结果：

代码：

select ename,job,hiredate,sal

from emp

where deptno=10

 

5.1.3 实践题目2

查询每个员工每个月拿到的总金额（emp.sal为工资，emp.comm为补助）。（提示：gaussdb中，nvl（ex1,ex2）表示如果ex1为空则返回ex2）

结果：

 

代码：

select ename,nvl(sal+comm,sal)as total

from emp

5.1.4 实践题目3

显示第3个字符为大写O的所有员工的姓名及工资。

结果：

 

代码：

select ename,sal

from emp

where ename like '__O%'

5.1.5 实践题目4

显示有补助的员工的姓名，工资，补助。

结果：

代码：

select ename,sal,comm

from emp

where comm is not NULL

5.1.6 实践题目5

显示员工的最高工资和最低工资。

结果：

 

代码：

select max(sal) as 最高工资, min(sal) as 最低工资

from emp

5.2 聚合查询

5.2.1 预备知识

在查询中，我们经常会遇到这样的问题：求平均值、求最值等等。我们需要使用一些函数如AVG(), MAX()等来进行计算，也需要通过GROUP BY子句来聚合属性。

5.2.2 实践题目1

显示每种职业的平均工资。

结果：

 

代码：

select job,avg(sal)as average

from emp

group by job

5.2.3 实践题目2

显示每个部门每种岗位的平均工资和最高工资。

结果：

 

代码：

select deptno, job, avg(sal) as average, max(sal) as max

from emp

group by deptno, job

5.2.4 实践题目3

显示平均工资低于2500的部门号，平均工资及最高工资。

结果：

 

代码：

select deptno,avg(sal)as average,max(sal)as max

from emp

group by deptno

having average<2500

5.3 多表查询

5.3.1 预备知识

在大部分情况下，我们所需要的信息并不仅仅包含在一张表中。我们首先需要使用join连接多个表，然后再进行查询

5.3.2 实践题目1

显示工资高于2500或岗位为MANAGER的所有员工的姓名，工资，职位，和部门号。

结果：     

 

代码：

select ename, sal, job, deptno

from emp

where sal > 2500 or job = 'MANAGER'

5.3.3 实践题目2

排序显示所有员工的姓名，部门号，工资（以部门号升序，工资降序，雇用日期升序显示）。

结果：

 

代码：

select ename, deptno, sal

from emp

order by deptno asc, sal desc, hiredate asc

5.3.4 实践题目3

采用自然连接的原理显示部门名以及相应的员工姓名。（Sql server不支持NATURAL JOIN语法。）

结果：

 

代码：

select dept.dname,emp.ename

from emp join dept on dept.deptno = emp.deptno

5.3.5 实践题目4

查询SCOTT的上级领导的姓名。

结果：

 

代码：

select e2.ename

from emp e1 join emp e2 on e1.mgr=e2.empno

where e1.ename='SCOTT'

5.3.6 实践题目5

显示部门的部门名称，员工名即使部门没有员工也显示部门名称。

结果：

 

代码：

select dept.dname, emp.ename

from emp right join dept on dept.deptno = emp.deptno