Think in SQL: What Is SQL? — Set Up in 5 Minutes

Understand what SQL does, why it’s still everywhere, and get a practice database running before you write your first real query.

When you order food on an app, something has to find the nearby restaurants, check the menu and save your order. When a company asks how sales went last month, someone has to get that answer from millions of records.

Behind both, there’s usually a database, and very often the language used to talk to it is SQL.

The first step in learning SQL trips up a lot of people, because they get stuck installing something before they’ve written a single line. So we’ll keep this simple. By the end of this article, you’ll know what SQL is, and you’ll have run a query on your own computer.

What is SQL, in plain words?

SQL (Structured Query Language, said “S-Q-L” or “sequel”) is the standard language for working with data stored in tables. With it, you can read data, add it, change it and remove it.

What makes SQL unusual is that you describe the result you want, not the steps to get it. You might say, “Give me the three most expensive products.” You don’t say which rows to check first or how to sort them. The database works that out.

Think of it like ordering in a restaurant. You ask for a dish, and the kitchen decides how to make it.

Why bother learning it?

SQL has been around for about 50 years, and it’s still a core skill for working with data. A few reasons it has lasted:

• It’s used in many jobs. Developers, data analysts, data engineers and many product and marketing people use it.
• It handles huge amounts of data. Queries that would freeze a spreadsheet are routine for a database.
• It carries over. What you learn on one database mostly works on the others.
• You can repeat your work. A query is just text, so you can save it, share it and run it again next month.

The words you need

You’ll see these terms in every article in this series:

• Database: an organised collection of data.
• Table: one set of related data, laid out like a spreadsheet tab.
• Column: one kind of information, such as price.
• Row: one item, such as a single product.
• Query: a question or instruction you send to the database.
• NULL: a value that’s unknown or missing. It isn’t zero, and it isn’t empty text.

Which SQL should you learn?

You’ll hear about several database systems. All of them use SQL, but each has its own small differences, a bit like regional accents of the same language.

• SQLite: a small database stored in a single file. It needs no server, which makes it great for learning.
• PostgreSQL: a powerful open-source database, popular for applications and data work.
• MySQL: another widely used open-source database, especially common in web applications.
• SQL Server: Microsoft’s database, common in larger companies.

We’ll start with SQLite because you can have it running in minutes. Later in the series, we’ll move to PostgreSQL, when we start building real databases. The core skills you learn now will carry over.

Set up your practice store

Throughout this series, you’ll work with the data of a small online store. Pick one of these two options.

Option A: DB Browser for SQLite (best for most beginners)

This is a free app for Windows, Mac and Linux. It gives you a simple window for writing queries and seeing the results in a neat grid.

Step 1: Install it

Go to sqlitebrowser.org/dl and find the section for your system.

The official download page. Windows downloads are listed first, then Mac and Linux.

On Windows: download Standard installer for 64-bit Windows, which suits most PCs. Open the file and follow the prompts, then open DB Browser for SQLite from the Start menu.

On Mac: download the Universal .dmg file and open it. Drag the app into your Applications folder, then open it from there.

On Linux: Snap is the simplest route on Ubuntu and many other systems. Open a terminal and run:

sudo snap install sqlitebrowser

If you don’t have Snap, download the AppImage from the same page instead. In the folder where you saved it, make it runnable, then start it. Your version number may differ from the one below:

chmod +x DB.Browser.for.SQLite-v3.13.1-x86.64-v2.AppImage
./DB.Browser.for.SQLite-v3.13.1-x86.64-v2.AppImage

If you get an error about FUSE, run the file with --appimage-extract added to the end, then start the app with ./squashfs-root/AppRun.

Step 2: Create your database

The next steps are the same on every system. Your window may look slightly different from these screenshots, which were taken on Linux, but the buttons are the same.

Open the app and click New Database. Save the file as store.db somewhere you can find it again.

Start here. Most buttons are greyed out until you create or open a database.

A window then pops up asking you to design a table. You don’t need it, because the store data will create the table for you, so click Cancel.

Click Cancel to close this window.

Step 3: Add the store data

Get Ajay Narayan’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Open the Execute SQL tab and paste in the data from “The store data” section below. Then click the Execute button, the blue play icon. On Windows and Linux, you can also press Ctrl+Return.

Paste the store data here, then click the play icon.

Step 4: Save your work

Check that the message at the bottom says “Execution finished without errors.” Then click Write Changes, which saves everything to your file.

Your data isn’t saved until you click Write Changes.

Option B: Python (if you already use it)

SQLite comes with Python, so there’s nothing else to install. Save the store data from the next section as store_setup.sql. Then, in the same folder, save this as sql_lab.py:

import sqlite3
from pathlib import Path

conn = sqlite3.connect(":memory:")
conn.executescript(Path("store_setup.sql").read_text())

# Change this query, then run the file again
query = """
SELECT name, price
FROM products
LIMIT 3;
"""

cursor = conn.execute(query)
print(" | ".join(column[0] for column in cursor.description))
for row in cursor:
    print(" | ".join("NULL" if value is None else str(value) for value in row))

Open a terminal in that folder and run python sql_lab.py. On Mac or Linux, you may need python3 sql_lab.py. The script rebuilds the store each time it runs, so you can’t break anything.

The store data

Here’s the data for both options. Copy the whole block. Don’t worry about understanding this code yet, because we’ll cover it later in the series. It creates a products table and fills it with 12 products.

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    category   TEXT NOT NULL,
    brand      TEXT,
    price      REAL,
    stock      INTEGER NOT NULL
);
INSERT INTO products VALUES
(1, 'Wireless Mouse', 'Accessories', 'Logi', 799, 120),
(2, 'USB-C Cable', 'Accessories', 'Anker', 499, 300),
(3, 'Mechanical Keyboard', 'Accessories', 'Keychron', 6999, 45),
(4, 'Laptop Stand', 'Accessories', NULL, 1299, 80),
(5, 'Webcam', 'Video', 'Logi', NULL, 25),
(6, 'Power Bank', 'Power', 'Anker', 1999, 150),
(7, 'Gaming Mouse', 'Accessories', 'Logi', 2499, 60),
(8, 'Ring Light', 'Video', 'Digitek', 1999, 0),
(9, 'Wall Charger', 'Power', 'Anker', 999, 200),
(10, '27-inch Monitor', 'Displays', 'Dell', 14999, 15),
(11, 'Portable Monitor', 'Displays', 'Asus', 14999, 10),
(12, 'Monitor Arm', 'Displays', NULL, 3499, 35);

Check that it works

Run this query. In DB Browser, first clear the store data from the editor so that only this query is left. In Python, it’s already in your script.

SELECT name, price
FROM products
LIMIT 3;

name | price
Wireless Mouse | 799.0
USB-C Cable | 499.0
Mechanical Keyboard | 6999.0

If you see those three products, you’re ready. You’ve also just run your first SQL query.

DB Browser shows the same results in a grid. In this series, I’ll show results as plain text so they’re easy to read and compare.

Your first query. If your results match these, your store is set up correctly.

Common mistakes

1. Running the store data twice

table products already exists

This means your table is already there, so you don’t need to add it again. In DB Browser, this message appears in a red box. Clear the editor and run your query on its own.

2. Losing your data in DB Browser

If you close the app without clicking Write Changes, the store won’t be saved. Click it after you create the table, then use Open Database to reopen store.db next time.

3. Python can’t find the file

FileNotFoundError: [Errno 2] No such file or directory: 'store_setup.sql'

Your two files need to be in the same folder, and your terminal needs to be open in that folder too.

Try it yourself

1. Run SELECT * FROM products; to see the whole table. The * means “all columns.” How many rows do you get?
2. Look through the results. Which product has no price, and which two have no brand?

Think It Through

These don’t need any code yet. Try answering them in your own words.

1. Your team tracks orders in a spreadsheet that’s getting slow. Several people edit it at once, and mistakes keep creeping in. What would a database give you that a spreadsheet doesn’t?
2. SQL lets you describe the result, and the database decides how to get it. Why is that useful? Think about what happens as a table grows from a thousand rows to a hundred million.
3. A query works perfectly in SQLite but fails on your company’s PostgreSQL database. Does that mean you’ve learned the wrong SQL?

Wrap-up

• SQL is the language for working with data in tables, and you describe the result you want.
• Different databases share the same core SQL, with small differences.
• You now have a practice store, ready for the rest of the series.

Next time, you’ll write your own queries to choose columns, calculate new values and sort your results. You’ll also see a missing comma that gives a wrong result without any error.

If this helped, follow along so you don’t miss the next one, and give it a clap so other learners can find it too.

Solutions

Try it yourself, 1

You get 12 rows, one for each product.

Try it yourself, 2

The Webcam has no price. The Laptop Stand and Monitor Arm have no brand. All three show NULL in those columns.

Think It Through, 1

A database can enforce rules, such as requiring every order to have a date, so bad data gets rejected. Many people can safely work with the data at once. It also handles far more data, and it lets you ask complicated questions with a saved query instead of rebuilding formulas. A spreadsheet is still a good choice for small, personal data.

Think It Through, 2

The database has a component called the query planner, which chooses a fast way to run your query based on the current data. As a table grows, or someone adds a shortcut called an index, the database can choose a better route while your query stays exactly the same. We’ll look at this properly later in the series.

Think It Through, 3

No. Most of what you’ve learned still applies, and the failing part is probably a small difference in syntax or a function that only one of them has. The error message usually points to it, and the fix is often a small change.