SQL Basics: SELECT, WHERE, JOIN, GROUP BY, Subqueries and Practice Exercises With a Sample Database

SQL (Structured Query Language) is the standard way to work with relational databases such as MySQL, PostgreSQL, SQL Server and SQLite. If you want to work with data, in analytics, development or business, SQL is among the most valuable skills. This guide uses a small sample database so every query can be tried.

Set up a practice database

The easiest option is SQLite, which comes with Python and needs no server. You can also use MySQL or PostgreSQL. Create these tables and rows:

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount REAL,
  order_date TEXT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);
INSERT INTO customers (name, city) VALUES
 ('Asha','Mumbai'),('Ravi','Delhi'),('Meera','Pune'),('Kabir','Mumbai');
INSERT INTO orders (customer_id, amount, order_date) VALUES
 (1, 1200, '2026-01-05'),(1, 800, '2026-02-10'),
 (2, 450, '2026-02-11'),(3, 2300, '2026-03-02');

A relational database stores data in tables with rows and columns. A primary key identifies each row uniquely, and a foreign key links to another table.

SELECT: reading data

SELECT * FROM customers;
SELECT name, city FROM customers;
SELECT DISTINCT city FROM customers;

WHERE: filtering

SELECT * FROM orders WHERE amount > 1000;
SELECT * FROM customers WHERE city = 'Mumbai' AND name LIKE 'A%';
SELECT * FROM orders WHERE order_date BETWEEN '2026-02-01' AND '2026-02-28';
SELECT * FROM customers WHERE city IN ('Delhi','Pune');

Use AND, OR and NOT to combine conditions, and parentheses to control order. LIKE uses % as a wildcard, and IS NULL checks for missing values (not = NULL).

ORDER BY and LIMIT

SELECT * FROM orders ORDER BY amount DESC LIMIT 2;

Aggregates and GROUP BY

SELECT COUNT(*) AS total_orders, SUM(amount) AS revenue, AVG(amount) AS avg_order
FROM orders;

SELECT customer_id, SUM(amount) AS spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;

GROUP BY collapses rows into groups, and HAVING filters the groups, whereas WHERE filters rows before grouping. Common functions are COUNT, SUM, AVG, MIN and MAX.

JOIN: combining tables

SELECT c.name, o.amount, o.order_date
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id;

SELECT c.name, COALESCE(SUM(o.amount), 0) AS spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

• INNER JOIN returns only matching rows from both tables.
• LEFT JOIN returns all rows from the left table, with NULLs where there is no match. The second query shows customers like Kabir with zero spend.
• RIGHT and FULL JOIN mirror this idea, and are not supported in every database.

Subqueries

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE amount > 1000);

Changing data

INSERT INTO customers (name, city) VALUES ('Zoya','Chennai');
UPDATE customers SET city = 'Navi Mumbai' WHERE name = 'Kabir';
DELETE FROM orders WHERE id = 3;

Always include a WHERE clause with UPDATE and DELETE. Run the equivalent SELECT first to see what will change, and use transactions (BEGIN, COMMIT, ROLLBACK) where possible.

Indexes and design basics

• Indexes speed up searches on columns you filter or join on, but slow down writes and take space.
• Normalise data to avoid duplication: store customers once and refer to them by ID.
• Choose proper data types. Use DECIMAL for money in production databases to avoid rounding errors.

Common mistakes

• Selecting all columns on large tables.
• Forgetting the JOIN condition, which creates a huge cross product.
• Mixing aggregated and non-aggregated columns without GROUP BY.
• Building queries by concatenating user input, which allows SQL injection. Use parameters, as in the Flask guide.
• Comparing with NULL using equals.

Practice questions

1. List customers from Mumbai who have placed at least one order.
2. Find the month with the highest revenue.
3. Show customers who never ordered.
4. Find each customer's largest order.
5. Rank customers by total spend.

Continue with Python for automation, PHP and MySQL for web apps, or the advanced Excel guide to compare spreadsheet and database approaches.

Window functions and CASE: the next level

Once the basics feel comfortable, learn the features analysts use daily. A CASE expression lets you create categories inside a query, such as labelling orders as small, medium or large. Window functions such as ROW_NUMBER(), RANK() and SUM() OVER (PARTITION BY ...) let you compute running totals or rank customers without collapsing rows. Common table expressions, written with WITH, break a long query into readable steps. Try rewriting your practice questions using each of these, and compare the clarity of the result with nested subqueries.

How to practise

Use free sandboxes and public sample databases, and set yourself a small business question every day: which city generates the most revenue, which customers have not ordered for ninety days, what is the monthly growth rate. Write the query, check the answer by hand on a few rows, and only then trust it. Save your best queries in a notes file with comments explaining the logic.

Frequently asked questions

What is SQL?

A language for storing, querying and managing data in relational databases.

What is the difference between WHERE and HAVING?

WHERE filters rows before grouping, HAVING filters groups after.

What is a JOIN?

A way to combine rows from two or more tables using related columns.

Which database should I learn first?

SQLite for practice, then MySQL or PostgreSQL.

Is SQL case sensitive?

Keywords are not, but data comparisons may be, depending on the database.

How do I prevent SQL injection?

Use parameterised queries and never concatenate user input.

Last reviewed 8 November 2026. Rates, limits and rules change, so confirm figures with the official source before you act.