Database System Administration
การบริหารจัดการระบบฐานข้อมูล

02 Restricting & Sorting Data

1. WHERE Clause — กรองแถว

2. LIKE Wildcards

3. Logical Operators: AND / OR / NOT

-- ถาม: พนักงานใน dept 10 หรือ dept 20 ที่เงินเดือน > 5000
-- ผิด (AND ทำก่อน OR):
WHERE dept_id = 10 OR dept_id = 20 AND salary > 5000
-- ถูก (ใส่วงเล็บ):
WHERE (dept_id = 10 OR dept_id = 20) AND salary > 5000

4. ORDER BY — จัดเรียงผลลัพธ์

-- ASC = น้อยไปมาก (default), DESC = มากไปน้อย
SELECT last_name, salary, hire_date
FROM   employees
ORDER BY salary DESC, last_name ASC;

-- เรียงด้วยตำแหน่งคอลัมน์ใน SELECT list
SELECT last_name, salary FROM employees
ORDER BY 2 DESC;  -- 2 = salary column

-- NULL values จะอยู่ที่ไหน?
ORDER BY commission_pct NULLS LAST;   -- NULL ไปอยู่ท้าย
ORDER BY commission_pct NULLS FIRST;  -- NULL อยู่หัว

SELECT first_name, last_name, salary
FROM   employees
WHERE  first_name LIKE 'A%'
AND    salary BETWEEN 5000 AND 10000
ORDER BY salary DESC;

SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary BETWEEN 5000 AND 10000
AND    job_id IN ('IT_PROG', 'SA_REP');

SELECT last_name, salary, commission_pct
FROM   employees
ORDER BY commission_pct DESC NULLS LAST, salary DESC;

03 Single-Row Functions (เจาะลึกการใช้ฟังก์ชันระดับแถว)

1. เจาะลึกฟังก์ชันจัดการข้อความ (Character Functions)

แบ่งออกเป็น 2 กลุ่มย่อยหลักตามการทำงาน:

A. Case-Manipulation Functions (แปลงขนาดตัวอักษร)

SELECT employee_id, last_name FROM employees WHERE LOWER(last_name) = 'higgins';

B. Character-Manipulation Functions (การหั่น/แต่ง/ดึงข้อมูลข้อความ)

• CONCAT(str1, str2): เชื่อมข้อความสองตัวเข้าด้วยกัน (มีค่าเท่ากับการใช้สัญลักษณ์ Pipe || ใน Oracle)
  ตัวอย่าง: CONCAT('Hello', 'World') ➜ 'HelloWorld'
• SUBSTR(str, position, [length]): หั่นข้อความจากตำแหน่งเริ่มต้นที่กำหนด ดึงยาวออกไปตามความยาวที่ระบุ
  💡 กฎเหล็กข้อสอบ:
  • พิกัดเริ่มต้นเป็น **1-indexed** (เริ่มนับที่ 1)
  • ถ้า position เป็นบวก จะเริ่มนับจากซ้ายไปขวา เช่น SUBSTR('HelloWorld', 1, 5) ➜ 'Hello'
  • ถ้า position เป็นลบ จะนับย้อนจากท้ายสุดมาทางซ้าย เช่น SUBSTR('HelloWorld', -5) ➜ 'World' (เริ่มนับจาก W)
  • หากไม่ระบุ length จะดึงข้อมูลยาวไปจนสุดประโยค
• LENGTH(str): นับจำนวนตัวอักษรของข้อความ (รวมช่องว่าง)
  ตัวอย่าง: LENGTH('HelloWorld') ➜ 10
• INSTR(str, substring, [position, occurrence]): ค้นหาตำแหน่งเริ่มต้นของคำย่อยในประโยค (หากไม่เจอจะส่งกลับเลข 0)
  ตัวอย่าง: INSTR('HelloWorld', 'W') ➜ 6 (ตัว W อยู่ในตำแหน่งลำดับที่ 6)
• LPAD(str, total_length, pad_char) / RPAD: เติมอักษรเสริมด้านซ้ายหรือขวาให้ได้ความยาวรวมที่ต้องการ
  ตัวอย่าง: LPAD(salary, 10, '*') ➜ '*****24000' (ช่วยในการแสดงสลิปเงินเดือนหรือบัตรเครดิต)
• REPLACE(str, search_str, replace_str): ค้นหาคำเป้าหมายแล้วแทนที่ด้วยคำใหม่ทั้งหมด
  ตัวอย่าง: REPLACE('JACK and JILL', 'J', 'BL') ➜ 'BLACK and BLILL'
• TRIM([both|leading|trailing] char FROM str): ตัดตัวอักษรส่วนเกิน (หรือช่องว่าง) ออกจากหัวและท้ายข้อความ
  ตัวอย่าง: TRIM('H' FROM 'HelloWorldH') ➜ 'elloWorld'

2. เจาะลึกฟังก์ชันตัวเลข (Number Functions)

ฟังก์ชันคณิตศาสตร์ที่จัดการทศนิยมและหาเศษ:

3. เจาะลึกฟังก์ชันวันที่และการคำนวณ (Date Functions)

ระบบ Oracle เก็บวันที่ในรูปแบบตัวเลขภายใน (Internal Numeric) ครอบคลุมศตวรรษ, ปี, เดือน, วัน, ชั่วโมง, นาที, วินาที โดยค่าแสดงผลเริ่มต้นคือ DD-MON-RR

A. การคำนวณทางคณิตศาสตร์กับวันที่ (Date Arithmetic)

• Date - Date: คืนค่าเป็น **จำนวนวัน** ที่ต่างกันระหว่างสองวันที่ (เช่น SYSDATE - hire_date)
• Date + Number: บวกจำนวนวันเข้าไปในวันที่เพื่อหาเป้าหมายปลายทาง
• Date + Number/24: บวกจำนวนชั่วโมงเข้าไปในวันที่นั้นๆ

B. ฟังก์ชันจัดการวันที่ที่ต้องเข้าใจก่อนสอบ

• SYSDATE: ส่งคืนวันที่และเวลาปัจจุบันของเซิร์ฟเวอร์ระบบ
• MONTHS_BETWEEN(date1, date2): หาจำนวนเดือนที่ห่างกันระหว่างสองช่วงเวลา (เอาค่ามากไว้ซ้ายเสมอเพื่อไม่ให้ติดลบ)
  ตัวอย่าง: MONTHS_BETWEEN('01-SEP-95', '11-JAN-94') ➜ 19.677419
• ADD_MONTHS(date, n): บวกเพิ่มหรือลดจำนวนเดือนเชิงปฏิทินเข้าไปในวันที่
  ตัวอย่าง: ADD_MONTHS('11-JAN-94', 6) ➜ '11-JUL-94'
• NEXT_DAY(date, 'DAY_OF_WEEK'): หาวันถัดไปที่เป็นชื่อวันที่ระบุ (เช่น หาพิกัดวันศุกร์ถัดไปหลังจากวันนี้)
  ตัวอย่าง: NEXT_DAY('24-MAY-04', 'FRIDAY') ➜ '28-MAY-04'
• LAST_DAY(date): ส่งคืนวันที่วันสุดท้ายของเดือนนั้นๆ (เช่น เช็คว่าเดือนกุมภาพันธ์ปีนั้นมี 28 หรือ 29 วัน)
  ตัวอย่าง: LAST_DAY('01-FEB-04') ➜ '29-FEB-04' (ปีอธิกสุรทิน)
• ROUND / TRUNC กับวันที่:
  • ROUND(SYSDATE, 'MONTH') ➜ หากเลยวันที่ 15 ของเดือนไปแล้ว จะปัดขึ้นเป็นวันที่ 1 ของเดือนถัดไป หากไม่ถึงปัดลงวันที่ 1 เดือนเดิม
  • TRUNC(SYSDATE, 'MONTH') ➜ ตัดเศษเวลาและวันทิ้ง ปัดกลับไปเป็นวันที่ 1 ของเดือนปัจจุบันทันที

4. เจาะลึกฟังก์ชันแปลงประเภทข้อมูล (Conversion Functions)

เมื่อต้องการเชื่อมโยงหรือเปรียบเทียบข้อมูลต่างชนิดกันอย่างถูกต้อง ป้องกันความผิดพลาดของระบบ:

TO_NUMBER() ───►  NUMBER  ◄─── TO_CHAR()
TO_DATE()   ───►   DATE   ◄─── TO_CHAR()

• TO_CHAR(date, 'format_model'): แปลงชนิดข้อมูลวันที่ให้ออกมาเป็นข้อความตามรูปแบบที่กำหนด
  💡 คีย์เวิร์ดรูปแบบที่ออกข้อสอบบ่อย:
  • YYYY / YY: แสดงปี ค.ศ. แบบ 4 หลัก หรือ 2 หลัก
  • MM / MONTH / MON: แสดงเดือนแบบตัวเลข 2 หลัก / ตัวหนังสือชื่อเต็ม / ตัวย่อ 3 อักษร
  • DD / DAY / DY: แสดงวันที่ตัวเลข / ชื่อวันเต็มสัปดาห์ / ชื่อวันย่อ
  • fm Prefix (Fill Mode): ใช้เขียนนำหน้าเพื่อ **ตัดช่องว่างเบาะรอง (Padded blanks)** หรือเลขศูนย์นำหน้าทิ้ง เช่น TO_CHAR(SYSDATE, 'fmDD Month YYYY')
• TO_CHAR(number, 'format_model'): แปลงตัวเลขเงินเดือน/โบนัสให้แสดงผลสวยงาม
  องค์ประกอบที่ใช้: 9 (แทนหนึ่งหลักตัวเลข), 0 (บังคับแสดงเลขศูนย์นำหน้า), $ (สกุลเงินดอลลาร์), L (สกุลเงินท้องถิ่นเช่น ฿), . (จุดทศนิยม), , (เครื่องหมายคั่นหลักพัน)
  ตัวอย่าง: TO_CHAR(salary, 'L99,999.00') ➜ ฿24,000.00
• TO_DATE(char, 'format_model'): แปลงข้อความให้กลายเป็นตัวแปรประเภทวันที่เพื่อให้ Oracle นำไปคำนวณเปรียบเทียบได้
  ตัวอย่างการค้นหาพนักงานก่อนปี 1990:

  SELECT last_name, hire_date FROM employees WHERE hire_date < TO_DATE('01-Jan-90', 'DD-Mon-RR');

5. เจาะลึกฟังก์ชันจัดการค่าว่าง (General & Null Handling Functions)

ค่าว่าง (NULL) เมื่อนำไปคำนวณทางคณิตศาสตร์มักส่งผลให้ผลลัพธ์กลายเป็น NULL เสมอ จึงต้องมีกลุ่มฟังก์ชันจัดการสิ่งนี้:

• NVL(expr1, expr2): หาก expr1 เป็นค่าว่าง (NULL) จะส่งกลับ expr2 ออกมาแทน หากไม่ใช่ส่งค่าเดิมออกไป
  ⚠️ กฎห้ามลืม: ข้อมูลของ expr1 และ expr2 **ต้องเป็นชนิดข้อมูลชนิดเดียวกัน**
  ตัวอย่าง: NVL(commission_pct, 0) ➜ หากพนักงานไม่มีค่าคอมฯ จะใส่เลข 0 แทนเพื่อใช้คูณสูตรรายได้
• NVL2(expr1, expr2, expr3): ตรวจสอบว่า expr1 เป็น NULL หรือไม่?
  • ถ้า ไม่ใช่ NULL ➜ ส่งคืน expr2
  • ถ้า เป็น NULL ➜ ส่งคืน expr3
  ตัวอย่าง: NVL2(commission_pct, 'SAL+COMM', 'SAL') ➜ เพื่อจัดกลุ่มประเภทเงินได้พนักงาน
• NULLIF(expr1, expr2): เปรียบเทียบค่าสองค่า
  • ถ้า เท่ากัน ➜ จะส่งคืนค่า NULL
  • ถ้า ไม่เท่ากัน ➜ จะส่งคืนค่าแรกคือ expr1
  ตัวอย่าง: NULLIF(LENGTH(first_name), LENGTH(last_name)) ➜ จะคืนค่า NULL หากพนักงานมีชื่อและนามสกุลยาวเท่ากันพอดี
• COALESCE(expr1, expr2, ..., exprn): ฟังก์ชันขั้นสูงที่ตรวจสอบหาค่าที่ไม่ใช่ NULL ค่าแรกในลิสต์พารามิเตอร์ที่เรียงไว้
  ข้อดีเหนือ NVL: รับอาร์กิวเมนต์ได้หลายตัวและจะตรวจสอบเรียงลำดับไปเรื่อยๆ จนกว่าจะเจอตัวที่มีค่า

6. เจาะลึกฟังก์ชันเงื่อนไข (Conditional Expressions)

ช่วยให้เราสามารถเขียนลอจิกเงื่อนไขประเภท IF-THEN-ELSE ได้โดยตรงในคำสั่งคิวรี่:

A. CASE Expression (รูปแบบมาตรฐานสากล ANSI SQL - แนะนำใช้ทั่วไป)

SELECT last_name, job_id, salary,
       CASE job_id WHEN 'IT_PROG'  THEN 1.10*salary
                   WHEN 'ST_CLERK' THEN 1.15*salary
                   ELSE salary END "REVISED_SALARY"
FROM   employees;

B. DECODE Function (ฟังก์ชันพิเศษเฉพาะของ Oracle เท่านั้น)

SELECT last_name, job_id, salary,
       DECODE(job_id, 'IT_PROG',  1.10*salary,
                      'ST_CLERK', 1.15*salary,
                      salary) "REVISED_SALARY"
FROM   employees;

🏋️ โจทย์ซ้อมเขียนมือระดับข้อสอบ & เฉลยคำสั่งทีละบรรทัด

ครูเตรียมแบบฝึกหัดท้าทายระดับข้อสอบจริง 5 ข้อ ให้นักเรียนได้ฝึกหัดเขียนคิดวิเคราะห์คำสั่งด้วยตนเองครับ:

SELECT INITCAP(SUBSTR(first_name, 1, 1)) || '. ' || UPPER(last_name) AS FULL_NAME,
       LENGTH(last_name) AS LAST_LEN
FROM   employees;

SELECT last_name,
       TRUNC(MONTHS_BETWEEN(SYSDATE, hire_date) / 12, 0) || ' years' AS EXPERIENCE,
       LAST_DAY(hire_date) AS FIRST_MONTH_END
FROM   employees;

SELECT last_name,
       NVL2(commission_pct, TO_CHAR(commission_pct * 100) || '%', 'No Comm') AS COMM_STATUS
FROM   employees;

SELECT last_name,
       INSTR(last_name, 'a') AS A_POSITION
FROM   employees
WHERE  LENGTH(last_name) > 5
  AND  SUBSTR(last_name, -1) = 's';

SELECT last_name, salary,
       DECODE(TRUNC(salary / 5000), 0, 'Tier 1',
                                    1, 'Tier 2',
                                    'Tier 3') AS SALARY_TIER
FROM   employees;

SELECT UPPER(first_name) AS first_upper,
       last_name,
       LENGTH(last_name) AS name_len,
       SUBSTR(email, 1, 3) AS email_prefix
FROM   employees
WHERE  LOWER(last_name) LIKE '%n';

SELECT last_name, salary,
       salary + (salary * NVL(commission_pct, 0)) AS total_income,
       CASE department_id
            WHEN 10 THEN 'Administration'
            WHEN 20 THEN 'Marketing'
            WHEN 30 THEN 'Purchasing'
            ELSE 'Other Departments'
       END AS dept_category
FROM   employees;

04 Group Functions & Aggregation

1. Group Functions หลัก

2. GROUP BY

-- ข้อบังคับ: ทุก column ใน SELECT ที่ไม่ใช่ aggregate function
-- ต้องอยู่ใน GROUP BY ด้วย!

-- ถูก:
SELECT department_id, AVG(salary)
FROM   employees
GROUP BY department_id;

-- ผิด! (first_name ไม่ได้อยู่ใน GROUP BY)
SELECT department_id, first_name, AVG(salary)  -- ERROR!
FROM   employees
GROUP BY department_id;

3. HAVING — กรองกลุ่ม

-- WHERE กรองแถวก่อน Group, HAVING กรองกลุ่มหลัง Group

SELECT   department_id, COUNT(*) AS emp_count, AVG(salary) AS avg_sal
FROM     employees
WHERE    job_id != 'SA_MAN'          -- กรองแถวก่อน (ไม่รวม Manager)
GROUP BY department_id
HAVING   AVG(salary) > 8000          -- กรองกลุ่มที่เงินเดือนเฉลี่ย > 8000
ORDER BY avg_sal DESC;

SELECT   department_id,
         COUNT(*)    AS emp_count,
         MAX(salary) AS max_salary,
         MIN(salary) AS min_salary,
         AVG(salary) AS avg_salary
FROM     employees
GROUP BY department_id
HAVING   COUNT(*) > 5
ORDER BY avg_salary DESC;

SELECT department_id, AVG(salary)
FROM   employees
WHERE  job_id != 'AD_PRES'      -- กรอง: ไม่รวม President
GROUP BY department_id
HAVING AVG(salary) > 6000;      -- กรอง: เฉพาะแผนกที่เฉลี่ย > 6000

SELECT department_id,
       COUNT(*) AS emp_count,
       ROUND(AVG(salary), 2) AS avg_salary,
       MAX(salary) AS max_salary
FROM   employees
WHERE  department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) > 7000
ORDER BY avg_salary DESC;

05 Joining Multiple Tables

1. ประเภทของ JOIN

2. ตัวอย่าง JOIN Syntax

-- NATURAL JOIN (join อัตโนมัติตามชื่อคอลัมน์ที่เหมือนกัน)
SELECT employee_id, last_name, department_name
FROM employees NATURAL JOIN departments;

-- JOIN...USING (ระบุคอลัมน์)
SELECT employee_id, last_name, department_name
FROM employees JOIN departments USING (department_id);

-- JOIN...ON (ยืดหยุ่นที่สุด ใช้ชื่อต่างกันได้)
SELECT e.employee_id, e.last_name, d.department_name
FROM   employees e JOIN departments d
ON     e.department_id = d.department_id;

-- LEFT OUTER JOIN
SELECT e.last_name, d.department_name
FROM   employees e LEFT OUTER JOIN departments d
ON     e.department_id = d.department_id;

-- SELF JOIN (หา Manager ของพนักงาน)
SELECT e.last_name AS employee, m.last_name AS manager
FROM   employees e JOIN employees m
ON     e.manager_id = m.employee_id;

SELECT e.last_name, d.department_name, l.city
FROM   employees e
JOIN   departments d ON e.department_id = d.department_id
JOIN   locations   l ON d.location_id   = l.location_id;

SELECT e.last_name, d.department_name
FROM   employees e LEFT OUTER JOIN departments d
ON     e.department_id = d.department_id;

SELECT e.last_name AS "Employee",
       m.last_name AS "Manager"
FROM   employees e
LEFT OUTER JOIN employees m ON e.manager_id = m.employee_id
ORDER BY m.last_name NULLS LAST;

SELECT e.last_name, j.job_title, d.department_name, l.city
FROM   employees e
JOIN   jobs j ON e.job_id = j.job_id
JOIN   departments d ON e.department_id = d.department_id
JOIN   locations l ON d.location_id = l.location_id;

SELECT e.last_name AS employee_name,
       NVL(m.last_name, 'No Manager') AS manager_name
FROM   employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

06 Using Subqueries to Solve Queries

1. ประเภทของ Subquery

2. Single-Row Subquery

-- หาพนักงานที่ได้รับเงินเดือนเท่ากับ Abel
SELECT last_name, salary
FROM   employees
WHERE  salary = (SELECT salary
                 FROM   employees
                 WHERE  last_name = 'Abel');

-- หาพนักงานที่เงินเดือนสูงกว่าค่าเฉลี่ย
SELECT last_name, salary
FROM   employees
WHERE  salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary;

3. Multi-Row Subquery: IN, ANY, ALL

-- IN: เท่ากับค่าใดค่าหนึ่งในรายการ
SELECT last_name FROM employees
WHERE department_id IN
      (SELECT department_id FROM departments WHERE location_id = 1700);

-- ANY: เปรียบเทียบกับค่าใดค่าหนึ่ง (< ANY = น้อยกว่าค่าสูงสุด)
SELECT last_name, salary FROM employees
WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG');

-- ALL: เปรียบเทียบกับทุกค่า (< ALL = น้อยกว่าทุกค่า = น้อยกว่าค่าต่ำสุด)
SELECT last_name, salary FROM employees
WHERE salary < ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG');

SELECT last_name, salary
FROM   employees
WHERE  salary = (SELECT MAX(salary) FROM employees);

-- เพิ่ม IS NOT NULL ใน subquery
SELECT employee_id FROM employees
WHERE  manager_id NOT IN
       (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL);

SELECT first_name, last_name, salary
FROM   employees
WHERE  salary > (SELECT AVG(salary) FROM employees);

SELECT last_name, department_id, salary
FROM   employees
WHERE  department_id IN (SELECT department_id 
                         FROM   employees 
                         WHERE  last_name = 'Taylor');

07 Using the Set Operators

1. Set Operators คืออะไร?

ใช้รวม ตัด หรือหาส่วนร่วมของผลลัพธ์จาก SELECT หลายชุด เสมือนทฤษฎีเซต (Set Theory)

2. กฎการใช้ Set Operators (สำคัญมาก!)

3. ตัวอย่าง

-- UNION: พนักงานในแผนก 10 หรือ Job = SA_REP (ไม่ซ้ำ)
SELECT employee_id, last_name FROM employees WHERE department_id = 10
UNION
SELECT employee_id, last_name FROM employees WHERE job_id = 'SA_REP';

-- INTERSECT: พนักงานที่อยู่ใน dept 10 และ เป็น SA_REP
SELECT employee_id FROM employees WHERE department_id = 10
INTERSECT
SELECT employee_id FROM employees WHERE job_id = 'SA_REP';

-- MINUS: พนักงาน dept 10 ที่ไม่ใช่ SA_REP
SELECT employee_id FROM employees WHERE department_id = 10
MINUS
SELECT employee_id FROM employees WHERE job_id = 'SA_REP';

SELECT employee_id, last_name FROM employees   -- 2 columns
UNION
SELECT department_id FROM departments;          -- 1 column (ERROR!)

SELECT employee_id FROM job_history
WHERE  department_id IN (20, 30)
MINUS
SELECT employee_id FROM employees
WHERE  department_id IN (20, 30);

SELECT employee_id, job_id FROM employees
INTERSECT
SELECT employee_id, job_id FROM job_history;

SELECT job_id FROM jobs
MINUS
SELECT job_id FROM employees;

08 Manipulating Data — DML & Transaction Control

1. คำสั่งการจัดการข้อมูล (DML Statements)

DML (Data Manipulation Language) ใช้สำหรับเพิ่ม แก้ไข หรือลบข้อมูลในตาราง โดยผลของการทำ DML จะยังไม่เปลี่ยนข้อมูลในดิสก์ถาวรจนกว่าจะทำ COMMIT

1.1 INSERT Statement — เพิ่มข้อมูลใหม่

-- 1. เพิ่มแถวข้อมูลตรงๆ (ระบุค่าครบทุกคอลัมน์)
INSERT INTO departments (department_id, department_name, manager_id, location_id)
VALUES (280, 'Data Analytics', 103, 1700);

-- 2. เพิ่มข้อมูลเฉพาะคอลัมน์ที่กำหนด (คอลัมน์ที่ไม่ระบุจะเป็น NULL)
INSERT INTO departments (department_id, department_name)
VALUES (290, 'Cyber Security');

-- 3. เพิ่มข้อมูลจากการคัดลอกอีกตารางผ่าน SELECT (Subquery Insert)
INSERT INTO sales_reps (id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM   employees
WHERE  job_id LIKE 'SA_%';

1.2 UPDATE Statement — แก้ไขข้อมูล

-- แก้ไขค่าในคอลัมน์ที่ต้องการ
UPDATE employees
SET    salary = salary * 1.10,
       commission_pct = 0.15
WHERE  employee_id = 104;

-- UPDATE ด้วย Subquery (ดึงค่าจ้างและตำแหน่งของพนักงาน 103 มาใส่ให้ 105)
UPDATE employees
SET    (job_id, salary) = (SELECT job_id, salary 
                           FROM   employees 
                           WHERE  employee_id = 103)
WHERE  employee_id = 105;

1.3 DELETE vs. TRUNCATE

1.4 MERGE Statement (Upsert)

ใช้ปรับปรุงข้อมูลแบบเงื่อนไข: ถ้ามีข้อมูลอยู่แล้วให้ทำ UPDATE แต่ถ้ายังไม่มีให้ทำ INSERT ในคำสั่งเดียว

MERGE INTO copy_emp c
USING employees e
ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
  UPDATE SET c.first_name = e.first_name,
             c.salary     = e.salary
WHEN NOT MATCHED THEN
  INSERT (c.employee_id, c.first_name, c.last_name, c.salary)
  VALUES (e.employee_id, e.first_name, e.last_name, e.salary);

2. การควบคุมธุรกรรม (Transaction Control & Read Consistency)

Transaction (ธุรกรรม): กลุ่มของคำสั่ง DML ที่ถือเป็นงานก้อนเดียวกัน มีจุดเริ่มเมื่อเริ่มทำ DML แรก และสิ้นสุดเมื่อสั่ง COMMIT หรือ ROLLBACK

-- เริ่มทำ DML
INSERT INTO departments VALUES (300, 'R&D', NULL, 1700);
SAVEPOINT sp_after_insert;

UPDATE employees SET salary = salary + 500 WHERE department_id = 300;

-- ย้อนคืนเฉพาะงานหลัง SAVEPOINT
ROLLBACK TO sp_after_insert;

-- บันทึกเปลี่ยนแปลงทั้งหมดลงดิสก์ถาวร
COMMIT;

MERGE INTO emp_target t
USING emp_source s
ON (t.employee_id = s.employee_id)
WHEN MATCHED THEN
  UPDATE SET t.salary = s.salary
WHEN NOT MATCHED THEN
  INSERT (t.employee_id, t.first_name, t.salary)
  VALUES (s.employee_id, s.first_name, s.salary);

09 DDL Statements — Creating & Managing Tables

1. คำสั่งกำหนดโครงสร้าง (DDL Statements)

DDL (Data Definition Language) ใช้สร้าง เปลี่ยนแปลง หรือลบอ็อบเจกต์ในฐานข้อมูล ทุกคำสั่ง DDL จะสั่ง COMMIT อัตโนมัติ (ไม่สามารถ ROLLBACK ได้)

2. ชนิดข้อมูลพื้นฐานใน Oracle (Data Types)

3. ข้อกำหนดความสมบูรณ์ข้อมูล (Constraints 5 ชนิด)

CREATE TABLE project_emp (
    emp_id       NUMBER(6)       CONSTRAINT prj_emp_id_pk PRIMARY KEY,
    email        VARCHAR2(100)   CONSTRAINT prj_email_uk UNIQUE,
    first_name   VARCHAR2(50)    NOT NULL,
    salary       NUMBER(8,2)     CONSTRAINT prj_sal_ck CHECK (salary > 0),
    dept_id      NUMBER(4),
    CONSTRAINT prj_dept_fk FOREIGN KEY (dept_id) 
        REFERENCES departments(department_id) ON DELETE CASCADE
);

4. การแก้ไขตารางด้วย ALTER TABLE

-- 1. เพิ่มคอลัมน์ใหม่
ALTER TABLE project_emp ADD (phone_number VARCHAR2(20));

-- 2. แก้ไขขนาดคอลัมน์ หรือเปลี่ยนเป็น NOT NULL
ALTER TABLE project_emp MODIFY (phone_number VARCHAR2(30) NOT NULL);

-- 3. ลบคอลัมน์
ALTER TABLE project_emp DROP COLUMN phone_number;

-- 4. กู้คืนตารางที่เพิ่งลบไป (Flashback Table)
DROP TABLE project_emp;
FLASHBACK TABLE project_emp TO BEFORE DROP;

CREATE TABLE course (
    course_id   NUMBER(5)      PRIMARY KEY,
    title       VARCHAR2(100)  NOT NULL,
    credits     NUMBER(2)      CHECK (credits > 0)
);

10 Creating Other Schema Objects — Views, Sequences, Indexes & Synonyms

1. View (วิว - ตารางจำลอง)

View คือ ตารางเสมือนที่สร้างขึ้นจากคำสั่ง SELECT เพื่อจำกัดสิทธิ์เข้าถึงคอลัมน์ หรือเพื่อซ่อนความซับซ้อนของการ JOIN ตาราง

-- สร้าง Simple View พร้อม WITH CHECK OPTION
CREATE OR REPLACE VIEW emp_dept20_vu AS
SELECT employee_id, last_name, salary, department_id
FROM   employees
WHERE  department_id = 20
WITH CHECK OPTION CONSTRAINT emp_dept20_ck;

-- WITH CHECK OPTION ป้องกันการ UPDATE/INSERT ข้อมูลที่มี department_id != 20 ผ่าน View นี้!

-- วิวที่ห้ามทำ DML เด็ดขาด (Read Only)
CREATE OR REPLACE VIEW emp_summary_vu AS
SELECT department_id, AVG(salary) AS avg_sal
FROM   employees
GROUP BY department_id
WITH READ ONLY;

2. Sequence (ตัวเจนลำดับตัวเลขอัตโนมัติ)

-- สร้าง Sequence
CREATE SEQUENCE dept_seq
    START WITH 300
    INCREMENT BY 10
    MAXVALUE 9999
    NOCACHE
    NOCYCLE;

-- เรียกใช้งาน Sequence
-- NEXTVAL: เจนเลขถัดไป | CURRVAL: อ่านเลขปัจจุบันใน Session
INSERT INTO departments (department_id, department_name)
VALUES (dept_seq.NEXTVAL, 'Digital Marketing');

3. Index (ดัชนีเร่งความเร็วในการค้นหา)

4. Synonym (ชื่อนามแฝง)

-- สร้าง Synonym ส่วนตัว เพื่อไม่ต้องพิมพ์ hr.employees ยอดนิยม
CREATE SYNONYM emp FOR hr.employees;

SELECT * FROM emp; -- ใช้งานแทนตารางเต็มได้ทันที

11 Managing Objects with Data Dictionary Views

1. Data Dictionary คืออะไร?

Data Dictionary เป็นชุดตารางอ้างอิง Read-Only ที่จัดเก็บ Metadata (ข้อมูลอธิบายโครงสร้างระบบ) ทั้งหมดของฐานข้อมูล Oracle เช่น รายชื่อตาราง, สิทธิ์การใช้งาน, ข้อกำหนด Constraints และสถิติต่างๆ

2. ระดับการเข้าถึง Data Dictionary Views

3. ตัวอย่างคำสั่งคิวรี่ Data Dictionary ที่ออกสอบบ่อย

-- 1. ดูรายชื่อตารางทั้งหมดที่เราเป็นเจ้าของ
SELECT table_name, num_rows FROM user_tables;

-- 2. ดูชื่อและเงื่อนไข Constraints ของตาราง EMPLOYEES
SELECT constraint_name, constraint_type, search_condition
FROM   user_constraints
WHERE  table_name = 'EMPLOYEES';

-- 3. ดูคอร์สคำสั่ง SQL เบื้องหลังของ View
SELECT view_name, text
FROM   user_views
WHERE  view_name = 'EMP_DEPT20_VU';

SELECT constraint_name, constraint_type, search_condition
FROM   user_constraints
WHERE  table_name = 'DEPARTMENTS';

12 Controlling User Access — DCL & Privileges

1. สิทธิ์การใช้งานใน Oracle (Privileges)

DCL (Data Control Language) คือ คำสั่งบริหารจัดการความปลอดภัยของฐานข้อมูล แบ่งเป็น 2 ประเภท:

2. การให้สิทธิ์ (GRANT) และการริบสิทธิ์คืน (REVOKE)

-- 1. มอบสิทธิ์ระดับระบบ (System Privilege) ให้ผู้ใช้ new_user
GRANT CREATE SESSION, CREATE TABLE TO new_user;

-- 2. มอบสิทธิ์ระดับอ็อบเจกต์ (Object Privilege) พร้อม WITH GRANT OPTION
GRANT SELECT, INSERT ON hr.employees TO alice WITH GRANT OPTION;
-- (WITH GRANT OPTION = ยินยอมให้ alice นำสิทธิ์ SELECT/INSERT นี้ไปมอบต่อให้ผู้อื่นได้)

-- 3. ริบสิทธิ์คืน (REVOKE)
REVOKE INSERT ON hr.employees FROM alice;

3. บทบาทและกลุ่มสิทธิ์ (Roles)

Role คือ กลุ่มของสิทธิ์ที่ถูกตั้งชื่อรวมกัน เพื่อให้ง่ายต่อการมอบสิทธิ์แก่ผู้ใช้จำนวนมากพร้อมกัน

-- สร้าง Role และเพิ่มสิทธิ์เข้าไปใน Role
CREATE ROLE app_developer;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO app_developer;

-- มอบ Role ให้ผู้ใช้หลายคนในคำสั่งเดียว
GRANT app_developer TO user1, user2, user3;

13 CTE & Correlated Subqueries (Advanced SQL)

1. Common Table Expressions (CTE - คำสั่ง WITH)

CTE คือ การสร้างตารางชั่วคราวที่มีชื่ออ้างอิงได้ในคิวรี่ ด้วยคำสั่ง WITH ช่วยแยกตรรกะซับซ้อนให้เป็นบล็อก อ่านง่ายขึ้น และสามารถอ้างอิงซ้ำได้หลายครั้งในคิวรี่เดียว

-- 1. ตัวอย่าง CTE พื้นฐาน (หาพนักงานที่เงินเดือนสูงกว่าค่าเฉลี่ยแผนกตนเอง)
WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM   employees
    GROUP BY department_id
)
SELECT e.first_name, e.last_name, e.salary, d.avg_salary
FROM   employees e
JOIN   dept_avg d ON e.department_id = d.department_id
WHERE  e.salary > d.avg_salary;

-- 2. CTE ผสาน Window Function (หา Top 3 เงินเดือนสูงสุดของแต่ละแผนก)
WITH ranked_employees AS (
    SELECT first_name, last_name, department_id, salary,
           ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
    FROM   employees
)
SELECT department_id, first_name, last_name, salary
FROM   ranked_employees
WHERE  rnk <= 3;

2. Recursive CTE (คิวรี่วนซ้ำสร้างผังสายบังคับบัญชา)

ใช้สำหรับดึงข้อมูลโครงสร้างลำดับชั้น (Hierarchy Structure) เช่น ผังองค์กร โดยประกอบด้วย 2 ส่วนเชื่อมด้วย UNION ALL:

• 1. Anchor Member: จุดเริ่มต้นบนสุดของลำดับชั้น (เช่น WHERE manager_id IS NULL)
• 2. Recursive Member: คิวรี่ที่ JOIN ตารางเดิมเข้ากับ CTE เพื่อไล่หาลูกน้องในระดับถัดไป

WITH org_chart (employee_id, first_name, last_name, manager_id, emp_level) AS (
    -- Anchor: CEO / President
    SELECT employee_id, first_name, last_name, manager_id, 1
    FROM   employees
    WHERE  manager_id IS NULL
    UNION ALL
    -- Recursive: ลูกน้องระดับถัดไป
    SELECT e.employee_id, e.first_name, e.last_name, e.manager_id, oc.emp_level + 1
    FROM   employees e
    JOIN   org_chart oc ON e.manager_id = oc.employee_id
)
SELECT LPAD(' ', (emp_level-1)*2) || first_name || ' ' || last_name AS org_tree,
       emp_level
FROM   org_chart
ORDER BY emp_level, last_name;

3. Correlated Subquery & EXISTS / NOT EXISTS

Correlated Subquery: ซับคิวรี่ที่มีการอ้างอิงคอลัมน์จากคิวรี่ภายนอก (ประมวลผลซ้ำทีละแถวของ Outer Query)

-- หาพนักงานที่เป็นผู้จัดการ (มีลูกน้องอย่างน้อย 1 คน) ด้วย EXISTS
SELECT e.employee_id, e.last_name
FROM   employees e
WHERE  EXISTS (SELECT 1 
               FROM   employees m 
               WHERE  m.manager_id = e.employee_id);

-- หาแผนกที่ไม่มีพนักงานอยู่เลยด้วย NOT EXISTS (ปลอดภัยจาก NULL!)
SELECT d.department_id, d.department_name
FROM   departments d
WHERE  NOT EXISTS (SELECT 1 
                   FROM   employees e 
                   WHERE  e.department_id = d.department_id);

4. ตารางเปรียบเทียบ CTE vs. Subquery vs. View

WITH dept_sal AS (
    SELECT department_id, SUM(salary) AS total_sal
    FROM   employees
    GROUP BY department_id
)
SELECT department_id, total_sal
FROM   dept_sal
WHERE  total_sal > 50000;