Free lesson

Tables, rows & columns

Meet the shape all data engineering revolves around, then write your first query.

6 min7 steps

In this lesson you will

  • Read a table: columns, rows and keys
  • Show a whole table with SELECT
  • Choose just the columns you need

Words you'll meet

Column
One kind of fact, running top to bottom.
Row
One thing that happened, running left to right.
Key
A column whose value is different on every row.
Query
A question written in SQL.
Start the lesson

The lesson, step by step

Each step teaches one idea, then hands you a real database to try it on. The exercises run in your browser.

  1. Step 1 of 7

    Columns

    Almost everything a data engineer touches ends up as a table. This one is called orders. It belongs to a small coffee chain.

    A column runs top to bottom and holds one kind of fact about every order.

    Your turn: Click the column that tells you which city each order came from.

  2. Step 2 of 7

    Rows

    A row runs left to right. Each row is one thing that happened: here, one order.

    Your turn: Click the row for order number 5.

  3. Step 3 of 7

    Keys

    Ada ordered twice. Latte appears three times. Most columns repeat.

    A column whose value is different on every row is called a key. Keys are how you tell one row from another, and how a pipeline notices when the same order has been filed twice.

    The formal name is primary key. A key can be a single column, or several together, such as shop plus receipt number.

    If two rows share a key, either the same thing was recorded twice or the column was never really a key. Both are worth finding out.

    Your turn: Which column could be the key of this table?

  4. Step 4 of 7

    Your first query

    You talk to tables in SQL. The simplest sentence in the language is:

    SELECT * FROM orders

    SELECT says what you want, * means "every column", and FROM says which table.

    SQL words are not case sensitive: select works the same as SELECT. Capitals are a habit that makes the SQL words stand out from your own table and column names.

    Line breaks and extra spaces do not matter either, so long queries are usually spread over several lines.

    Your turn: Type SELECT * FROM orders in the box below, then press Run.

  5. Step 5 of 7

    What * means

    The star, *, is shorthand. Right after SELECT it means "every column, in the order the table stores them". You did not have to know the column names to see everything.

    That is handy for a first look at a table you have never seen. The cost: you get every column whether you need it or not, and if someone adds a column tomorrow, your result changes shape.

    Watch out: * has a second job in SQL. Between two numbers, as in quantity * price, it means multiply. The position tells you which: alone after SELECT it means every column; between two values it means times.

    Your turn: SELECT * FROM orders returned this. How many columns does * stand for here?

  6. Step 6 of 7

    Choosing columns

    You rarely want everything. Name the columns you want, separated by commas:

    SELECT customer, city FROM orders

    Asking only for the columns you need is more than tidiness. Databases built for analysis store each column separately, so fewer columns means less data to read, which means faster and cheaper queries.

    SELECT * is fine for looking around. In a pipeline it is a risk, because the result changes shape the day someone adds a column.

    Your turn: Show only the customer and the item for every order.

  7. Step 7 of 7

    Challenge

    Recap. SELECT lists the columns you want, separated by commas, in the order you want them. FROM names the table.

    The delivery team wants a list. No hints on this one.

    Your turn: Show the order_id, city and order_date for every order, in that order.

Reading is the easy part.

Every step above has an exercise. Do it for real, free, no account needed.

Start the lesson