返回两个部门的值：
COL Emp_name FORMAT A20
COL Title FORMAT A20
SELECT Emp_Name, Title FROM HRMAN.Employees WHERE Dep_Id =
(SELECT Dep_Id FROM HRMAN.Departments WHERE Dep_name = '办公室' OR Dep_name = '人事部');

应用场景193：在查询中使用具和函数返回值

统计表中所有工资低于品级工资的员工的信息

COL Emp_name FORMAT A20
COL Title FORMAT A20
SELECT Emp_name, Title, Wage FROM HRMAN.Employees WHERE Wage <
(SELECT AVG(Wage) FROM HRMAN.Employees);

应用场景194：IN关键字与返回值的子查询

查询办公室和人事部中的员工信息

COL Emp_name FORMAT A20
COL Title FORMAT A20
SELECT Emp_name, Title, Wage FROM HRMAN.Employees WHERE Dep_Id IN
(SELECT Dep_Id FROM HRMAN.Departments WHERE Dep_name = '办公室' OR Dep_name = '人事部');

应用场景195：EXISTS关键字与子查询

查询人事部中的员工信息
COL Emp_name FORMAT A20
COL Title FORMAT A20
SELECT Emp_name, Title, Wage FROM HRMAN.Employees e
WHERE EXISTS
(SELECT Dep_id FROM HRMAN.Departments d
WHERE e.Dep_id = d.Dep_id AND d.Dep_name='人事部');

使用IN关键字：
COL Emp_name FORMAT A20
COL Title FORMAT A20
SELECT Emp_name, Title, Wage FROM HRMAN.Employees e
WHERE e.Dep_id IN
(SELECT Dep_id FROM HRMAN.Departments d
WHERE e.Dep_id = d.Dep_id AND d.Dep_name='人事部');

应用场景196：使用UNION关键字的合并查询

从表中Employee中查询各个部门的部门经理

COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT Dep_Id, Dep_Name FROM HRMAN.Departments
UNION
SELECT Dep_Id, Emp_Name FROM HRMAN.Employees WHERE Title = '部门经理';

工资大于3000的员工记录：

COL Emp_name FORMAT A20
COL Title FORMAT A20
SELECT Emp_name, Title, Wage FROM HRMAN.Employees
UNION
SELECT Emp_name, Title, Wage FROM HRMAN.Employees WHERE Wage > 3000;

高效率：
COL Emp_name FORMAT A20
COL Title FORMAT A20
SELECT Emp_name, Title, Wage FROM HRMAN.Employees
UNION ALL
SELECT Emp_name, Title, Wage FROM HRMAN.Employees WHERE Wage > 3000;

应用场景197：使用select语句中的DECODE函数

SELECT Emp_name, DECODE(Sex, '男', '先生', '女', '女士', '未知') AS Sex
FROM HRMAN.Employees;

应用场景198：使用select与剧中CASE函数

SELECT Emp_name, CASE Sex WHEN '男' THEN '先生' WHEN '女' THEN '女士' ELSE '未知' END AS Sex
FROM HRMAN.Employees;

查询表Employees中的员工工资级别：

SELECT Emp_name, Wage, CASE WHEN Wage<=3000 THEN '低' WHEN Wage>3000 AND Wage<5000 THEN '中' WHEN Wage>=5000 THEN '高' END AS GRADE
FROM HRMAN.Employees;

应用场景199：保存查询结果

将办公室的所有员工的姓名和职务信息保存到表中OFFICE：

COL Emp_name FORMAT A20
COL Title FORMAT A20
CREATE TABLE HRMAN.Office AS
SELECT e.Emp_Name, e.Title
FROM HRMAN.Employees e, HRMAN.Departments d
WHERE e.Dep_id = d.Dep_Id AND d.Dep_Name = '办公室';
SELECT * FROM HRMAN.Office;

应用场景200：插入数据语句

INSERT INTO HRMAN.Departments VALUES(100, '公关部');
SELECT * FROM HRMAN.Departments;

INSERT INTO HRMAN.Employees (Emp_Name, Sex,Title, IdCard, Dep_Id)
VALUES ('小明', '男', '职员', '110123456789', 2);

应用场景201：在插入数据时利用默认值

ALTER TABLE HRMAN.Employees ADD InputDate date DEFAULT(sysdate);

INSERT INTO HRMAN.Employees (Emp_Name, Sex,Title, IdCard, Wage, Dep_Id)
VALUES ('小李', '男', '职员', '210123456789', 2500, 2);

应用场景202：修改数据语句

将表中的所有记录工资增加10%：

UPDATE HRMAN.Employees SET Wage=Wage*1.1;

将表中所有部门为"办公室"的员工工资增加10%

UPDATE HRMAN.Employees SET Wage=Wage*1.1
WHERE Dep_id = (SELECT Dep_id FROM HRMAN.Departments WHERE Dep_name = '办公室');

应用场景203：修改数据时不允许在唯一性约束列中使用相同的值

ALTER TABLE HRMAN.Employees
ADD CONSTRAINT UK_EMPNAME
UNIQUE(Emp_name);

UPDATE HRMAN.Employees SET Emp_name='张三' WHERE Emp_name='李四';

应用场景204：修改数据是不能违反检查约束

ALTER TABLE HRMAN.Employees
ADD CONSTRAINT CK_EMPWAGE CHECK(WAGE>0);

UPDATE HRMAN.Employees SET Wage=-1 WHERE Emp_Name='张三';