Intermediate SQL

Database schema before class: database_day5_end_of_class.sql
Slides from class
SQL script from class

Database notes

View

A virtual table based on a SELECT command that is saved as an object in the database. The view does not store a separate copy of the data, instead if references data in other tables.

Transaction

A sequence of database requests to access the database. A transaction is a logical unit of work. It must complete entirely, or must abort. No intermediate states are allowed.

COMMIT

A SQL command that permanently saves data to the database. By default in MySQL auto commit is enabled, so each SQL command commits after it executes.

ROLLBACK

A SQL command that restored the database to the condition that existed after the last commit. By default in MySQL auto commit is enabled, so each SQL command commits after it executes. Turn off auto commit with SET AUTOCOMMIT = 0; Turn on auto commit with SET AUTOCOMMIT = 1;

AUTO INCREMENT

A characteristic of an attribute used to uniquely identify rows. Normally starts at 1 and increments by 1 each time a row in inserted.

NOT NULL

A constraint on an attribute to ensure the value cannot be null.

UNIQUE

A constraint on an attribute to ensure no two rows share the value.

CHECK

A constraint on an attribute to ensure the value is appropriate. Example: CHECK (Salary > 0)