Twenty flashcards covering the essentials of relational databases and SQL. Includes primary and foreign keys, the major join types, normalisation up to third normal form, aggregate functions and GROUP BY, indexes, transactions and ACID properties, and the diff...
Twenty flashcards covering the essentials of relational databases and SQL. Includes primary and foreign keys, the major join types, normalisation up to third normal form, aggregate functions and GROUP BY, indexes, transactions and ACID properties, and the difference between SQL and NoSQL. Useful for interviews, coursework and database certification prep.
Ready to test yourself?
Flip through all 20 cards in interactive study mode.
A database that organises data into tables (relations) of rows and columns, with relationships between tables enforced through keys.
A column, or set of columns, that uniquely identifies each row in a table. It cannot contain NULL values and must be unique.
A column that references the primary key of another table, enforcing referential integrity between the two tables.
Only the rows that have matching values in both joined tables; non-matching rows from either table are excluded.
All rows from the left table, plus matching rows from the right table; unmatched right-table columns are returned as NULL.
WHERE filters individual rows before grouping. HAVING filters groups after a GROUP BY aggregation has been applied.
COUNT(), SUM(), AVG(), and MAX()/MIN() — used to compute a single summary value across a set of rows.
Groups rows sharing the same value in specified columns into summary rows, typically used with aggregate functions to compute per-group statistics.
The process of organising tables to reduce data redundancy and improve integrity, achieved by progressively applying normal forms.
1NF: atomic columns, no repeating groups. 2NF: 1NF plus no partial dependency on part of a composite key. 3NF: 2NF plus no transitive dependency on non-key columns.
Deliberately introducing redundancy into a normalised schema to improve read performance, often used in reporting or data warehouse designs.
A data structure (commonly a B-tree) that speeds up row lookups on indexed columns, at the cost of extra storage and slower write operations.
A primary key made up of two or more columns whose combined values uniquely identify a row, used when no single column is unique alone.
Atomicity, Consistency, Isolation, Durability — the four properties guaranteeing reliable transaction processing in a database.
A sequence of operations executed as a single logical unit of work — either all operations succeed and are committed, or all are rolled back on failure.
COMMIT permanently saves all changes made during the current transaction. ROLLBACK undoes them, restoring the database to its state before the transaction began.
An attack that inserts malicious SQL through unsanitised user input to manipulate or extract data. Prevented with parameterised queries/prepared statements.
DELETE removes rows (can be filtered, logged, rolled back). TRUNCATE removes all rows quickly, resetting identity, minimal logging. DROP removes the entire table structure.
A virtual table defined by a stored query, which presents data from one or more underlying tables without duplicating storage.
SQL databases use structured schemas and relational tables with strong consistency (MySQL, PostgreSQL). NoSQL databases use flexible schemas — document, key-value, or graph — favouring scalability (MongoDB, Redis).
