Run a mock round

MySQL doesn't have a native BOOLEAN type. It uses TINYINT(1) where 0 = false, 1 = true, and any non-zero value is treated as true in conditions.

CREATE TABLE users (is_active TINYINT(1) DEFAULT 1);

PostgreSQL, however, has a true BOOLEAN type accepting TRUE, FALSE, and NULL.

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

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.

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.

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.

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).

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')

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.

GRANT. To remove: REVOKE.

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