<< Home

Chapter 4: Setting Up Your Environment

Before you can start writing and running SQL statements, you need a working database environment. Most systems today use one of a few major engines (MySQL, PostgreSQL, SQLite, or SQL Server) and all of them can be installed quickly on a local machine. This chapter walks through installing a database, connecting from both command-line and graphical tools, and creating your first database and table.

💡 You do not need a powerful computer to practice SQL. Even a small laptop can host lightweight databases such as SQLite or a Docker-based PostgreSQL image.

Installing and connecting to a server

Every database system provides its own installer or package. Choose one that matches your platform and preferred workflow. MySQL and PostgreSQL run as background services, while SQLite is an embedded library that stores data in a single file.

Installing MySQL

MySQL can be downloaded from the official Oracle distribution or installed with a package manager:

# Ubuntu or Debian
sudo apt install mysql-server

# macOS (Homebrew)
brew install mysql

Once installed, start the service and connect to it:

sudo service mysql start
mysql -u root -p

The -u flag specifies a username and -p prompts for a password. After connecting, you can create databases and users directly from the prompt.

Installing PostgreSQL

PostgreSQL offers similar installation methods. On most systems you can use the default package manager:

# Ubuntu or Debian
sudo apt install postgresql

# macOS (Homebrew)
brew install postgresql

To connect, use the psql client:

sudo service postgresql start
psql -U postgres

Inside psql you can execute SQL statements directly. Commands starting with a backslash (like \l or \d) control the session rather than sending SQL to the server.

⚠️ PostgreSQL separates authentication roles from databases. The default superuser role is postgres; you will often create your own user with limited privileges for daily work.

Using SQLite

SQLite is built into many operating systems and requires no setup beyond downloading a small binary if it is not already installed. To start an interactive session, simply run:

sqlite3 mydata.db

This creates (or opens) a file called mydata.db. All data lives inside this file. SQLite is ideal for experiments, testing, or small applications that do not require a networked server.

Using CLI and GUI tools

Every major database comes with a command-line client, and many also offer graphical tools for browsing and editing data. Both approaches are valuable: the CLI is fast and scriptable, while GUIs provide context and visualization.

Command-line clients

The command line is the quickest way to execute SQL. Once connected, you type statements and press Enter. A semicolon (;) marks the end of a statement. For example:

mysql> SHOW DATABASES;
psql> \l
sqlite> .tables

These commands list databases or tables depending on the system. You can also redirect input from a file to run a complete script:

mysql -u user -p < setup.sql
💡 Keep a collection of .sql files for schema creation and testing. They can be version-controlled and reused on new systems.

Graphical tools

If you prefer a visual interface, there are many free and commercial clients available:

Most GUI tools let you browse tables, run queries, and export data in CSV or JSON formats. They are convenient for learning and for checking results of your SQL scripts.

⚠️ GUI tools can mask the underlying commands. Always understand what SQL they execute, especially when performing changes that affect data integrity.

Creating your first database and table

After installing your chosen system, you can create your first database and a simple table. The syntax differs slightly between engines, but the core idea is the same: define a database, connect to it, and create a table with columns and constraints.

Creating a database

-- MySQL and PostgreSQL
CREATE DATABASE sampledb;

-- SQLite (creates a file automatically)
sqlite3 sampledb.db

Once created, connect to the new database:

USE sampledb;        -- MySQL
\c sampledb          -- PostgreSQL

Creating a table

CREATE TABLE users (
  user_id INTEGER PRIMARY KEY,
  name    TEXT    NOT NULL,
  email   TEXT    UNIQUE,
  joined  DATE    DEFAULT CURRENT_DATE
);

This table stores user information. Try inserting and selecting rows:

INSERT INTO users (user_id, name, email)
VALUES (1, 'Alice', 'alice@example.com');

SELECT * FROM users;

The output confirms that your database and table are working correctly. You now have a functioning environment for running every example in this book.

💡 Keep your practice database small and simple. When experiments go wrong, it is faster to drop and recreate the schema than to debug broken data.

With the tools installed and your first database ready, you can begin learning the language itself—starting with how to query and retrieve data using SELECT statements in the next chapter.