Basic SQL Knowledge Graph

An interactive knowledge graph of 205 connected ideas: SQL, Database, DBMS, Relational Model, Schema, Table.

By Bao LE · 205 ideas · 221 connections

Ideas

• SQL — A declarative language for defining, querying, and changing data stored in relational databases.
• Database — An organized collection of persisted data managed by a database system.
• DBMS — Software that stores data, executes SQL, enforces rules, and manages concurrent access.
• Relational Model — A data model that represents facts as relations (tables) and relationships through keys.
• Schema — The database blueprint: tables, columns, data types, constraints, indexes, and views.
• Table — A named relation that stores entities or events in rows and columns.
• Row — One record or tuple in a table.
• Column — A named attribute with a data type and optional constraints.
• Data Type — A rule that determines the kind, range, and operations of values a column accepts.
• NULL — A marker for missing or unknown data; it is not zero or an empty string.
• SQL Statement — A complete instruction such as SELECT, INSERT, CREATE TABLE, or UPDATE.
• Query — A statement that asks the database for a result set.
• SELECT — Chooses columns or expressions to return from a query result.
• FROM — Names the table, view, or joined source that supplies rows.
• Result Set — The tabular rows returned by a query.
• DISTINCT — Removes duplicate rows from a SELECT result.
• Alias — A temporary name for a column or table that improves readability.
• WHERE — Filters individual rows before grouping.
• Predicate — A true/false/unknown expression used to decide whether a row qualifies.
• Comparison Operators — Operators such as =, <>, <, >, <=, and >= that form predicates.
• AND — Requires every combined predicate to be true.
• OR — Accepts a row when at least one predicate is true.
• NOT — Reverses a predicate’s truth value.
• LIKE — Matches text against a pattern.
• Wildcards — Pattern tokens such as % for any-length text and _ for one character.
• IN — Tests whether a value belongs to a list or subquery result.
• BETWEEN — Tests an inclusive lower and upper bound.
• ORDER BY — Sorts result rows by one or more expressions.
• Row Limiting — TOP, LIMIT, or FETCH restricts returned rows; dialects differ.
• INSERT — Adds new rows to a table.
• UPDATE — Changes values in existing rows; WHERE determines the scope.
• DELETE — Removes rows; without WHERE every row is affected.
• Aggregate Function — Computes one value from many rows.
• MIN() — Returns the smallest non-NULL value.
• MAX() — Returns the largest non-NULL value.
• COUNT() — Counts rows or non-NULL values; COUNT(*) counts rows.
• SUM() — Adds numeric non-NULL values.
• AVG() — Computes the mean of numeric non-NULL values.
• GROUP BY — Partitions rows so aggregates are calculated per group.
• HAVING — Filters groups after aggregation.
• JOIN — Combines related rows from two sources using a join condition.
• Join Condition — The ON predicate that determines which rows match.
• INNER JOIN — Keeps only matching rows from both inputs.
• LEFT JOIN — Keeps all left rows; unmatched right values become NULL.
• RIGHT JOIN — Keeps all right rows; often rewritten as a LEFT JOIN.
• FULL OUTER JOIN — Keeps matched and unmatched rows from both inputs.
• Self Join — Joins a table to itself through aliases, often for hierarchies.
• UNION — Combines compatible result sets and removes duplicates.
• UNION ALL — Combines compatible result sets while preserving duplicates.
• Subquery — A query nested inside another SQL statement.
• EXISTS — Tests whether a subquery returns at least one row.
• ANY — Compares a value to at least one value from a subquery.
• ALL — Compares a value to every value from a subquery.
• CASE — Returns a value conditionally inside a SQL expression.
• NULL Functions — Functions such as COALESCE replace or handle NULL values.
• CREATE DATABASE — Creates a new database container.
• CREATE TABLE — Defines a table, columns, types, and constraints.
• ALTER TABLE — Changes an existing table’s columns or constraints.
• DROP DATABASE — Permanently removes a database and its contents.
• DROP TABLE — Permanently removes a table definition and its data.
• Constraint — A database-enforced rule that protects data quality.
• NOT NULL — Requires a column to have a value.
• UNIQUE — Prevents duplicate values in a column or column set.
• PRIMARY KEY — Uniquely identifies each row; it is unique and non-NULL.
• FOREIGN KEY — Enforces that a value references an existing key in another table.
• CHECK — Enforces a boolean validation rule, e.g. price >= 0.
• DEFAULT — Supplies a value when INSERT omits a column.
• Auto-generated Key — Generates numeric identifiers: IDENTITY, AUTO_INCREMENT, SERIAL, or a sequence.
• Index — A lookup structure that speeds selected reads at a storage and write cost.
• View — A stored query exposed as a virtual table.
• Stored Procedure — A named database program that encapsulates SQL operations.
• SQL Comments — Non-executing documentation using -- or /* ... */.
• Parameter — A bound value kept separate from SQL command text.
• Prepared Statement — A statement with placeholders parsed before parameter values are bound.
• SQL Injection — An attack where untrusted input changes a query’s intended meaning.
• Database Backup — A recoverable copy of a database used for restoration.
• Database Hosting — Operating a database service with access control, monitoring, capacity, and backups.
• Date and Time — Native temporal types represent dates, times, and timestamps with engine-specific rules.
• Identifier — A name for a database object such as a table, column, or constraint.
• Literal — A value written directly in SQL, such as 42, 'text', or DATE '2026-01-01'.
• Expression — A calculation or value-producing unit built from literals, columns, operators, and functions.
• Scalar Function — A function that returns one value for each input row.
• String Function — A scalar function that transforms or searches character data.
• Numeric Function — A scalar function that calculates or transforms numeric values.
• Date and Time Function — A scalar function that extracts, constructs, or transforms temporal values.
• Type Conversion — An explicit or implicit change from one data type to another.
• Integer Type — Stores whole numbers with an engine-specific range.
• Decimal Type — Stores exact fixed-point numeric values, appropriate for currency.
• Floating-point Type — Stores approximate real numbers; avoid it for exact money.
• Character String — Stores text values such as names, codes, and descriptions.
• Boolean Type — Stores logical true/false values where the engine supports them.
• Binary Type — Stores bytes such as hashes, files, or encoded data.
• Timestamp — Represents a date and time, sometimes with time-zone semantics.
• Three-valued Logic — SQL predicates can evaluate to TRUE, FALSE, or UNKNOWN because of NULL.
• IS NULL — Tests whether a value is NULL; equality operators cannot do this.
• Logical Query Processing — A conceptual order: FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT.
• Projection — Selecting the columns or expressions that appear in a result set.
• Source Rows — The input row set produced by FROM and JOIN before later clauses refine it.
• Pagination — Returning results in bounded pages, typically with ordering plus LIMIT/OFFSET or FETCH.
• SET Clause — The list of column assignments made by UPDATE.
• VALUES Clause — The literal row values supplied by INSERT.
• Affected Rows — The rows changed by INSERT, UPDATE, or DELETE; inspect this count for safety.
• Cartesian Product — Every left row paired with every right row before a join condition filters matches.
• Matched Row — A pair of source rows satisfying the join condition.
• Unmatched Row — A source row that has no counterpart satisfying the join condition.
• NULL Extension — NULL values supplied for the missing side of an outer join.
• One-to-many Relationship — One parent key can be referenced by many child rows through a foreign key.
• Column Compatibility — UNION inputs need the same number of columns with compatible types.
• Correlated Subquery — A subquery that refers to values from the outer query row.
• Database Object — A named schema element such as a table, view, index, procedure, or constraint.
• Candidate Key — A minimal column set that can uniquely identify a row.
• Composite Key — A key formed from two or more columns.
• Surrogate Key — A system-generated identifier with no business meaning.
• Natural Key — A meaningful business attribute used as an identifier.
• Entity Integrity — The rule that primary-key values must be unique and non-NULL.
• Referential Integrity — The rule that a foreign key references a valid parent key or is NULL when allowed.
• Domain Integrity — Data-type, CHECK, NOT NULL, and DEFAULT rules that constrain permitted values.
• Query Optimizer — The DBMS component that chooses an execution plan for a query.
• Execution Plan — The operations the DBMS will use to execute a query.
• Selectivity — How strongly a condition narrows rows; selective predicates can make indexes useful.
• Input Validation — Application-side checks that reject malformed or unacceptable input before use.
• Least Privilege — Giving database accounts only the permissions they need.
• Authentication — Verifying who is connecting to the database.
• Authorization — Determining which objects and operations an authenticated principal may use.
• Restore — Recovering data or schema from a backup.
• Schema Migration — A controlled, versioned change to database structure.
• Transaction — A unit of work that commits or rolls back as a whole.
• COMMIT — Makes a transaction’s changes durable.
• ROLLBACK — Undoes uncommitted changes in a transaction.
• Concurrency Control — DBMS mechanisms that preserve correctness when transactions overlap.
• Entity — A real-world thing or business concept represented by a table.
• Attribute — A property of an entity represented by a column.
• Relationship — An association between entities represented through matching keys.
• One-to-one Relationship — Each row in one table relates to at most one row in another.
• Many-to-many Relationship — Multiple rows on each side can relate; model it through a junction table.
• Junction Table — A table that resolves a many-to-many relationship using two or more foreign keys.
• Normalization — Organizing schema to reduce redundancy and update anomalies.
• First Normal Form — Rows are unique and each column holds atomic, non-repeating values.
• Second Normal Form — Non-key attributes depend on the whole key, not only part of a composite key.
• Third Normal Form — Non-key attributes depend on the key, the whole key, and nothing but the key.
• Denormalization — Deliberately duplicating or precomputing data to improve read performance at a consistency cost.
• Common Table Expression — A named temporary result defined with WITH for one statement.
• Recursive CTE — A CTE that repeatedly combines an anchor query with recursive rows, useful for hierarchies.
• Window Function — Computes across related rows while preserving individual rows, unlike GROUP BY.
• PARTITION BY — Divides a window-function result into independent groups.
• Window ORDER BY — Defines ordering inside a window calculation.
• Window Frame — The subset of rows considered for a window calculation relative to the current row.
• ROW_NUMBER() — Assigns a sequential number to rows within a window.
• RANK() — Ranks rows while leaving gaps after ties.
• LEAD() and LAG() — Read values from following or preceding rows without a self join.
• ACID — The four transaction goals: atomicity, consistency, isolation, and durability.
• Atomicity — All operations in a transaction succeed together or none do.
• Consistency — A transaction moves data between valid states while constraints hold.
• Isolation — Concurrent transactions should not improperly interfere with each other.
• Durability — Committed changes survive a crash or restart.
• Lock — A concurrency-control mechanism that coordinates conflicting reads and writes.
• Deadlock — Transactions wait on each other’s locks in a cycle; one must be aborted.
• Isolation Level — A policy trading consistency anomalies against concurrency, such as read committed or serializable.
• Trigger — Database code that runs automatically in response to INSERT, UPDATE, or DELETE events.
• User-defined Function — A reusable database routine that returns a value or table, subject to engine rules.
• Cursor — A mechanism for processing query rows one at a time; set-based SQL is usually preferred.
• Replication — Copying data across database instances for availability, scale, or disaster recovery.
• Point-in-time Recovery — Restoring a database to a chosen moment using backups plus transaction logs.
• Database Monitoring — Observing availability, latency, errors, storage, locks, and slow queries.
• Slow Query — A query whose execution time or resource use exceeds an acceptable threshold.
• EXPLAIN — A command that reveals or estimates a query execution plan.
• Covering Index — An index containing all values a query needs, potentially avoiding table lookups.
• Cardinality Estimation — Optimizer prediction of row counts, which influences plan choice.
• SELECT INTO — Creates a new table from a query result; exact syntax varies by engine.
• INSERT INTO ... SELECT — Copies query results into an existing table without moving rows through the client.
• Materialized View — Stores a query result physically for faster reads at the cost of refresh complexity.
• Materialized View Refresh — Rebuilds stored materialized-view data from base tables.
• Referential Action — Controls what happens to child rows when a referenced parent key changes or is deleted.
• ON DELETE CASCADE — Deletes dependent rows automatically when their parent is deleted.
• ON DELETE RESTRICT — Refuses a parent delete while dependent child rows exist.
• ON DELETE SET NULL — Sets a child foreign key to NULL when its parent is deleted.
• SAVEPOINT — Marks a rollback point inside a transaction.
• Dirty Read — Reads another transaction's uncommitted change.
• Non-repeatable Read — A later read sees another transaction's committed update.
• Phantom Read — A repeated predicate query sees newly inserted or removed rows.
• Lost Update — One write silently overwrites another write based on stale data.
• Optimistic Locking — Detects write conflicts with a version or timestamp.
• Pessimistic Locking — Locks data before changing it to prevent conflicts.
• Transaction Retry — Retries safely after a deadlock or serialization failure.
• Table Scan — Reads every row of a table.
• Index Scan — Uses an index to find qualifying rows.
• Nested Loop Join — Probes one input for each row of the other input.
• Hash Join — Builds a hash table for one input and probes it with the other.
• Merge Join — Combines two inputs ordered on the join key.
• Sargability — A predicate form that enables efficient index searches.
• Composite Index — An index over multiple columns whose key order affects supported queries.
• Database Statistics — Value-distribution summaries used for cardinality estimates.
• Functional Dependency — A relationship where one attribute set determines another.
• Partial Dependency — A non-key attribute depends on only part of a composite key.
• Transitive Dependency — A non-key attribute depends on another non-key attribute.
• Boyce-Codd Normal Form — A stricter normal form where every determinant is a candidate key.
• Database Role — A named principal or group to which privileges are granted.
• Database Privilege — Permission to act on a database object.
• GRANT — Assigns privileges to a role or user.
• REVOKE — Removes privileges that were granted.

Connections

• SQL → executed by → DBMS
• DBMS → manages → Database
• Database → has blueprint → Schema
• Schema → defines → Table
• Table → contains → Row
• Table → has → Column
• Column → typed as → Data Type
• Column → may contain → NULL
• Relational Model → represents as → Table
• SQL → expressed as → SQL Statement
• SQL Statement → may be → Query
• Query → uses → SELECT
• Query → reads from → FROM
• SELECT → produces → Result Set
• SELECT → can deduplicate with → DISTINCT
• SELECT → can name with → Alias
• Query → filters with → WHERE
• WHERE → evaluates → Predicate
• Predicate → built from → Comparison Operators
• Predicate → combines with → AND
• Predicate → combines with → OR
• Predicate → can negate with → NOT
• WHERE → supports → LIKE
• LIKE → interprets → Wildcards
• WHERE → supports → IN
• WHERE → supports → BETWEEN
• Query → sorts with → ORDER BY
• ORDER BY → pairs with → Row Limiting
• SQL Statement → may be → INSERT
• SQL Statement → may be → UPDATE
• SQL Statement → may be → DELETE
• INSERT → adds rows to → Table
• UPDATE → scoped by → WHERE
• DELETE → scoped by → WHERE
• Query → can use → Aggregate Function
• Aggregate Function → includes → MIN()
• Aggregate Function → includes → MAX()
• Aggregate Function → includes → COUNT()
• Aggregate Function → includes → SUM()
• Aggregate Function → includes → AVG()
• Aggregate Function → groups with → GROUP BY
• GROUP BY → filtered after by → HAVING
• Query → can combine with → JOIN
• JOIN → requires → Join Condition
• JOIN → has variant → INNER JOIN
• JOIN → has variant → LEFT JOIN
• JOIN → has variant → RIGHT JOIN
• JOIN → has variant → FULL OUTER JOIN
• JOIN → has variant → Self Join
• FOREIGN KEY → commonly supplies → Join Condition
• Query → can combine with → UNION
• UNION → preserves duplicates → UNION ALL
• Query → may contain → Subquery
• Subquery → tested by → EXISTS
• Subquery → compared with → ANY
• Subquery → compared with → ALL
• SELECT → can evaluate → CASE
• NULL → handled by → NULL Functions
• SQL Statement → may be → CREATE DATABASE
• SQL Statement → may be → CREATE TABLE
• SQL Statement → may be → ALTER TABLE
• SQL Statement → may be → DROP DATABASE
• SQL Statement → may be → DROP TABLE
• CREATE TABLE → creates → Table
• CREATE TABLE → declares → Constraint
• Constraint → includes → NOT NULL
• Constraint → includes → UNIQUE
• Constraint → includes → PRIMARY KEY
• Constraint → includes → FOREIGN KEY
• Constraint → includes → CHECK
• Constraint → includes → DEFAULT
• PRIMARY KEY → referenced by → FOREIGN KEY
• PRIMARY KEY → often generated by → Auto-generated Key
• Table → optimized by → Index
• View → stores → Query
• SQL Statement → can call → Stored Procedure
• SQL Statement → documented by → SQL Comments
• Parameter → bound through → Prepared Statement
• Prepared Statement → mitigates → SQL Injection
• Database → protected by → Database Backup
• DBMS → operated through → Database Hosting
• Column → may use → Date and Time
• SQL Statement → names → Identifier
• SQL Statement → contains → Literal
• SELECT → performs → Projection
• Expression → may call → Scalar Function
• Scalar Function → includes → String Function
• Scalar Function → includes → Numeric Function
• Scalar Function → includes → Date and Time Function
• Expression → may use → Type Conversion
• Data Type → includes → Integer Type
• Data Type → includes → Decimal Type
• Data Type → includes → Floating-point Type
• Data Type → includes → Character String
• Data Type → includes → Boolean Type
• Data Type → includes → Binary Type
• Date and Time → includes → Timestamp
• NULL → causes → Three-valued Logic
• Three-valued Logic → governs → Predicate
• NULL → tested by → IS NULL
• Query → evaluated in → Logical Query Processing
• FROM → produces → Source Rows
• Logical Query Processing → begins with → FROM
• Logical Query Processing → then → WHERE
• Logical Query Processing → then → GROUP BY
• Logical Query Processing → then → HAVING
• Logical Query Processing → then → SELECT
• Logical Query Processing → then → ORDER BY
• Row Limiting → supports → Pagination
• INSERT → uses → VALUES Clause
• UPDATE → uses → SET Clause
• INSERT → reports → Affected Rows
• UPDATE → reports → Affected Rows
• DELETE → reports → Affected Rows
• JOIN → filters → Cartesian Product
• Join Condition → selects → Matched Row
• LEFT JOIN → preserves → Unmatched Row
• LEFT JOIN → creates → NULL Extension
• FOREIGN KEY → models → One-to-many Relationship
• UNION → requires → Column Compatibility
• Subquery → may be → Correlated Subquery
• Schema → contains → Database Object
• Candidate Key → one chosen as → PRIMARY KEY
• Composite Key → can be → Candidate Key
• PRIMARY KEY → may be → Surrogate Key
• PRIMARY KEY → may be → Natural Key
• PRIMARY KEY → enforces → Entity Integrity
• FOREIGN KEY → enforces → Referential Integrity
• Constraint → enforces → Domain Integrity
• Index → considered by → Query Optimizer
• Query Optimizer → chooses → Execution Plan
• WHERE → has → Selectivity
• Selectivity → guides use of → Index
• SQL Injection → reduced by → Input Validation
• DBMS → requires → Authentication
• Authentication → precedes → Authorization
• Authorization → implemented by → Least Privilege
• Database Backup → enables → Restore
• Schema → changed by → Schema Migration
• Transaction → ends with → COMMIT
• Transaction → can end with → ROLLBACK
• DBMS → manages → Concurrency Control
• Table → models → Entity
• Column → models → Attribute
• FOREIGN KEY → implements → Relationship
• Relationship → may have cardinality → One-to-one Relationship
• Relationship → may have cardinality → One-to-many Relationship
• Relationship → may have cardinality → Many-to-many Relationship
• Many-to-many Relationship → resolved by → Junction Table
• Normalization → includes → First Normal Form
• Normalization → includes → Second Normal Form
• Normalization → includes → Third Normal Form
• Denormalization → trades off with → Normalization
• Query → can use → Common Table Expression
• Common Table Expression → may be → Recursive CTE
• Query → can use → Window Function
• Window Function → partitions with → PARTITION BY
• Window Function → orders with → Window ORDER BY
• Window Function → scoped by → Window Frame
• Window Function → includes → ROW_NUMBER()
• Window Function → includes → RANK()
• Window Function → includes → LEAD() and LAG()
• Transaction → aims for → ACID
• ACID → includes → Atomicity
• ACID → includes → Consistency
• ACID → includes → Isolation
• ACID → includes → Durability
• Concurrency Control → uses → Lock
• Lock → can cause → Deadlock
• Transaction → governed by → Isolation Level
• SQL Statement → can fire → Trigger
• Stored Procedure → related to → User-defined Function
• Query → may return → Cursor
• Database Hosting → may use → Replication
• Database Backup → supports → Point-in-time Recovery
• Database Hosting → requires → Database Monitoring
• Database Monitoring → detects → Slow Query
• Slow Query → analyzed with → EXPLAIN
• Index → may be → Covering Index
• Query Optimizer → uses → Cardinality Estimation
• Query → can-materialize → SELECT INTO
• INSERT → has-variant → INSERT INTO ... SELECT
• Query → supplies-rows-to → INSERT INTO ... SELECT
• View → can-be-materialized-as → Materialized View
• Materialized View → requires → Materialized View Refresh
• FOREIGN KEY → governed-by → Referential Action
• Referential Action → has-variant → ON DELETE CASCADE
• Referential Action → has-variant → ON DELETE RESTRICT
• Referential Action → has-variant → ON DELETE SET NULL
• ON DELETE SET NULL → requires → NULL
• Transaction → can-contain → SAVEPOINT
• Isolation Level → prevents-at-stronger-levels → Dirty Read
• Isolation Level → prevents-at-stronger-levels → Non-repeatable Read
• Isolation Level → prevents-at-stronger-levels → Phantom Read
• Transaction → can-suffer-from → Lost Update
• Lost Update → mitigated-by → Optimistic Locking
• Lost Update → mitigated-by → Pessimistic Locking
• Deadlock → handled-by → Transaction Retry
• Query Optimizer → may-choose → Table Scan
• Query Optimizer → may-choose → Index Scan

Make your own knowledge graph with LightMesh