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
DELETE on a given table.
The target table is the table that is having rows added, changed or removed.
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
that does not include column names of the target table.
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:
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
regions) are specified in the statement.
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;
UPDATE statement is used to change data in a table and includes
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.
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';
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
DELETE statement using an
IN clause in the case of complex queries.
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' );
DELETE statement can be used to remove all
of the rows from a table, the
TRUNCATE TABLE statement is
often preferable for this purpose.
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
COMMIT statement or rolled back (undone) using a
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
-- insert a new employee: INSERT INTO employees (employee_id,last_name, hire_date, email, job_id) VALUES (2019, 'Thomas', sysdate, 'email@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, 'firstname.lastname@example.org','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:
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:
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
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.
MERGE statement can be used to combine several DML operations into a single statement.
--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;
-- --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;