Basic SQL Commands

Dialect note: AUTO_INCREMENT, DIV, and LIMIT are MySQL-style features. DISTINCT removes duplicate selected rows (or duplicate combinations when several columns are selected).

Reading Data with SELECT, FROM, WHERE, ORDER BY, LIMIT, DISTINCT, and AS

1. Introduction

Once you understand what a database is and how SQL works, the next step is learning how to read data from your tables.

In real-world projects, most of your SQL work starts with questions like:

• Which users signed up today?
• What are the top 10 highest-paying customers?
• Which countries belong to Asia?
• Which products are out of stock?

To answer these kinds of questions, SQL gives us a small set of powerful commands that work together.

These commands help you:

• choose the correct table
• select only the columns you need
• filter unwanted rows
• sort results
• limit output
• remove duplicates
• rename columns for cleaner output

These are the building blocks of almost every SQL query.

2. The Basic SQL Commands

At a high level, every SQL query answers five simple questions:

1. From which table do I want data?
2. Which columns do I want?
3. Which rows should I keep or remove?
4. In what order should the results appear?
5. Do I want all rows or only a few?

These questions are answered using:

• SELECT
• FROM
• WHERE
• ORDER BY
• LIMIT
• DISTINCT
• AS

3. Mental Model of Basic SQL Commands

This is the simplest way to think about basic SQL:

• SELECT → which columns to show
• FROM → where to read from
• WHERE → which rows to keep
• ORDER BY → how to sort
• LIMIT / OFFSET → how many rows to return
• DISTINCT → remove duplicates
• AS → rename columns/tables in output

This is the core mental model of reading data in SQL.

4. Sample Table Used in Examples

We will use a simple countries table throughout this chapter.

CREATE TABLE countries (
  id INT PRIMARY KEY AUTO_INCREMENT,
  country VARCHAR(64) NOT NULL,
  population BIGINT NULL,
  area INT NOT NULL,
  region VARCHAR(64) NOT NULL
);

Insert Sample Data

INSERT INTO countries (country, population, area, region) VALUES
('India',     1400000000, 3287000, 'Asia'),
('China',     1200000000, 4287000, 'Asia'),
('Brazil',     214000000, 8516000, 'South America'),
('Australia',   25700000, 7692000, 'Oceania'),
('USA',        331000000, 9834000, 'North America');

Sample Data

5. SELECT and FROM

What is SELECT?

SELECT tells SQL which columns you want to retrieve.

Without SELECT, SQL does not know what data you want to see.

What is FROM?

FROM tells SQL which table to read data from.

Without FROM, SQL does not know where the data lives.

Example 1 — Select All Columns

SELECT *
FROM countries;

Explanation

• SELECT * means fetch all columns
• FROM countries means read data from the countries table

This is useful when exploring a new table.

Output

Example 2 — Select Specific Columns

SELECT country, population
FROM countries;

Explanation

Only fetch:

• country
• population

This makes output smaller, faster, and cleaner.

In real projects, always select only what you need.

Output

6. WHERE

What is WHERE?

WHERE is used to filter rows.

It decides which rows stay in the result and which rows are removed.

Only rows matching the condition are returned.

Example — Filter by Region

SELECT *
FROM countries
WHERE region = 'Oceania';

Explanation

Keep only rows where region is Oceania.

Output

7. Comparison Operators

Comparison operators are used inside WHERE conditions.

These operators are the foundation of filtering data.

8. Logical Operators

Logical operators combine multiple conditions.

They are used inside WHERE.

8.1 AND

AND means all conditions must be true.

SELECT *
FROM countries
WHERE population > 50000000 AND area < 5000000;

Explanation

Return countries where:

• population > 50M
• area < 5M

Both conditions must be true.

Output

8.2 OR

OR means at least one condition must be true.

SELECT *
FROM countries
WHERE region = 'Asia' OR region = 'Oceania';

Explanation

Return countries in:

• Asia
• Oceania

Output

8.3 NOT

NOT reverses a condition.

SELECT *
FROM countries
WHERE NOT region = 'North America';

Explanation

Exclude countries in North America.

Output

9. Arithmetic Operators

Arithmetic operators are used to perform calculations.

These are commonly used in computed columns and analytics.

10. ORDER BY and LIMIT

ORDER BY

ORDER BY sorts rows.

Default sort order is ASC (ascending).

Use DESC for descending order.

LIMIT

LIMIT restricts how many rows SQL returns.

Useful for:

• top N queries
• pagination
• previews

Example — Top 2 Largest Countries by Area

SELECT country, area
FROM countries
ORDER BY area DESC
LIMIT 2;

Explanation

1. Sort by area (largest first)
2. Return only top 2 rows

Output

11. DISTINCT and AS

DISTINCT

DISTINCT removes duplicate values.

Use it when you want only unique results.

AS (Alias)

AS gives a temporary name to a column or table in the result.

This improves readability.

Example — Unique Regions

SELECT DISTINCT region AS unique_region
FROM countries;

Explanation

• DISTINCT removes duplicate regions
• AS unique_region renames the output column

Output

unique_region

Asia

South America

Oceania

North America

12. Summary

SELECT

Choose which columns to return.

FROM

Choose which table to read.

WHERE

Filter rows.

Comparison Operators

Compare values.

Logical Operators

Combine conditions.

ORDER BY

Sort results.

LIMIT

Return only a few rows.

DISTINCT

Remove duplicates.

AS

Rename output columns.

Final Mental Model

A basic SQL query answers:

• What data do I need?
• Where does it live?
• Which rows matter?
• How should it be sorted?
• How much should I return?