SQL = The Ol' Complete Package

Transaction

A discrete series of actions that must be either completely processed, or not processed at all.

• Example: When you want to delete two rows in a database, and you delete the first row but there is an error preventing you from deleting the second row

  • You can return the database to the state before you deleted the first row, referred to as a rollback (undo)

SET AUTOCOMMIT=0;

Stops MySQL from saving each statement permanently the moment it runs; turns autocommit off for the whole session, where after COMMIT / ROLLBACK, the next transaction starts automatically (based on wanting manual control for everything)

START TRANSACTION;

Stops MySQL from saving each statement permanently the moment it runs; everything it holds until you COMMIT (save) or ROLLBACK (undo) --> if you do either, each statement is saved immediately again

SELECT TITLE from BOOKS;

If I want to select a single column from a table, specifically Title from Books, what should I input?

SELECT TITLE, PUBLISHER from BOOKS;

If I want to select several columns from a table, specifically Title from Publisher, what should I input?

All columns, returns every column in the table (in the order they were defined)

SELECT * FROM AUTHORS: What does the asterisk represent?

AUTOCOMMIT

Mode of operation in database connection, each SQL statement submitted in AUTOCOMMIT mode will be executed in its own transaction that is implicitly committed

Converts each Title in Books to uppercase

Example: SELECT UPPER(TITLE) AS TITLE FROM BOOKS;

Question: What does this do?

Alias

Used to temporarily assign another name to a table or column for the duration of the selected SELECT query —> this doesn't actually rename the column or table, but is useful when either tables or columns have long or complex names

EXAMPLE: SELECT UPPER(TITLE) AS BOOK_NAME FROM BOOKS;

• Column heading appears normal, without it, the MySQL Workbench would label the column with the expression itself — in this case, that would be UPPER(TITLE)

Filters out unwanted rows from your result set

What does the WHERE clause do in a SELECT statement?

Example: SELECT * FROM AUTHORS WHERE LAST_NAME=’DOE’

Question: SQL Select, when a value is TRUE or FALSE is expected,

LIKE

(SQL SELECT Statement) Comparison similarity order, on strings in which '%' represents zero or more characters and '_' represents a single character

SELECT * FROM AUTHORS;

SELECT * FROM AUTHORS WHERE author_name (????) ‘M&’

Matches any character string that ends with "Now"

Question: When using LIKE in a SELECT Statement, what does this do? = %Now

SQL

A computer language designed for managing data in relational database management systems (RDBMS)

• Command based statements, which always end in a semicolon

Data Definition Language / DDL

Statements that create and modify database objects, which involve the data model and structure of the database, and not the data stored within, as with DML and DQL

• Includes CREATE, ALTER, RENAME, and DROP statements

Data Manipulation Language / DML

Statements that modify data stored in database objects (examples: tables)

• Includes INSERT, UPDATE, and DELETE

Data Query Language / DQL

Statements that query the database, but do not alter any data or database objects

• Includes the SELECT statement

Data Control Language / DCL

Statements that manage the privileges that database users have regarding database objects

• Includes the GRANT and REVOKE statements, which will not be mentioned in class