Manipulating Data I

Oracle 12c: Introduction to SQL

Slide 1

Manipulating Data I

______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________

©John Mullins Page 2

Slide 2

Questions?

Questions?
Use the Question/Comment Box

Let’s get started!

2 ©2010 John Mullins

______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________

©John Mullins Page 3

Slide 3

Module Topics
Write INSERT statements to add rows to a table

Insert Special Values

Copy Rows from Another Table

Update Rows in a Table

Use DELETE statements to remove rows from a table

Delete Rows Based on Another Table

The COMMIT and ROLLBACK statements

Using the SAVEPOINT statement

3 ©2010 John Mullins

______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________

©John Mullins Page 4

Slide 4

INSERT, UPDATE, DELETE

Can be used as
single SQL
Used to add
statements, in
data, change
PL/SQL
data, and
programs, in Java
removed data
programs, or in
3GL programs

4 ©2010 John Mullins

______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________

©John Mullins Page 5

Slide 5

INSERT

Syntax

INSERT INTO
tablename [(column list)]
VALUES (values list);

INSERT INTO
tablename [(column list)]
select statement;

5 ©2010 John Mullins

______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________

©John Mullins Page 6

Slide 6

UPDATE

Syntax

UPDATE tablename
SET columnname = value
[,columnname = value,…]
[where clause];

6 ©2010 John Mullins

______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________

©John Mullins Page 7

Slide 7

DELETE

Syntax
DELETE FROM
tablename
[where clause];

7 ©2010 John Mullins

______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________

©John Mullins Page 8

Slide 8

COMMIT

Makes
changes
visible to
other
users
Makes
changes
permanent

8 ©2010 John Mullins

______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________

©John Mullins Page 9

Slide 9

ROLLBACK

 Reverses the results of an

INSERT, UPDATE, or DELETE
operation

9 ©2010 John Mullins

______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________

©John Mullins Page 10

Slide 10

SAVEPOINT

Creates a marker or
label

Used with
ROLLBACK

Cannot be used
with COMMIT

10 ©2010 John Mullins

______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________

©John Mullins Page 11