Learn SQL: the complete beginner's guide

Why learn SQL today?

A company stores its customers, orders, products and payments in structured data. SQL allows you to ask a specific question to this data: find an order, calculate a revenue, reconcile customers with their purchases or check an anomaly.

SQL is a language. PostgreSQL, MySQL and SQL Server are database management systems that each understand a dialect of this language. So you can learn the common principles before specializing in an engine.

A spreadsheet remains very useful for a one-off analysis. A database becomes particularly relevant when the data is numerous, linked, shared or updated by several processes.

Start with the question, not the code

Before writing a request, rephrase the need into a testable sentence. “Which customers ordered?” » and “Which customers paid?” » do not designate the same perimeter. This precision avoids producing a result that is technically valid but false for the profession.

Then identify the granularity: does a line represent a customer, an order or a product ordered? Finally, identify the useful columns and the control that will verify the result.

• Question: What result should be delivered?
• Scope: which lines should be included or excluded?
• Granularity: what exactly does a row of the result represent?
• Control: what total or sample can detect an error?

Read and filter with SELECT, FROM and WHERE

Let's imagine a table orders containing the order ID, city, status and amount. SELECT chooses the columns to display, FROM indicates the table consulted and WHERE only keeps the rows that meet a condition.

The following query looks for paid orders of at least €100. Sorting places the highest amounts first to facilitate visual inspection.

• The texts are placed in apostrophes.
• Numbers are compared without apostrophes.
• AND requires both conditions to be true.
• ORDER BY changes the display order, not the stored data.

SELECT order_id, city, amount
FROM orders
WHERE status = 'paid'
  AND amount >= 100
ORDER BY amount DESC;

Calculate an indicator with GROUP BY

An analysis question often requires grouping several lines. To find out the revenue paid per city, SQL adds the amounts with SUM and creates a group per city.

Each column displayed that is not aggregated should generally appear in GROUP BY. The filter remains applied before the calculation: a canceled order does not contribute to revenue.

SELECT
  city,
  SUM(amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY city
ORDER BY revenue DESC;

Connect tables with JOIN

The customer name belongs to table customers, while the amount belongs to orders. A join reconstitutes the relationship using a common key, here customer_id.

The following query calculates the amount paid per customer. INNER JOIN only keeps orders associated with an existing customer. Before aggregating, verify that join does not multiply rows unexpectedly.

SELECT
  c.customer_id,
  c.full_name,
  SUM(o.amount) AS total_paid
FROM customers AS c
INNER JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.status = 'paid'
GROUP BY c.customer_id, c.full_name
ORDER BY total_paid DESC;

In what order should I learn SQL?

An effective progression alternates a short concept and immediate practice. Start by querying a table, then add aggregations and joins. The CTE and window functions become useful when these foundations are stable.

Do not measure your level by the number of keywords memorized. Measure it by your ability to explain the result, spot a scoping error and adapt a query to a new question.

• Step 1: SELECT, WHERE, ORDER BY and NULL values.
• Step 2: COUNT, SUM, AVG, GROUP BY and HAVING.
• Step 3: INNER JOIN, LEFT JOIN and granularity check.
• Step 4: Subqueries, CTE, CASE and window functions.
• Step 5: business projects and query optimization.

Mistakes that slow down beginners

Copying a solution without predicting its result gives the impression of speed, but builds little autonomy. Take a first try, read the error message, then use a targeted hint before showing the fix.

The most costly errors are not always syntactic. A join on the wrong key, a filter placed too late, or counting on the wrong granularity can produce a credible but incorrect figure.

• Using SELECT * without knowing which columns are needed.
• Confuse WHERE, which filters rows, and HAVING, which filters groups.
• Forget that NULL is tested with IS NULL or IS NOT NULL.
• Trust the result without controlling a few lines and a total.

Turn this guide into practice

If you're starting from scratch, follow the beginner's path in order and run each example. If you have already written a few queries, the level test will tell you the skills to consolidate. The catalog of exercises then allows you to work on a specific concept.

• Start the course SQL beginner
• Practice with the corrected SQL exercises
• Test your level SQL
• Delve deeper into SELECT, FROM and WHERE