Start Here: The SQL Thinking Loop — Your First Query, CloudBeaver & the Koans

Watching isn't the same as doing

You can watch twenty SQL tutorials back to back and still go blank the first time someone asks you to write a query yourself. That's not a discipline problem. Watching and doing are just different skills, and only one of them is the one you'll need at your desk.

So we're going to do it the other way around. You write the queries, against real, production-like data, starting in the next few minutes. Each lesson gives you only as much theory as you need to write something useful, then gets out of the way so you can practice. The finer details come afterward, once you've got something concrete to hang them on.

This is the first of the twelve lessons in Series 1 — SQL Fundamentals. Together they take you from this first query through filtering, sorting, totals, grouping, joins and missing values, to a real report built from three tables.

How every lesson goes

1. Type the query yourself. Typing teaches your hands and your head; copy-paste only teaches your eyes.
2. Predict the answer before you run it. Say it out loud: "I expect four." If you're wrong, you just learned something. If you're right, that confidence is earned. (The koans turn this into code: you fill in the number, run it, and watch it go green.)
3. Run it. No watching, and no slides you'll have forgotten by lunch.

Keep that up for a handful of lessons and you'll find yourself answering questions nobody else around you can.

The loop you'll use every time

There's one habit underneath everything else in SQL. Once it's second nature, the rest is mostly vocabulary. Here it is:

1. Ask your question in plain language.
2. Translate it into SQL. (SQL reads a lot like English, with stricter grammar.)
3. Guess the answer before you run anything.
4. Run it, and see how close your guess was.

That's the whole loop. Let's run it together right now.

A question someone might actually ask

Picture your manager stopping by your desk:

How many orders did we get in June 2024?

That's step one, the plain-language question. For step two, you can almost translate it word by word:

• It asks for data, so you start with SELECT.
• It's about orders, so you read from the "Orders" table.
• It asks "how many," which is count(*).
• It's only about June, so you filter the date with WHERE.

SELECT count(*)
FROM "Orders"
WHERE "OrderDate" >= DATE '2024-06-01'
  AND "OrderDate" <  DATE '2024-07-01';

Now step three, and please don't skip it: take a guess. I'd say fewer than ten, since summer tends to be quiet for most businesses. Make your own guess before you read on.

Your turn

Type this into CloudBeaver yourself (resist the copy-paste, typing is part of how it sticks) and hit Run:

Four. The guess held up, and a single line of SQL just answered a real question about a real company. Read it back the way you'd say it out loud: count the rows in Orders where the order date falls in June. count(*) counts the rows, WHERE keeps only the ones you asked about, and the range from June 1 up to (but not including) July 1 catches the whole month without you ever having to think about how many days it has.

So why guess first?

The guess isn't a party trick. When you know your data and how the business runs, your guess is an informed one, and the small act of making it teaches you something each time. Do it for a few weeks and you start to develop a feel for the numbers: you'll hear a question and already sense roughly what the answer should look like. And when a result comes back wildly different from what you expected, that gap is usually trying to tell you something, like a typo, a wrong assumption, or a surprise hiding in the data.

Why SQL reads like English

You probably noticed how readable that query was. There's a reason for it. SQL is a declarative language: you describe what you want and let the database work out how to get it. That's the opposite of an imperative language like Java, where you have to spell out every step yourself. Because you're stating the what and not the how, a lot of SQL really does read like a sentence, which means you're further along than you think. Plenty of the time you can simply read a query and know what it does.

What you're running it on

That query ran against PostgreSQL, loaded with the Northwind sample dataset, and you reached it through CloudBeaver. (If none of that is set up yet, the Learn Data guide gets Northwind PostgreSQL and CloudBeaver running in a couple of minutes.)

Why PostgreSQL? It starts quickly, it's light to run, and its SQL stays close to the ANSI standard. "ANSI SQL" just means SQL written the standard way, the way that works across pretty much every serious database, so what you practice here carries over to Oracle, SQL Server, MySQL, and Db2 with little fuss. Postgres is also showing up more and more inside big companies, which doesn't hurt. The point is that you aren't learning one product. You're learning SQL.

Real data, not toy data

No foo, no bar here. These are real customers in real countries, so go ahead and ask who they are:

SELECT "CompanyName", "Country"
FROM "Customers"
ORDER BY "CompanyName"
LIMIT 5;

LIMIT 5 asks for five rows, and ORDER BY decides which five. Without it you'd still get five, but the database would be free to hand you any five it liked, in any order — and a different day, or a different engine, could hand you a different set. Whenever you cut a result short, say what you're sorting by. (There's a lesson of its own on this later in the series.)

This is Northwind, a small import/export business with the usual moving parts: customers, orders, products, suppliers, employees. It's real enough to ask honest questions of, and small enough to keep the whole thing in your head.

The koans: now you fill in the blanks

So far I've handed you the queries. The koans are where you start filling them in yourself. A koan is a tiny fill-in-the-blank exercise: a failing test with a ___ where the answer should go. You fill in the blank to make the test pass, passing it unlocks the next one, and you move along one small step at a time, "to enlightenment." It's SQL by doing, in the smallest possible doses.

From a terminal:

cd c:/DataPallas/db/datazeus
.\zeus.bat koans learnsql series1 _00

On macOS or Linux, run ./zeus.sh koans learnsql series1 _00 from the same folder. The three arguments are course, series and episode.

Says the lesson isn't there? Your copy of DataZeus predates this lesson — run .\zeus.bat update (or ./zeus.sh update) once to fetch the latest lessons, then run the command again. Koans you have already solved are kept.

The file you edit is:

c:\DataPallas\db\datazeus\tests\src\koans\groovy\datazeus\learnsql\series1\_00\StartHereKoans.groovy

Open it in any editor — Notepad++, VS Code, whatever you have. Each koan has a ___ where the answer belongs. Replace one, save, re-run the command above, and watch that koan go green.

These run on DuckDB, a database that lives in a single file. Think SQLite, but much closer to PostgreSQL in how it behaves. The koans use a ready-made northwind.duckdb file, so there's nothing to install and no server to start. You run the command, the first koan goes green, the next shows up red with a hint, and you work down the list: fix, rerun, green, and on to the next.

Two engines, one Northwind

You might wonder why two databases are in play. They're really two doors into the same data:

• DuckDB is for practice. It's a file, there's no server, and nothing gets between you and the exercise. It's what the koans run on.
• PostgreSQL is for exploring. It's a real database server, and CloudBeaver connects to it out of the box, so you can poke around visually.

Both come loaded with the same Northwind, same tables and same rows. DuckDB's SQL was actually derived from PostgreSQL's, so the same query gives you the same answer in either one. Practice in DuckDB, explore in Postgres, and trust that what you learn in one holds in the other.

Why all the double quotes?

You may have caught the quotes around the table names: "Orders", "Customers". One rule explains the whole thing:

An unquoted name gets case-folded by the database. A quoted name is taken exactly as you wrote it.

Northwind's tables were created with quoted, capitalized names, so the table really is called "Orders", capital O and all. The query quotes them to match:

SELECT count(*)
FROM "Orders"
WHERE "OrderDate" >= DATE '2024-06-01'
  AND "OrderDate" <  DATE '2024-07-01';

Now suppose you drop the quotes, and write orders instead of Orders:

SELECT count(*)
FROM orders
WHERE OrderDate >= DATE '2024-06-01'
  AND OrderDate <  DATE '2024-07-01';

In DuckDB, say while you're doing the koans, this works fine: DuckDB ignores case, so orders finds "Orders" and OrderDate finds "OrderDate". Paste that same unquoted query into CloudBeaver against PostgreSQL, though, and it falls over. Postgres lowercases the unquoted names, goes looking for a table called orders and a column called orderdate, and finds neither. Same SQL, different outcome.

That's why the quoted version is the one to write. Double-quoted names match the stored name exactly, and a DATE literal is read the same way everywhere, so the quoted query behaves identically on DuckDB and PostgreSQL.

The ANSI-standard form, double-quoted names plus a DATE literal, gives you the same result on DuckDB and PostgreSQL.

A good default, then: write standard SQL, and when two engines genuinely disagree (like this quoting business), don't paper over it, understand why. There's a twist worth holding onto, though. Every so often a vendor's own non-standard feature is exactly where a big performance win lives. So lean on the standard by default, stay aware of where the engines differ, and reach for a vendor-specific trick when you have a real reason to, something we'll run into later in the course.

What's next

Next up is SELECT, where you meet your tables, rows, and columns by actually querying them. (The rest of the series lives on the Learn SQL course page.)