Part I: Getting Started with SQL Chapter 2: Setting Up Your Environment

 Chapter 2: Setting Up Your Environment

Before you write your first query, it’s essential to install and configure a relational database on your workstation. A solid environment ensures you spend time learning SQL—not battling installation errors. In this chapter, we’ll:

• Compare four popular RDBMS options

• Walk through installation on Windows, macOS, and Linux

• Demonstrate how to connect via command-line and graphical tools

• Load a sample database so you can start querying immediately

By the end, your machine will be a fully functional SQL playground.

1. Choosing an RDBMS: MySQL, PostgreSQL, SQLite, SQL Server

Different relational database systems excel in different scenarios. Here’s a quick comparison to help you pick one:

Feature	MySQL	PostgreSQL	SQLite	SQL Server
Use case	Web apps, LAMP stacks	Analytics, GIS, advanced SQL	Lightweight, embedded apps	Enterprise .NET, Windows ecosystems
Licensing	GPL (Community) / Commercial (Enterprise)	Open Source (PostgreSQL License)	Public domain	Proprietary (Developer edition free)
Extensibility	Plugins, limited JSON support	Advanced data types, extensions	Minimal overhead	CLR integration, T-SQL rich
Concurrency & Scaling	Good, with InnoDB engine	Best-in-class MVCC and parallelism	Single file, limited concurrency	Robust, enterprise clustering
Tooling & Community	Mature ecosystem; phpMyAdmin, Workbench	pgAdmin, DBeaver, Active community	Bare-bones CLI + many GUIs	SQL Server Management Studio (SSMS)

If you’re unsure which to choose, PostgreSQL is a solid all-rounder. SQLite is perfect for learning or quick demos. MySQL and SQL Server are ubiquitous in web and enterprise environments, respectively.

2. Installing Your RDBMS

2.1 Windows

PostgreSQL

1. Download the installer from .

2. Run the .exe and follow the wizard:

   • Choose installation directory

   • Set a password for the postgres superuser

   • Select default port (5432)

   • Install StackBuilder (optional packages)

3. Verify installation by opening PowerShell and running:

   ps1

   psql --version
   

4. Confirm the service is running in Services (look for “PostgreSQL”).

MySQL

1. Download MySQL Installer from .

2. Choose “Developer Default” in the installer to get MySQL Server, Workbench, Shell, and connectors.

3. Configure:

   • Select authentication method (use Strong Password Encryption)

   • Create a root password

   • Add a standard user account if desired

   • Set the MySQL Windows service to start automatically

4. Verify in PowerShell:

   ps1

   mysql --version
   

SQLite

1. Download the precompiled binaries (sqlite-tools-win32-x86-*.zip) from .

2. Unzip and add the folder to your PATH environment variable.

3. Test in PowerShell:

   ps1

   sqlite3 --version
   

SQL Server Developer Edition

1. Download from .

2. Run the installer, select “Developer” edition, and install.

3. Choose a mixed-mode authentication with an sa password.

4. Install SQL Server Management Studio (SSMS) separately if not included.

5. Verify:

   ps1

   sqlcmd -S localhost -U sa -P YourPassword
   

2.2 macOS

Homebrew Overview

On macOS, Homebrew makes installation painless. First, install Homebrew: /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

PostgreSQL

bash

brew install postgresql
brew services start postgresql
psql --version

MySQL

bash

brew install mysql
brew services start mysql
mysql_secure_installation   # walk through root password setup
mysql --version

SQLite

bash

brew install sqlite
sqlite3 --version

SQL Server (Docker)

SQL Server isn’t native on macOS, but you can run it in Docker:

bash

docker pull mcr.microsoft.com/mssql/server:latest
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=YourPassword" \
   -p 1433:1433 --name sqlserver -d mcr.microsoft.com/mssql/server:latest

Connect from macOS using Azure Data Studio or sqlcmd in a Docker container.

2.3 Linux (Ubuntu Example)

PostgreSQL

bash

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl enable postgresql
sudo systemctl start postgresql
sudo -u postgres psql -c "SELECT version();"

MySQL

bash

sudo apt update
sudo apt install mysql-server
sudo mysql_secure_installation
sudo systemctl status mysql
mysql --version

SQLite

bash

sudo apt update
sudo apt install sqlite3 libsqlite3-dev
sqlite3 --version

SQL Server (Ubuntu)

bash

# Add Microsoft repo
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list)"
sudo apt update
sudo apt install mssql-server
sudo /opt/mssql/bin/mssql-conf setup      # choose edition, SA password
sudo systemctl status mssql-server

3. Connecting to Your Database

3.1 Command-Line Interface (CLI)

PostgreSQL (psql CLI)

bash

psql -h localhost -U postgres -d postgres
# Enter password when prompted

Key commands:

• \l list databases

• \c dbname connect to database

• \dt list tables

• \q quit

MySQL (mysql CLI)

bash

mysql -h localhost -u root -p

Key commands:

• SHOW DATABASES;

• USE dbname;

• SHOW TABLES;

• EXIT;

SQLite (sqlite3 CLI)

bash

sqlite3 /path/to/database.db

Key commands:

• .tables

• .schema tablename

• .quit

SQL Server (sqlcmd)

bash

sqlcmd -S localhost -U sa -P YourPassword

Key commands:

• SELECT name FROM sys.databases; GO

• USE YourDatabase; GO

• SELECT * FROM YourTable; GO

• EXIT

3.2 Graphical Tools

Using a GUI can speed up development and exploration.

• pgAdmin (PostgreSQL): Create servers, manage objects, run queries.

• MySQL Workbench: Visual database design, query editor, performance dashboard.

• DBeaver / HeidiSQL: Multi-dialect clients supporting MySQL, PostgreSQL, SQLite, SQL Server.

• Azure Data Studio / SSMS: Microsoft tools for SQL Server.

To connect, simply open your chosen tool, enter host (localhost), port (e.g., 5432, 3306, 1433), username, and password. Test the connection and save a profile for quick access.

4. Loading a Sample Database

A sample dataset helps you practice writing queries immediately. We’ll load the classic “Pagila” schema for PostgreSQL and “sakila” for MySQL, both based on a DVD rental business.

4.1 PostgreSQL: Pagila

1. Download the SQL files:

2. Create the database and load schema:

   bash

   createdb pagila
   psql -d pagila -f pagila-schema.sql
   psql -d pagila -f pagila-data.sql
   

3. Verify:

   bash

   psql -d pagila -c "\dt"
   

4.2 MySQL: Sakila

1. Download from MySQL site:

2. Create and load:

   bash

   mysql -u root -p
   CREATE DATABASE sakila;
   USE sakila;
   SOURCE /path/to/sakila-schema.sql;
   SOURCE /path/to/sakila-data.sql;
   

3. Verify:

   sql

   SHOW TABLES;
   

4.3 SQLite: Chinook

Chinook is a music store sample:

1. Download Chinook_Sqlite.sqlite from .

2. Open in CLI or GUI:

   bash

   sqlite3 Chinook_Sqlite.sqlite
   .tables
   

4.4 SQL Server: AdventureWorks

1. Download the backup from Microsoft:

2. Restore via SSMS or sqlcmd:

   sql

   RESTORE DATABASE AdventureWorks 
   FROM DISK = 'C:\Backups\AdventureWorks.bak' 
   WITH MOVE 'AdventureWorks_Data' TO 'C:\MSSQL\Data\AdventureWorks.mdf',
        MOVE 'AdventureWorks_Log'  TO 'C:\MSSQL\Data\AdventureWorks.ldf';
   

3. Verify with SSMS Object Explorer.

5. Your SQL Playground Is Ready

Congratulations! You now have:

• A relational database engine installed

• Command-line and graphical tools connected

• A sample database loaded and ready for queries

In the next chapter, we’ll dive into basic SELECT statements—retrieving and filtering data to answer real questions. Fire up your SQL client and get ready to explore tables, filter rows, and sort results. Your data-driven journey begins now!