Oracle SQL Tutorial day 12
Oracle SQL provides powerful Data Manipulation Language (DML) statements to work with data stored in relational tables. The core DML operations include INSERT, UPDATE, DELETE, and MERGE. These operations allow you to add, modify, remove, and conditionally update/insert data. In this blog, we will use Oracle's HR sample schema to demonstrate practical examples of each DML statement with two example queries and brief explanations.
1. INSERT Statement
The INSERT statement adds new rows into a table. You can insert data into all columns or only specific ones. It supports inserting values directly or from another SELECT query.
Example 1:
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary)
VALUES (300, 'John', 'Doe', 'JDOE', SYSDATE, 'IT_PROG', 6000);
Description:
This query adds a new employee John Doe with the job ID 'IT_PROG' into the employees table. All required columns are provided with valid values. The hire date is set to the current system date.
Example 2:
INSERT INTO departments (department_id, department_name, manager_id, location_id)
SELECT 300, 'Innovation', manager_id, location_id FROM departments WHERE department_id = 90;
Description:
This query inserts a new department named 'Innovation' using the manager and location details from department 90. It demonstrates how to use a subquery in an INSERT statement.
2. UPDATE Statement
The UPDATE statement modifies existing rows in a table. It can update one or multiple rows depending on the WHERE condition. You can update one or more columns in a single statement.
Example 1:
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 50;
Description:
This query increases the salary of all employees in department 50 by 10%. It multiplies the existing salary by 1.1. This is useful for applying company-wide raises.
Example 2:
UPDATE departments
SET location_id = 1700
WHERE department_name = 'Marketing';
Description:
This statement updates the location of the Marketing department to location_id 1700. Only departments with the name 'Marketing' are affected.
3. DELETE Statement
The DELETE statement removes rows from a table. Use caution with DELETE as it permanently removes data unless a rollback is performed. Always use a WHERE clause to avoid deleting all rows.
Example 1:
DELETE FROM employees
WHERE employee_id = 300;
Description:
This query deletes the employee with ID 300 from the employees table. This is typically used for removing terminated or incorrect records.
Example 2:
DELETE FROM job_history
WHERE end_date < TO_DATE('01-JAN-2010', 'DD-MON-YYYY');
Description:
This query deletes all job history records that ended before January 1, 2010. It helps clean up old, irrelevant data from the system.
4. MERGE Statement
The MERGE statement performs a conditional INSERT or UPDATE operation. It is also known as UPSERT. You match a target table with a source and decide what action to take (insert/update).
Example 1:
MERGE INTO employees e
USING (SELECT 100 AS employee_id, 7000 AS new_salary FROM dual) s
ON (e.employee_id = s.employee_id)
WHEN MATCHED THEN
UPDATE SET e.salary = s.new_salary
WHEN NOT MATCHED THEN
INSERT (employee_id, salary) VALUES (s.employee_id, s.new_salary);
Description:
This query updates the salary of employee 100 if they exist, or inserts a new employee with ID 100 and salary 7000 if not. This is a typical scenario in data sync operations.
Example 2:
MERGE INTO departments d
USING (SELECT 999 AS department_id, 'R&D' AS department_name FROM dual) src
ON (d.department_id = src.department_id)
WHEN MATCHED THEN
UPDATE SET d.department_name = src.department_name
WHEN NOT MATCHED THEN
INSERT (department_id, department_name) VALUES (src.department_id, src.department_name);
Description:
This query ensures that the department with ID 999 is either updated or created. If the department exists, its name is updated to 'R&D'. If it doesn̢۪t exist, a new department is added.
Commit
commit needs to use to save changes perminatly after performing any DML operation. Simply write commit; after issue of any DML command.
It is session based if user logout without commit changes will revert back.
Any DDL or DCL performed after DML then auto commit applied.
Rollback
To revert changes we can use rollback. Whatever DML changes performed will be revert back after issue of rollback command.
If session closed or logout without commit then changes will be rollback automatically.
Whenever any DML operation performed oracle database return a success or failure message like below
- 1 row inserted
- 1 row updated
- 2 rows deleted
- Ora-xxxx integrity constraint xxx
Conclusion
Oracle SQLDML (data manipulation language) operations are essential for managing the data lifecycle within a database.
Whether you're adding new employees, updating department information, removing old records, or performing UPSERT logic, these statements are your go-to tools. The HR schema provides an excellent playground for practicing these commands in real-world scenarios. Make sure to always test your DML queries in a safe environment and use transactions to ensure data integrity.
0 Comments