SQL

SQL

Structured Query Language; standard language for relational databases.

SEQUEL (historical name)

Original name for SQL

Core SQL

The common, standard part used in basically all relational DB setups.
ex. CREATE TABLE, SELECT, INSERT, UPDATE, DELETE.

SQL extensions

Vendor-specific additions beyond the core.
Different DBs support different extensions.
ex. Specialized analytics/data mining features.

Statement terminator (;)

Ends an SQL statement.
Without it, DB may wait for more input.

DDL (Data Definition Language)

SQL statements that define/change schema objects.
Defines tables/domains; removes objects.
ex. CREATE TABLE, CREATE DOMAIN, ALTER TABLE, DROP TABLE.

DML (Data Manipulation Language)

SQL statements that add/change/delete table rows.
Used for actual data.
ex. INSERT, UPDATE, DELETE.

Query

SQL that asks the DB for data.
Usually SELECT.
ex. SELECT Name FROM PERSON WHERE …

CREATE TABLE

Creates a new table (relation) with attributes, types, and constraints.

Attribute / column

A named field in a table with a data type (and maybe constraints).
Defines what kind of values a column can store.
ex. Name VARCHAR(30) NOT NULL.

Row / tuple

One record in a table.
A single collection of column values.

Data type

Defines what kind of data a column can hold.
Prevents invalid values and shapes storage.
ex. INT vs VARCHAR vs DATE.

INT (ex. Id INT)

Integer number type.
Used for counts/IDs.

SMALLINT (ex. Credit_hours SMALLINT)

Smaller-range integer type.
Can save space vs INT.

FLOAT (ex. GPA FLOAT)

Approximate numeric type.
Used for decimals where exactness isn’t critical.

DOUBLE PRECISION

More precise floating-point type than FLOAT.

CHAR(n)

ex. (CHAR(12) phone format like zzz-zzz-zzzz)

Fixed-length character string, padded to n characters.
Good when length is always the same.

VARCHAR(n)

ex. (Name VARCHAR(30))

Variable-length string up to n characters.
Saves space when lengths vary.

BOOLEAN

TRUE or FALSE.
NULL means unknown (neither true nor false).

DATE

Calendar date.
Format shown: YYYY-MM-DD.

TIME

Time of day.
Format shown: HH:MM:SS.

TIMESTAMP

Date + time together.
Used for event times.

Domain (custom type)

(ex. CREATE DOMAIN NET_ID AS CHAR(9).)

A named type you define, often as an alias.
Lets you change the underlying type in one place.

CREATE DOMAIN

Creates a named domain (custom data type).
Used to standardize types across tables.
ex. NET_ID instead of repeating CHAR(9).

Constraint

A rule the data must satisfy; DB rejects inserts/updates that violate it.
Used to keep data valid.
ex. NOT NULL, PRIMARY KEY, FOREIGN KEY, CHECK.

NOT NULL

ex. Email VARCHAR(40) NOT NULL.

Column cannot be NULL.
Forces a value to be provided.

PRIMARY KEY

ex. Netid PRIMARY KEY.

Uniquely identifies each row.
Cannot be NULL; enforces uniqueness.

Composite primary key

Primary key made of multiple columns.
Declared as PRIMARY KEY(col1, col2).

ex. PRIMARY KEY(Studentid, Courseid).

Unique identification

ex. No two PERSON rows share the same Netid.

Idea behind keys: one row per key value.
Makes rows referable and joinable.

FOREIGN KEY

ex. SECTION.Instructor_id REFERENCES PERSON(Netid).

Column(s) that must match a key in another table.
Links tables and prevents orphan references.

REFERENCES

ex. REFERENCES PERSON(Netid).

SQL keyword used in foreign key definitions.
States the parent table and referenced column(s).

Parent table

ex. PERSON is parent of SECTION (Instructor_id).

The referenced table in a foreign key relationship.
Holds the “original” key values.

Child table

The table that contains the foreign key.
Depends on parent table values.
ex. SECTION depends on PERSON(Netid).

Orphan row

A row with a foreign key value that doesn’t exist in the parent.
FK constraints prevent this.
ex. SECTION.Instructor_id = 'x' with no PERSON('x').

Referential action

What the DB does to child rows when a referenced parent row is deleted/updated.
Declared on the foreign key side.
ex. ON DELETE …, ON UPDATE ….

ON DELETE CASCADE

ex. Delete PERSON → delete their SECTION rows (if set).

Deleting a parent row deletes referencing child rows too.
Prevents orphans by removing dependents.

ON DELETE SET DEFAULT

ex. Set Instructor_id to 'Staff' when instructor leaves.

Deleting parent sets child’s FK to a default value.
Default must still be valid (exist in parent).

ON DELETE SET

Sets to a specific valid value when parent deleted.
Idea: keep child row, but point it somewhere safe.

ex. Instructor_id becomes 'Staff'.

ON UPDATE CASCADE

If parent key value changes, update matching foreign keys automatically.
Keeps references consistent.

ex. Netid changes → SECTION.Instructor_id updates.

Restrict delete (default idea)

Normally you can’t delete a parent row if children reference it.
Prevents creating orphans.

ex. Can’t delete PERSON if SECTION points to them (unless action set).

CHECK constraint

ex. Year CHECK (Year >= 1968).

Boolean expression that must be true for new/updated values.
Used for validation.

Validation by DB

Constraints are the DB doing validations for you.
Prevents bad data from being stored.

ex. Reject Credit_hours >= 6 if CHECK says < 6.

ALTER TABLE

ex. ALTER TABLE PERSON ADD COLUMN …

Changes an existing table definition.
Can add/drop columns or add constraints.

DROP

Removes a schema object; opposite of CREATE.
Can remove tables, domains, schemas.

DROP TABLE

ex. DROP TABLE SECTION;

Deletes the table definition and its data.
Table does not need to be empty.

DROP TABLE IF EXISTS

ex. DROP TABLE IF EXISTS PERSON;

Avoids an error if the table doesn’t exist.
Safer in scripts.

DROP TABLE RESTRICT

Don’t drop the table if something else references it.
Prevents breaking dependencies.

ex. FK in another table blocks drop.

DROP TABLE CASCADE

Drop dependent relationships/constraints too.
More destructive; removes references.

ex. Drops FK constraint referencing the table.

INSERT INTO

ex. INSERT INTO PERSON …

Adds new rows to a table.
DB checks constraints; rejects violations.

INSERT simple form

INSERT INTO Table VALUES (…) in column-definition order.
Easy but order-dependent.

ex. VALUES follow the table’s column order.

INSERT named form

ex. Insert only required fields.

INSERT INTO Table(col1,col2,…) VALUES (…).
Lets you choose column order and omit optional columns.

INSERT multirow form

Insert multiple rows in one statement.
Uses multiple parenthesized row value sets.

ex. (…), (…), (…).

Required attributes for insert

You must provide keys and NOT NULL columns.
Defaults can be omitted.

ex. Netid + Name + Email required in PERSON example.

Constraint rejection

DB rejects inserts that violate constraints.
Prevents duplicates and invalid values.

ex. Duplicate primary key insert fails.

SELECT

A “statement”(or command) to retrieve data.
Structure: SELECT … FROM … WHERE …

ex. SELECT Course FROM SECTION WHERE …

SELECT clause

ex. SELECT Name, Email.

What columns/expressions you want returned.
This is what comes back.

FROM clause

ex. FROM PERSON, SECTION.

What table(s) you’re working with.
Lists tables used by SELECT/WHERE.

WHERE clause

ex. WHERE Year = 2025 AND Semester='Fall'.

Conditions that filter rows.
Optional; without it you get all rows.

SELECT *

ex. SELECT * FROM SECTION;

Returns entire rows (all columns).
Convenient but often more data than needed.

Table alias

Short name for a table in a query.
Makes queries shorter and avoids ambiguity.

ex. FROM PERSON P, SECTION S.

Qualified attribute (Table.Column)

Used when multiple tables have columns with same names.
Prevents ambiguity.

ex. PERSON.Id vs SECTION.Id.

Join condition (implicit join)

A WHERE condition that matches rows across two tables.
Usually FK = PK.

ex. P.Netid = S.Instructor_id.

Join (concept)

Combines related rows from multiple tables.
Gets you attributes from both together.

ex. PERSON + SECTION data in one result.

Comparison operators

Operators used in WHERE.
Includes =, <>, <, >, <=, >=.

ex. Year < 2000.

Equality operator (=)

Tests equality in SQL.
Single equals sign (not ==).

ex. WHERE Name = 'Chiikawa'.

“<>”

ex. WHERE Department <> 'CS'.

SQL not-equal operator.
(Some DBs also allow !=.)

AND

ex. Year=2025 AND Semester='Fall'.

Combines conditions; both must be true.
Used to narrow results.

OR

ex. Semester='Fall' OR Semester='Spring'.

Combines conditions; either can be true.
Used to broaden results.

NOT

ex. NOT (Year < 2000).

Negates a condition.
With NULL logic, NOT UNKNOWN stays UNKNOWN.

LIKE

ex. WHERE Name LIKE '%Chiikawa_n'.

Pattern match for strings.
Supports _ (one char) and % (wildcard).

_ wildcard

ex. 'Chiikawa_n' matches Chiikawa/Chiikawe.

Matches exactly one character in LIKE.
Used for “one char differs” patterns.

% wildcard

ex. '%This%'

Matches 0 or more characters in LIKE.
Used for “contains” or “starts with” patterns.

Pattern matching intuition

LIKE patterns must match the whole string, but % can cover the rest.
Helps explain why some patterns don’t match.

ex. 'is a %' doesn’t match 'This is a test' (wrong start).

BETWEEN

ex. Credit_hours BETWEEN 1 AND 2 includes 1 and 2.

Checks if a value is in an inclusive range.
Use parentheses to be clear.

Inclusive range

BETWEEN includes endpoints.
Common exam detail.

ex. BETWEEN 1 AND 2 matches 1, 1.5, 2.

ORDER BY

ex. ORDER BY Year DESC.

Sorts query results.
Default ascending; DESC for descending.

Ascending (default)

ex. ORDER BY Year.

ORDER BY sorts smallest→largest by default.
If no DESC, assume ASC.

DESC

ex. ORDER BY Year DESC.

Descending order.
Reverses sort.

Multiple ORDER BY keys

ex. ORDER BY LastName, FirstName.

You can sort by multiple attributes.
Second key breaks ties for the first.

DISTINCT

ex. SELECT DISTINCT Year FROM SECTION.

Removes duplicates in query results.
Useful when selecting non-key attributes.

Duplicates in results

ex. Many sections in same year → repeated Year values.

If you don’t select a key, multiple rows may produce the same output values.
DISTINCT removes repeated outputs.

NULL

Represents missing/unknown/unavailable/not applicable.
Not equal to anything (even to itself).

NULL meanings

NULL can mean unknown, unavailable, or not applicable.

Reason comparisons get tricky.
ex. Unknown phone vs phone doesn’t exist.

NULL equality issue

You can’t use = or <> with it.
Two of them might be the same, but SQL treats it as unknown.

IS NULL

Checks whether a column value is NULL.
Use IS / IS NOT, not =.

ex. WHERE Phone_number IS NULL.

IS NOT NULL

ex. WHERE Phone_number IS NOT NULL.

Checks whether a column value is not NULL.
Useful for filtering to known values.

Three-valued logic (3VL)

SQL uses TRUE, FALSE, and UNKNOWN.
UNKNOWN usually means “don’t select the row.”
ex. Condition with NULL becomes UNKNOWN.

UNKNOWN (3VL meaning)

Result when SQL can’t decide true/false due to NULL.
Generally treated as not selected in WHERE.

ex. Salary > 50000 is UNKNOWN if Salary is NULL.

AND with UNKNOWN

If either side is FALSE → FALSE; if both TRUE → TRUE; otherwise UNKNOWN.
This is why some rows disappear.
ex. TRUE AND UNKNOWN = UNKNOWN.

OR with UNKNOWN

If either side is TRUE → TRUE; if both FALSE → FALSE; otherwise UNKNOWN.
UNKNOWN can still become TRUE if other side is TRUE.
ex. TRUE OR UNKNOWN = TRUE.

NOT with UNKNOWN

NOT TRUE = FALSE; NOT FALSE = TRUE; NOT UNKNOWN = UNKNOWN.
Because you still don’t know.
ex. NOT UNKNOWN stays UNKNOWN.

IN operator

ex. WHERE Letter_grade IN ('A','B','C').

Matches an attribute against a defined set of values.
Works for numbers and strings.

IN (set membership)

Cleaner than a long chain of ORs.
Often used for categories.
ex. Grade IN ('A+','A','A-').

Aggregation

The process of combining individual data items into a grouped summary for analysis

SQL can compute summary stats: SUM, MIN, MAX, COUNT, AVG.
These are written in SELECT (what you want back).
ex. SELECT COUNT(*) FROM EMPLOYEE.

SUM

Adds numeric values across matched rows.
Used for totals.
ex. Total salary paid per year.

COUNT(*)

Counts rows.

* means “all rows,” not “all columns.”
ex. Total number of employees.

COUNT(DISTINCT X)

Counts unique values of X.
Used to count distinct categories.
ex. Number of departments.

MAX

Largest value across matched rows.
Used for top values.
ex. Maximum salary.

MIN

Smallest value across matched rows.
Used for lowest values.
ex. Minimum salary.

AVG

Average value across matched rows.
Used for means.
ex. Average salary.