SQL & Databases Interview Questions and Answers

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.