(SQL)

Manipulating Data

Dr. Asif Sohail

University of the Punjab
Department of Information Technology, FCIT

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 1
 Objectives

• After completing this lesson, you should be

able to do the following:
– Describe each DML statement
– Insert rows into a table
– Update rows in a table
– Delete rows from a table
– Control transactions

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 2
 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.

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 3
 Adding a New Row to a Table

50 DEVELOPMENT DETROIT
New row
“…insert a new row
DEPT
into DEPT table…”
DEPTNO DNAME LOC
------ ---------- --------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS DEPT
30 SALES CHICAGO DEPTNO DNAME LOC
40 OPERATIONS BOSTON ------ ---------- --------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 DEVELOPMENT DETROIT

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 4
 The INSERT Statement

– Add new rows to a table by using the INSERT

statement.

INSERT
INSERT INTO
INTO table
table [(column
[(column [,
[, column...])]
column...])]
VALUES
VALUES (value
(value [,
[, value...]);
value...]);
– Only one row is inserted at a time with this
syntax.

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 5
 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.

SQL> INSERT INTO dept (deptno, dname, loc)

2 VALUES (50, 'DEVELOPMENT', 'DETROIT');
1 row created.

– Enclose character and date values within single

quotation marks.

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 6
 Inserting Rows with Null Values

– Implicit method: Omit the column from the

column list.

SQL> INSERT INTO dept (deptno, dname )

2 VALUES (60, 'MIS');
1 row created.

• Explicit method: Specify the NULL keyword.

SQL> INSERT INTO dept
2 VALUES (70, 'FINANCE', NULL);
1 row created.

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 7
 Inserting Special Values

SQL> INSERT INTO emp (empno, ename, job,

mgr, hiredate, sal, comm, deptno)
VALUES (7196, 'GREEN', 'SALESMAN',
7782, SYSDATE, DEFAULT, NULL,10);
1 row created.

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 8
 Inserting Specific Date Values

– Add a new employee.

SQL> INSERT INTO emp
2 VALUES (2296,'AROMANO','SALESMAN',7782,
3 TO_DATE('FEB 3, 97', 'MON DD, YY'),
4 1300, NULL, 10);
1 row created.

• Verify your addition.

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ------- -------- ---- --------- ---- ---- ------
2296 AROMANO SALESMAN 7782 03-FEB-97 1300 10

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 9
 Copying Rows
from Another Table

– Write your INSERT statement with a subquery.

SQL> INSERT INTO managers(id, name, salary, hiredate)

2 SELECT empno, ename, sal, hiredate
3 FROM emp
4 WHERE job = 'MANAGER';
3 rows created.

– Do not use the VALUES clause.

– Match the number of columns in the INSERT
clause to those in the subquery.

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 10
 INSERT ALL Statement
INSERT ALL
INTO table_name(col1,col2,col3) VALUES(val1,val2,val3)
INTO table_name(col1,col2,col3) VALUES(val4,val5,val6)
INTO table_name(col1,col2,col3) VALUES(val7,val8,val9)
Subquery;

• Each value expression val1, val2, or val3, … must refer

to a column returned by the select list of the subquery.

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 11
 Changing Data in a Table
EMP
EMPNO ENAME JOB ... DEPTNO
“…update a row
7839 KING PRESIDENT 10
7698 BLAKE MANAGER 30 in EMP table…”
7782 CLARK MANAGER 10
7566 JONES MANAGER 20
...

EMP
EMPNO ENAME JOB ... DEPTNO

7839 KING PRESIDENT 10

7698 BLAKE MANAGER 30
7782 CLARK MANAGER 20
10
7566 JONES MANAGER 20
...

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 12
 The UPDATE Statement

– Modify existing rows with the UPDATE statement.

UPDATE
UPDATE table
table
SET
SET column
column == value
value [,
[, column
column == value,
value, ...]
...]
[WHERE
[WHERE condition];
condition];

– Update more than one row at a time, if required.

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 13
 Updating Rows in a Table
– Specific row or rows are modified when you
specify the WHERE clause.

SQL> UPDATE emp

2 SET deptno = 20
3 WHERE empno = 7782;
1 row updated.

– All rows in the table are modified if you omit the

WHERE clause.
SQL>
SQL> UPDATE
UPDATE employee
employee
22 SET
SET deptno
deptno == 20;
20;
14
14 rows
rows updated.
updated.

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 14
 Updating with
Multiple-Column Subquery

• Update employee 7698’s job and department to

match that of employee 7499.

SQL> UPDATE emp

2 SET (job, deptno) =
3 (SELECT job, deptno
4 FROM emp
5 WHERE empno = 7499)
6 WHERE empno = 7698;
1 row updated.

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 15
 Updating Rows Based
on Another Table

• Use subqueries in UPDATE statements to update rows in

a table based on values from another table.

SQL>
SQL> UPDATE
UPDATE employee
employee
22 SET
SET deptno
deptno == (SELECT
(SELECT deptno
deptno
33 FROM
FROM emp
emp
44 WHERE
WHERE empno
empno == 7788)
7788)
55 WHERE
WHERE job
job == (SELECT
(SELECT job
job
66 FROM
FROM emp
emp
77 WHERE
WHERE empno
empno == 7788);
7788);
22 rows
rows updated.
updated.

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 16
 Updating Rows:
Integrity Constraint Error

ist
t ex
SQL> UPDATE emp n o
SQL> UPDATE emp
es
22 SET
SET deptno
deptno == 55
55 do
33 WHERE
WHERE deptno
deptno == 10;
10; 5 5
r
m be
nu
nt
m e
UPDATE
UPDATE emp
emp art
** ep
ERROR at line 1: •D
ERROR at line 1:
ORA-02291:
ORA-02291: integrity
integrity constraint
constraint (USR.EMP_DEPTNO_FK)
(USR.EMP_DEPTNO_FK)
violated
violated -- parent
parent key
key not
not found
found

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 17
 Inserting or Updating Rows Based
on DEFAULT value

• DEFAULT with INSERT:

INSERT
INSERT INTO
INTO departments
departments
(department_id,
(department_id, department_name,
department_name, manager_id)
manager_id)
VALUES
VALUES (300,
(300, 'Engineering',
'Engineering', DEFAULT);
DEFAULT);

• DEFAULT with UPDATE:

UPDATE
UPDATE departments
departments
SET
SET manager_id
manager_id == DEFAULT
DEFAULT WHERE
WHERE department_id
department_id == 10;
10;

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 18
 Removing a Row from a Table
DEPT
DEPTNO DNAME LOC
------ ---------- --------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS “…delete a row
30 SALES CHICAGO
40 OPERATIONS BOSTON from DEPT table…”
50 DEVELOPMENT DETROIT
60 MIS DEPT
...
DEPTNO DNAME LOC
------ ---------- --------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
60 MIS
...

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 19
 The DELETE Statement

• You can remove existing rows from a table by using the

DELETE statement.

DELETE
DELETE [FROM]
[FROM] table
table
[WHERE
[WHERE condition];
condition];

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 20
 Deleting Rows from a Table

– Specific rows are deleted when you specify the

WHERE clause.

SQL>
SQL> DELETE
DELETE FROM
FROM department
department
22 WHERE
WHERE dname
dname == 'DEVELOPMENT';
'DEVELOPMENT';
11 row
row deleted.
deleted.
– All rows in the table are deleted if you omit the
WHERE clause.

SQL>
SQL> DELETE
DELETE FROM
FROM department;
department;
44 rows
rows deleted.
deleted.

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 21
 Deleting Rows Based
on Another Table

• Use subqueries in DELETE statements to remove rows

from a table based on values from another table.

SQL> DELETE FROM employee

2 WHERE deptno =
3 (SELECT deptno
4 FROM dept
5 WHERE dname ='SALES');
6 rows deleted.

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 22
 Deleting Rows:
Integrity Constraint Error

a row
SQL>
SQL> DELETE
DELETE FROM
FROM dept
dept
e lete k ey
22 WHERE deptno
deptno == 10;10; not d m ary ey
pri ign k
WHERE
can a
• You ntains a fore .
at co d a s
t ab le
DELETE
DELETE FROM
FROM dept
dept th s use ther
a t i an o
**
t h in
ERROR
ERROR at
at line
line 1:
1:
ORA-02292:
ORA-02292: integrity
integrity constraint
constraint (USR.EMP_DEPTNO_FK)
(USR.EMP_DEPTNO_FK)
violated
violated -- child
child record
record found
found

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 23
 Truncating a Table
– The TRUNCATE TABLE statement:
• Removes all rows from a table
• Releases the storage space used by that table
• You can do the same with DELETE statement, which is a
DML statement and hence recoverable, but it is slow.

SQL> TRUNCATE TABLE department;

Table truncated.

– You cannot roll back row removal when using

TRUNCATE.
– Alternatively, you can remove rows by using the
DELETE statement.

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 24
 COMMIT, ROLLBACK and SAVEPOINT
• Statements for transaction management.
• A transaction begins with the first DML statement or by
using an explicit SET TRANSACTION statement and ends
when a COMMIT or ROLLBACK statement is issued.
• Use COMMIT and ROLLBACK SQL statements to
terminate a transaction explicitly.
• The syntax for COMMIT statement is:
– COMMIT [WORK][COMMENT text];
• The optional word WORK is used just to improve
readability.
• The COMMENT is usually employed by distributed
transactions.
– COMMIT COMMENT ‘Employees details have been changed’;
© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 25
 COMMIT, ROLLBACK and SAVEPOINT
• SVAEPOINT gives more control on transaction
management.
• SVAEPOINT marks and saves the current point in a
transaction.
• This allows you to rollback the changes up to that point.
• There can be maximum 5 savepoints in a transaction.
• The syntax for SAVEPOINT statement is:
– SVAEPOINT savepoint-name;
– SVAEPOINT Emp_savepoint;

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 26
 COMMIT, ROLLBACK and SAVEPOINT
• ROLLBACK statement is used to undo the changes made
by the transaction. However, it cant undo the changes that
have been made permanent by COMMIT statement.
• The syntax for ROLLBACK statement is:
– ROLLBACK [WORK][TO savepoint_name];
• Examples:
• ROLLBACK; // it will undo the changes made by the
current transaction (up to the last COMMIT)
• ROLLBACK TO rollback_point;
• // it will undo the changes up to the specific savepoint.

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 27
 COMMIT, ROLLBACK and SAVEPOINT (Example)
Time
Transaction t1
SAVEPOINT s1;
---------- (a)
---------- (b)
t2 SAVEPOINT s2;
---------- (c)
---------- (d)
t3 SAVEPOINT s3;
---------- (e)
----------(f)
t4 ROLLBACK TO s2;
---------- (g)
---------- (h)
t5 COMMIT; // work done since the start of
the transaction plus (g) and (h) will be
saved.

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 28
 Thank you for your attention.

Asif Sohail

Assistant Professor
University of the Punjab
Punjab University College of Information Technology (PUCIT)
Allama Iqbal (Old) Campus, Anarkali
Lahore, Pakistan
Tel: +92-(0)42-111-923-923 Ext. 154
E-mail: [email protected]

© 2009 Punjab University College of Information Technology (PUCIT) September 8, 2009 Slide 29