Before SQL: What is a Relational Table?

A relational table is a structured collection of data organized into rows and columns. Each row represents a single discrete entity or event, and each column represents a specific attribute with a declared data type (such as integers, decimals, text, or dates).

Here is a sample slice from ShopKart's orders table loaded into your interactive workspace:

Notice two critical column labels:

• Primary Key (order_id): A column guaranteed to be unique for every single row. No two orders ever share the same order_id.
• Foreign Key (customer_id): A reference pointing to the primary key in another table (customers.customer_id). This is how relational databases link disparate business events without duplicating names and addresses on every order.

Retrieving & Filtering: SELECT and WHERE

Every SQL query starts with two fundamental clauses: FROM (which table to read from disk) and SELECT (which columns to return to the caller).

SELECT
    order_id,
    customer_id,
    amount
FROM orders;

Next, add a WHERE clause to filter out rows that don't meet your business criteria. For example, ShopKart's finance team only recognizes revenue from completed transactions:

SELECT
    order_id,
    customer_id,
    amount,
    order_status
FROM orders
WHERE order_status = 'COMPLETED'
  AND amount > 500;

Three-Valued Logic: Why NULL is Not Zero or Empty String

In computer science, boolean logic is binary: an expression evaluates to either TRUE or FALSE.

SQL is fundamentally different. SQL implements Three-Valued Logic (3VL):

In SQL, NULL does not mean zero, false, or an empty string "". It represents unknown, missing, or inapplicable data.

Because NULL represents an unknown value, comparing anything to NULL with an equality operator (=) cannot be determined:

SELECT
    customer_id,
    name,
    city,
    phone
FROM customers
WHERE phone IS NULL;

Sorting, Deduplication & Pagination

Databases store rows as an unordered mathematical set. Unless you explicitly provide an ORDER BY clause, the order in which rows return is non-deterministic. Never assume rows will return in insertion order.

SELECT
    order_id,
    customer_id,
    amount,
    order_date
FROM orders
WHERE order_status = 'COMPLETED'
ORDER BY amount DESC
LIMIT 3;

Conditional Logic with CASE Expressions

The CASE expression is SQL's inline conditional. It evaluates conditions sequentially from top to bottom and returns the result of the first branch that evaluates to TRUE.

SELECT
    order_id,
    amount,
    CASE
        WHEN amount >= 1000 THEN 'Premium'
        WHEN amount >= 500 THEN 'Standard'
        ELSE 'Low Value'
    END AS order_tier
FROM orders
ORDER BY amount DESC
LIMIT 5;

The 8-Step Logical Query Processing Order

When you write code in Python, code executes top to bottom. But in SQL, the order you write clauses is completely different from the order the database engine executes them.