D426 Chapter 2A — SQL, Tables & Row Changes

WGU D426 zyBooks Ch 2, sections 2.1–2.6 (2.5 Null values transcribed from the live zyBook because it didn't print). Key terms verbatim plus concept, comparison and scenario cards. Not official WGU or zyBooks material.

Structured Query Language (SQL)

Structured Query Language (SQL) is the standard query language of relational databases. SQL is commonly pronounced 'seekwəl', but the preferred pronunciation is 'S-Q-L'. (§2.1)

Sublanguages

SQL statements are grouped into five sublanguages, described in the table below. A sublanguage is an informal grouping of statements. | Table 2.1.1: DDL defines database structure (ex: CREATE TABLE). | DQL retrieves data (ex: SELECT). | DML inserts, updates, and deletes data (ex: INSERT). | DTL manages transactions (ex: START TRANSACTION, COMMIT). | DCL specifies user access to data (ex: CREATE USER, GRANT). (§2.1)

Data Definition Language (DDL)

Data Definition Language defines database structure. | Book's example: CREATE TABLE. ALTER TABLE and DROP TABLE also change database structure. (§2.1, §2.3)

Data Query Language (DQL)

Data Query Language retrieves data. | Book's example: a SELECT statement. (§2.1)

Data Manipulation Language (DML)

Data Manipulation Language inserts, updates, and deletes data. | Statements: INSERT, UPDATE, and DELETE (the book's example is an INSERT). (§2.1, §2.6)

Data Transaction Language (DTL)

Data Transaction Language manages transactions. | Book's example: START TRANSACTION; ... COMMIT; (§2.1)

Data Control Language (DCL)

Data Control Language specifies user access to data. Data Control Language is primarily of interest to database administrators and not discussed extensively. | Book's examples: CREATE USER and GRANT statements. (§2.1)

Literal

Explicit value such as a character string or number. String literals are enclosed in either single or double quotes. | Ex: 'Maria', "Hello world!", 129, 80.3. String literals are always case-sensitive. (§2.1)

Keyword

Word with a special meaning for the language processor. Keywords are defined by the database system. | Ex: SELECT, INSERT, INTEGER. (§2.1)

Identifier

Name of a database object, such as a column, table, or database. Identifiers are specified by the programmer. | Ex: City, FirstName, Population. (§2.1)

Expression

Sequence of literals, identifiers, and operations that evaluate to a single value. | Ex: Population > 1000 OR Name = 'Tokyo' (§2.1)

Comment

Text that is ignored by the language processor. Single-line comments begin with -- followed by a space. Multi-line comments are enclosed in /* and */. (§2.1)

Statement

A statement is a complete, executable instruction, ending with a semicolon. A statement consists of one or more clauses. | Line breaks within a statement are ignored, so one statement may span several lines. (§2.1)

Clause

Each clause begins with a keyword, followed by additional language elements. | Good practice: write each clause on a separate line. Ex: SELECT Name / FROM City / WHERE Population > 10000; is one statement with three clauses. (§2.1)

SQL standard

The SQL standard specifies the official syntax and behavior of SQL statements. The standard is published jointly by the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO). | First published in 1986; the latest version (2023) has 16 parts: 11 active, 5 never released or withdrawn. MySQL implements the most important parts, with limited differences and custom extensions. (§2.1)

Part (of the SQL standard)

A part is a formal specification of syntax and behavior. | Parts are not the same as sublanguages, which are informal groupings of statements. Ex: Framework defines concepts and terms; Foundation defines the core language. (§2.1)

Foundation (part of the SQL standard)

All five sublanguages are specified in one part, called Foundation. | Foundation defines the core language, including the five sublanguages. | MySQL implements Foundation, SQL/PSM (procedural statements such as loops and procedures), SQL/Schemata (the information_schema database), and SQL/XML functions (but not the XML data type). (§2.1)

Database system instance

A database system instance is a single executing copy of a database system. | Personal computers usually run just one instance; shared computers, such as those used for cloud services, usually run multiple instances. Each instance usually contains multiple system and user databases. (§2.2)

CREATE DATABASE DatabaseName

CREATE DATABASE DatabaseName creates a new database. (§2.2)

DROP DATABASE DatabaseName

DROP DATABASE DatabaseName deletes a database, including all tables in the database. (§2.2)

USE DatabaseName

USE DatabaseName selects a default database for use in subsequent statements. | Ex: after USE, SHOW TABLES lists that database's tables. (§2.2)

SHOW DATABASES

SHOW DATABASES lists all databases in the database system instance. (§2.2)

SHOW TABLES

SHOW TABLES lists all tables in the default database. The optional clause FROM DatabaseName lists tables in a named database. (§2.2)

SHOW COLUMNS FROM TableName

SHOW COLUMNS FROM TableName lists all columns in the TableName table of the default database. | The result has one row per column, giving the column name, data type, and other characteristics (Field, Type, Null, Key, Default, Extra). (§2.2)

SHOW CREATE TABLE TableName

SHOW CREATE TABLE TableName shows the CREATE TABLE statement for the TableName table of the default database. (§2.2)

Table

A table has a name, a fixed sequence of columns, and a varying set of rows. | All data in a relational database is structured in tables. (§2.3)

Column

A column has a name and a data type. | Duplicate column names are not allowed in the same table, but are allowed in different tables. (§2.3)

Row

A row is an unnamed sequence of values. Each value corresponds to a column and belongs to the column's data type. | A table is a set of rows, so rows may not repeat and are not ordered. (§2.3)

Cell

A cell is a single column of a single row. | Exactly one value per cell: a cell may not contain multiple values. (§2.3)

Empty table

A table must have at least one column but any number of rows. A table without rows is called an empty table. (§2.3)

NULL

Unknown data is represented with a special NULL value. | A column omitted from an INSERT is assigned NULL unless it has a DEFAULT value; if the column has the NOT NULL constraint, the insert is rejected. | NULL is a special value that represents either unknown or inapplicable data. NULL is not the same as zero for numeric data types or blanks for character data types. (§2.3, §2.5, §2.6)

Data independence

Rule 4 is called data independence. Data independence allows database administrators to improve query performance by changing the organization of data on storage devices, without affecting query results. | Rule 4 (No row order): Rows are not ordered. The organization of rows on a storage device, such as a disk drive, never affects query results. (§2.3)

CREATE TABLE

The CREATE TABLE statement creates a new table by specifying the table name, column names, and column data types. | Example data types: INT or INTEGER (integer values), VARCHAR(N) (values with 0 to N characters), DATE (date values), DECIMAL(M, D) (numeric values with M digits, of which D digits follow the decimal point). (§2.3)

DROP TABLE

The DROP TABLE statement deletes a table, along with all the table's rows, from a database. (§2.3)

ALTER TABLE

The ALTER TABLE statement adds, deletes, or modifies columns on an existing table. | It gives the table name followed by a clause that indicates what should be altered: ADD, CHANGE, or DROP. (§2.3)

Data type

A data type is a named set of values from which column values are drawn. | Most fall into seven categories: integer, decimal, character, date and time, binary, spatial, and document. Specialized types include MONEY (currency), BOOLEAN (true-false), BIT (zeros and ones), and ENUM (a small, fixed set of alternative values). (§2.4)

Integer data types

Integer data types represent positive and negative integers. Several integer data types exist, varying by the number of bytes allocated for each value. | Common: INT (4 bytes) and SMALLINT (2 bytes). (§2.4)

Decimal data types

Decimal data types represent numbers with fractional values. Decimal data types vary by number of digits after the decimal point and maximum size. | Common: FLOAT and DECIMAL. (§2.4)

Character data types

Character data types represent textual characters. Common character data types include CHAR, a fixed string of characters, and VARCHAR, a string of variable length up to a specified maximum size. (§2.4)

Date and time data types

Date and time data types represent date, time, or both. | Some include a time zone or specify a time interval, and some represent an interval rather than a point in time. Common: DATE, TIME, DATETIME, TIMESTAMP. (§2.4)

Binary data types

Binary data types store data exactly as the data appears in memory or computer files, bit for bit. The database manages binary data as a series of zeros and ones. | Common: BLOB, BINARY, VARBINARY, IMAGE. (§2.4)

Spatial data types

Spatial data types store geometric information, such as lines, polygons, and map coordinates. | Examples: POLYGON, POINT, GEOMETRY. They are relatively new, so they vary greatly across database systems. (§2.4)

Document data types

Document data types contain textual data in a structured format such as XML or JSON. (§2.4)

Signed

A signed number may be negative. | Ex: signed TINYINT ranges from -128 to 127. (§2.4)

Unsigned

An unsigned number cannot be negative. | Unsigned data types can store larger numbers than the signed version of the same data type. Ex: unsigned TINYINT ranges from 0 to 255. (§2.4)

INSERT

The INSERT statement adds rows to a table. | It has two clauses: INSERT INTO and VALUES. (§2.6)

INSERT INTO

The INSERT INTO clause names the table and columns where data is to be added. The keyword INTO is optional. (§2.6)

VALUES

The VALUES clause specifies the column values to be added. | It may list any number of rows in parentheses to insert multiple rows, and the value order must match the column order in the INTO clause. (§2.6)

DEFAULT

The optional DEFAULT keyword and default value follow the column name and data type in a CREATE TABLE statement. The column is assigned the default value, rather than NULL, when omitted from an INSERT statement. | Pattern: ColumnName DataType DEFAULT value (§2.6)

UPDATE

The UPDATE statement modifies existing rows in a table. | An optional WHERE clause specifies which rows are updated. Omitting the WHERE clause results in all rows being updated. (§2.6)

SET

The UPDATE statement uses the SET clause to specify the new column values. | Separate several columns with commas: SET Column1 = Value1, Column2 = Value2 (§2.6)

DELETE

The DELETE statement deletes existing rows in a table. | An optional WHERE clause specifies which rows should be deleted. Omitting the WHERE clause results in all rows in the table being deleted. (§2.6)

FROM (in a DELETE statement)

The FROM keyword is followed by the table name whose rows are to be deleted. | Pattern: DELETE FROM TableName WHERE condition; (§2.6)

TRUNCATE

The TRUNCATE statement deletes all rows from a table. TRUNCATE is nearly identical to a DELETE statement with no WHERE clause except for minor differences that depend on the database system. | Syntax: TRUNCATE TABLE TableName; (§2.6)

MySQL syntax definitions: what UPPERCASE, lowercase, [ ], { }, ... and ( ) mean

UPPERCASE: SQL keywords. | lowercase: an identifier or expression provided by the user (sometimes a placeholder for a construction defined elsewhere). | [ ]: an optional language element. | { }: a series of alternatives separated by vertical bars; one, and only one, must be included. | ... (ellipsis): the preceding element may be repeated. | ( ) and commas: literal symbols that appear in the statement exactly as written. | Ex: INSERT [INTO] tbl_name ... {VALUES|VALUE} (value_list) means INTO is optional and the clause starts with VALUES or VALUE. (§2.1)

Syntax to create a database, make it the default, inspect it, and delete it

CREATE DATABASE DatabaseName; | USE DatabaseName; (sets the default database) | SHOW DATABASES; / SHOW TABLES [FROM DatabaseName]; / SHOW COLUMNS FROM TableName; / SHOW CREATE TABLE TableName; | DROP DATABASE DatabaseName; (also deletes all its tables) | The mysql database is installed automatically by MySQL and used for system administration. (§2.2)

Rules governing tables: the four relational rules

1) Exactly one value per cell; unknown data is represented with NULL. | 2) No duplicate column names in the same table (allowed in different tables). | 3) No duplicate rows: no two rows may have identical values in all columns. | 4) No row order: the organization of rows on a storage device never affects query results (data independence). | Rules 3 and 4 follow from the definition of a table as a set of rows. Most databases do allow duplicate rows, usually in temporary tables; duplicates are normally eliminated as data moves to a permanent table. (§2.3)

Syntax to create a table and to delete a table without errors

CREATE TABLE [IF NOT EXISTS] TableName (Column1 DATA_TYPE, Column2 DATA_TYPE, ..., ColumnN DATA_TYPE); | DROP TABLE [IF EXISTS] TableName; | CREATE TABLE fails if the named table already exists, and DROP TABLE fails if the named table does not exist. The optional IF NOT EXISTS and IF EXISTS clauses avoid these failures. (§2.3)

Syntax to add, modify, or delete a column (ALTER TABLE)

ADD adds a column: ALTER TABLE TableName ADD ColumnName DataType; | CHANGE modifies a column: ALTER TABLE TableName CHANGE CurrentColumnName NewColumnName NewDataType; | DROP deletes a column: ALTER TABLE TableName DROP ColumnName; | CHANGE always gives the current name, the new name, and the new data type, so one statement can rename a column and change its type. (§2.3)

MySQL integer data types: storage and ranges

TINYINT: 1 byte; signed -128 to 127; unsigned 0 to 255 | SMALLINT: 2 bytes; signed -32,768 to 32,767; unsigned 0 to 65,535 | MEDIUMINT: 3 bytes; signed -8,388,608 to 8,388,607; unsigned 0 to 16,777,215 | INTEGER or INT: 4 bytes; signed -2,147,483,648 to 2,147,483,647; unsigned 0 to 4,294,967,295 | BIGINT: 8 bytes; signed -2^63 to 2^63 - 1; unsigned 0 to 2^64 - 1 | To minimize table size, use the type with the smallest storage that fits. Ex: TINYINT for integers from -100 to 100. (§2.4)

MySQL decimal data types: DECIMAL(M,D) vs FLOAT vs DOUBLE

DECIMAL(M,D): exact decimal number; M = number of significant digits, D = number of digits after the decimal point; storage varies with M and D. | FLOAT: approximate decimal numbers; 4 bytes; range -3.4E+38 to 3.4E+38. | DOUBLE: approximate decimal numbers; 8 bytes; range -1.8E+308 to 1.8E+308. | Key difference: DECIMAL is exact, while FLOAT and DOUBLE are approximate. (§2.4)

MySQL date and time data types: DATE vs TIME vs DATETIME

DATE: 3 bytes; format YYYY-MM-DD; range '1000-01-01' to '9999-12-31'. | TIME: 3 bytes; format hh:mm:ss. | DATETIME: 5 bytes; format YYYY-MM-DD hh:mm:ss; range '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. | The book's statements write date values as quoted strings, ex: DEFAULT '2000-01-01'. (§2.4, §2.6)

MySQL character data types: CHAR(N) vs VARCHAR(N) vs TEXT

CHAR(N): fixed-length string of length N; N bytes; 0 ≤ N ≤ 255. | VARCHAR(N): variable-length string with maximum N characters; storage = number of characters + 1 byte if N ≤ 255, or + 2 bytes if N > 255; 0 ≤ N ≤ 65,535. | TEXT: variable-length string with maximum 65,535 characters; storage = number of characters + 2 bytes. | Character data types use one or two bytes per character. (§2.4)

Syntax to insert one or more rows (INSERT)

INSERT [INTO] TableName (Column1, Column2, ...) VALUES (Value1, Value2, ...); | The value order must match the column order in the INTO clause. | Column names may be omitted as long as VALUES lists all column values in the same order as the table's columns. | Any number of rows in one statement: VALUES (...), (...), (...); (§2.6)

Syntax to change or delete existing rows (UPDATE, DELETE)

UPDATE TableName SET Column1 = Value1, Column2 = Value2, ... WHERE condition; | DELETE FROM TableName WHERE condition; | In both, the WHERE clause is optional and specifies which rows are affected; omitting it updates or deletes every row in the table. (§2.6)

Keyword vs identifier vs literal

Keyword: a word with special meaning, defined by the database system (SELECT, FROM, INTEGER). | Identifier: the name of a database object, specified by the programmer (City, FirstName). | Literal: an explicit value; string literals go in single or double quotes ('Maria', 129). | String literals are always case-sensitive; for keywords and identifiers, case-sensitivity varies by database system. MySQL allows a keyword to be used as an identifier (Name is both), and the language processor tells them apart from context. (§2.1)

Changing rows vs changing columns: INSERT, UPDATE, DELETE vs ALTER TABLE

Rows (data, DML): INSERT adds rows; UPDATE changes values in existing rows; DELETE removes rows. | Columns (structure, DDL): ALTER TABLE with ADD adds a column, CHANGE renames it and/or changes its data type, and DROP deletes it. | So giving every record a new attribute is ALTER TABLE ... ADD, while fixing one record's value is UPDATE ... SET ... WHERE. (§2.1, §2.3, §2.6)

DELETE vs TRUNCATE vs DROP TABLE

DELETE: deletes existing rows; WHERE picks which ones, and with no WHERE every row is deleted. The table remains. | TRUNCATE: deletes all rows; nearly identical to DELETE with no WHERE clause, except for minor differences that depend on the database system. The table remains as an empty table. | DROP TABLE: deletes the table itself, along with all its rows. | Related: ALTER TABLE ... DROP deletes one column; DROP DATABASE deletes a database and all its tables. (§2.2, §2.3, §2.6)

Scenario: A game studio stores each player's level, a whole number from 1 to 200. Which MySQL data type uses the least storage?

TINYINT UNSIGNED: 1 byte, range 0 to 255. Signed TINYINT only reaches 127, so it's too small, and SMALLINT would fit but uses 2 bytes per value. To minimize table size, choose the smallest type that fits. (§2.4)

Scenario: An airline's Crew table has columns ID (INT), Name (VARCHAR(40)), and Role (VARCHAR(20)), in that order. Someone runs INSERT INTO Crew VALUES ('Purser', 4410, 'Ana Ruiz'); What's wrong, and how do you fix it?

With no column list, VALUES must give every column value in the table's column order (ID, Name, Role), and each value must belong to its column's data type, but here 'Purser' lands in the INT column ID. Fix: INSERT INTO Crew VALUES (4410, 'Ana Ruiz', 'Purser'); or name the columns in the values' order: INSERT INTO Crew (Role, ID, Name) VALUES ('Purser', 4410, 'Ana Ruiz'); (§2.3, §2.6)

Scenario: A clinic's Visit table was created with ID SMALLINT UNSIGNED, Patient VARCHAR(50), Copay DECIMAL(5,2) DEFAULT 25.00, and Notes VARCHAR(200). It then runs INSERT INTO Visit (ID, Patient) VALUES (310, 'R. Patel'); What are Copay and Notes in the new row?

Copay is 25.00, its DEFAULT value. Notes is NULL, because an omitted column with no default is assigned NULL. If Notes had the NOT NULL constraint, the insert would be rejected instead. (§2.6)

Scenario: A bike-share analyst wants DockCount set to 30 only for the Station row with ID 12, but runs UPDATE Station SET DockCount = 30; What happens, and what's the fix?

With no WHERE clause, all rows are updated, so every station now has DockCount 30. Fix: UPDATE Station SET DockCount = 30 WHERE ID = 12; (§2.6)

NOT NULL

The NOT NULL constraint prevents a column from having a NULL value. Statements that insert NULL, or update a value to NULL, are automatically rejected. NOT NULL follows the column name and data type in a CREATE TABLE statement. | By default, columns may contain NULL values. | Pattern: ColumnName DataType NOT NULL, ex: Name VARCHAR(60) NOT NULL (§2.5)

NULL vs zero vs blank

NULL: a special value that represents either unknown or inapplicable data. | Zero and blank are not NULL: NULL is not the same as zero for numeric data types or blanks for character data types. | Ex: a zero bonus indicates an employee can, but has not, earned a bonus. A zero bonus is known and applicable, and should not be represented as NULL. (§2.5)

Unknown vs inapplicable: the two meanings of NULL

Unknown: the data exists but isn't known. Ex: in a moving company's Shipment table, a NULL WeightKg means unknown, since every shipment has a weight. | Inapplicable: the data doesn't apply to that row. Ex: in a gym's Member table, a NULL LockerNumber for a plan that includes no locker means inapplicable. | NULL can mean either one; which one applies depends on what the column means for that row. (§2.5)

Scenario: A pharmacy's Prescription table was created with RxID INT UNSIGNED NOT NULL, Drug VARCHAR(40) NOT NULL, and Refills TINYINT UNSIGNED, and it already holds a row for RxID 8810. A technician runs INSERT INTO Prescription (RxID, Drug, Refills) VALUES (8812, NULL, 2); and then UPDATE Prescription SET Refills = NULL WHERE RxID = 8810; What happens with each statement?

The INSERT is automatically rejected: Drug has the NOT NULL constraint, and statements that insert NULL into a NOT NULL column are rejected. The UPDATE runs and sets row 8810's Refills to NULL, because Refills has no NOT NULL constraint and columns may contain NULL values by default. Had Refills been NOT NULL, updating it to NULL would be rejected too. (§2.5)