Insert, Update, Delete, RI, and Constraints

Question 1

INSERT

Answer 1

Adds a new row of data to a table
Can be shortened to insert every column:
INSERT INTO table_name VALUES (value1, value2, … value_n);

Question 2

UPDATE

Answer 2

Updates the value of columns on an existing row of data for the specific rows.
UPDATE table_name
SET column = value
WHERE column = value;

Can update multiple columns in a since update statement.
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE column = value;

Question 3

DELETE

Answer 3

Deletes row(s) of data from a table.
DELETE FROM table_name
WHERE column=value;

Question 4

Referential Integrity

Answer 4

Referential Integrity is a property of the data stating whether or not references within it are valid. For example, to use a foreign key on a table, the value must exist on the primary table.

Question 5

Constraints

Answer 5

defines properties that the column data must comply with. It sets a rule that must be obeyed to maintain the integrity of the data

Question 6

Constraints examples

Answer 6

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT

Question 7

NOT NULL constraint

Answer 7

The column cannot contain a null value

Question 8

UNIQUE constraint

Answer 8

The column can only contain unique values

Question 9

PRIMARY KEY constraint

Answer 9

Enforces NOT NULL and UNIQUE. Allows Foreign Key relationships to be established.

Question 10

FOREIGN KEY constraint

Answer 10

Only allows values where the value exists on the related table. Does not allow the related value to be removed from the related table as long as the foreign key value is in use.

Question 11

CHECK constraint

Answer 11

Specifies a list of acceptable values that can be added into a column

Question 12

DEFAULT constraint

Answer 12

Provides a default value for a column, if no value is provided.

Question 13

Transactions

Answer 13

a single unit of work made up of multiple SQL statements that must all succeed or fail as one

Question 14

When a transaction is successful it is

Answer 14

committed and the data is saved in the new state

Question 15

When a transaction fails it is

Answer 15

rolled back and all the data is left in the original state.

Question 16

The ACID Test

Answer 16

is used to determine whether a series of actions should be a transaction:  Must the actions occur as all or none
Atomicity
Consistency
Isolation
Durability

Question 17

Atomicity

Answer 17

Must the actions occur as all or none

Question 18

Consistency

Answer 18

Once the series of actions is complete is the data left in a consistent state, meaning any rules that pass before the transaction still pass after the transaction

Question 19

Isolation

Answer 19

Does the final result of the transaction leave the data in the same state as they would have if executed serially

Question 20

Durability

Answer 20

Once the transaction has been committed, will it remain so even after an error, system crash, or power loss