Chapter 3: Manipulating Data

Describe SAVEPOINT.

SAVEPOINT savepointName ; - on call, it rolls database back to the point at which savepoint was executed - all savepoints must include a name - newest SP name overrides in the event of duplicate names - once commit occurs,SP's are erased from memory (and any reference produces error)

Describe the effect of an update attempt that violates a constraint.

The described update attempt will reject the entire update at execution.

What will happen when data that is inserted violates a constraint?

There will be a runtime error, and the statement will not execute (no data will be inserted)

Describe TCL, with examples (3).

Transaction Control Language - statements that save or cancel changes made to the database ---- COMMIT, ROLLBACK, SAVEPOINT

Syntax: UPDATE table

UPDATE tableName SET col1, ..., coln = value WHERE columnName = value ;

Describe the UPDATE statement.

UPDATE: a DML statement that (1) is used to modify existing data (2) operates on one table or view at a time (3) is used to modify one or more columns of data (4) can be used to change all or selected rows in a table or view

implicit conversion

the process by which incorrectly submitted data is converted to a compatible datatype, i.e., '12/12/1992', a string, will be converted to DATE

Describe COMMIT.

COMMIT ; - a TCL statement that saves all changes from the beginning of transaction or most recent commit

What system actions are executed on INSERT (4)?

1) Existence and validity of table and of columns 2) Expressions are evaluated 3) Value datatypes are compared to column datatypes 4) Constraints are applied

What are the three types of SQL statements?

1) data definition language 2) data manipulation language 3) transaction control language

Will column names included in SET clause be changed?

No, column names included in the SET clause of an UPDATE statement will not be updated.

Describe the interaction between commits and other database users.

Other database users cannot see the effects of changes that have not been committed to the database.

Describe ROLLBACK (4).

ROLLBACK ; - undoes changes back to the last session commit - only undoes changes executed by user executing statement - implicit rollback - occurs when program abnormally terminates - ignores savepoints unless you use the following syntax: ---- ROLLBACK [WORK] TO savepointName ;

explicit vs. implicit commit

- explicit commit: occurs whenever a commit is executed - implicit commit: occurs immediately before and after DDL execution and on a normal exit from Oracle tools

What is the role of the WHERE clause in an UPDATE statement?

- if omitted, all rows will be updated - if included, determines which rows will be updated

Syntax: Delete rows

DELETE [FROM] tableName [WHERE columnName = value] ; ***Will run even if there are no relevant rows to delete

Describe DDL, with examples (10).

Data Definition Language - statements that build database objects ---- CREATE, ALTER, DROP, RESUME, TRUNCATE, GRANT, REVOKE, FLASHBACK, PURGE, COMMENT

Describe DML, with examples (5).

Data Manipulation Language - statements that work with the data in database objects ---- SELECT, INSERT, UPDATE, DELETE, MERGE

True/False: The SET clause of an UPDATE statement must reference all columns in a particular order.

FALSE - Not all columns need to be referenced, and, if they are referenced, the order does not matter.

True/False: Column names must be listed in order in an INSERT statement.

FALSE - as long as column names are enumerated and each value follows the order indicated by listed columns in datatype (and ultimately, meets constraints)

Describe INSERT and its syntax.

INSERT: adds one or more rows to a table or view - Syntax: INSERT INTO tableName (<cols>) < (unneeded if these cols and next match) VALUES (<vals>) ;