Chapter 2:
Data Manipulating Language
Objectives
After completing this lesson, you should be able to do the following:
Describe each data manipulation language (DML) –
statement
Insert rows into a table –
Update rows in a table –
Delete rows from a table –
Control transactions –
Data Manipulation Language
A DML statement is executed when you: –
Add new rows to a table ■
Modify existing rows in a table ■
Remove existing rows from a table ■
A transaction consists of a collection of DML statements –
that form a logical unit of work.
Adding a New Row to a Table
New
DEPARTMENTS row
Insert new row
into the
DEPARTMENTS table
INSERT Statement Syntax
Add new rows to a table by using the INSERT statement: –
INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);
With this syntax, only one row is inserted at a time. –
Inserting New Rows
Insert a new row containing values for each column. –
List values in the default order of the columns in the –
table.
Optionally, list the columns in the INSERT clause. –
INSERT INTO departments(department_id,
department_name, manager_id, location_id)
VALUES (70, 'Public Relations', 100, 1700);
Enclose character and date values in single quotation –
1 row created.
marks.
Inserting Rows with Null Values
Implicit method: Omit the column from the –
column list.
INSERT INTO departments (department_id,
department_name )
VALUES (30, 'Purchasing');
1 row created.
• Explicit method: Specify the NULL keyword in the
VALUES clause.
INSERT INTO departments
VALUES (100, 'Finance', NULL, NULL);
1 row created.
Inserting Special Values
The SYSDATE function records the current date and time.
INSERT INTO employees (employee_id,
first_name, last_name,
email, phone_number,
hire_date, job_id, salary,
commission_pct, manager_id,
department_id)
VALUES (113,
'Louis', 'Popp',
'LPOPP', '515.124.4567',
SYSDATE, 'AC_ACCOUNT', 6900,
NULL, 205, 100);
1 row created.
Inserting Specific Date Values
Add a new employee. –
INSERT INTO employees
VALUES (114,
'Den', 'Raphealy',
'DRAPHEAL', '515.127.4561',
TO_DATE('FEB 3, 1999', 'MON DD, YYYY'),
'AC_ACCOUNT', 11000, NULL, 100, 30);
1 row created.
Verify your addition. –
Creating a Script
Use : substitution in a SQL statement to prompt for values. –
is a placeholder for the variable value.: –
INSERT INTO departments
(department_id, department_name, location_id)
VALUES (:department_id, :department_name,:location);
1 row created.
Copying Rows
from Another Table
Write your INSERT statement with a subquery: –
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';
4 rows created.
Do not use the VALUES clause. –
Match the number of columns in the INSERT clause to –
those in the subquery.
Changing Data in a Table
EMPLOYEES
Update rows in the EMPLOYEES table:
UPDATE Statement Syntax
Modify existing rows with the UPDATE statement: –
UPDATE table
SET column = value [, column = value, ...]
[WHERE condition];
Update more than one row at a time (if required). –
Updating Rows in a Table
Specific row or rows are modified if you specify the WHERE –
clause:
UPDATE employees
SET department_id = 70
WHERE employee_id = 113;
1 row updated.
All rows in the table are modified if you omit the WHERE –
clause:
UPDATE copy_emp
SET department_id = 110;
22 rows updated.
Updating Two Columns with a Subquery
Update employee 114’s job and salary to match that of employee 205.
UPDATE employees
SET job_id = (SELECT job_id
FROM employees
WHERE employee_id = 205),
salary = (SELECT salary
FROM employees
WHERE employee_id = 205)
WHERE employee_id = 114;
1 row updated.
Updating Rows Based
on Another Table
Use subqueries in UPDATE statements to update rows in a table based
on values from another table:
UPDATE copy_emp
SET department_id = (SELECT department_id
FROM employees
WHERE employee_id = 100)
WHERE job_id = (SELECT job_id
FROM employees
WHERE employee_id = 200);
1 row updated.
Removing a Row from a Table
DEPARTMENTS
Delete a row from the DEPARTMENTS table:
DELETE Statement
You can remove existing rows from a table by using the DELETE
statement:
DELETE [FROM] table
[WHERE condition];
Deleting Rows from a Table
Specific rows are deleted if you specify the WHERE clause: –
DELETE FROM departments
WHERE department_name = 'Finance';
1 row deleted.
All rows in the table are deleted if you omit the WHERE –
clause:
DELETE FROM copy_emp;
22 rows deleted.
Deleting Rows Based
on Another Table
Use subqueries in DELETE statements to remove rows from a table
based on values from another table:
DELETE FROM employees
WHERE department_id =
(SELECT department_id
FROM departments
WHERE department_name
LIKE '%Public%');
1 row deleted.
Database Transactions
A database transaction consists of one of the following:
DML statements that constitute one consistent change to –
the data
One DDL statement –
One data control language (DCL) statement –
Database Transactions
Begin when the first DML SQL statement is executed –
End with one of the following events: –
A COMMIT or ROLLBACK statement is issued. ■
A DDL or DCL statement executes (automatic commit). ■
The user exits iSQL*Plus. ■
The system crashes. ■
Advantages of COMMIT
and ROLLBACK Statements
With COMMIT and ROLLBACK statements, you can:
Ensure data consistency –
Preview data changes before making changes permanent –
Group logically related operations –
Controlling Transactions
Time COMMIT
Transaction
DELETE
SAVEPOINT A
INSERT
UPDATE
SAVEPOINT B
INSERT
ROLLBACK ROLLBACK ROLLBACK
to SAVEPOINT B to SAVEPOINT A
Rolling Back Changes to a Marker
Create a marker in a current transaction by using the –
SAVEPOINT statement.
Roll back to that marker by using the ROLLBACK TO –
SAVEPOINT statement.
UPDATE...
SAVEPOINT update_done;
Savepoint created.
INSERT...
ROLLBACK TO update_done;
Rollback complete.
Implicit Transaction Processing
An automatic commit occurs under the following –
circumstances:
DDL statement is issued ■
DCL statement is issued ■
Normal exit from iSQL*Plus, without explicitly issuing ■
COMMIT or ROLLBACK statements
An automatic rollback occurs under an abnormal –
termination of iSQL*Plus or a system failure.