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