facebook twitter
Webucator's Free Advanced Oracle SQL Queries Tutorial

Lesson: SQL Data Manipulation Language

Welcome to our free Advanced Oracle SQL Queries tutorial. This tutorial is based on Webucator's Advanced Oracle SQL Queries course.

The Data Manipulation Language (DML) is used to manipulate data in the database.

The ability to query data is a different kind of responsibility than the ability to change data. Because of this, a database user must have been granted appropriate security privileges to perform an INSERT, UPDATE, or DELETE on a given table.

The target table is the table that is having rows added, changed or removed.

Lesson Goals

  • Learn about the INSERT statement.
  • Learn about the UPDATE statement.
  • Learn about the DELETE statement.
  • Learn about transactions.
  • Learn about complex table references.
  • Learn about the MERGE statement.

The INSERT Statement

A database user with appropriate privileges can use the INSERT statement to add data to a target table. There are a number of variations of the INSERT statement, all of which designate the target table's name. In many cases, the target table's column names are specified as well.

The following example shows a simple single table INSERT statement that does not include column names of the target table.

Code Sample:

SQL-Data-Manipulation-Language/Demos/insert.sql
INSERT INTO regions VALUES (5, 'Antarctica');

Although this is a convenient shorthand, it is considered a "best practice" to explicitly specify the column names. This prevents problems from occuring if table definitions change and makes the intentions of the SQL programmer apparent if the statement requires updating later.

A better, more explicit query that includes a column name specification is shown below:

Code Sample:

SQL-Data-Manipulation-Language/Demos/insert_cols.sql
INSERT INTO regions (region_id, region_name) VALUES (6, 'Australia');

This following multitable INSERT adds a new country (South Africa) in a new region (Africa) to the database. All of the information that needs to be inserted is included in a subquery (that uses the dual table). Two different tables (countries and regions) are specified in the statement.

Code Sample:

SQL-Data-Manipulation-Language/Demos/insert_all_select.sql
INSERT ALL
INTO regions (region_id, region_name) 
		VALUES (reg_id, reg_name)
INTO countries (country_id, country_name, region_id) 
		VALUES (ctry_id, ctry_name, reg_id)
SELECT 
	7 AS reg_id, 
	'Africa' AS reg_name, 
	'ZA' AS ctry_id,
	'South Africa' AS ctry_name 
FROM dual;

The UPDATE Statement

An UPDATE statement is used to change data in a table and includes a SET clause to specify the columns to be updated.

The following update statement changes the region name to upper case for regions that have three A's in them.

Code Sample:

SQL-Data-Manipulation-Language/Demos/update.sql
SELECT * 
FROM regions 
WHERE region_name LIKE 'A%a%a';

UPDATE regions 
SET region_name = UPPER(region_name) 
WHERE region_name LIKE 'A%a%a';

The DELETE Statement

A DELETE statement is used to remove data from a table.

Take a look at the following query. Notice how the criteria used in the initial query can be used to construct a DELETE statement using an IN clause in the case of complex queries.

Code Sample:

SQL-Data-Manipulation-Language/Demos/delete.sql
SELECT * 
FROM regions 
WHERE region_name LIKE 'A%a%a';

--Use of the SELECT statement to limit the rows deleted...
DELETE FROM regions 
WHERE region_id IN (
	SELECT region_id 
	FROM regions 
	WHERE region_name LIKE 'A%a%a'
);

Although the DELETE statement can be used to remove all of the rows from a table, the TRUNCATE TABLE statement is often preferable for this purpose.

Transactions

A transaction is a logical unit of work performed by a Data Manipulation Language (DML) operation. It contains one or more SQL statements comprising an atomic unit. A transaction's effects can be committed (applied) using a COMMIT statement or rolled back (undone) using a ROLLBACK statement. Some relational database management systems (RDBMS) automatically commit changes when a DML statement is run in client software. In general, Oracle requires a user to explicitly commit or rollback DML changes will depend on the AUTOCOMMIT setting. An explicit commit occurs when the COMMIT keyword is used. An implicit commit occurs upon normal termination of an application or when a DDL statement is executed.

The following demo shows how to use ROLLBACK and COMMIT:

-- insert a new employee:
INSERT INTO employees
(employee_id,last_name, hire_date, email, job_id)
VALUES (2019, 'Thomas', sysdate, 'thomas@example.com','AC_MGR');

-- select the new employee:
SELECT last_name
FROM employees
WHERE employee_id=2019;

-- undo the insert:
ROLLBACK;

-- try to select the employee (employee will not be found!):
SELECT last_name
FROM employees
WHERE employee_id=2019;

-- now, insert the employee again:
INSERT INTO employees
(employee_id,last_name, hire_date, email, job_id)
VALUES (2019, 'Thomas', sysdate, 'thomas@example.com','AC_MGR');

-- commit the change to make the insert permanent:
COMMIT;

-- select the employee:
SELECT last_name
FROM employees
WHERE employee_id=2019;

-- clean up by deleting the employee:
DELETE FROM employees
WHERE employee_id=2019;

-- make the change permanent:
COMMIT;

-- try to select the employee (employee will not be found!):
SELECT last_name
FROM employees
WHERE employee_id=2019;

It is a good idea to commit changes whenever you have completed any DML. If you do not, there is the potential for database locks to be held which will cause other database sessions to "hang" while waiting for the transaction to complete.

In SQL Developer, you can set your preference for autocommitting by:

  1. Selecting Preferences from the Tools menu.
  2. Selecting Database > Advanced.
  3. Checking or unchecking Autocommit.

Autocommit setting

If you do not have autocommit turned on, when you close SQL Developer, if you have any uncommitted changes you will be prompted to commit them or roll them back: Uncommitted Changes

Complex Table References

DML statements that specify criteria in the WHERE clause are simple to construct. However, more complex processing is possible, including updates that use correlated subqueries or an IN clause.

To determine the effects of DML statements that change existing data, you can first construct a query that returns the rows that you intend to act upon. You can also verify your changes and roll them back rather than committing them if the changes made did not meet your intentions.

The MERGE Statement

The MERGE statement can be used to combine several DML operations into a single statement.

Code Sample:

SQL-Data-Manipulation-Language/Demos/merge.sql
--Start by examining the contents of the regions table.
SELECT * FROM regions;

--Create a new_regions table and insert one row
CREATE TABLE new_regions AS 
SELECT 
	3 AS id , 
	'ASIA' AS name 
FROM dual;

--Insert another row into new_regions
INSERT INTO new_regions(id, name) 
VALUES(9, 'MARS');

--Merge rows from new_regions into regions
MERGE INTO regions r 
USING (
	SELECT * 
	FROM new_regions
) n 
ON (r.REGION_ID = n.id)
WHEN MATCHED THEN UPDATE SET r.region_name = n.name -- if id exists, update
WHEN NOT MATCHED THEN INSERT (r.region_id, r.region_name) VALUES (n.id, n.name); -- if id new, insert

SELECT * 
FROM regions;

DELETE FROM regions 
WHERE region_id = 9;

UPDATE regions SET region_name=initcap(region_name) 
WHERE region_name='ASIA';

DROP TABLE new_regions;

SELECT * 
FROM regions;

Data Manipulation Language (DML)

Duration: 15 to 25 minutes.
  1. Add a new location with a primary key identifier of 999, an address of 600 Forbes Avenue in Pittsburgh PA (in the US) with a zip code of 15282.
  2. Change this location address to include "Ave" rather than "Avenue"
  3. Delete this address based upon its primary key.
  4. Rollback the changes.

Solution:

SQL-Data-Manipulation-Language/Solutions/dml_solutions.sql
--
--Columns omitted for brevity...
--
INSERT INTO locations VALUES (999, '600 Forbes Avenue', '15282', 
	'Pittsburgh', 'PA','US');

UPDATE locations SET street_address = replace(street_address,
	'Avenue', 'Ave') 
WHERE location_id = 999;

DELETE FROM locations 
WHERE location_id=999;

ROLLBACK;