🟢 CREATE TABLE

CREATE TABLE: CREATE TABLE is like casting the column headers and accepted data types of an Excel spreadsheet into a permanent mold — with one key difference: every piece of data

CREATE TABLE is like casting the column headers and accepted data types of an Excel spreadsheet into a permanent mold — with one key difference: every piece of data entered into the table must conform to that mold, or the database rejects it. But if Java class definitions already declare field types, why write CREATE TABLE separately? Because a database table is created once and persists on disk permanently; Java objects vanish from memory when the program exits. When you write `class TestResult { String name; String status; int durationMs; }` in Java, the SQL equivalent is `CREATE TABLE test_results (name VARCHAR(100), status VARCHAR(10), duration_ms INT)` — the difference is that SQL makes the schema physically permanent with constraints (NOT NULL, PRIMARY KEY) and default values. For a QA engineer, getting this schema right is critical: a wrongly typed column (for example, INT instead of BIGINT for microsecond-precision timings) can cause silent data loss or assertion failures during test data setup — and that bug may not surface until production.

CREATE TABLE — Defining Structure

Micro Lab: SQL — Table creation

Replace the TODO line with the critical line from the expected solution. This is not a real runtime; the goal is to reinforce writing the correct structure in a controlled way.

Step by Step: SQL — Table creation

Determine what entity the table represents (users, orders, products)

Choose name and appropriate data type for each column (INT, VARCHAR(255), DATE, BOOLEAN)

Identify PRIMARY KEY column — unique, immutable, cannot be NULL

Add NOT NULL constraint to mandatory business fields

Reference the related table with FOREIGN KEY, choose ON DELETE behavior

What is the correct order when designing a new SQL table?

When defining tables, we add rules (constraints) to columns to ensure data integrity: 1. **PRIMARY KEY**: A column that **uniquely** identifies each row. No two rows can have the same primary key, and it can never be empty (`NULL`). 2. **AUTO_INCREMENT**: Automatically generates a sequential number (1, 2, 3...) when a new row is inserted. 3. **NOT NULL**: Ensures that a column cannot have a `NULL` (empty) value. 4. **VARCHAR(n)**: Variable-length text, where `n` is the maximum character limit (e.g., `VARCHAR(100)`). 5. **DEFAULT**: Specifies a fallback value if no value is provided during insertion (e.g., `DEFAULT 0` or `DEFAULT FALSE`).

🎬 CREATE TABLE: The Permanent Schema Mold

Row Violating Constraint