1 / 10

Basic Data Manipulation - Changing Data

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. The INSERT Statement. INSERT INTO table [(column list)]

makaio
Download Presentation

Basic Data Manipulation - Changing Data

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related