Run a mock round

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.

SQL stands for Structured Query Language.

Originally developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s, it was initially named SEQUEL (Structured English Query Language) designed to manipulate and retrieve data stored in IBM's original relational database management system, System R.

### Key Aspects of SQL:

1. Declarative Paradigm: Unlike imperative programming languages (where you write step-by-step algorithms), SQL is declarative—you describe *what* data you want, and the database query optimizer plans *how* to execute the retrieval.
2. ANSI & ISO Standards: SQL became an ANSI standard in 1986 and an ISO standard in 1987. Major relational engines (PostgreSQL, MySQL, Oracle, SQL Server) adhere to core SQL standards while offering dialect-specific extensions.
3. Core Language Subsets:

• DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE
• DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
• DCL (Data Control Language): GRANT, REVOKE
• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT

-- MySQL
SELECT * FROM tbl ORDER BY RAND() LIMIT 10;
-- PostgreSQL
SELECT * FROM tbl ORDER BY RANDOM() LIMIT 10;
-- SQL Server
SELECT TOP 10 * FROM tbl ORDER BY NEWID();

Performance note: This is O(n log n) and full table scan. For large tables, use a random ID approach.

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;

Relational Database Management Systems (RDBMS). SQL is the ANSI-standard language for creating, querying, updating, and managing relational databases. Major implementations include MySQL, PostgreSQL, SQL Server, Oracle, and SQLite.

*Real-world context*: Every web application you use — Instagram, Netflix, Uber — stores its data in SQL databases (often sharded or replicated, but SQL at the core).

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.

• 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;

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;

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.