The four guarantees of a reliable transaction: Atomicity (all or nothing), Consistency (rules stay satisfied), Isolation (concurrent transactions do not interfere) and Durability (committed changes survive crashes). Relational databases are known for them.
Example. After a bank transfer commits and the server loses power, the new balances are still there when it restarts.
Related: Transaction, Atomicity, Isolation level, Write-ahead log
A function that combines many rows into one value, such as COUNT, SUM, AVG, MIN and MAX. Except COUNT(*), they ignore NULL. Used with GROUP BY, they give one result per group; without it, one for the whole table.
Example. SELECT AVG(amount) FROM orders; returns the mean order value across all orders.
Related: GROUP BY, HAVING, NULL, Window function
The DDL statement that changes an existing table, for example adding or dropping a column, changing a type or adding a constraint. On large live tables it can lock the table or rewrite data, so it needs planning.
Example. ALTER TABLE customers ADD COLUMN pincode CHAR(6); adds a new column to every row.
Related: DDL, Schema, Migration
The ACID property that a transaction's changes happen entirely or not at all. If any step fails, everything done so far is undone, so the database never shows half-finished work.
Example. If the second of two UPDATE statements in a money transfer fails, the first is rolled back.
Related: ACID, Transaction, COMMIT and ROLLBACK
The default index type in most databases: a balanced, sorted tree of keys that supports equality, range and ordering lookups in logarithmic time. Lookups touch only a few disk pages even for huge tables.
Example. An index on orders(order_date) serves WHERE order_date BETWEEN ... and ORDER BY order_date efficiently.
Related: Index, Hash index, Composite index, Clustered index
A contrasting acronym to ACID for many distributed NoSQL systems: Basically Available, Soft state and Eventually consistent. It favours availability and scalability over immediate strict consistency. It describes the trade-off many distributed stores choose.
Example. A shopping site keeps accepting cart updates during a partial outage and reconciles copies afterwards.
Related: ACID, Eventual consistency, CAP theorem, NoSQL
Boyce-Codd normal form: a stricter version of the third form requiring that, for every dependency in the table, the determining columns are a candidate key. It resolves rare anomalies involving overlapping candidate keys.
Example. If teacher determines subject but teacher is not a candidate key of the table, the table violates it and should be split.
Related: Third normal form, Functional dependency, Candidate key
Any minimal set of columns that could uniquely identify every row, meaning no column can be removed without losing uniqueness. A table may have several; one is chosen as the primary key and the rest remain alternatives.
Example. In an employees table, both employee_id and official_email are unique, so each is a candidate key; the team picks employee_id as the primary key.
Related: Primary key, Unique constraint, Functional dependency
A result stating that a distributed data store facing a network partition must choose between consistency (all nodes see the same data) and availability (every request gets an answer). It guides trade-offs in distributed databases.
Example. During a network split, a system may refuse writes to stay consistent, or accept them and let copies diverge.
Related: Eventual consistency, Replication, NoSQL, ACID
In indexing and optimisation, the number of distinct values in a column; in modelling, the count of related rows (one-to-many). The optimiser uses estimated cardinality to judge the size of intermediate results.
Example. A country column with 30 distinct values has low cardinality; user_id in a users table has high cardinality.
Related: Selectivity, Index, Query optimizer, One-to-many relationship
SQL's way of writing if-then-else inside a query. It checks conditions in order and returns the value of the first true branch, or an ELSE value. It can appear in SELECT, WHERE, ORDER BY and inside aggregates.
Example. CASE WHEN amount >= 1000 THEN 'high' ELSE 'low' END labels each order as high or low value.
Related: SELECT, Aggregate function, COALESCE
A rule that attaches a true-or-false condition to a column or row; any insert or update that makes the condition false is rejected. It enforces business limits directly in the database.
Example. CHECK (rating BETWEEN 1 AND 5) rejects a review with rating 7.
Related: Constraint, NOT NULL, Data integrity
An index that determines the physical order in which a table's rows are stored, so the table itself is the index. A table can have only one, and range scans on it are very fast. InnoDB and SQL Server use this.
Example. In MySQL InnoDB, the primary key is the clustered index, so rows sit on disk in primary key order.
Related: Index, B-tree index, Primary key
A function that returns the first argument that is not NULL, scanning left to right. It is used to substitute a default for missing values before displaying or calculating. It is handy for reports.
Example. COALESCE(discount, 0) treats a missing discount as 0, so price - COALESCE(discount, 0) never becomes NULL.
Related: NULL, CASE expression, NOT NULL
A named field in a table that stores one kind of value for every row, such as a price or a date. Each column has a data type and may carry rules like NOT NULL. In relational theory it is called an attribute.
Example. The signup_date column in a users table stores a DATE value for each user.
Related: Table, Row, Constraint, Schema
COMMIT permanently saves all changes made in the current transaction and makes them visible to others; ROLLBACK discards them and restores the earlier state. Together they end a transaction. They are the endpoints of every transaction.
Example. BEGIN; UPDATE ...; ROLLBACK; leaves the data exactly as it was before BEGIN.
Related: Transaction, Savepoint, Atomicity
A named temporary result defined with WITH at the start of a query and used like a table in the rest of it. It splits long queries into readable steps and can be referenced several times.
Example. WITH big AS (SELECT * FROM orders WHERE amount > 1000) SELECT city, COUNT(*) FROM big GROUP BY city;
Related: Subquery, Recursive CTE, View
An index over several columns, sorted by the first column, then the second within it, and so on. It helps queries that filter on a leftmost prefix of its columns, so column order matters.
Example. An index on (city, signup_date) serves WHERE city = 'Pune' AND signup_date > ..., but not WHERE signup_date > ... alone.
Related: Index, B-tree index, Covering index, Selectivity
A cache of open database connections that applications reuse instead of opening a new one per request. Opening connections is slow and each uses server memory, so pools raise throughput and protect the database.
Example. A web service keeps 20 open connections and lends one to each request, returning it afterwards.
Related: Transaction, DBMS, Object-relational mapper
An index that contains every column a query needs, so the database answers it from the index alone without visiting the table. It avoids extra reads but makes the index larger.
Example. With an index on (customer_id, amount), SELECT SUM(amount) WHERE customer_id = 5 reads only the index.
Related: Index, Composite index, Execution plan
A join that pairs every row of one table with every row of another, with no condition, giving m times n rows. It is useful for generating combinations but dangerous when forgotten between large tables.
Example. Cross joining 3 sizes with 4 colours produces 12 product variants.
Related: Join, INNER JOIN, Cartesian product
An organised collection of data stored electronically so that programs can save, find, change and remove information reliably. Unlike loose files, it enforces structure and lets many users and applications work with the same data at once without corrupting it.
Example. A Swiggy-like app keeps restaurants, menus, customers and orders in one place so the app, the delivery tracker and finance reports all read the same facts.
Related: DBMS, Relational database, Table
Database management system: the software that stores data on disk, answers queries, enforces rules, controls who may access what, and recovers after crashes. Applications talk to it instead of touching data files directly. PostgreSQL, MySQL and SQLite are common examples.
Example. Your Python service sends a query to PostgreSQL, which finds the rows, checks permissions and returns results, while also logging changes for crash recovery.
Related: Database, PostgreSQL, SQLite
Data definition language: the SQL statements that create or change the structure of a database, such as CREATE, ALTER and DROP. In many systems they take effect immediately or need special handling in transactions.
Example. CREATE TABLE products (id INT PRIMARY KEY, name TEXT); defines a new table.
Related: DML, ALTER TABLE, Schema, Migration
The statement that removes rows matching a WHERE condition; without one it removes all rows. It is logged row by row, so it can be rolled back and fires triggers, unlike TRUNCATE.
Example. DELETE FROM sessions WHERE expires_at < NOW(); clears expired login sessions.
Related: TRUNCATE, UPDATE, Cascade, ROLLBACK
Deliberately storing duplicated or precomputed data, against normal form rules, to make reads faster by avoiding joins. It trades extra storage and harder updates for speed and is common in reporting and NoSQL designs.
Example. Storing customer_name on every order row so order lists need no join, and accepting that name changes must update many rows.
Related: Normalization, Materialized view, Document database
An anomaly where a transaction reads changes another transaction has not yet committed, which may later be rolled back. It only occurs at the weakest isolation level. It is the weakest form of inconsistency.
Example. Transaction B sees a balance of ₹0 written by A, then A rolls back, and B has acted on a value that never existed.
Related: Isolation level, Non-repeatable read, Phantom read
A keyword that removes duplicate rows from a result so each combination of the selected values appears once. It needs the database to sort or hash the rows, which can be expensive on big results.
Example. SELECT DISTINCT city FROM customers; lists each city once even if thousands of customers live there.
Related: SELECT, GROUP BY, UNION
Data manipulation language: the SQL statements that read and change the data itself, mainly SELECT, INSERT, UPDATE and DELETE. They run inside transactions and can be rolled back. These statements can be undone with a rollback.
Example. UPDATE and DELETE statements are DML, while CREATE INDEX is DDL.
Related: DDL, INSERT, UPDATE, Transaction
A NoSQL database that stores self-contained, flexible documents, usually JSON, that can hold nested fields and arrays. Related data is often embedded in one document instead of joined. MongoDB is a well-known example.
Example. One order document contains customer details and an array of line items, read in a single lookup.
Related: NoSQL, JSONB, Denormalization, Key-value store
A way of designing a database by identifying entities (things like customers), their attributes and the relationships between them, usually drawn as a diagram, before converting it into tables. It helps agree the design with non-technical people.
Example. An ER diagram shows Customer places Order and Order contains Product, which then become three tables plus a link table.
Related: One-to-many relationship, Many-to-many relationship, Schema
A guarantee that if no new updates occur, all copies of the data will eventually hold the same value, although reads may briefly return stale data. It allows high availability and speed in distributed systems.
Example. After you change your profile photo, a friend in another region may still see the old one for a few seconds.
Related: CAP theorem, Replication, BASE
A set operator that returns rows from the first query's result that do not appear in the second, removing duplicates. Oracle calls it MINUS. Order matters. It is the set-difference of two result sets.
Example. SELECT id FROM all_users EXCEPT SELECT id FROM paying_users; lists users who never paid.
Related: UNION, INTERSECT, Anti-join
The step-by-step strategy the database chooses to run a query: which tables are read in what order, which indexes and join methods are used, and estimated or actual costs and row counts. Reading it is the main tuning skill.
Example. A plan showing a sequential scan on a 10 million row table suggests a missing index.
Related: EXPLAIN, Query optimizer, Full table scan
A predicate that is true when its subquery returns at least one row. It stops at the first match, so it is efficient for checking presence, and it is safe with NULL, unlike NOT IN.
Example. WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) keeps customers who have placed an order.
Related: Subquery, IN operator, Anti-join, Correlated subquery
A command that shows the execution plan for a query without, or with (EXPLAIN ANALYZE), running it. The output lists steps, estimated costs and row counts, and with ANALYZE the real timings.
Example. EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42; reveals whether an index or full scan was used.
Related: Execution plan, Query optimizer, Index
The first level of normalisation: every column holds a single, indivisible value and every row is unique, with no repeating groups or lists packed in one cell. It is the base every later form builds on.
Example. A phone_numbers column containing '98xxx, 99xxx' breaks it; move the numbers to a separate table with one per row.
Related: Normalization, Second normal form, Third normal form
A column in one table whose values must match a primary key (or unique) value in another table, or be NULL. The database rejects rows that point to nothing. It expresses relationships and protects data from becoming inconsistent.
Example. orders.customer_id references customers.customer_id, so you cannot insert an order for customer 9999 if that customer does not exist.
Related: Primary key, Referential integrity, Join, One-to-many relationship
A join that keeps all rows from both tables, matching where it can and filling the other side with NULL where it cannot. It is useful for reconciling two lists to see what is in one, the other or both.
Example. Comparing this month's and last month's subscriber lists to find who joined, who left and who stayed.
Related: INNER JOIN, LEFT JOIN, NULL
A read strategy where the database examines every row of a table to find matches. It is fine for small tables or when most rows are needed, but costly when a small fraction is wanted and an index could help.
Example. Without an index on email, looking up one user among 5 million rows reads all 5 million.
Related: Index, Execution plan, Selectivity
A relationship where the value of one set of columns determines the value of another, written A -> B. Normal forms are defined in terms of these dependencies. Normalisation theory is built on these.
Example. pincode -> city means each pincode always maps to one city.
Related: Normalization, Candidate key, Third normal form
A database that stores entities as nodes and relationships as edges, both with properties, and is optimised for traversing connections. It suits questions about paths and networks that need many joins in SQL. Neo4j is an example.
Example. Finding friends of friends who like the same film by following edges instead of joining a table repeatedly.
Related: NoSQL, Recursive CTE, Self join
A clause that collects rows sharing the same value in the listed columns into groups, so an aggregate function can be computed for each group. Every selected column must be either grouped or aggregated.
Example. SELECT city, COUNT(*) FROM customers GROUP BY city; returns one row per city with its customer count.
Related: Aggregate function, HAVING, SELECT, Window function
A clause that filters groups after aggregation, using conditions on aggregate results. WHERE cannot do this because it runs before grouping. Use WHERE for row filters and HAVING for group filters.
Example. GROUP BY city HAVING COUNT(*) > 100 keeps only cities with more than 100 customers.
Related: WHERE, GROUP BY, Aggregate function
A condition that is true when a value equals any item in a given list or in the result of a subquery. It is shorthand for several OR comparisons. NOT IN behaves unexpectedly if the list contains NULL.
Example. WHERE city IN ('Mumbai','Pune','Nagpur') keeps rows from any of those three cities.
Related: WHERE, Subquery, EXISTS, NULL
A separate structure that lets the database find rows by column value without scanning the whole table, like a book's index. It speeds up reads but takes space and slows writes, since it must be updated with each change.
Example. An index on customers(email) makes looking up one email among millions almost instant.
Related: B-tree index, Composite index, Covering index, Full table scan
A join that combines rows from two tables where the join condition is true and drops rows with no match on the other side. It is the default kind of join and answers questions needing data present in both tables.
Example. orders INNER JOIN customers ON orders.customer_id = customers.id returns only orders whose customer exists.
Related: LEFT JOIN, Join, Foreign key, FULL OUTER JOIN
The statement that adds new rows to a table, giving values for its columns either directly or from a SELECT. Constraints are checked, and omitted columns take defaults or auto-generated values.
Example. INSERT INTO customers (name, city) VALUES ('Meera', 'Kochi');
Related: UPDATE, DELETE, Upsert, DML
A set operator that returns only rows appearing in the results of both queries, with duplicates removed. Columns must match in number and compatible type. It is the set-intersection of two result sets.
Example. Emails in both newsletter_subscribers and app_users, giving people on both lists.
Related: UNION, EXCEPT, INNER JOIN
A setting that controls how much one transaction can see of the uncommitted or newly committed work of others. Stricter levels prevent more anomalies but reduce concurrency. The standard levels are read uncommitted, read committed, repeatable read and serializable.
Example. Under serializable isolation, concurrent transactions behave as if run one after another.
Related: ACID, Dirty read, Phantom read, MVCC
An operation that combines rows from two or more tables into one result by matching values, usually a foreign key against a primary key. It lets normalised data spread over many tables be read back together.
Example. Joining orders to products on product_id lets a report show the product name next to each order line.
Related: INNER JOIN, LEFT JOIN, Foreign key, Normalization
A NoSQL database that works like a giant dictionary: each value is stored and fetched by a unique key, with very fast lookups but limited querying by value. Redis and DynamoDB can be used this way.
Example. Caching a user's session under the key session:8f3a so the app fetches it in microseconds.
Related: NoSQL, Hash index, Document database
Window functions that read a value from an earlier row (LAG) or a later row (LEAD) in the ordered partition, without a self join. They are used for changes between consecutive rows.
Example. amount - LAG(amount) OVER (ORDER BY month) gives month-on-month change in sales.
Related: Window function, Window frame, Self join
A join that keeps every row from the left table and fills the right table's columns with NULL when no match exists. It is used to find items with no related records, by filtering for those NULLs.
Example. customers LEFT JOIN orders ... WHERE orders.id IS NULL lists customers who have never ordered.
Related: INNER JOIN, RIGHT JOIN, NULL, Anti-join
LIMIT caps how many rows a query returns; OFFSET skips a number of rows first. Together they page through results. Large offsets are slow because skipped rows are still read. SQL Server writes this as TOP or OFFSET FETCH.
Example. ORDER BY id LIMIT 10 OFFSET 20 returns rows 21 to 30, the third page of ten.
Related: ORDER BY, Pagination, SELECT
The mechanism by which a database prevents conflicting concurrent access by making transactions wait for a row, page or table to be released. Shared locks allow reading together, exclusive locks allow only one writer.
Example. SELECT ... FOR UPDATE locks the selected rows so no one else changes them until the transaction ends.
Related: Deadlock, MVCC, Optimistic concurrency control, Isolation level
A relationship where rows on each side can relate to many on the other, implemented with a third link table holding a foreign key to each side. A link table is always needed in relational designs.
Example. A student takes many courses and a course has many students, so an enrolments table links them.
Related: Junction table, One-to-many relationship, Composite key
A view whose query result is physically stored and refreshed on demand or on a schedule. Reads are fast because the work is precomputed, but the data can be stale between refreshes.
Example. A materialized view holding daily sales per city, refreshed every night, serves a dashboard without re-scanning millions of orders.
Related: View, Index, Denormalization
A versioned, repeatable script that moves a database schema from one state to another, kept in source control and applied in order across environments. Tools such as Alembic and Flyway automate applying and tracking them.
Example. A migration adds a phone column in version 12, so every developer and the production database reach the same schema.
Related: Schema, ALTER TABLE, Object-relational mapper
Multi-version concurrency control: the database keeps several versions of a row so readers see a consistent snapshot while writers create new versions, meaning readers do not block writers. PostgreSQL and MySQL InnoDB use it.
Example. While a long report reads yesterday's snapshot, new orders keep being inserted without waiting.
Related: Isolation level, Locking, Write-ahead log
A performance trap where code runs one query to get a list, then one more query per item to get related data, giving N+1 queries. It is fixed with a join or by loading related rows in a single batch.
Example. Fetching 100 orders and then querying each order's customer separately makes 101 queries instead of one join.
Related: Object-relational mapper, Join, Query optimizer
An anomaly where a transaction reads the same row twice and gets different values because another transaction committed a change in between. Repeatable read isolation prevents it. It affects a single row read twice.
Example. A report reads a product price as ₹100, another user updates it to ₹120, and the report's second read shows ₹120.
Related: Isolation level, Dirty read, Phantom read
The process of organising tables so each fact is stored once, by splitting data into related tables following rules called normal forms. It reduces duplication and prevents update, insert and delete anomalies.
Example. Storing customer city only in customers, not repeated on every order row, means a change of address needs one update.
Related: First normal form, Third normal form, Denormalization, Functional dependency
A broad name for databases that do not use the traditional relational table model, such as document, key-value, wide-column and graph stores. They often trade strict schemas or joins for flexibility and horizontal scaling.
Example. A product catalogue with varied attributes stored as JSON documents in MongoDB.
Related: Document database, Key-value store, Wide-column store, Graph database
A constraint that forbids a column from holding NULL, so every row must supply a real value. It is used for fields that always must exist, and it makes queries simpler because missing values cannot appear there.
Example. email VARCHAR(255) NOT NULL means inserting a user without an email raises an error.
Related: NULL, Constraint, Column
A special marker meaning a value is missing or unknown, not zero or an empty string. Comparisons with it yield unknown rather than true or false, so you must test it with IS NULL. Aggregates such as AVG ignore it.
Example. A customer who has not given a phone number has phone = NULL; WHERE phone = NULL matches nothing, while WHERE phone IS NULL finds them.
Related: Three-valued logic, COALESCE, NOT NULL
A library that lets code use programming-language objects to read and write database rows, generating SQL behind the scenes. SQLAlchemy and Django ORM are examples. It saves boilerplate but can hide inefficient queries.
Example. In Python, session.query(User).filter_by(city='Pune') becomes a SELECT with a WHERE clause.
Related: N+1 query problem, Migration, SQL injection
A relationship where one row in a table relates to many rows in another, but each of those relates back to only one. It is implemented with a foreign key on the many side.
Example. One customer places many orders; each order belongs to one customer, so orders holds customer_id.
Related: Foreign key, Many-to-many relationship, Entity-relationship model
The clause that sorts a query's result by one or more columns or expressions, ascending by default or descending with DESC. Without it, row order is not guaranteed and may change between runs.
Example. ORDER BY amount DESC, order_date ASC lists biggest orders first, breaking ties with the earliest date.
Related: LIMIT, SELECT, Index
A query written with placeholders for values, which are sent to the database separately from the SQL text, so input is always treated as data and never as code. It blocks injection and allows plan reuse.
Example. cursor.execute('SELECT * FROM users WHERE email = %s', (email,)) keeps user input out of the SQL string.
Related: SQL injection, Prepared statement, Stored procedure
The part of a window definition that splits rows into independent groups, so the window function restarts in each. Unlike GROUP BY, all rows remain in the output. It appears inside the OVER parentheses.
Example. RANK() OVER (PARTITION BY city ORDER BY sales DESC) ranks stores separately inside each city.
Related: Window function, GROUP BY, ROW_NUMBER
An anomaly where the same query run twice inside a transaction returns different sets of rows because another transaction inserted or deleted matching rows in between. It affects sets of rows rather than one row.
Example. Counting orders over ₹1,000 gives 40, then 41 in the same transaction after someone commits a new large order.
Related: Isolation level, Non-repeatable read, Dirty read
A powerful open-source relational database system known for standards compliance, strong transactions and extensibility, including JSON support and extensions. It is widely used in production for web and analytics workloads.
Example. A startup runs its orders, users and payments tables on PostgreSQL.
Related: DBMS, SQLite, JSONB, MVCC
A column, or set of columns, whose values uniquely identify each row in a table and can never be NULL. The database enforces this and usually builds an index on it, so looking up a row by key is fast. Other tables refer to rows through it.
Example. In a customers table, customer_id is the primary key: no two customers share one, and every customer has one.
Related: Foreign key, Candidate key, Surrogate key, Unique constraint
The database component that considers different ways to run a query, such as join orders and index choices, estimates each one's cost using table statistics, and picks the cheapest. It is why the same SQL can run in different ways.
Example. For a join of a huge table and a tiny filtered one, the optimiser starts from the tiny one and uses an index on the huge one.
Related: Execution plan, EXPLAIN, Cardinality, Selectivity
Window functions that number rows by an ordering, giving tied rows the same rank. RANK then skips numbers after a tie (1, 1, 3), while DENSE_RANK does not (1, 1, 2).
Example. Scores 90, 90, 80: RANK gives 1, 1, 3 and DENSE_RANK gives 1, 1, 2.
Related: ROW_NUMBER, Window function, ORDER BY
A common table expression that refers to itself: an anchor query gives starting rows, and a recursive part repeatedly adds rows built from the previous step until nothing new appears. It walks hierarchies such as org charts.
Example. Starting from the CEO row, each step adds employees whose manager_id matches the previous level, producing the whole reporting tree.
Related: Common table expression, Self join, Graph database
The guarantee that every foreign key value points to an existing row in the referenced table. The database blocks or cascades changes that would break this, such as deleting a customer who still has orders.
Example. With ON DELETE RESTRICT, deleting customer 7 fails while orders for customer 7 still exist.
Related: Foreign key, Constraint, Cascade
The formal set of operations on tables that underlies SQL: selection (filter rows), projection (pick columns), union, difference, product and join. Query optimisers rewrite queries using its equivalences. It gives SQL its mathematical foundation.
Example. Selection by city = 'Pune' followed by projection onto name corresponds to SELECT name ... WHERE city = 'Pune'.
Related: Relational database, Join, Query optimizer
A database that stores data as tables of rows and columns, where tables are linked by shared key values rather than nested inside one another. Data is queried with SQL. The model rests on set theory and gives strong guarantees about consistency.
Example. An orders table holds a customer_id that points to a row in a customers table, so customer details are stored once and linked wherever needed.
Related: Table, Primary key, Foreign key, Relational algebra
Copying data from one database server (primary) to others (replicas), continuously. It gives fault tolerance and lets read queries be spread over replicas, though replicas can lag behind. It is also the basis of many backup strategies.
Example. Reports run on a read replica so they do not slow down the main app database.
Related: Write-ahead log, Eventual consistency, Sharding
One record in a table, holding one value for each column and describing a single entity or event. In relational theory it is called a tuple. Rows have no inherent order unless a query requests one.
Example. (101, 'Asha Rao', 'Chennai') is one row in a customers table with columns id, name and city.
Related: Table, Column, Primary key
A window function that gives each row a unique sequence number 1, 2, 3 within its partition, following the ORDER BY of the window. Ties get different numbers. It is the standard way to pick the top row per group or remove duplicates.
Example. ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) = 1 selects each customer's latest order.
Related: RANK and DENSE_RANK, Window function, PARTITION BY
The blueprint of a database: which tables exist, their columns and data types, keys, constraints and relationships. In some systems a schema is also a named namespace that groups tables. Changing it later requires care because live data must stay valid.
Example. The schema says orders has order_id (integer, primary key), customer_id (foreign key) and amount (numeric, NOT NULL).
Related: Table, Constraint, Migration, Normalization
A table is in this form if it satisfies the first form and every non-key column depends on the whole primary key, not just part of a composite key. It removes partial dependencies.
Example. In order_items(order_id, product_id, product_name), product_name depends only on product_id, so it belongs in products.
Related: First normal form, Third normal form, Functional dependency, Composite key
The SQL statement that reads data. It names the columns or expressions to return, the table(s) to read from, and optional filters, grouping and ordering. It never changes stored data.
Example. SELECT name, price_inr FROM products; returns just those two columns for every product.
Related: FROM clause, WHERE, ORDER BY, GROUP BY
The fraction of a table's rows that a condition matches. A highly selective condition matches few rows, so an index helps a lot; a low-selectivity one matches many, and a scan may be cheaper.
Example. A filter on a unique PAN-like id matches one row in a million and is highly selective, while gender = 'F' matches about half.
Related: Cardinality, Index, Query optimizer
A join of a table with itself, using aliases to treat it as two copies. It is used when rows relate to other rows of the same table, such as employees and their managers.
Example. employees e JOIN employees m ON e.manager_id = m.id lists each employee beside their manager's name.
Related: Join, Alias, Recursive CTE
Splitting a large dataset across several database servers, each holding a subset of rows chosen by a shard key such as user id. It scales writes and storage beyond one machine but complicates joins and transactions across shards.
Example. Users with ids 1 to 1 million go to server A and the next million to server B.
Related: Partitioning, Replication, CAP theorem
Structured Query Language: the standard declarative language for defining, querying and changing data in relational databases. You describe the result you want, and the database engine decides how to compute it. Dialects differ slightly between products, but the core is shared.
Example. SELECT name FROM customers WHERE city = 'Pune'; asks for the names of customers in Pune without saying how to search.
Related: SELECT, DDL, DML, Query optimizer
An attack where malicious text supplied as input is interpreted as part of a SQL statement, letting an attacker read or change data. It is prevented by parameterised queries, not by manual string building.
Example. Typing ' OR '1'='1 into a login box that concatenates strings could log in without a password.
Related: Parameterised query, Stored procedure, Object-relational mapper
A small, serverless relational database that lives in a single file and runs inside the application process. It needs no setup and ships with Python, making it ideal for learning, tests and small apps.
Example. Python's sqlite3 module can open practice.db and run SQL with no server installed.
Related: DBMS, PostgreSQL, Relational database
A named block of SQL and control logic saved inside the database and run by calling it. It centralises rules, reduces network round trips and can restrict direct table access.
Example. CALL apply_monthly_interest(); runs a saved routine that updates all savings accounts.
Related: Trigger, Transaction, SQL injection
A query nested inside another query, in the WHERE, FROM or SELECT part. The inner query's result feeds the outer one. It lets you break a problem into steps without creating temporary tables.
Example. WHERE amount > (SELECT AVG(amount) FROM orders) keeps orders above the average value.
Related: Correlated subquery, Common table expression, EXISTS, IN operator
The basic container in a relational database: a named grid of rows and columns where every row describes one item of the same kind and every column holds one attribute of it. Its structure is fixed by a schema, and each column has a data type.
Example. A products table with columns product_id, name and price_inr, holding one row per item sold.
Related: Row, Column, Schema, Primary key
A table meets this form if it satisfies the second form and non-key columns depend only on the key, not on other non-key columns. It removes transitive dependencies. It is the usual target in business database design.
Example. If employees stores dept_id and dept_name, dept_name depends on dept_id, so it should live in a departments table.
Related: Second normal form, BCNF, Normalization, Functional dependency
The logic SQL uses for conditions, where the result can be true, false or unknown, because NULL makes some comparisons undecidable. A WHERE clause keeps only rows whose condition is true, dropping both false and unknown.
Example. NULL = NULL evaluates to unknown, and NOT (unknown) is still unknown, so WHERE NOT (x = 5) silently skips rows where x is NULL.
Related: NULL, WHERE, COALESCE
A group of database operations treated as one indivisible unit: either all changes take effect together, or none do. It protects against partial updates caused by errors, crashes or concurrent users.
Example. A UPI-style transfer subtracts ₹500 from one account and adds it to another inside one transaction, so money is never lost halfway.
Related: ACID, COMMIT and ROLLBACK, Isolation level, Atomicity
A statement that quickly empties an entire table while keeping its structure. It skips row-by-row logging, so it is much faster than DELETE for large tables, and in many systems it cannot be filtered by condition.
Example. TRUNCATE TABLE staging_orders; clears a loading table before the next batch arrives.
Related: DELETE, DDL, Transaction
A set operator that stacks the results of two queries with the same column layout into one, removing duplicates. UNION ALL keeps duplicates and is faster because it skips the duplicate check.
Example. Combining customers from a Mumbai table and a Delhi table into one list with UNION ALL.
Related: INTERSECT, EXCEPT, DISTINCT
A rule that no two rows may hold the same value (or combination of values) in the chosen column(s). Unlike a primary key, the column may allow NULL in many systems, and a table can have several such rules.
Example. UNIQUE(email) ensures two accounts cannot register with the same email address.
Related: Primary key, Candidate key, Index, Constraint
The statement that changes values in existing rows. A WHERE clause selects which rows; without it, every row changes. It is good practice to run the matching SELECT first. It runs inside a transaction and can be rolled back.
Example. UPDATE products SET price_inr = price_inr * 1.05 WHERE category = 'snacks'; raises snack prices by 5 percent.
Related: INSERT, DELETE, WHERE, Transaction
A stored query that behaves like a virtual table. It holds no data of its own; each use runs the underlying query. Views simplify complex joins, hide sensitive columns and give stable interfaces.
Example. CREATE VIEW active_customers AS SELECT * FROM customers WHERE status = 'active'; lets analysts query it like a table.
Related: Materialized view, SELECT, Common table expression
The clause that filters rows before any grouping, keeping only those for which its condition is true. It can combine comparisons with AND, OR and NOT. Filtering early reduces the work later steps do.
Example. SELECT * FROM orders WHERE amount > 500 AND city = 'Delhi'; keeps only large Delhi orders.
Related: HAVING, SELECT, Three-valued logic, Index
The range of rows around the current row that a window function uses, set with ROWS or RANGE between bounds like UNBOUNDED PRECEDING and CURRENT ROW. It turns SUM or AVG into running totals and moving averages.
Example. AVG(sales) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) gives a seven-day moving average.
Related: Window function, LAG and LEAD, Aggregate function
A function that computes a value for each row using a set of related rows, its window, without collapsing rows the way GROUP BY does. Examples are ranking, running totals and previous-row lookups, written with OVER.
Example. SUM(amount) OVER (PARTITION BY city) shows each order beside its city's total.
Related: PARTITION BY, ROW_NUMBER, LAG and LEAD, Window frame
A durability technique where every change is first appended to a sequential log on disk before the data pages are changed. After a crash, the database replays the log to restore committed work. It also underpins replication.
Example. The server crashes after a commit but before data pages are written; on restart, the log is replayed and the committed change reappears.
Related: ACID, Replication, Transaction