Setting Up a Local Database for Practice

Reading about SQL only gets you so far — actually running queries against a real database is what makes the syntax and concepts stick. This lesson walks through getting a working database on your own machine (or a free hosted alternative), connecting to it with either a GUI tool or the command-line client, and creating your first practice database and table.

Why it matters

SQL is learned by typing queries and reading what comes back, not by reading about it. A database you can experiment in freely, and break without consequences, is the most useful tool you can have while learning. The same create, insert and select cycle you run here is what every later lesson builds on.

Installing PostgreSQL or MySQL Locally

PostgreSQL and MySQL are the two most widely used open-source relational databases, and either is a fine choice for learning SQL — the core language is nearly identical between them, with only minor syntax differences. Download the installer for your operating system from the official PostgreSQL or MySQL site and run it; the installer typically also sets up a default admin user and asks you to set a password during setup. If you'd rather avoid a local install, hosted free-tier services (such as Supabase or Neon for PostgreSQL, or PlanetScale for MySQL) give you a real remote database in a couple of minutes, reachable with the same tools and the same SQL.

Connecting with a GUI Client or the CLI

A GUI database client shows your tables, columns, and data visually and is often friendlier while learning. Popular options include TablePlus, DBeaver, and pgAdmin (which ships alongside PostgreSQL). To connect, you supply a host (usually localhost for a local install), port (5432 for PostgreSQL, 3306 for MySQL by default), username, password, and database name. Alternatively, the command-line clients psql (PostgreSQL) and mysql (MySQL) let you type SQL directly in a terminal: psql -U postgres -d postgres or mysql -u root -p.

Creating Your First Practice Database and Table

Once connected, create a dedicated database to practice in — keeping it separate from any default system database avoids accidentally cluttering or breaking anything the tool itself relies on. Then create a simple table and insert a few rows to confirm everything works end to end.

Example

-- Run inside psql after connecting
CREATE DATABASE practice_db;

\c practice_db

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    grade INT
);

INSERT INTO students (name, grade) VALUES ('Asha Patel', 92), ('Ravi Kumar', 85);

SELECT * FROM students;

You are now connected to database "practice_db" as user "postgres".
CREATE TABLE
INSERT 0 2
 id |    name     | grade
----+-------------+-------
  1 | Asha Patel  |    92
  2 | Ravi Kumar  |    85
(2 rows)

Exercise

Create a table named students with an integer id as primary key and a text name. Insert two students, Asha and Ravi. Then count the rows, naming the result column total.

Expected output: total then 2

-- TODO: create the students table

-- TODO: insert Asha (id 1) and Ravi (id 2)

-- TODO: count the rows as "total"

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT
);

INSERT INTO students (id, name) VALUES (1, 'Asha'), (2, 'Ravi');

SELECT COUNT(*) AS total FROM students;

Quick quiz

Interview questions