SQL & Databases Interview Questions and Answers

The BETWEEN operator selects values within an inclusive range (numbers, text strings, or dates):

SELECT product_id, name, price 
FROM products 
WHERE price BETWEEN 100 AND 500;

### Key Semantics:
BETWEEN low AND high is mathematically equivalent to:
price >= 100 AND price <= 500 (both lower and upper endpoints are included).

To exclude boundaries, use explicit relational operators:
price > 100 AND price < 500.

SELECT * FROM Sales 
WHERE Date BETWEEN '2017-12-01' AND '2018-01-01';

Use ISO-8601 format (YYYY-MM-DD) to avoid locale issues.

To select records alphabetically between two string boundaries, use the BETWEEN operator:

SELECT * 
FROM Customers 
WHERE LastName BETWEEN 'Brooks' AND 'Gray' 
ORDER BY LastName ASC;

### Collation Warning:
String range ordering is governed by the database table's collation (e.g. whether uppercase letters precede lowercase, or whether accented characters are sorted together). Furthermore, a name like 'Gray' will match, but 'Grayson' will not because 'Grayson' is alphabetically greater than `'Gray'"".

Checks if a value matches any in a list. It's a shorthand for multiple ORs and often more readable.

-- These are equivalent:
WHERE state IN ('CA', 'NY', 'TX')
WHERE state = 'CA' OR state = 'NY' OR state = 'TX'

IN can also contain a subquery: WHERE id IN (SELECT customer_id FROM orders).

Converts to uppercase. LOWER to lowercase. INITCAP (Oracle/PostgreSQL) capitalizes first letter of each word.

SELECT UPPER('hello'); -- HELLO

To round a numeric value up to the next nearest integer, use the CEIL() (or CEILING()) function:

SELECT CEIL(25.01);   -- Returns 26
SELECT CEIL(25.00);   -- Returns 25
SELECT CEIL(-25.80);  -- Returns -25

### Related Rounding Functions:

• FLOOR(x): Rounds down towards negative infinity (FLOOR(25.99) -> 25).
• ROUND(x, d): Rounds to d decimal places following standard mathematical rounding (ROUND(25.5) -> 26).
• TRUNCATE(x, d): Truncates digits past d decimal places without rounding.

In MySQL, you retrieve the current date (year, month, day) without the time component using CURDATE() or CURRENT_DATE:

SELECT CURDATE();        -- Example: '2026-09-20'
SELECT CURRENT_DATE();   -- Standard synonym

### Comparison Across Engines:

• MySQL: CURDATE() or CURRENT_DATE()
• PostgreSQL: CURRENT_DATE
• SQL Server: CAST(GETDATE() AS DATE)
• Oracle: TRUNC(SYSDATE)

To retrieve both date and time, use NOW() or CURRENT_TIMESTAMP.

The keyword and aggregate function used to retrieve the maximum value of a column is MAX():

SELECT MAX(salary) AS highest_salary 
FROM employees;

### With Grouping:

SELECT department_id, MAX(salary) AS top_dept_salary 
FROM employees 
GROUP BY department_id;

If an index exists on the target column, the database engine can find MAX() in $O(1)$ time by reading the last entry of the B-tree index.

COUNT(). COUNT(*) counts rows. COUNT(column) counts non-NULL values.

SELECT COUNT(*) FROM orders;           -- Total orders
SELECT COUNT(shipped_date) FROM orders; -- Only shipped orders

Aggregate functions perform a computation on a set of values across multiple rows and return a single summarizing value:

### Core Standard Aggregate Functions:

1. COUNT(): Returns the number of rows or non-null values.
2. SUM(): Computes the mathematical sum of numeric values.
3. AVG(): Computes the arithmetic mean of numeric values (automatically ignores NULLs).
4. MIN(): Returns the minimum value (numeric, string, or date).
5. MAX(): Returns the maximum value.

### Statistical & String Aggregates:

• GROUP_CONCAT() (MySQL) / STRING_AGG() (PostgreSQL/SQL Server): Concatenates strings from a group.
• STDDEV(), VARIANCE(): Statistical dispersion calculations.

To return the total count of rows in the Customers table, use **COUNT(*)**:

SELECT COUNT(*) AS total_customers 
FROM Customers;

### COUNT(*) vs COUNT(column):

• **COUNT(*)**: Counts every row in the table, regardless of whether individual columns contain NULL values.
• COUNT(column_name): Counts only rows where column_name is not NULL. If 10 out of 100 rows have NULL email addresses, COUNT(email) returns 90.

In the SQL ORDER BY clause, sorting direction is controlled by two keywords:

1. ASC (Ascending): Sorts from lowest to highest (1 to 9, A to Z, oldest date to newest). This is the default order if omitted.
2. DESC (Descending): Sorts from highest to lowest (9 to 1, Z to A, newest date to oldest).

### Multi-Column Example:

SELECT product_name, category, price 
FROM products 
ORDER BY category ASC, price DESC;

This query sorts products alphabetically by category, and within each category sorts from most expensive to least expensive.

If no direction keyword is specified in an ORDER BY clause, the default sort order is ASC (Ascending):

-- These two queries produce identical ordering:
SELECT * FROM Customers ORDER BY LastName;
SELECT * FROM Customers ORDER BY LastName ASC;

### Important Database Reality:
In relational theory, tables are unordered sets. If a SQL query has no ORDER BY clause at all, the database makes no guarantees about the order of returned rows—rows may return in different sequences based on query execution plans or parallel worker threads.

-- MySQL / PostgreSQL
SELECT name, mark FROM Students ORDER BY mark DESC LIMIT 3;
-- SQL Server
SELECT TOP 3 name, mark FROM Students ORDER BY mark DESC;
-- Oracle
SELECT name, mark FROM Students WHERE ROWNUM <= 3 ORDER BY mark DESC;

To sort the Customers table in descending alphabetical order (Z to A) by FirstName, append ORDER BY FirstName DESC:

SELECT * 
FROM Customers 
ORDER BY FirstName DESC;

To ensure this query executes efficiently on large tables without writing temporary files to disk, add a B-tree index on FirstName.

In relational algebra and set theory, the INTERSECTION of two sets $A \cap B$ yields only the tuples that appear in both relation sets simultaneously.

### SQL Implementation (INTERSECT):

SELECT employee_id FROM engineering_dept
INTERSECT
SELECT employee_id FROM project_managers;

This query returns only employees who belong to both the engineering department and the project manager pool.
*Engine support:* Supported natively in PostgreSQL, SQL Server, Oracle, and SQLite; simulated via INNER JOIN or EXISTS in older MySQL versions.

MINUS (Oracle) or EXCEPT (PostgreSQL/SQL Server):

SELECT name FROM searchers WHERE product = 'X'
EXCEPT
SELECT name FROM buyers WHERE product = 'X';

---

IN, ANY, ALL, EXISTS, NOT EXISTS, and standard comparison operators (=, >, <, >=, <=, <>).

WHERE salary > ALL (SELECT salary FROM employees WHERE dept = 'Sales')
WHERE salary > ANY (SELECT salary FROM employees WHERE dept = 'Sales')

SQL's version of IF-THEN-ELSE. Two forms: simple and searched.

-- Searched CASE (more flexible)
SELECT name,
    CASE 
        WHEN salary >= 100000 THEN 'Executive'
        WHEN salary >= 60000  THEN 'Senior'
        WHEN salary >= 40000  THEN 'Mid'
        ELSE 'Junior'
    END AS level
FROM employees;

-- Simple CASE
SELECT name,
    CASE dept
        WHEN 'Engineering' THEN 'Tech'
        WHEN 'Sales' THEN 'Revenue'
        ELSE 'Other'
    END AS dept_category
FROM employees;

---

DDL (CREATE, ALTER, DROP, TRUNCATE), DML (INSERT, UPDATE, DELETE, SELECT), DQL (SELECT — sometimes grouped with DML), DCL (GRANT, REVOKE), TCL (COMMIT, ROLLBACK, SAVEPOINT).

DDL stands for Data Definition Language. DDL statements define, alter, and destroy database schema structures (tables, indexes, views, schemas, constraints):

### Primary DDL Statements:

• CREATE: Creates new database objects (CREATE TABLE, CREATE INDEX).
• ALTER: Modifies the structure of existing objects (ALTER TABLE ADD COLUMN).
• DROP: Permanently deletes objects from the database catalog (DROP TABLE).
• TRUNCATE: Rapidly deletes all records from a table while preserving table schema.
• RENAME: Renames a table or database object.

*Key Characteristic:* In many databases (like MySQL and Oracle), DDL statements trigger an implicit COMMIT, meaning they cannot be rolled back.

DML stands for Data Manipulation Language. DML commands manage, insert, retrieve, and modify actual data rows stored inside database tables:

### Core DML Statements:

• SELECT: Retrieves data from one or more tables.
• INSERT: Inserts new data rows into a table (INSERT INTO ... VALUES).
• UPDATE: Modifies existing data values (UPDATE ... SET ... WHERE).
• DELETE: Removes existing data rows (DELETE FROM ... WHERE).

Unlike DDL, DML statements operate inside transactional contexts and can be rolled back before a COMMIT.

GRANT and REVOKE belong to DCL (Data Control Language).

### Purpose of DCL:
DCL commands manage user security privileges, access rights, and permissions across database catalogs:

• GRANT: Provides privileges to a database user or role:

  GRANT SELECT, INSERT ON employees TO 'hr_user'@'localhost';
  

• REVOKE: Withdraws previously granted privileges:

  REVOKE DELETE ON employees FROM 'hr_user'@'localhost';
  

The INSERT INTO statement is used to add new rows of data into a database table:

INSERT INTO Customers (first_name, last_name, email)
VALUES ('John', 'Doe', 'john.doe@example.com');

### Multi-Row Batch Insertion (High Performance):

INSERT INTO Customers (first_name, last_name, email)
VALUES 
    ('Alice', 'Smith', 'alice@example.com'),
    ('Bob', 'Jones', 'bob@example.com');

Batching multiple rows into a single INSERT statement is orders of magnitude faster than issuing hundreds of individual INSERT statements because it eliminates network round-trips and transaction log flush overhead.

No, if you provide values for ALL columns in the exact table order. But best practice: always specify columns. It prevents errors when schema changes and is self-documenting.

-- Risky: breaks if columns are reordered
INSERT INTO customers VALUES (1, 'John', 'Doe');

-- Safe: explicit and clear
INSERT INTO customers (id, first_name, last_name) VALUES (1, 'John', 'Doe');

To insert a new record into the Customers table, specify the column names followed by the VALUES clause:

INSERT INTO Customers (id, first_name, last_name, email) 
VALUES (1, 'John', 'Doe', 'john.doe@example.com');

If the id column is configured with AUTO_INCREMENT (MySQL) or SERIAL / IDENTITY (PostgreSQL / SQL Server), omit the id column to let the engine auto-generate the next integer sequence.

To insert a row populating only the LastName column:

INSERT INTO Customers (LastName) 
VALUES ('Hawkins');

*Prerequisite:* This statement succeeds only if all other columns in Customers either allow NULL values or have defined DEFAULT values. If any omitted column has a NOT NULL constraint without a default, the engine will reject the query with a constraint violation error.

CREATE TEMPORARY TABLE temp_top_customers
SELECT * FROM customers WHERE total_spent > 10000;

It exists only for the current session/connection and is automatically dropped when the session ends.

The UPDATE statement modifies existing data values in one or more rows of a table:

UPDATE Customers 
SET email = 'new.email@example.com', 
    updated_at = NOW() 
WHERE id = 42;

*Critical Safety Rule:* Always supply a targeted WHERE clause. Executing an UPDATE without a WHERE clause updates every single row across the entire table!

The keyword used in an UPDATE statement to specify new values for columns is SET:

UPDATE employees 
SET salary = salary * 1.10, 
    title = 'Senior Software Engineer' 
WHERE employee_id = 101;

The SET keyword precedes a comma-separated list of column_name = new_value assignments.

To update all customers with the last name "Jackson" to "Hawkins":

UPDATE Customers 
SET LastName = 'Hawkins' 
WHERE LastName = 'Jackson';

### Production Best Practice:
Before running this update on a live production database:

1. First run: SELECT COUNT(*) FROM Customers WHERE LastName = 'Jackson'; to verify how many rows will be affected.
2. Wrap the operation in an explicit transaction:

   START TRANSACTION;
   UPDATE Customers SET LastName = 'Hawkins' WHERE LastName = 'Jackson';
   -- Verify affected rows before committing:
   COMMIT; -- or ROLLBACK;
   

The DELETE statement removes existing rows from a table based on a condition:

DELETE FROM Customers 
WHERE is_active = 0 AND last_login < '2023-01-01';

### DELETE vs TRUNCATE:

• DELETE removes rows one by one, records each deletion in the transaction log, and fires ON DELETE triggers.
• TRUNCATE deallocates data pages in bulk, does not fire triggers, and resets identity seeds.

To delete all rows where FirstName equals "John":

DELETE FROM Customers 
WHERE FirstName = 'John';

*Safety Warning:* If you accidentally omit WHERE FirstName = 'John', the database will delete every record in the table. Always verify with a SELECT * FROM Customers WHERE FirstName = 'John' first.

The FROM clause defines the source table(s), views, joined datasets, or subqueries from which rows are read:

SELECT u.username, p.title 
FROM users u 
INNER JOIN posts p ON u.id = p.user_id;

It is the foundation of data sourcing across SELECT, UPDATE (with joins), and DELETE statements.

The CREATE TABLE statement defines and provisions a new table schema:

CREATE TABLE Customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

It defines column names, data types, default values, and structural constraints.

Collation defines the rules for comparing and sorting characters. It works with the character set.

• utf8mb4_general_ci: Case-insensitive, faster
• utf8mb4_unicode_ci: Case-insensitive, accurate for all Unicode
• utf8mb4_bin: Binary comparison (case-sensitive)

SELECT * FROM users WHERE name = 'john'; -- Matches 'John' with _ci collation

Automatically generates sequential unique integers for a column, typically the PRIMARY KEY.

CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));
INSERT INTO users (name) VALUES ('Alice');  -- id becomes 1 automatically

In PostgreSQL, use SERIAL or GENERATED ALWAYS AS IDENTITY. In SQL Server, IDENTITY(1,1).

While both DROP and TRUNCATE are DDL operations, their scope is fundamentally different:

| Feature | TRUNCATE TABLE | DROP TABLE |
| :--- | :--- | :--- |
| Data | Deletes all rows inside the table | Destroys all rows |
| Structure | Preserves table schema, columns, constraints, and indexes | Deletes table schema entirely from database dictionary |
| Identity / Seed | Resets AUTO_INCREMENT counter to 1 | Table no longer exists |
| Speed | Extremely fast (deallocates data storage pages) | Fast (drops catalog references) |
| Triggers | Does not fire ON DELETE triggers | Does not fire triggers |
| Next Step | Table is immediately ready for new INSERT statements | Must run CREATE TABLE before using again |

To add an order_date column to an existing table, use ALTER TABLE ... ADD COLUMN:

ALTER TABLE `orders` 
ADD COLUMN order_date DATE NOT NULL DEFAULT (CURRENT_DATE);

### Reserved Word Note:
Because ORDER is a reserved SQL keyword, enclose it in backticks (` order ) in MySQL or double quotes ("order") in PostgreSQL. Industry best practice is to always use plural table names (orders`) to avoid keyword collisions.

-- MySQL
ALTER TABLE Customer CHANGE Address Addr VARCHAR(50);
-- PostgreSQL / Standard
ALTER TABLE Customer RENAME COLUMN Address TO Addr;

To remove a column from an existing table schema, use ALTER TABLE ... DROP COLUMN:

ALTER TABLE addresses 
DROP COLUMN city;

*Production Warning:* Dropping a column is an irreversible DDL operation that permanently deletes all data stored in that column and invalidates any views, stored procedures, or queries referencing city.

GRANT. To remove: REVOKE.

GRANT SELECT, INSERT ON database.orders TO 'app_user'@'%';
REVOKE DELETE ON database.orders FROM 'app_user'@'%';

1. System privileges: CREATE, DROP, ALTER (database-level)
2. Object privileges: SELECT, INSERT, UPDATE, DELETE, EXECUTE (table/procedure-level)

In SQL security management, database administrators can grant specific privileges using the GRANT statement:

• Data Manipulation Privileges: SELECT, INSERT, UPDATE, DELETE
• Schema Definition Privileges: CREATE, ALTER, DROP, INDEX
• Administrative Privileges: REFERENCES, EXECUTE, SHOW VIEW, PROCESS, RELOAD
• All-Inclusive: ALL PRIVILEGES

GRANT SELECT, INSERT ON ecommerce.* TO 'app_user'@'10.0.0.%';

A code injection attack where malicious SQL is inserted via user input. Prevention:

1. Parameterized queries (prepared statements)
2. Input validation
3. ORM frameworks (most handle escaping automatically)
4. Least privilege (app user shouldn't have DROP permissions)
5. Stored procedures with parameters

---

ROLLBACK does nothing after a COMMIT has executed.

### Why?
Once a transaction issues COMMIT, the changes are permanently written to the database transaction log and storage engine. The transaction boundary is closed.
A subsequent ROLLBACK has no active transaction to undo and cannot revert committed transactions. Undoing committed data requires manual compensation transactions or point-in-time backup restoration.

Yes, in MySQL (using the standard InnoDB storage engine), autocommit is enabled by default (autocommit = 1):

### What Autocommit Means:

• Every individual SQL statement (INSERT, UPDATE, DELETE) is automatically wrapped in its own single-statement transaction and committed immediately.
• To execute multi-statement atomic transactions, you must explicitly start a transaction block:

  START TRANSACTION;
  -- Multiple queries here...
  COMMIT;
  

Or disable autocommit for the session: SET autocommit = 0;.

Declarative (non-procedural). You state *what* result you want, not *how* to compute it. The database's query optimizer decides the "how" — which indexes to use, which join order is fastest, etc.

Compare with PL/SQL (Oracle) or T-SQL (SQL Server), which are procedural extensions that add loops, variables, and conditional logic.

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, JOIN, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX, UNION, LIMIT, DISTINCT, AS, AND, OR, NOT, IN, BETWEEN, LIKE, IS NULL, EXISTS, CASE, WHEN, THEN, ELSE, END.

SELECT. Optional clauses: WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, JOIN.

SELECT name AS full_name, salary * 12 AS annual_salary
FROM employees
WHERE department = 'Engineering'
ORDER BY annual_salary DESC;

SELECT * FROM products;

⚠️ *Interview tip*: Always follow up with "But in production, I list explicit columns to reduce network overhead and prevent breaking if columns are reordered or added."

The FROM clause identifies the source table(s), views, joined tables, or subqueries from which data is to be retrieved, filtered, and aggregated.

### Logical SQL Processing Order:
In SQL execution internals, FROM is the very first clause evaluated by the database engine:

1. FROM (identifies source tables and evaluates JOIN operations)
2. WHERE (filters individual rows)
3. GROUP BY (aggregates rows into groups)
4. HAVING (filters aggregated groups)
5. SELECT (projects columns and computes expressions)
6. DISTINCT (deduplicates rows)
7. ORDER BY (sorts output)
8. LIMIT / OFFSET (paginates results)

Because FROM executes first, aliases defined in the FROM clause are recognized in all subsequent clauses.

SELECT name, COUNT(name) FROM customers GROUP BY name;

In standard SQL (and MySQL 5.7+ with ONLY_FULL_GROUP_BY), any non-aggregated column in SELECT must appear in GROUP BY.

| WHERE | HAVING |
|-------|--------|
| Filters rows | Filters groups |
| Executes before GROUP BY | Executes after GROUP BY |
| Cannot use aggregates | Can use COUNT(), SUM(), etc. |
| Can reference any column | Can only reference grouped columns or aggregates |

SELECT department, AVG(salary) AS avg_sal
FROM employees
WHERE hire_date > '2020-01-01'      -- Filter rows first
GROUP BY department
HAVING AVG(salary) > 60000;         -- Then filter groups

---

A JOIN clause in SQL combines rows from two or more tables based on a related column between them (typically a Foreign Key referencing a Primary Key).

### The Need for JOINs:
In relational databases, data is normalized across multiple tables to avoid data redundancy:

SELECT 
    orders.order_id,
    orders.order_date,
    customers.first_name,
    customers.email
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

### Common Join Types:

• INNER JOIN: Returns rows when there is a match in both tables.
• LEFT JOIN: Returns all rows from left table, with matching rows from right table (or NULLs).
• RIGHT JOIN: Returns all rows from right table.
• FULL OUTER JOIN: Returns rows when there is a match in either table.
• CROSS JOIN: Produces a Cartesian product of both tables.

The INNER JOIN is the most widely used type of join in relational database systems.

In fact, INNER is the default join type in standard SQL—writing FROM A JOIN B ON ... is parsed by database engines as FROM A INNER JOIN B ON ....

It returns exclusively those rows where the join condition evaluates to TRUE in both tables, filtering out orphan records.

1. INNER JOIN — matching rows only
2. LEFT (OUTER) JOIN — all left + matched right
3. RIGHT (OUTER) JOIN — all right + matched left
4. FULL OUTER JOIN — all rows from both
5. CROSS JOIN — Cartesian product
6. SELF JOIN — table joined to itself

The ON clause specifies the join condition. It improves readability over the old comma-syntax, supports multi-column joins (ON a.id = b.id AND a.type = b.type), and is required for outer joins. Any statement claiming otherwise is false.

An INNER JOIN returns only the subset of rows where the join predicate (ON condition) matches in both tables simultaneously:

### Visual Venn Diagram Concept:
If Table A has IDs [1, 2, 3] and Table B has IDs [2, 3, 4], the inner join on A.id = B.id returns only rows for IDs [2, 3].

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

If an employee has a NULL or non-existent dept_id, that employee will not appear in the result set.

Yes. There's no practical limit (though performance degrades with many joins).

SELECT f.name, d.name AS division, c.name AS country
FROM faculty f
INNER JOIN division d ON f.division_id = d.id
INNER JOIN country c ON f.country_id = c.id;

Yes — SELF JOIN. You must use aliases to distinguish the two instances.

SELECT a.name AS employee, b.name AS manager
FROM employees a, employees b
WHERE a.manager_id = b.id;

A Cartesian product occurs when tables are joined without a condition. Every row in Table A pairs with every row in Table B: m × n rows total. It's usually a bug, but CROSS JOIN is explicit and valid for combinatorial data.

In relational algebra, the UNION of two sets $A \cup B$ combines all distinct tuples from both relations into a single unified relation.

### UNION vs UNION ALL in SQL:

-- UNION removes duplicate records (performs sorting/deduplication)
SELECT city FROM customers
UNION
SELECT city FROM suppliers;

-- UNION ALL retains all rows including duplicates (much faster)
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

*Performance rule:* Always prefer UNION ALL unless business logic strictly requires deduplicating rows across the combined sets.

| UNION | UNION ALL |
|-------|-----------|
| Removes duplicates | Keeps duplicates |
| Slower (sort + dedup) | Faster |
| Use when uniqueness matters | Use when sets are already distinct or duplicates are meaningful |

A SELECT statement nested inside another SQL statement to supply values for filtering or computation is called a subquery (also known as an inner query or nested query):

SELECT product_name, price 
FROM products 
WHERE price > (
    SELECT AVG(price) FROM products
);

### Types of Subqueries:

1. Scalar Subquery: Returns exactly one row and one column.
2. Multi-Row Subquery: Returns a list of values, evaluated with IN, ANY, or ALL.
3. Correlated Subquery: References columns from the outer query, re-evaluated for each outer row.

Subqueries can be nested within virtually every major SQL clause and statement:

1. WHERE Clause: Filtering candidate rows (WHERE id IN (SELECT ...))
2. FROM Clause: Acting as a derived table or inline view (FROM (SELECT ...) AS sub)
3. SELECT Projection Clause: Computing scalar values per row
4. HAVING Clause: Filtering aggregated groups
5. INSERT INTO Statement: Populating tables from query results
6. UPDATE and DELETE Statements: Targeting records based on subquery criteria

A subquery placed inside another subquery is called a nested subquery (or multi-level subquery):

SELECT customer_name 
FROM customers 
WHERE customer_id IN (
    SELECT customer_id 
    FROM orders 
    WHERE order_id IN (
        SELECT order_id 
        FROM order_items 
        WHERE product_id = 101
    )
);

While nested subqueries are intuitive, deeply nested subqueries can degrade execution plan efficiency. Modern optimizers often rewrite them into JOIN operations, or developers rewrite them into Common Table Expressions (CTEs) using WITH.

A correlated subquery. It executes once for each row of the outer query.

SELECT * FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept = e.dept);

Statements such as COMMIT, ROLLBACK, CREATE, ALTER, and DROP are NOT DML statements:

• COMMIT and ROLLBACK are TCL (Transaction Control Language).
• CREATE, ALTER, DROP, and TRUNCATE are DDL (Data Definition Language).
• GRANT and REVOKE are DCL (Data Control Language).

*DML statements are strictly:* SELECT, INSERT, UPDATE, and DELETE.

See the comparison table above. Key differences: DELETE is DML (row-by-row, rollbackable, fires triggers, has WHERE). TRUNCATE is DDL (bulk page deallocation, faster, no WHERE, resets auto-increment, doesn't fire triggers).

MySQL supports a complete set of integrity constraints:

1. PRIMARY KEY: Uniquely identifies each row (implies NOT NULL and UNIQUE).
2. FOREIGN KEY: Enforces referential integrity matching a primary key in another table.
3. UNIQUE: Guarantees all values in a column are distinct.
4. NOT NULL: Prohibits NULL values.
5. CHECK (MySQL 8.0+): Validates that values satisfy a boolean expression (CHECK (age >= 18)).
6. DEFAULT: Assigns a fallback value if omitted during insert.
7. AUTO_INCREMENT: Auto-generates sequential integer identifiers.

Any assertion claiming that constraints are optional in production or that constraints significantly slow down reads is false.

### Why Constraints Are Vital:

• Constraints preserve data integrity at the lowest physical database tier, preventing corrupted data even if application code contains bugs.
• Primary key and unique constraints automatically create underlying B-tree indexes, accelerating query performance rather than degrading it.

The NOT NULL constraint enforces that a column must always contain a valid data value and cannot accept NULL:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    hired_date DATE NOT NULL
);

Attempting to insert an empty NULL into a NOT NULL column causes the database to reject the transaction with an integrity violation error.

To enforce that every value in a non-primary key column is unique, apply the UNIQUE constraint:

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    phone_number VARCHAR(20) UNIQUE
);

### PRIMARY KEY vs UNIQUE:

• A table can have only one PRIMARY KEY.
• A table can have multiple UNIQUE constraints.
• UNIQUE columns typically allow NULL values (in SQL standards, multiple NULL values are permitted because NULL != NULL).

A CHECK constraint ensures that all values stored in a column satisfy a specific boolean condition:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) CHECK (price > 0),
    discount_percent INT CHECK (discount_percent BETWEEN 0 AND 100)
);

*(Note: MySQL introduced full enforcement of CHECK constraints in MySQL 8.0.16. Earlier MySQL 5.7 versions parsed the syntax but silently ignored enforcement.)*

| PRIMARY KEY | UNIQUE |
|-------------|--------|
| One per table | Multiple per table |
| Cannot be NULL | Usually allows one NULL |
| Implicitly indexed | Explicitly indexed |
| Identifies the row | Alternate identifier |

Permanently removes the table definition, all data, indexes, triggers, and constraints. Cannot be undone (unless in a transaction that rolls back, in some DBs).

DROP TABLE IF EXISTS old_customers;

A mechanism to prevent concurrent transactions from interfering with each other. Locks can be shared (read locks, multiple allowed) or exclusive (write locks, only one allowed).

• COMMIT — Make changes permanent
• ROLLBACK — Undo all changes in the transaction
• SAVEPOINT — Set a partial rollback point

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT before_credit;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Oops, wrong account!
ROLLBACK TO SAVEPOINT before_credit;
-- Now retry with correct account
COMMIT;

A database transaction that finishes all operations successfully and persists its changes permanently to durable storage is said to be Committed:

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- Transaction is now committed and permanent

Once committed, the transaction satisfies the Durability property of ACID and survives power outages or server crashes.

The four foundational properties that guarantee database transactions are processed reliably are known as ACID:

1. Atomicity: "All or nothing." If any statement within the transaction fails, all changes are rolled back to the initial state.
2. Consistency: Transactions transition the database from one valid state to another, strictly obeying all constraints, cascades, and triggers.
3. Isolation: Concurrent transactions execute independently without interfering with each other (governed by isolation levels like READ COMMITTED, REPEATABLE READ, and SERIALIZABLE).
4. Durability: Once committed, changes are permanently recorded on disk and will not be lost even during system failure.

Every individual statement is automatically committed. In MySQL, autocommit is ON by default. Turn it off for multi-statement transactions:

SET autocommit = 0;
-- ... multiple statements ...
COMMIT;
SET autocommit = 1;

In MySQL and relational databases, a virtual table is called a View:

CREATE VIEW active_customer_summary AS 
SELECT id, first_name, last_name, email 
FROM Customers 
WHERE is_active = 1;

### Characteristics of a View:

• A view does not store physical data rows on disk (unlike base tables).
• It stores a pre-packaged SELECT query definition.
• When an application queries the view (SELECT * FROM active_customer_summary), the database dynamically runs the underlying query against the base tables.

Some views are updatable, but under strict structural criteria:

### Conditions for a View to Be Updatable:

1. Must reference only one base table in the FROM clause.
2. Cannot contain aggregate functions (SUM, COUNT, AVG, MAX, MIN).
3. Cannot contain DISTINCT, GROUP BY, HAVING, or UNION / UNION ALL.
4. Cannot use LIMIT in the view definition.
5. All underlying NOT NULL columns without default values must be included in the view projection so that INSERT statements can populate required fields.

1. Simplify complex queries — hide JOINs and aggregations
2. Security — expose only specific columns/rows
3. Logical independence — shield apps from schema changes
4. Consistency — same calculation logic everywhere

Standard SQL views do NOT contain data.

• Standard Views (Virtual): Contain only a stored SQL query definition in the data dictionary. Every query against the view executes dynamically against the underlying base tables.
• Materialized Views (Cached on Disk): Supported in PostgreSQL, Oracle, and SQL Server (and via plugins/triggers in MySQL), materialized views physically cache query results on disk and must be periodically refreshed (REFRESH MATERIALIZED VIEW).

To delete a view from the database, use the DROP VIEW command:

DROP VIEW IF EXISTS active_customer_summary;

Dropping a view removes only the stored query definition; the underlying base tables and their data rows remain completely unaffected.

Yes, a view can be defined on top of another existing view (known as a nested view):

CREATE VIEW us_customers AS 
SELECT * FROM Customers WHERE country = 'USA';

CREATE VIEW vip_us_customers AS 
SELECT * FROM us_customers WHERE total_spend > 5000;

### Architectural Caution:
While nested views promote modular query definitions, deeply nesting views (3+ levels) can make query execution plan optimization difficult for the query planner, leading to unexpected performance bottlenecks.

To iterate row-by-row over a query result set inside a stored procedure, use a CURSOR:

### Cursor Lifecycle Steps:

1. DECLARE: Define the cursor and query.
2. OPEN: Initialize the cursor result set.
3. FETCH: Retrieve the next row into local variables.
4. CLOSE: Free cursor memory resources.

DELIMITER //
CREATE PROCEDURE ProcessCustomers()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE cust_id INT;
    DECLARE cur CURSOR FOR SELECT id FROM Customers;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO cust_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- Process each customer ID here
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

*Performance Warning:* Databases are optimized for set-based operations. Cursors execute row-by-row ($O(n)$ procedural execution) and should only be used when set-based SQL logic is impossible.

A stored procedure that automatically executes in response to a DML event (INSERT, UPDATE, DELETE) or DDL event (CREATE, ALTER, DROP). Components: Event (when), Condition (optional), Action (what to do).

A specialized database procedure that automatically executes in response to data modification events (INSERT, UPDATE, or DELETE) on a specific table is called a Trigger:

CREATE TRIGGER before_order_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    IF NEW.order_total < 0 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Order total cannot be negative';
    END IF;
END;

### Common Use Cases for Triggers:

• Automated audit logging (recording who modified a record and when into an audit table).
• Enforcing complex multi-table business validation rules.
• Maintaining real-time summary aggregation tables.

Data structures (typically B-Trees) that speed up data retrieval at the cost of slower writes and extra storage. Think of them as a book's table of contents.

• Clustered: Determines physical storage order. One per table. The data IS the index.
• Non-Clustered: Separate structure with pointers to data. Multiple allowed per table.
• Clustered indexes excel at range queries. Non-clustered are more flexible.

REPEATABLE READ (InnoDB engine). Prevents dirty reads and non-repeatable reads, but phantom reads are possible (though InnoDB uses MVCC + gap locking to mostly prevent them).

Query optimization. The database's query optimizer generates multiple execution plans and picks the cheapest one based on statistics, indexes, and cost models. You can inspect plans with EXPLAIN.