Learn SQL Basics: A Beginner's Guide to Databases

Whether you want to build web applications, analyze data, or land your first programming job, you need to learn SQL basics. SQL (Structured Query Language) is the standard language for interacting with relational databases, and it remains one of the most in-demand skills in technology. This guide takes you from zero to confident with practical, hands-on examples you can run in any database.

Relational databases power everything from small mobile apps to enterprise systems. Companies like Netflix, Uber, and banks of all sizes rely on them to store and retrieve data reliably. SQL is the universal language that lets you talk to all of them, and the syntax you learn here transfers across PostgreSQL, MySQL, SQLite, SQL Server, and Oracle with only minor variations.

What is SQL? Learn SQL Basics from the Ground Up

SQL is a declarative language used to store, manipulate, and retrieve data in relational databases. A relational database organizes data into tables (rows and columns), similar to a spreadsheet. Each table has a defined structure with columns of specific data types, and each row represents one record.

When you learn SQL basics, you discover that SQL statements fall into a few categories based on what they do:

• DDL (Data Definition Language): CREATE, ALTER, DROP — define and modify the structure of your tables and databases.
• DML (Data Manipulation Language): INSERT, UPDATE, DELETE — modify the data stored in your tables.
• DQL (Data Query Language): SELECT — retrieve and query data, which is what you will do most often.
• DCL (Data Control Language): GRANT, REVOKE — manage permissions and access control.

The beauty of SQL is that you describe what data you want, not how to get it. The database engine figures out the most efficient way to retrieve it. This is what makes SQL declarative rather than imperative.

Creating Tables

Before you can query data, you need a table. Here is how to create one with proper constraints:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Key points to understand: SERIAL auto-increments the id column for each new row, PRIMARY KEY uniquely identifies each row and creates an index automatically, NOT NULL prevents empty values in that column, and UNIQUE guarantees no duplicate values. The DEFAULT clause provides a value when none is specified.

A foreign key links tables together and enforces referential integrity:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    total DECIMAL(10, 2),
    status VARCHAR(20) DEFAULT 'pending'
);

The REFERENCES users(id) clause enforces that every user_id in orders must correspond to an existing id in users. This prevents orphaned records and keeps your data consistent.

CRUD Operations

CRUD stands for Create, Read, Update, Delete — the four fundamental data operations every application needs. When you learn SQL basics, these are the commands you will use most frequently.

Create a new row:

INSERT INTO users (username, email, age)
VALUES ('jane_dev', 'jane@example.com', 28);

Read data with SELECT. You can retrieve specific columns or all columns:

SELECT username, email FROM users;
SELECT * FROM users WHERE age > 25;

Update existing rows to change their values:

UPDATE users SET age = 29 WHERE username = 'jane_dev';

Delete rows you no longer need:

DELETE FROM users WHERE username = 'jane_dev';

You can also insert multiple rows in a single statement, which is more efficient than running separate inserts:

INSERT INTO users (username, email, age) VALUES
  ('alice', 'alice@example.com', 30),
  ('bob', 'bob@example.com', 35),
  ('carol', 'carol@example.com', 22);

Always include a WHERE clause with UPDATE and DELETE — without it, you will modify or remove every row in the table, which can be disastrous. A good habit is to write a SELECT with the same WHERE clause first to preview which rows will be affected before you commit to the change.

Filtering with WHERE

The WHERE clause filters rows based on conditions. This is one of the most important skills when you learn SQL basics because almost every real-world query needs filtering:

SELECT * FROM users
WHERE age >= 18 AND email LIKE '%@gmail.com';

Operators you should know and use regularly:

• =, !=, <> — equality and inequality
• >, <, >=, <= — comparison
• AND, OR, NOT — combine multiple conditions
• LIKE — pattern matching (% matches any sequence of characters, _ matches exactly one character)
• IN (...) — match any value in a list
• BETWEEN x AND y — range check, inclusive of both endpoints
• IS NULL / IS NOT NULL — check for missing values

NULL deserves special attention. NULL means unknown or missing, not zero or empty string. Comparing NULL with = always returns unknown rather than true or false, so always use IS NULL to test for it. This is a common source of bugs for beginners.

Sorting and Pagination

Use ORDER BY to sort results by one or more columns:

SELECT * FROM users
ORDER BY created_at DESC, username ASC;

To limit results — essential for pagination in web applications — combine LIMIT and OFFSET. LIMIT restricts how many rows come back, and OFFSET skips that many rows before starting to return results:

SELECT * FROM users
ORDER BY id ASC
LIMIT 10 OFFSET 20;

This returns page 3 assuming 10 records per page. OFFSET can be slow on very large tables because the database still scans all skipped rows, so for high-performance pagination consider keyset pagination using a WHERE clause on the id column instead. DISTINCT removes duplicate rows so you can see unique values:

SELECT DISTINCT status FROM orders;

Joins

Joins combine rows from two or more tables based on a related column. To learn SQL basics thoroughly, you must understand joins because real applications almost always spread data across multiple tables.

SELECT users.username, orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id;

The four main join types behave differently:

• INNER JOIN: Returns only rows that have a match in both tables. Non-matching rows are excluded entirely.
• LEFT JOIN: Returns all rows from the left table, plus matches from the right. Rows with no match get NULL for the right columns.
• RIGHT JOIN: Returns all rows from the right table, plus matches from the left. The mirror of LEFT JOIN.
• FULL OUTER JOIN: Returns all rows from both tables, matching where possible and filling with NULL otherwise.

You can also join more than two tables by chaining JOIN clauses, and you can alias table names to keep your queries concise:

SELECT u.username, o.total, p.name AS product
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN products p ON o.product_id = p.id;

Aggregate Functions

Aggregate functions summarize multiple rows into a single value, which is essential for reporting and analytics:

SELECT
    COUNT(*) AS total_orders,
    SUM(total) AS revenue,
    AVG(total) AS average_order,
    MIN(total) AS smallest_order,
    MAX(total) AS largest_order
FROM orders;

Combine aggregates with GROUP BY to compute per-group statistics:

SELECT user_id, COUNT(*) AS order_count, AVG(total) AS avg_total
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5
ORDER BY avg_total DESC;

The difference between WHERE and HAVING is crucial and often confuses beginners: WHERE filters individual rows before grouping, while HAVING filters groups after aggregation. You cannot use aggregate functions in WHERE, but you can in HAVING.

Best Practices

As you continue to learn SQL basics, follow these principles to write safe and efficient queries:

• Always preview DELETE and UPDATE with a SELECT first to confirm which rows will be affected.
• Use transactions (BEGIN, COMMIT, ROLLBACK) for multi-step operations so you can undo mistakes if something goes wrong partway through.
• Index columns you filter or join on to dramatically speed up queries on large tables.
• Avoid SELECT star in production — list the columns you need explicitly to save memory and prevent errors if the schema changes.
• Use consistent naming conventions for tables and columns, such as snake_case, to keep your schema readable.
• Back up your data before running destructive commands like DROP or bulk DELETE.

SQL is a powerful, portable skill that will serve you throughout your career. The more you learn SQL basics, the more valuable your data skills become across PostgreSQL, MySQL, SQLite, and beyond. Practice by creating a sample database, inserting test data, and writing queries that join, filter, and aggregate. Think you have mastered the fundamentals? Take the quiz below to test your knowledge of SELECT, joins, grouping, and aggregates.