Getting Started with Relational Databases and SQL

What a Relational Database Actually Is

A Relational Database organizes data into Tables, each consisting of rows and columns, where each row represents one record and each column represents one attribute of that record. This tabular structure, formalized decades ago, remains the dominant way to store structured data because it makes relationships between different kinds of data explicit and queryable.

Example table: customers
| customer_id | name        | email               |
|-------------|-------------|---------------------|
| 1           | Alice Smith | [email protected]   |
| 2           | Bob Jones   | [email protected]      |

A Relational Database Management System (RDBMS) is the software that stores these tables and lets users interact with them — common examples include PostgreSQL, MySQL, and SQLite. Users interact with an RDBMS almost entirely through SQL (Structured Query Language), a specialized language for retrieving and manipulating structured data.

Writing a First SQL Query

The most fundamental SQL statement is SELECT, used to retrieve data from a table.

SELECT name, email
FROM customers
WHERE customer_id = 1;

This query reads naturally: select the name and email columns, from the customers table, where the customer_id equals 1. SQL's design goal is to be declarative — the query describes what data is wanted, not the step-by-step procedure for finding it, leaving the database engine to determine the most efficient way to actually retrieve it.

The Basic Building Blocks of a SQL Query

Beyond a simple `SELECT`, a handful of clauses handle the vast majority of everyday data retrieval needs.

• WHERE filters rows based on a condition, returning only rows that satisfy it.
• ORDER BY sorts the returned rows by one or more columns, ascending or descending.
• LIMIT restricts the number of rows returned, useful for previewing large result sets.
• GROUP BY groups rows sharing a common value, typically combined with an aggregate function.

SELECT city, COUNT(*) AS customer_count
FROM customers
GROUP BY city
ORDER BY customer_count DESC
LIMIT 5;

This query counts customers per city, sorts cities by that count from highest to lowest, and returns only the top 5 — a pattern extremely common in reporting and analytics tasks.

Managing Tables: Creating Structure

Before any data can be stored, a table's structure must be defined using CREATE TABLE, specifying each column's name and Data Type — the kind of value that column will hold, such as text, integers, or dates.

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100)
);

The PRIMARY KEY designation marks customer_id as the column that uniquely identifies each row — no two rows can share the same value, and this column cannot be left empty. NOT NULL similarly requires that a column always have a value, preventing incomplete records.

Managing Data: Inserting, Updating, and Deleting

Once a table exists, three statements handle changing its contents.

-- Add a new row
INSERT INTO customers (customer_id, name, email)
VALUES (3, 'Carol White', '[email protected]');

-- Modify an existing row
UPDATE customers
SET email = '[email protected]'
WHERE customer_id = 1;

-- Remove a row
DELETE FROM customers
WHERE customer_id = 2;

Each of these statements typically includes a WHERE clause to target specific rows; omitting it from an UPDATE or DELETE would apply the change to every row in the table, a common and costly mistake worth being especially careful about.

Why These Fundamentals Matter

Every more advanced database concept — relationships between tables, normalization, indexing, and performance optimization — builds directly on this foundation of tables, data types, and the core SQL statements introduced here. A solid grasp of how to define a table's structure and reliably query and modify its contents is the prerequisite for everything that follows in database design.