facebook google plus 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.

SQL Data Manipulation Language is also known as DML. It 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.

INSERT Statement Categories

Code Sample:

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

This is an example of a simple single table INSERT statement that does not include column names of the target table. Although this is a convenient shorthand, it is considered a "best practice" for SQL in production settings 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 later support attention.

Code Sample:

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

This single table INSERT includes a column name specification.

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;

This 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.

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.

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';

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

The DELETE Statement

  • A DELETE statement is used to remove data from a table.
  • A DELETE statement can be used to remove all of the rows from a table, but the TRUNCATE table statement is often preferable for this purpose.

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'
);

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.

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. 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.

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.

Complex Table References

  • Simple DML statements will require basic criteria to be specified in the WHERE clause.
  • More complex processing can include updates that use correlated subqueries or the use of 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.

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 the use of 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
SELECT * FROM regions;

CREATE TABLE new_regions AS 
SELECT 
	3 AS id , 
	'ASIA' AS name 
FROM dual;

INSERT INTO new_regions(id, name) 
VALUES(9, 'MARS');

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
WHEN NOT MATCHED THEN INSERT (r.region_id, r.region_name) VALUES (n.id, n.name);

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;

Start by examining the contents of the regions table. A new table is created called new regions and an additional record is inserted into it. The merge statement is used to merge the data in the new_regions table into the regions table. If a match is found, the region name is updated. If no match is found, a new record was inserted. The remaining statements reset the schema to its original state.

Data Manipulation Language (DML)

Duration: 10 to 15 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;