Basic Data Manipulation - Changing Data

1
Basic Data Manipulation - Changing Data

• Objectives
• To learn how to add/change/remove data
• Contents
• the INSERT Statement
• Practical 4-1
• the UPDATE Statement
• the DELETE Statement
• Practical 4-2
• Transaction control

2
The INSERT Statement
INSERT INTO table (column list) VALUES
(value list)
Example INSERT INTO dept (dept_no,
dept_name) VALUES (13, Education)
Which one of these two is valid? INSERT INTO
dept VALUES (12, Engineering, Simon Moore,
218000) INSERT INTO dept VALUES (12, Simon
Moore, Engineering, 218000)

• Using this Values syntax, supply either a
  value, an expression that derives a value, or the
  reserved words NULL or DEFAULT

3
INSERT statement using a SELECT

• Multirow values can be derived via a
  SELECT statement
• For this code to work any other mandatory
  columns in
• dept must have defaults defined.

INSERT INTO dept (dept_no, dept_name) SELECT
no, name FROM newdepts WHERE agreed Y
4
Ch10Practical1 - INSERT statement

• Follow instructions in the practical to add
  yourself as a new row in the contact table

5
The UPDATE Statement
UPDATE salesperson SET sales_target
400000 WHERE dept_no 3
UPDATE salesperson SET sales_target
sales_target 1.2, notes Has had 20 pay
rise WHERE dept_no 3
UPDATE salesperson SET sales_target 400000
6
The DELETE Statement
DELETE FROM salesperson WHERE sales_target lt 1000
DELETE FROM dept
7
Ch10Practical 2 - Updates and Deletes

• Follow instructions in the practical to perform
  Updates and Deletes

8
A Typical Transaction
Deposit a/c
Cheque a/c
1250
100
(Consistent State) Starting balances
Steps
Add 750
(Consistent State) Ending balances

• BEGIN TRAN, followed by either COMMIT Work or
  ROLLBACK Work
• Begins a transaction, completes a transaction
  either making permanent or undoing changes

9
SUMMARY

• INSERT is used to enter new rows (1 or many)
• It is a row level operation
• UPDATE is used to change column entries
• It is a column level operation, across many rows
• DELETE is used to remove rows of data
• It is a row level operation
• SQL is a set based language
• This is good news because
• A quick command can do a lot of work
• You tell the system what to do not how to do it
• This is bad news because
• A quick command can do a lot of work!
• It is possible to alter / delete more than you
  intended to
• Use COMMIT and ROLLBACK to control sets of changes

10
(No Transcript)