Basic Data Manipulation - Changing Data - PowerPoint PPT Presentation

makaio
basic data manipulation changing data n.
Skip this Video
Loading SlideShow in 5 Seconds..
Basic Data Manipulation - Changing Data PowerPoint Presentation
Download Presentation
Basic Data Manipulation - Changing Data

play fullscreen
1 / 10
Download Presentation
Basic Data Manipulation - Changing Data
108 Views
Download Presentation

Basic Data Manipulation - Changing Data

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  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) Using this ‘Values’ syntax, supply either a value, an expression that derives a value, or the reserved words NULL or DEFAULT 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)

  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 < 1000 DELETE FROM dept

  7. Ch10Practical 2 - Updates and Deletes • Follow instructions in the practical to perform Updates and Deletes

  8. 500 100 Remove 750 500 100 Update Audit Trail (Debit) 500 100 Validate Account Details 500 100 Update Audit Trail (Credit) 500 850 500 850 A Typical Transaction Deposit a/c Cheque a/c BEGIN TRAN, followed by either COMMIT [Work] or ROLLBACK [Work] Begins a transaction, completes a transaction either making permanent or undoing changes 1250 100 (Consistent State) Starting balances: Steps Add 750 (Consistent State) Ending balances:

  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