DBMS & SQL Cheatsheet

Database & DBMS

A database is an organized collection of interrelated data representing some aspect of the real world, stored in a format that can be easily accessed. A DBMS (Database Management System) is the software used to create, manage, and query databases while handling security, concurrency, and recovery. Applications request data from the DBMS, and the DBMS retrieves it from storage.

RDBMS : A Relational DBMS stores data in tables (relations) made of rows (records/tuples) and columns (attributes), with relationships between tables. Examples: MySQL, PostgreSQL, Oracle, SQL Server.
Non-Relational (NoSQL) : Databases that do not store data in tables, such as document, key-value, and graph databases. Example: MongoDB.
SQL vs MySQL : SQL is the language used to interact with relational databases; MySQL is one RDBMS that understands SQL. SQL keywords are not case sensitive.

Why DBMS over File Systems -

DBMSs were developed to solve the problems of storing data in plain files:

Data redundancy and inconsistency (the same data duplicated in multiple files, going out of sync).
Difficulty in accessing data (a new program needed for every new query).
Data isolation (data scattered across multiple files and formats).
Integrity problems (no central place to enforce rules like "balance cannot be negative").
Atomicity of updates (a crash mid-update leaves data half-changed).
No safe concurrent access by multiple users.
Weak security (no fine-grained control over who sees what).

ER Model

An ER (Entity Relationship) diagram is a conceptual model that graphically represents the logical structure of a database: its entities, their attributes, and the relationships and constraints among them. It is drawn before creating the actual tables.

Entity Sets -

Strong Entity Set : Has enough attributes to uniquely identify each entity, meaning a primary key exists (shown by underlining the key attribute). Example: a Student identified by Roll_no.
Weak Entity Set : Cannot uniquely identify its entities on its own; it has no primary key, only a partial key called a discriminator (underlined with a dashed line) and depends on a strong entity set.

Types of Attributes -

Simple : Cannot be divided further. Example: Age.
Composite : Composed of several simple attributes. Example: Name (first, last), Address.
Multi-Valued : Can hold more than one value for an entity. Example: Mobile No, Email ID.
Derived : Can be computed from other attributes. Example: Age derived from DOB.
Key : Uniquely identifies an entity in the set. Example: Roll No.

Relationships -

A relationship is an association among entities. Based on how many entity sets participate: unary (one), binary (two, the most common), ternary (three), and n-ary (n entity sets).

Cardinality Constraints -

One-to-One : An entity in A relates to at most one entity in B, and vice versa. Example: person and passport.
One-to-Many : An entity in A can relate to many entities in B, but each entity in B relates to at most one in A. Example: department and employees.
Many-to-One : The reverse of one-to-many. Example: many students to one city.
Many-to-Many : Entities on both sides can relate to any number on the other side. Example: students and courses.

Keys

A key is a set of attributes that can identify each tuple (row) uniquely in a relation (table).

Super Key : Any set of attributes that uniquely identifies each tuple. It may contain extra, unnecessary attributes.
Candidate Key : A minimal super key: remove any attribute and it stops being unique. A relation can have several candidate keys.
Primary Key : The candidate key the designer chooses to identify rows. It must be unique and NOT NULL, and there is only one per table.
Alternate Key : The candidate keys left over after choosing the primary key.
Foreign Key : A column whose values refer to the primary key of another table, linking the two. The table holding the primary key is the referenced relation; the one holding the foreign key is the referencing relation. Foreign keys can repeat and can be NULL.
Composite Key : A primary key made of two or more attributes together.
Unique Key : Enforces uniqueness like a primary key but may allow NULL, and a table can have several unique keys.

Cascading for Foreign Keys -

ON DELETE CASCADE : Deleting a parent row automatically deletes the child rows that reference it.
ON UPDATE CASCADE : Updating the referenced key automatically updates the referencing rows in the child table.

Constraints

Constraints are rules imposed on the data to keep the database correct and consistent.

Relational Model Constraints -

Domain Constraint : Every attribute value must be an atomic value from its defined domain (type and allowed range).
Tuple Uniqueness Constraint : All tuples in a relation must be unique.
Key Constraint : Primary key values must be unique and never NULL.
Entity Integrity Constraint : No part of a primary key may be NULL.
Referential Integrity Constraint : Every foreign key value must either exist as a primary key value in the referenced relation or be NULL.

SQL Constraints -

NOT NULL : The column cannot store NULL.
UNIQUE : All values in the column must be different.
PRIMARY KEY : UNIQUE plus NOT NULL; one per table.
FOREIGN KEY : Prevents actions that would break links between tables.
DEFAULT : Sets a default value when none is provided.
CHECK : Limits the values allowed, such as CHECK (age >= 18).

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    salary DECIMAL(10,2) DEFAULT 0,
    dept_id INT,
    CHECK (salary >= 0),
    FOREIGN KEY (dept_id) REFERENCES departments(id)
);

Functional Dependency & Decomposition

A functional dependency α → β holds in a relation if any two tuples with the same value of attribute set α also have the same value of attribute set β. In other words, α determines β. Example: Roll_no → Name.

Trivial FD : X → Y where Y is a subset of X. Always holds. Example: (Roll_no, Name) → Name.
Non-Trivial FD : X → Y where at least one attribute of Y is not in X. These are the dependencies that matter for design.
Closure of an Attribute Set : The set of all attributes that can be functionally determined from a given attribute set. Used to find candidate keys.

Decomposition -

Decomposition is breaking one relation into two or more sub-relations, done during normalization. A good decomposition must satisfy two properties:

Lossless Join : Joining the sub-relations back gives exactly the original relation, so no information is lost: R1 ⋈ R2 ⋈ ... ⋈ Rn = R. If the join produces extra spurious tuples (⊃ R), the decomposition is lossy.
Dependency Preservation : Every functional dependency of the original relation still holds in (or can be checked from) the sub-relations.

Normalization

Normalization is the process of organizing a database to reduce redundancy and ensure data integrity through lossless decomposition. Each normal form builds on the previous one.

First Normal Form (1NF) : Every cell holds a single atomic value; no multi-valued or repeating attributes. A column like "Phone1, Phone2" in one cell violates 1NF.
Second Normal Form (2NF) : In 1NF, plus no partial dependency: no non-prime attribute may depend on only a part of a candidate key. Only relevant when the key is composite.
Third Normal Form (3NF) : In 2NF, plus no transitive dependency: a non-prime attribute must not depend on another non-prime attribute (A → B where A is not a super key and B is non-prime).
Boyce-Codd Normal Form (BCNF) : In 3NF, plus for every non-trivial dependency A → B, A must be a super key. The stricter version of 3NF.

Memory aid: every non-key attribute must depend on "the key (1NF), the whole key (2NF), and nothing but the key (3NF)".

Transactions & ACID

A transaction is a single logical unit of work made of a set of operations, such as transferring money (debit one account, credit another). Its basic operations are Read(A), which loads a value into the main-memory buffer, and Write(A), which writes the updated value from the buffer back to the database.

Transaction States -

Active : Instructions are executing; changes live only in the buffer.
Partially Committed : The last instruction has executed, but changes are still in the buffer, not yet on disk.
Committed : All changes are safely stored in the database; the transaction is final.
Failed : A failure occurred during execution and the transaction cannot continue.
Aborted : All changes made by the failed transaction have been rolled back.
Terminated : The end of the life cycle, reached after commit or abort.

ACID Properties -

Atomicity : A transaction happens completely or not at all; never partially.
Consistency : Integrity constraints hold before and after the transaction, so the database moves from one valid state to another.
Isolation : Concurrent transactions do not interfere; the result is the same as if they ran one after another.
Durability : Once committed, changes are permanent on disk and survive any failure.

Schedules & Serializability

A schedule is the order in which the operations of multiple transactions execute.

Serial Schedule : Transactions run one after another with no overlap. Always consistent, recoverable, cascadeless, and strict, but slow.
Non-Serial Schedule : Operations of transactions are interleaved for concurrency. Faster, but not guaranteed to be consistent or recoverable.

Serializability -

A non-serial schedule is serializable if it is equivalent to some serial schedule, which guarantees it keeps the database consistent.

Conflict Serializable : The schedule can be converted to a serial one by swapping its non-conflicting operations.
View Serializable : The schedule is view-equivalent to some serial schedule (a weaker, broader condition).

Recoverability -

Irrecoverable Schedule : A transaction dirty-reads from an uncommitted transaction and commits before it; if the source then rolls back, the damage cannot be undone.
Recoverable Schedule : The reader's commit is delayed until the transaction it read from commits or rolls back.
Cascading Schedule : One transaction's failure forces a chain of dependent transactions to roll back.
Cascadeless Schedule : A transaction may not read a value until the transaction that wrote it has committed or aborted, preventing cascading rollbacks.
Strict Schedule : A transaction may neither read nor write a value until the last writer has committed or aborted. The safest and most common in practice.

Relational Algebra

Relational algebra is a procedural query language that takes relations as input and produces a relation as output. It is the theoretical foundation of SQL. For two relations with m and n rows:

Operator	Name	Meaning
σ	Selection	Selects rows that satisfy a condition (like WHERE)
∏	Projection	Selects specific columns (like SELECT col1, col2)
X	Cross Product	Pairs every row of R1 with every row of R2, giving m*n rows
U	Union	Tuples in R1 or R2; rows between max(m,n) and m+n
−	Minus	Tuples in R1 but not in R2; rows between m-n and m
ρ	Rename	Renames a relation or its attributes
∩	Intersection	Tuples in both R1 and R2; at most min(m,n) rows
⋈c	Conditional Join	Cross product followed by selection on a condition
⋈	Equi Join	Conditional join using only equality conditions
⋈	Natural Join	Equi join on all common attributes, duplicates removed. With no common attribute it equals the cross product
⟕	Left Outer Join	All tuples of R kept; unmatched ones get NULL for S's attributes
⟖	Right Outer Join	All tuples of S kept; unmatched ones get NULL for R's attributes
⟗	Full Outer Join	All tuples of both R and S kept, with NULLs where unmatched
/	Division	A/B returns tuples of A associated with every tuple of B (attributes of B must be a proper subset of A's)

Indexing & File Structures

An index is an ordered auxiliary structure that speeds up data retrieval, like the index of a book: instead of scanning every block, the DBMS looks up the key and follows a pointer to the data block.

Types of Indexes -

Primary Index : An ordered file of fixed-length records with two fields: the primary key of the data file and a pointer to its data block. Average block accesses with the index are about log2(Bi) + 1, where Bi is the number of index blocks.
Clustering Index : Created on a data file that is physically ordered on a non-key field (the clustering field).
Secondary Index : Provides an additional access path on a field for which primary access already exists.

B-Trees and B+ Trees -

B-Tree : A balanced multi-way search tree where every node stores keys along with data pointers (to a block or record). For a tree of order P (the maximum number of children): the root has between 2 and P children, internal nodes have between ⌈P/2⌉ and P children, and internal nodes hold between ⌈P/2⌉-1 and P-1 keys.
B+ Tree : Stores data pointers only in the leaf nodes; internal nodes hold just keys for navigation, so leaf and non-leaf nodes have different orders (non-leaf higher). This makes the tree shallower and searching faster, and the linked leaves make range queries efficient. Most database indexes are B+ trees.

SQL Basics

SQL (Structured Query Language) is the standard language for storing, manipulating, and retrieving data in relational databases. It covers the CRUD operations: Create, Read, Update, Delete.

Types of SQL Commands -

DDL (Data Definition Language) : Defines and changes structure: CREATE, ALTER, DROP, TRUNCATE, RENAME.
DQL (Data Query Language) : Retrieves data: SELECT.
DML (Data Manipulation Language) : Modifies data: INSERT, UPDATE, DELETE.
DCL (Data Control Language) : Manages permissions: GRANT, REVOKE.
TCL (Transaction Control Language) : Manages transactions: COMMIT, ROLLBACK, SAVEPOINT.

Common Data Types -

Data Type	Description
`CHAR(n)`	Fixed-length string (0-255 characters)
`VARCHAR(n)`	Variable-length string up to n; uses only needed space, generally preferred
`BLOB`	Binary large object (files, images)
`TINYINT`	Integer, -128 to 127
`INT`	Integer, about -2.1 billion to 2.1 billion
`BIGINT`	Very large integers
`FLOAT / DOUBLE`	Approximate decimal numbers (up to 23 / 53 digits precision)
`DECIMAL(p,s)`	Exact decimal, used for money
`BOOLEAN`	0 or 1
`DATE`	YYYY-MM-DD
`TIME`	HH:MM:SS
`YEAR`	4-digit year, 1901 to 2155

Add UNSIGNED to numeric types that only store positive values, which doubles the positive range, e.g., TINYINT UNSIGNED stores 0 to 255.

DDL Commands

DDL commands define and manage the structure of databases and their objects (tables, indexes, constraints).

Database Level -

CREATE DATABASE IF NOT EXISTS db_name;
DROP DATABASE IF EXISTS db_name;
SHOW DATABASES;
USE db_name;
SHOW TABLES;

Table Level -

-- create
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2)
);

-- delete table with structure
DROP TABLE employees;

-- delete all data, keep structure
TRUNCATE TABLE employees;

ALTER TABLE -

-- add column
ALTER TABLE employees ADD COLUMN email VARCHAR(100);

-- drop column
ALTER TABLE employees DROP COLUMN email;

-- change datatype/constraint
ALTER TABLE employees MODIFY COLUMN name VARCHAR(80) NOT NULL;

-- rename column (with new definition)
ALTER TABLE employees CHANGE COLUMN name full_name VARCHAR(80);

-- rename table
ALTER TABLE employees RENAME TO staff;

Indexes -

CREATE INDEX idx_emp_name ON employees (name);
DROP INDEX idx_emp_name;

DROP vs TRUNCATE vs DELETE : DROP removes the table itself; TRUNCATE removes all rows but keeps the structure (no WHERE, faster, usually cannot be rolled back); DELETE removes rows one by one, supports WHERE, and can be rolled back.

DML Commands

DML commands add, change, and remove the data inside tables.

INSERT -

INSERT INTO employees (name, salary)
VALUES ('John Doe', 50000),
       ('Jane Roe', 60000);

-- without column list, values must match column order
INSERT INTO employees VALUES (1, 'John Doe', 50000);

UPDATE -

UPDATE employees
SET salary = 55000, name = 'John D.'
WHERE id = 1;

DELETE -

DELETE FROM employees WHERE id = 1;

-- without WHERE, deletes ALL rows (structure remains)
DELETE FROM employees;

Always double-check the WHERE clause on UPDATE and DELETE; without it, the change applies to every row in the table. MERGE (upsert) inserts a row or updates it if it already exists.

Querying Data

The SELECT statement retrieves data and is the foundation of querying.

SELECT col1, col2 FROM table_name;   -- specific columns
SELECT * FROM table_name;            -- all columns
SELECT DISTINCT country FROM customers;   -- unique values
SELECT name AS "Full Name" FROM employees; -- rename output column

WHERE Clause -

Filters rows using conditions built from operators: comparison (=, != or <>, >, >=, <, <=), logical (AND, OR, NOT), and arithmetic.

SELECT * FROM customers WHERE country = 'Mexico';
SELECT * FROM customers
WHERE country = 'Germany' AND (city = 'Berlin' OR city = 'Munich');

IN : Shorthand for multiple ORs: WHERE country IN ('Germany', 'France', 'UK'). Also works with a subquery.
BETWEEN : Inclusive range: WHERE price BETWEEN 10 AND 20. Works with numbers, text, and dates.
IS NULL / IS NOT NULL : NULL cannot be tested with =; use WHERE email IS NULL.

LIKE Patterns -

% matches any number of characters (including zero); _ matches exactly one character.

Pattern	Matches
`LIKE 'a%'`	Values starting with "a"
`LIKE '%a'`	Values ending with "a"
`LIKE '%or%'`	Values containing "or" anywhere
`LIKE '_r%'`	"r" in the second position
`LIKE 'a_%'`	Starts with "a", at least 2 characters
`LIKE 'a%o'`	Starts with "a", ends with "o"

Sorting & Limiting -

-- sort (ASC is default; NULLs sort first in ASC)
SELECT * FROM customers ORDER BY country ASC, name DESC;

-- limit rows (varies by database)
SELECT * FROM customers LIMIT 3;                  -- MySQL / PostgreSQL
SELECT TOP 3 * FROM customers;                    -- SQL Server
SELECT * FROM customers FETCH FIRST 3 ROWS ONLY;  -- standard
SELECT * FROM customers WHERE ROWNUM <= 3;        -- Oracle

Aggregates & Grouping

Aggregate functions perform a calculation over a set of rows and return a single value: COUNT() (number of rows), SUM(), AVG(), MAX(), and MIN().

SELECT COUNT(*) FROM products;
SELECT MIN(price) AS cheapest, MAX(price) AS costliest FROM products;
SELECT AVG(price) FROM products;
SELECT SUM(quantity) FROM order_details;

GROUP BY -

Groups rows that share a value into summary rows, almost always combined with an aggregate function. Grouping by multiple columns creates hierarchical groups.

-- customers per country
SELECT country, COUNT(customer_id)
FROM customers
GROUP BY country
ORDER BY COUNT(customer_id) DESC;

HAVING -

WHERE cannot use aggregate functions, so HAVING exists to filter groups after grouping. WHERE filters rows before grouping and runs first.

SELECT country, COUNT(customer_id)
FROM customers
GROUP BY country
HAVING COUNT(customer_id) > 5;

General Clause Order -

SELECT column(s)
FROM table_name
WHERE condition        -- filter rows
GROUP BY column(s)     -- group them
HAVING condition       -- filter groups
ORDER BY column(s)     -- sort result
LIMIT n;               -- cap result

Joins

A join combines rows from two or more tables based on a related column between them, usually a foreign key matching a primary key.

INNER JOIN : Returns only the rows with matching values in both tables. Non-matching rows are dropped.
LEFT (OUTER) JOIN : Returns all rows from the left table plus matches from the right; where there is no match, the right table's columns are NULL.
RIGHT (OUTER) JOIN : The mirror image: all rows from the right table, NULLs for unmatched left columns.
FULL (OUTER) JOIN : All rows from both tables, with NULLs on whichever side has no match. MySQL has no FULL JOIN keyword; emulate it with LEFT JOIN ... UNION ... RIGHT JOIN.
CROSS JOIN : Every row of the first table paired with every row of the second (Cartesian product); no join condition, m*n rows.
SELF JOIN : A table joined with itself using two aliases, used for hierarchical data such as employees and their managers.

-- inner join
SELECT c.customer_name, o.product
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

-- left join
SELECT c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

-- self join: employee with their manager
SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;

Exclusive Joins : A left-exclusive join (rows only in the left table) is a LEFT JOIN filtered with WHERE right_table.key IS NULL; the right-exclusive version mirrors it with RIGHT JOIN.

Set Operations

Set operations combine the result sets of two or more SELECT queries, stacking rows vertically. Each SELECT must return the same number of columns, in the same order, with compatible data types (unlike joins, which combine columns side by side based on a condition).

UNION : Combines both result sets and removes duplicates.
UNION ALL : Same, but keeps duplicates. Faster since no de-duplication happens.
INTERSECT : Returns only the rows present in both result sets.
EXCEPT (MINUS in Oracle) : Returns rows in the first result set that are not in the second.

SELECT city FROM customers
UNION
SELECT city FROM suppliers
ORDER BY city;

Subqueries & Views

A subquery (nested or inner query) is a query inside another query: the inner query runs first and its result feeds the outer query. Subqueries appear in WHERE, FROM, and HAVING clauses and are used for filtering, comparison, and calculation.

-- students who scored above the class average
SELECT name, marks
FROM students
WHERE marks > (SELECT AVG(marks) FROM students);

-- with IN
SELECT * FROM customers
WHERE country IN (SELECT country FROM suppliers);

-- in FROM: max marks among Delhi students
SELECT MAX(marks)
FROM (SELECT * FROM students WHERE city = 'Delhi') AS delhi_students;

Subqueries vs Joins : Subqueries break a complex task into steps and are easier to read for simple cases; joins are generally more efficient for combining data from multiple tables at scale.

Views :

A view is a virtual table defined by a stored SELECT statement. It holds no data of its own; the database re-runs the query each time the view is used, so a view always shows up-to-date data. Views simplify complex queries and restrict which columns or rows users can see.

CREATE VIEW high_earners AS
SELECT name, salary FROM employees WHERE salary > 80000;

SELECT * FROM high_earners;
DROP VIEW high_earners;

DCL & TCL

DCL (Data Control Language) -

DCL manages access rights and permissions, ensuring only authorized users can read or modify data.

-- give a user SELECT permission on a table
GRANT SELECT ON employees TO analyst;

-- take it back
REVOKE SELECT ON employees FROM analyst;

TCL (Transaction Control Language) -

TCL groups DML statements into transactions so related changes succeed or fail together.

COMMIT : Permanently saves all changes made since the transaction began.
ROLLBACK : Undoes all changes made in the current transaction, typically after an error.
SAVEPOINT : Marks a named point inside a transaction so you can roll back to it without undoing everything before it.

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 123;
SAVEPOINT before_credit;

UPDATE accounts SET balance = balance + 100 WHERE id = 456;
-- error here?
ROLLBACK TO before_credit;   -- first update still applied

COMMIT;                      -- make it permanent