一、建立数据表
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