Oracle442個應用情境------------基礎應用情境，情境應用

Oracle442個應用情境------------基礎應用情境，情境應用

 

/////////////////基礎知識//////////////////

 

應用情境178：最簡單的select語句

SELECT * FROM Employees;

應用情境179：指定要查詢的列

COL EMP_NAME FORMAT A20
COL SEX FORMAT A10
COL TITLE FORMAT A10
SELECT Emp_name, Sex, Title FROM HRMAN.Employees;

應用情境180：使用DISTINCT關鍵字

SELECT Title FROM HRMAN.Employees;

SELECT DISTINCT Title FROM HRMAN.Employees;

應用情境181：使用ROWNUM

COL EMP_NAME FORMAT A20
COL SEX FORMAT A10
COL TITLE FORMAT A10
SELECT Emp_name, Sex, Title FROM HRMAN.Employees
WHERE ROWNUM<=3;

應用情境182：改變顯示的欄位標題

COL 姓名 FORMAT A20
COL 性別 FORMAT A4
COL 職務 FORMAT A10
COL 身份證 FORMAT A20
SELECT EMP_NAME AS 姓名, SEX AS 性別, TITLE AS 職務, WAGE AS 工資, IDCARD AS 身份證 FROM HRMAN.Employees;

應用情境183：設定查詢條件

COL EMP_NAME FORMAT A20
SELECT Emp_Name, Wage FROM HRMAN.Employees WHERE Wage > 3000 AND Wage < 4000;

應用情境184：在查詢條件中使用BITWEEN 關鍵字

COL EMP_NAME FORMAT A20
SELECT Emp_Name, Wage FROM HRMAN.Employees WHERE Wage BETWEEN 3000 AND 4000;

應用情境185：在查詢條件中使用IN關鍵

COL EMP_NAME FORMAT A20
COL TITLE FORMAT A20
SELECT EMP_NAME, TITLE, WAGE FROM HRMAN.Employees WHERE Emp_name IN ('張三', '李四', '王五');

應用情境186：實現模糊查詢

COL EMP_NAME FORMAT A20
COL TITLE FORMAT A20
COL IDCard FORMAT A20
SELECT EMP_NAME, TITLE, IDCard FROM HRMAN.Employees
WHERE IDCard LIKE '%ddd%';

COL EMP_NAME FORMAT A20
COL TITLE FORMAT A20
COL IDCard FORMAT A20
SELECT EMP_NAME, TITLE, IDCard FROM HRMAN.Employees
WHERE IDCard LIKE '110123_adx_';

應用情境187：排序結果集

COL EMP_NAME FORMAT A20
COL TITLE FORMAT A20
COL IDCard FORMAT A20
SELECT EMP_NAME, TITLE, IDCard FROM HRMAN.Employees
ORDER BY Emp_name;

COL EMP_NAME FORMAT A20
COL TITLE FORMAT A20
SELECT EMP_NAME, TITLE, Wage FROM HRMAN.Employees
ORDER BY Wage DESC;

應用情境188：對多列進行排序

COL EMP_NAME FORMAT A20
COL SEX FORMAT A20
SELECT EMP_NAME, Sex, Wage FROM HRMAN.Employees
ORDER BY Sex, Wage;

應用情境189：使用分組統計

COL 職務 FORMAT A10
SELECT Title AS 職務, AVG(Wage) AS 平均公資 FROM HRMAN.Employees GROUP BY Title;

COL Sex FORMAT A10
COL Title FORMAT A10
SELECT Sex, Title, AVG(Wage) FROM HRMAN.Employees GROUP BY Title;

SELECT Dep_Id, AVG(Wage) FROM HRMAN.Employees
GROUP BY Dep_Id HAVING AVG(Wage) > 4000;

應用情境190：串連查詢

內串連
COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT  t1.Dep_name, t2.Emp_name FROM HRMAN.Departments t1, HRMAN.Employees t2
WHERE t1.Dep_id=t2.Dep_id;

外連結：
COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT  t1.Dep_name, t2.Emp_name FROM HRMAN.Departments t1 INNER JOIN HRMAN.Employees t2
ON t1.Dep_id=t2.Dep_id;

COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT  t1.Dep_name, t2.Emp_name FROM HRMAN.Departments t1 LEFT JOIN HRMAN.Employees t2
ON t1.Dep_id=t2.Dep_id;

COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT  t1.Dep_name, t2.Emp_name FROM HRMAN.Employees t2 RIGHT JOIN HRMAN.Departments t1
ON t1.Dep_id=t2.Dep_id;

COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT  t1.Dep_name, t2.Emp_name FROM HRMAN.Employees t2 FULL JOIN HRMAN.Departments t1
ON t1.Dep_id=t2.Dep_id;

交叉串連
COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT  t1.Dep_name, t2.Emp_name FROM HRMAN.Employees t2 CROSS JOIN HRMAN.Departments t1;

應用情境191：在串連查詢中對空值中對空值的判斷

COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT  t1.Dep_name, t2.Emp_name FROM HRMAN.Departments t1 LEFT JOIN HRMAN.Employees t2
ON t1.Dep_id=t2.Dep_id;

COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT  t1.Dep_name, t2.Emp_name FROM HRMAN.Departments t1 LEFT JOIN HRMAN.Employees t2
ON t1.Dep_id=t2.Dep_id
WHERE t2.Emp_id IS NULL;

應用情境192：一個簡單地子查詢

查詢辦公室的所有員工：
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 = '辦公室');

返回兩個部門的值：
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='張三';

應用情境205：修改資料時不能違反外鍵約束

為表HRMAN。Departments的DEP_id列和表HRMAN.Employees的DEP_ID列中建立外鍵約束
ALTER TABLE HRMAN.Employees
ADD CONSTRAINT FK_EMP_DEPID
FOREIGN KEY(Dep_id) REFERENCES HRMAN.Departments(Dep_id);

UPDATE HRMAN.Employees SET Dep_id=200 WHERE Emp_Name='張三';

應用情境206：刪除資料語句

DELETE FROM HRMAN.Employees WHERE Emp_Name = '小明';

TRUNCATE TABLE HRMAN.Employees;

著作權聲明：歡迎轉載，希望在你轉載的同時，添加原文地址，謝謝配合