SQL and PostgreSQL: from your first query to optimisation

The steps

1. Step 1
   Beginner

   SQL for beginners: explore a PostgreSQL database

   Mama Ngo has run her shop in Nkoldongo, Yaoundé, for twenty years. Groceries, soap, sardines, Maggi cubes. And a notebook, where everything was written down. In the spring, her nephew set up a till on an old laptop; since then, everything is entered and nobody knows anything any more. "The notebook, I opened it and I could see. Here, I see a black screen." This workshop is a first encounter with a database, and it is designed for someone who has never opened one. You will not write a line of SQL and you will not open any terminal: you work in a web interface, from a link, and you look. Learning the command line and the idea of a table at the same time means stacking two difficulties that have nothing to do with each other. The guide is not read, it is practised. In six places it asks you to answer before carrying on, and the commented answer waits under a ribbon you only pull once you have decided. Looking for an answer, even getting it wrong, fixes an idea far better than reading it. In an hour and a quarter, you will know what a table, a column, a row and a type are; why a price stored as text breaks the calculations six months later; and why an empty cell and a zero do not mean the same thing, the confusion that will make two thirds of someone else's sales disappear, later in the path. SQL is the most requested technical skill for entry-level jobs, and the one most independent of the employer's technical choices: it is just as useful in an SME running an ERP as for reconciling a mobile money account. This is where you start.

   • 4.0 out of 5, (1 reviews)
   • Beginner
   • 1 h 15 min
   • 250 credits
   • First lab free
2. Step 2
   Beginner

   SQL: filter and sort with WHERE and ORDER BY

   "Before, I opened the notebook on Sunday and I could see what was going to run out. Now I can't see anything coming. What is about to run out on the shelves?" You know what a table is. This workshop teaches you to ask it a question: your first SQL query, in the same web interface, still without a terminal. SELECT chooses the columns, WHERE chooses the rows, ORDER BY sorts, LIMIT cuts. Every chapter ends with an exercise you write yourself, with the commented answer under a ribbon. In an hour and a half you write the query that gives Mama Ngo back her Sunday page. And you meet the two mistakes that raise no message. The first: looking for an empty cell with "= NULL" returns zero rows when there are three, and nothing warns you. The second: AND comes before OR, silently; your query answers you, and it has answered a different question from yours. You will see it on a result that changes before your eyes depending on where you put two brackets. The workshop ends with a troubleshooting table built on PostgreSQL's real messages, including the most dangerous of all, the one that does not exist: an empty result that looks like an answer.

   • 5.0 out of 5, (1 reviews)
   • Beginner
   • 1 h 30 min
   • 250 credits
   • First lab free
3. Step 3
   Beginner

   SQL: count and group with COUNT and GROUP BY

   "How much did I sell this month? And above all: what really brings money in? Because rice, I sell a lot of it, but the bag is expensive too." Until now you were narrowing and sorting rows. Here they disappear: three hundred and twenty sales fold into a handful of figures. It is SQL's second wall after the join, and it gets a workshop of its own rather than a sub-chapter. COUNT, SUM, DISTINCT, GROUP BY, HAVING, and SQL's most frequent error message, the one that complains about a column missing from the GROUP BY. You will trigger it on purpose, and you will understand why PostgreSQL refuses rather than inventing an answer where other databases used to pick a row at random without saying anything. You will also learn to filter the groups themselves, which WHERE cannot do, and you will see why: when WHERE does its work, the groups do not exist yet. The workshop ends with a challenge with no solution given: find the product that brought in the most money in August. The answer is in the guide, folded under a ribbon you only pull after searching. And a surprise awaits you in the figures: a category comes second when it should not. You already know the cause. Wrong data does not show in the data, it shows in the result that surprises you, and a surprising result is checked before it is announced. That is the professional reflex this workshop builds.

   • 5.0 out of 5, (1 reviews)
   • Beginner
   • 1 h 30 min
   • 250 credits
   • First lab free
4. Step 4
   Beginner

   SQL: combine two tables with JOIN

   "The notebook said: three soaps to Mrs Essomba. The screen tells me: product 5, customer 1. I have a shop to run, not numbers to decode." This is SQL's wall, and it gets a workshop of its own. No functions, no decorative aliases: linking two tables, and understanding what the operation keeps and what it throws away. You will start by answering yourself the question workshop 1 left open: why the till records a number rather than a name. The answer fits in one sentence that holds every database in the world together: a piece of information is written in one place only. Then you will see 218 sales disappear at a stroke. A strict join on customers takes the shop from 320 sales down to 102, without the slightest error message, because two thirds of people pay cash without leaving their name. That is the difference between JOIN and LEFT JOIN, and it is never chosen out of habit: it is chosen by the question you are asking. A whole chapter is devoted to the reflex that will one day stop you announcing a wrong figure: counting the rows after each join and comparing with the starting total. A join does not only lose rows, it can also invent some, and no message will warn you. Finally you will learn the anti-join, the pattern that answers every "who has never" question: customers with no order, invoices with no payment, products nobody ever buys on credit. The workshop ends with a challenge, Mama Ngo's five biggest debtors, whose answer is folded under a ribbon you only pull after searching.

   • 5.0 out of 5, (1 reviews)
   • Beginner
   • 1 h 30 min
   • 400 credits
   • First lab free
5. Step 5
   Beginner

   SQL: INSERT, UPDATE, DELETE and first steps with psql

   "A lady asked for the liquid soap. The till said twelve thousand five hundred. She left. Ludovic typed one zero too many. Fix it, but don't break anything, my whole shop is in there." Until now, a wrong query returned a wrong result. From this workshop on, a wrong query destroys. It is the first workshop of the path where the interface allows writing. You will first open your first terminal, and it is not a decorative extra: what follows requires a session, that is, a continuous conversation with the database. A web interface does not show you the session; psql does, and the prompt itself changes before your eyes to tell you at every moment what you are risking. You will then trigger the disaster on purpose: an UPDATE without a WHERE clause, which sets the shop's twenty-five products to the same price in one second. Then you undo it with one word. That is the whole lesson: BEGIN before, a check in the middle, COMMIT only once you understand what you are touching. You will also discover that the row counter lies by omission. "DELETE 1" proves you deleted a single row, never that it was the right one; a false duplicate will show you. And "UPDATE 0" proves your fix never happened, which nothing else will warn you about. Finally the workshop adds writing a new row with INSERT, the primary key that physically refuses a duplicate, and the foreign key that stops you leaving a sale orphaned. A well-built database is not an obstacle to get around: it is a colleague who reviews your work and stops you when you get it wrong. It is also the bridge to the backups path, whose founding incident is exactly the UPDATE without WHERE you have just triggered safely.

   • No reviews yet
   • Beginner
   • 1 h 30 min
   • 400 credits
   • First lab free
6. Step 6
   General

   PostgreSQL: constraints, data types and privileges

   Mama Ngo is back from the village. While she was away, her niece Nadia ran the till for a whole week. "There are sales at zero, a payment method nobody knows, and the rice twice. Couldn't the machine refuse on its own?" In the previous workshop, you learnt to write carefully. But care only protects what you type yourself. The only protection that works for everyone is the database that refuses. You start by finding the damage with the queries you know, then you try to set the rule that would have prevented it, and the database refuses to create it: existing rows already violate it. That refusal becomes your clean-up plan. You clean up without losing anything, after asking Nadia what really happened, because strange data is not necessarily wrong. Then you set the rules the database will enforce on everyone: CHECK, UNIQUE, DEFAULT. You merge the duplicated rice despite the foreign key standing in the way, you choose the type of each piece of information on a delivery note, and you create the deliveries table, with its automatic numbering and its link to the products. Finally, you discover that a new table is closed to everyone, and you grant the right to read it, and only to read it. Everything happens in the terminal, with the database owner's account. At the end, the rules you set and the table you created are checked in the database, and the result counts for your ranking.

   • No reviews yet
   • Intermediate
   • 2 h
   • 750 credits
   • First lab free
7. Step 7
   General

   Advanced SQL: subqueries, WITH (CTEs) and window functions

   The shop finally knows what it pays the wholesaler. Mama Ngo has two questions: "Was September better than August? And what do I really earn on, once the wholesaler is paid?" Neither fits in a simple query. The first compares a month with the previous one, the second combines tables grouped differently. It is SQL's new wall: the question that takes several steps. You learn to write it the way you think it, one named step after another, each checkable on its own. Along the way, three traps that raise no message. A division between integers that shows 0 % instead of 58 %. A NOT IN that returns zero rows because a single empty value lurks in the list. And a direct join between two tables that multiplies the amounts without warning. Each one is triggered on figures you know, so that you see they are wrong instead of believing them. You group by month and by day of the week, you calculate each product's margin with WITH, you classify with CASE, and you discover window functions: lag to compare each month with the previous one, rank to rank without grouping. The challenge: the most profitable product in each aisle. And a surprise in the figures: what fills the till is not what keeps the shop alive.

   • No reviews yet
   • Intermediate
   • 2 h
   • 750 credits
   • First lab free
8. Step 8
   General

   PostgreSQL: speed up a query with EXPLAIN and indexes

   The shop has joined the Nkoldongo traders' cooperative: forty shops, a single database, a million sales. The chairman calls Mama Ngo: "When a loyal customer comes in, the screen takes ages to bring up their history. And on Saturdays, it's worse." No query is wrong: they are slow. And in front of a slow query, the temptation is to try things at random. This workshop does the opposite: before speeding up a query, you read how PostgreSQL runs it. You measure, then you read the plan with EXPLAIN and EXPLAIN ANALYZE: a million rows read to return fourteen, by three processes in parallel. You create the index that changes the plan, and you prove it with the plan itself, learning to look for a single word in it. Then the three cases where an index exists and is useless: a function on the column, a condition that is not selective enough, a search in the middle of a text. You also measure what an index costs on every write, and you drop the one that is not used. Finally, full-text search: searching for words rather than letters, plural or singular, upper case or not, with the index that takes it from more than a second to a few dozen milliseconds. The challenge: the Saturday dashboard, and the two-column index that makes it instant.

   • No reviews yet
   • Intermediate
   • 2 h 15 min
   • 1,000 credits
   • First lab free
9. Step 9
   General

   PostgreSQL: transactions, locks and deadlocks

   Mama Ngo has bought a second till. Last Saturday, there was only one bottle of vegetable oil left: "A lady paid for it at Nadia's till while a gentleman was paying for it at mine. Two receipts, one bottle. Didn't the machine see we were selling the same thing at the same time?" As long as there was only one till, sales went through one after the other. With two, they cross. This workshop opens two terminals, one per till, and replays the Saturday in the exact order in which the two tills act. You first see what one session sees of another's work, before and after COMMIT, then you reproduce the phantom sale: no error, two sales committed, one bottle. You meet your first lock, a terminal that does not give the prompt back because the other till holds the row. Then you write the sale that cannot oversell, by letting the database count, and you see PostgreSQL re-read the row after the wait. You reserve a row before deciding with SELECT FOR UPDATE, you work in a frozen snapshot in REPEATABLE READ until the serialisation error, and you trigger a deadlock that the database breaks by sacrificing one of the two tills. And one last scene: the forgotten transaction that freezes everyone. Finding it and cutting it off is the administrator's job, and it is the challenge that closes the path.

   • No reviews yet
   • Intermediate
   • 2 h 15 min
   • 1,000 credits
   • First lab free
10. Step 10
    General

    PostgreSQL challenge: unblock a session that freezes the till

    Saturday, 10 a.m. The shop is full, and Nadia's till is spinning in the void. It does not crash, it does not display anything: it waits. Ludovic is not answering his phone. Mama Ngo is waiting with a customer, a pack of sardines in her hand. This challenge has no step-by-step guide. It has a mission, rules, and the official PostgreSQL documentation. For the first time you are on the other side of the server: superuser, with a view of every open session, what each one is doing, and what each one is waiting for. It is up to you to trace the blocked till back to the session blocking it, to understand what it holds and since when, then to cut it off, and only it, without restarting the server or stopping the till. You will also have to make sure it does not happen again: configure the server so that a forgotten transaction can no longer block the shop for more than thirty seconds, and leave Ludovic a note so he understands what happened. This is exactly the work of a database administrator: observing, understanding, stepping in without interrupting the service, and relying on the documentation. The natural next step after this path.

    • No reviews yet
    • Intermediate
    • 1 h 30 min
    • 1,000 credits
    • First lab free