Setting Up Your SQL Environment (SQLite, MySQL, PostgreSQL)

Table of Contents

1. Introduction
2. Choosing the Right SQL Database for Learning
3. Overview of SQLite
4. Installing SQLite
5. Using SQLite Command Line Interface
6. GUI Tools for SQLite
7. Overview of MySQL
8. Installing MySQL on Windows/macOS/Linux
9. Accessing MySQL via Command Line
10. GUI Tools for MySQL (MySQL Workbench)
11. Overview of PostgreSQL
12. Installing PostgreSQL on Windows/macOS/Linux
13. Accessing PostgreSQL via psql
14. GUI Tools for PostgreSQL (pgAdmin)
15. Creating Your First Database
16. Creating Tables Using SQL
17. Importing Sample Data
18. Running Your First Query
19. Tips for Managing Local SQL Environments
20. Summary and What’s Next

1. Introduction

Before you can write SQL queries, you need a running database environment. In this module, we’ll guide you through setting up three of the most popular relational database management systems: SQLite, MySQL, and PostgreSQL. Each one is free and widely used across different industries.

2. Choosing the Right SQL Database for Learning

SQLite is ideal for quick experimentation. MySQL is best if you’re interested in web apps like WordPress. PostgreSQL is perfect for those exploring advanced SQL features.

3. Overview of SQLite

SQLite is a self-contained, serverless, and zero-configuration database engine. It’s widely used in mobile applications, browsers, and embedded systems.

4. Installing SQLite

Windows/macOS/Linux:

1. Go to: https://www.sqlite.org/download.html
2. Download the precompiled binaries for your platform.
3. Extract and add to system PATH.

To test:

sqlite3 --version

5. Using SQLite Command Line Interface

Start a new database:

sqlite3 mydatabase.db

Example:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);
INSERT INTO users (name, email) VALUES ('Ravi', '[email protected]');
SELECT * FROM users;

Exit with .exit.

6. GUI Tools for SQLite

If you’re not comfortable with the terminal, try:

• DB Browser for SQLite (https://sqlitebrowser.org/)
• SQLiteStudio

These tools provide drag-and-drop table creation, SQL editing, and data visualization.

7. Overview of MySQL

MySQL is a high-performance open-source RDBMS maintained by Oracle. It’s the backbone of many content management systems and supports large-scale applications.

8. Installing MySQL on Windows/macOS/Linux

Windows:

1. Download the MySQL Installer from https://dev.mysql.com/downloads/installer/
2. Choose “Developer Default”
3. Set a root password and remember it

macOS:

brew install mysql
brew services start mysql

Linux (Ubuntu):

sudo apt update
sudo apt install mysql-server
sudo mysql_secure_installation

9. Accessing MySQL via Command Line

Start the MySQL shell:

mysql -u root -p

Create and use a database:

CREATE DATABASE testdb;
USE testdb;
CREATE TABLE demo (id INT PRIMARY KEY, name VARCHAR(100));

10. GUI Tools for MySQL (MySQL Workbench)

Download from: https://dev.mysql.com/downloads/workbench/

Features:

• SQL Editor
• ER Diagrams
• Server Configuration
• User and Privilege Management

11. Overview of PostgreSQL

PostgreSQL is a powerful, open-source object-relational database. Known for standards compliance and extensibility, it is popular in financial, GIS, and analytics-heavy systems.

12. Installing PostgreSQL on Windows/macOS/Linux

Windows/macOS:

Use the installer from: https://www.postgresql.org/download/

During setup:

• Choose components (pgAdmin, StackBuilder)
• Set password for superuser postgres

Linux (Ubuntu):

sudo apt update
sudo apt install postgresql postgresql-contrib

13. Accessing PostgreSQL via psql

Connect to the shell:

sudo -u postgres psql

Create and use a database:

CREATE DATABASE mydb;
\c mydb
CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(100));

Exit with \q.

14. GUI Tools for PostgreSQL (pgAdmin)

pgAdmin is the official PostgreSQL GUI:

• Visual query editor
• Backup/restore tools
• User management

Download from: https://www.pgadmin.org/download/

15. Creating Your First Database

In any of the three systems:

CREATE DATABASE testdb;

Use the database:

-- MySQL / PostgreSQL
USE testdb;

-- SQLite: done when file is opened

16. Creating Tables Using SQL

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE
);

Syntax may vary slightly (e.g., AUTO_INCREMENT in MySQL, SERIAL in PostgreSQL).

17. Importing Sample Data

You can import CSV files using:

SQLite:

.mode csv
.import users.csv users

MySQL:

LOAD DATA INFILE '/path/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

PostgreSQL:

COPY users(name, email)
FROM '/path/users.csv'
DELIMITER ','
CSV HEADER;

18. Running Your First Query

Try this after creating and inserting data:

SELECT * FROM users;
SELECT name FROM users WHERE email LIKE '%@gmail.com';

Use ORDER BY, LIMIT, and WHERE for richer queries.

19. Tips for Managing Local SQL Environments

• Use GUI tools if CLI feels overwhelming
• Always back up before major changes
• Avoid using root/admin users in practice
• Use version control for schema files (.sql)
• Organize your queries and notes

20. Summary and What’s Next

In this module, you learned how to:

• Set up SQLite, MySQL, and PostgreSQL
• Use CLI and GUI tools for each system
• Create databases, tables, and run queries

Whether you’re a beginner or intermediate developer, having at least one local SQL environment is essential for practice and learning.