Skip to content
Courses

SQL

SQL is the language for asking a database for data: which columns, from which table, which rows and how to summarize them. You get a table back.

Select any words here to ask your Maestro about them.

SELECT branch, SUM(amount) FROM orders WHERE amount > 30 GROUP BY branch HAVING SUM(amount) > 300. Without the WHERE, North adds up to $349; two of its orders are $21 and $28. Does North show up in the result?

Each dot is a row in the orders table; the bars, the sum per branch. Example data.

What it's for: getting answers out of a database without copying anything into Excel: how many orders each branch had, which customers haven't come back, how much sold yesterday. What you'd do in Excel with filters and a pivot table is, in SQL, a query a few lines long that you can run again tomorrow.

How to read it: a query says which columns you want (SELECT), from which table (FROM), which rows (WHERE) and how to summarize it (GROUP BY). What comes back is another table, which you can export as a CSV. Before you count rows, be clear on what each row is: the table's grain.

Say it "S-Q-L" or "sequel"; both are common. It stands for Structured Query Language.

Learn it from scratch in SQL for Data People. If you already group, go on to In what order does a SQL query with window functions run?.

How it's used

How to write it

How many orders each branch had

  1. marked: SELECT branch, COUNT(*) AS order_countThe columns you want back.
  2. FROM ordersThe table they come from.
  3. GROUP BY branchOne row per branch.
  4. ORDER BY order_count DESC;The one with the most, first.
  5. Downtown 9 · North 8 · South 7What comes back: another table, 3 rows long.
It reads almost like a sentence: select the branch and how many orders, from orders, grouped by branch. It works the same in PostgreSQL, MySQL, SQL Server, SQLite or BigQuery, with small differences.

When to look closer

Rows with NULL go missing

Orders that didn't use the TEN coupon

  1. marked: SELECT COUNT(*) FROM orders
  2. WHERE coupon <> 'TEN';

Of 24 orders, 6 used the TEN coupon, 14 another coupon and 4 none (NULL). How many does this query count?

Ask your Maestro about SQL

It answers from the free course page closest to this word, the way it answers while you study.

Example

You: What does "SQL" mean, in plain words?

Your Maestro: I'll explain it with a case of yours: it sticks better than a definition. Which question at work would you answer with a query?

Asking about: Answers from the page "How do I check a table's rows, columns and data types in pandas?"

Only this browser keeps what you ask here. How it works

Learn more

Related terms

  • Database

    A database stores data in connected tables, with rules for each column, so many people and programs can read and change it without stepping on each other.

  • Pivot table

    A pivot table summarizes a long table in seconds: it groups by the columns you pick and sums, counts or averages another, with no formulas.

  • CSV

    A CSV (comma-separated values) is a plain-text file that holds a table: one row per line, with commas between the columns.

  • Grain (of a table)

    What one row of a table stands for: a person, a transaction, a planet, a person per language. Counting rows counts what you want only if you know the grain.

The whole glossary