暑假实训-2_Oracle数据库-2_限制数据和对数据排序

-- 2018/07/11 08:30-11:30 实训第2天上午

SELECT * FROM emp;

-- 限制数据和对数据排序

-- 选择限定数据
-- 1-1 查询职位为 SALESMAN 的员工编号，职位，入职日期
SELECT empno AS 员工编号, job AS 职位, hiredate AS 入职日期
FROM emp
WHERE JOB ='SALESMAN' -- hiredate > '31-12月-85';

-- 1-2 查询1985年12月31日之前入职的员工姓名及入职日期
SELECT ename AS 员工姓名, hiredate AS 入职日期
FROM emp
WHERE hiredate < '31-12月-85';

-- 1-3 查询部门编号不在10部门的员工姓名、部门编号
SELECT ename AS 员工姓名, deptno AS 部门编号
FROM emp
WHERE deptno != 10;

-- 特殊比较运算符
-- 2-1 查询入职日期在82年至85年的员工姓名，入职日期
SELECT ename AS 员工姓名, hiredate AS 入职日期
FROM emp
WHERE hiredate BETWEEN '1-1月-82' AND '31-12月-85';

-- 2-2 查询月薪在3000到5000的员工姓名，月薪
SELECT ename AS 员工姓名, sal AS 月薪
FROM emp
WHERE sal BETWEEN 3000 AND 5000;

-- 2-3 查询部门编号为10或者20的员工姓名，部门编号
SELECT ename AS 员工姓名, deptno AS 部门编号
FROM emp
WHERE deptno IN (10, 20);

-- 2-4 查询经理编号为7902,7566,7788的员工姓名，经理编号
SELECT ename AS 员工姓名, mgr AS 经理编号
FROM emp
WHERE mgr IN (7902, 7566, 7788);

SELECT ename AS 员工姓名
FROM emp
WHERE ename LIKE '_L%';

-- 查询 "MAN_" 开头的雇员
SELECT ename AS 员工姓名, job AS 职位
FROM emp
WHERE job LIKE 'MAN@_%' ESCAPE '@';

SELECT ename AS 员工姓名, mgr AS 经理编号
FROM emp
WHERE mgr IS NULL;

-- 3-1 查询员工姓名以W开头的员工姓名
SELECT ename AS 员工姓名
FROM emp
WHERE ename LIKE 'W_%';

-- 3-2 查询员工姓名倒数第二个字符为T的员工姓名
SELECT ename AS 员工姓名
FROM emp
WHERE ename LIKE '%T_';

-- 3-3 查询奖金为空的员工姓名，奖金
SELECT ename AS 员工姓名, comm AS 奖金
FROM emp
WHERE comm IS NULL;

-- 逻辑运算符
SELECT empno AS 员工编号, ename AS 员工姓名, job AS 职位, sal AS 月薪
FROM emp
WHERE sal >= 1100
AND job = 'CLERK';

SELECT empno AS 员工编号, ename AS 员工姓名, job AS 职位, sal AS 月薪
FROM emp
WHERE sal >= 1100
OR job = 'CLERK';

SELECT ename AS 员工姓名, job AS 职位
FROM emp
WHERE job NOT IN ('CLERK', 'MANAGER', 'ANALYST');

-- 4-1 查询工资超过2000并且职位是MANAGER，或着职位是SALESMAN的员工姓名，职位，工资
SELECT ename AS 员工姓名, job AS 职位, sal AS 工资
FROM emp
WHERE sal > 2000 
AND job = 'MANAGER' 
OR job = 'SALESMAN';

-- 4-2 查询工资超过2000并且职位是MANAGER或SALESMAN的员工姓名，职位，工资
SELECT ename AS 员工姓名, job AS 职位, sal AS 工资
FROM emp
WHERE sal > 2000 
AND (job = 'MANAGER' 
OR job = 'SALESMAN')  -- job IN ('MANAGER', 'SALESMAN');

-- 4-3 查询部门在10或者20，并且工资在3000到5000之间的员工姓名，部门，工资
SELECT ename AS 员工姓名, deptno AS 部门编号, sal AS 工资
FROM emp
WHERE (deptno = 10 
OR deptno = 20)  -- deptno IN (10, 20)
AND (sal BETWEEN 3000 AND 5000);

-- 4-4 查询入职日期在81年，并且职位不是SALES开头的员工姓名、入职日期、职位
SELECT ename AS 员工姓名, hiredate AS 入职日期, job AS 职位
FROM emp
WHERE hiredate LIKE '%81'
AND job NOT LIKE 'SALES%';

-- 4-5 查询职位为SALESMAN或MANAGER，部门编号为10或者20，姓名包含A的员工姓名、职位、部门编号
SELECT ename AS 员工姓名, job AS 职位, deptno AS 部门编号
FROM emp
WHERE job IN ('SALESMAN', 'MANAGER')
AND deptno IN (10, 20)
AND ename LIKE '%A%';

-- ORDER BY 子句
SELECT ename , job, deptno, hiredate
FROM emp
ORDER BY hiredate;

SELECT ename , job, deptno, hiredate
FROM emp
ORDER BY hiredate DESC;

-- 5-1 查询部门在20或30的员工姓名，部门编号，并且按照各自升序排序
SELECT ename AS 员工姓名, deptno AS 部门编号
FROM emp
WHERE deptno IN (20, 30)
ORDER BY sal;

-- 5-2 查询工资在2000-3000之间
SELECT ename AS 员工姓名, deptno AS 部门编号, sal AS 工资
FROM emp
WHERE sal BETWEEN 2000 AND 3000
AND deptno != 10
ORDER BY deptno, sal DESC;

-- 5-3 查询入职日期在82年至83年之间，职位以SALES或者MAN开头的员工姓名，入职日期，职位，并按照入职日期降序排序
SELECT ename AS 员工姓名, hiredate AS 入职日期, job AS 职位
FROM emp
WHERE (hiredate BETWEEN '1-1月-82' AND '31-12月-83')
AND (job LIKE 'SALES%'
OR job LIKE 'MAN%')
ORDER BY hiredate DESC;

-- 课后作业
-- 1 查询入职时间在1982-7-9之后，并且不从事
SELECT ename AS 员工姓名, hiredate AS 入职日期, job AS 职位
FROM emp
WHERE hiredate > '9-7月-82'
AND job != 'SALESMAN';

-- 2 查询员工姓名的第三个字母是a的员工姓名
SELECT ename AS 员工姓名
FROM emp
WHERE ename LIKE '__A%' -- lower(ename) LIKE '__a%';

-- 3 查询除了10、20号部门以外的员工姓名、部门编号
SELECT ename AS 员工姓名, deptno AS 部门编号
FROM emp
WHERE deptno NOT IN (10, 20);

--- 4 查询部门号为30的员工的信息，先按工资降序排序，再按姓名升序排序
SELECT *
FROM emp
WHERE deptno = 30
ORDER BY sal DESC, ename;

-- 5 查询没有上级的员工（经理好为空）的员工姓名
SELECT ename AS 员工姓名
FROM emp
WHERE mgr IS NULL;

-- 6 查询工资大于等于4500并且部门为10或者20的员工的姓名、工资、部门编号
SELECT ename AS 员工姓名, sal AS 工资, deptno AS 部门编号
FROM emp
WHERE sal >= 4500
AND deptno IN (10, 20);