Environment Setup: SQL Server, SSMS and Databases

While you can practice SQL directly on CodePeet without any setup, having a local SQL environment gives you the freedom to experiment with your own databases and explore advanced features.

In this tutorial, you will learn:

• Options for practicing SQL locally
• How to set up SQL Server and SSMS (Windows)
• Alternative options for Mac and Linux users
• How to connect to a database
• Creating your first local database

Note: This setup is optional. All course exercises run directly in your browser on CodePeet.

Several options exist for running SQL on your local machine.

Option Comparison

Recommendations by Goal

Our Recommendation for This Course

Use CodePeet for course exercises - Everything runs in browser.

For local practice:

• Windows users → SQL Server Express + SSMS
• Mac/Linux users → PostgreSQL + pgAdmin or SQLite

SQLite is the easiest way to start practicing SQL locally. It requires no server setup.

What is SQLite?

• Self-contained: Entire database is a single file
• Zero-configuration: No server process to manage
• Cross-platform: Works on Windows, Mac, Linux
• Standard SQL: Compatible with this course's syntax

Installation

Windows:

1. Download from https://www.sqlite.org/download.html
2. Choose "sqlite-tools-win32-x86" (Precompiled Binaries)
3. Extract to a folder (e.g., C:\sqlite)
4. Add folder to PATH environment variable

Mac:

# SQLite is pre-installed on Mac
# Verify with:
sqlite3 --version

Linux:

# Ubuntu/Debian
sudo apt-get install sqlite3

# Fedora/RHEL
sudo dnf install sqlite

Basic Usage

# Create/open a database
sqlite3 mydatabase.db

# Inside SQLite:
sqlite> CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
sqlite> INSERT INTO users (name) VALUES ('Alice');
sqlite> SELECT * FROM users;
sqlite> .exit

GUI Options for SQLite

Microsoft SQL Server is widely used in enterprise environments. The Express edition is free for learning.

What You'll Install

1. SQL Server Express - The database engine (free edition)
2. SQL Server Management Studio (SSMS) - The graphical interface

Step 1: Download SQL Server Express

1. Go to: https://www.microsoft.com/en-us/sql-server/sql-server-downloads
2. Scroll to "Express" edition
3. Click "Download now"
4. Run the installer

Step 2: Installation Options

When the installer opens, choose:

Basic (Recommended for beginners)

• Simplest installation
• Uses default settings
• Quick setup

Custom (For more control)

• Choose installation location
• Select specific features
• Configure instance name

Step 3: During Installation

Step 4: Download SSMS

1. After SQL Server installs, download SSMS
2. Or go to: https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms
3. Run the SSMS installer
4. Follow prompts (mostly "Next")

Step 5: Connect to Your Server

1. Open SQL Server Management Studio
2. In "Connect to Server" dialog:
   • Server type: Database Engine
   • Server name: localhost\SQLEXPRESS or .\SQLEXPRESS
   • Authentication: Windows Authentication
3. Click "Connect"

Verification

Once connected, you should see:

• Object Explorer panel on the left
• Your server listed with databases
• System databases (master, model, msdb, tempdb)

PostgreSQL is a powerful open-source database that runs on all platforms.

Step 1: Download PostgreSQL

1. Go to: https://www.postgresql.org/download/
2. Choose your operating system
3. Download the installer from EnterpriseDB (recommended)

Step 2: Installation

Run the installer and configure:

Step 3: Using pgAdmin

pgAdmin is included with PostgreSQL installation.

1. Open pgAdmin 4
2. Click on "Servers" in left panel
3. Right-click → "Register" → "Server"
4. Configure:
   • General tab → Name: "Local PostgreSQL"
   • Connection tab:
     • Host: localhost
     • Port: 5432
     • Username: postgres
     • Password: (your password from installation)
5. Click "Save"

Step 4: Create a Database

1. Right-click on "Databases"
2. Select "Create" → "Database"
3. Enter database name (e.g., "practice")
4. Click "Save"

Step 5: Open Query Tool

1. Right-click on your database
2. Select "Query Tool"
3. Write SQL in the query panel
4. Click "Execute" (or F5) to run

PostgreSQL-Specific Syntax Notes

MySQL is one of the most popular open-source databases, especially for web applications.

Step 1: Download MySQL Community Server

1. Go to: https://dev.mysql.com/downloads/mysql/
2. Choose your operating system
3. Download the installer

Step 2: Download MySQL Workbench

1. Go to: https://dev.mysql.com/downloads/workbench/
2. Choose your operating system
3. Download the installer

Alternatively, use the MySQL Installer (Windows) which includes both.

Step 3: Installation

Run the MySQL Installer:

Step 4: Using MySQL Workbench

1. Open MySQL Workbench
2. Click on your local connection (usually already configured)
3. Enter password when prompted
4. Write SQL in the query tab
5. Click lightning bolt (⚡) to execute

Step 5: Create a Database

-- Create a new database
CREATE DATABASE practice;

-- Switch to the database
USE practice;

-- Create a table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

MySQL-Specific Syntax Notes

Once your environment is set up, let's create a simple practice database.

Universal SQL Example

This example works on most database systems with minor modifications:

-- Create a new database (syntax may vary)
CREATE DATABASE practice;

-- Use the database
-- SQL Server: USE practice;
-- PostgreSQL: \c practice (or use GUI)
-- MySQL: USE practice;
-- SQLite: sqlite3 practice.db

-- Create a customers table
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT,
    city TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Create an orders table
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE,
    total_amount DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Insert sample data
INSERT INTO customers (customer_id, name, email, city) VALUES
(1, 'Alice Johnson', 'alice@mail.com', 'New York'),
(2, 'Bob Smith', 'bob@mail.com', 'Los Angeles'),
(3, 'Carol Davis', 'carol@mail.com', 'Chicago');

INSERT INTO orders (order_id, customer_id, order_date, total_amount) VALUES
(101, 1, '2024-01-15', 150.00),
(102, 1, '2024-01-20', 89.99),
(103, 2, '2024-01-18', 299.00),
(104, 3, '2024-01-22', 45.50);

-- Test query
SELECT c.name, COUNT(o.order_id) AS order_count, SUM(o.total_amount) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY total_spent DESC;

Expected Output

Here are solutions to common setup problems.

SQL Server Issues

Cannot connect to server

Error: Cannot connect to localhost\SQLEXPRESS

Solutions:

1. Verify SQL Server service is running:
   • Open "Services" (services.msc)
   • Find "SQL Server (SQLEXPRESS)"
   • Ensure it's running
2. Try alternative server names:
   • .\SQLEXPRESS
   • (local)\SQLEXPRESS
   • 127.0.0.1\SQLEXPRESS
3. Check SQL Server Configuration Manager for TCP/IP settings

PostgreSQL Issues

Authentication failed

Error: password authentication failed for user "postgres"

Solutions:

1. Double-check password (case-sensitive)
2. Reset password via command line:

   sudo -u postgres psql
   ALTER USER postgres PASSWORD 'newpassword';
   

Connection refused

Error: Connection refused on port 5432

Solutions:

1. Check PostgreSQL service is running
2. Verify port 5432 is not blocked by firewall

MySQL Issues

Access denied

Error: Access denied for user 'root'@'localhost'

Solutions:

1. Verify password is correct
2. Reset root password:

   mysql -u root --skip-password
   ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
   

SQLite Issues

'sqlite3' is not recognized
Solutions:

1. Add SQLite folder to system PATH
2. Or use full path to sqlite3.exe

General Tips

1. Restart services after configuration changes
2. Check logs for detailed error messages
3. Firewall settings may block database connections
4. Run as Administrator when installing (Windows)

If local installation is problematic, cloud databases offer an alternative.

Free Cloud Options

AWS Free Tier

Amazon RDS offers 12 months free:

• 750 hours of db.t2.micro instance
• MySQL, PostgreSQL, MariaDB, SQL Server options
• Requires AWS account and credit card

Google Cloud

Cloud SQL offers:

• $300 free credits for new accounts
• MySQL, PostgreSQL, SQL Server options

Benefits of Cloud Databases

• No installation - Runs in browser
• Accessible anywhere - Any device with internet
• Automatic backups - Data is safe
• Scalable - Grow as needed

Drawbacks

• Requires internet connection
• May have usage limits on free tiers
• Some features may be paid only

Before setting up a local environment, practice these queries on CodePeet to understand what you'll be able to do locally.

You now know how to set up a local SQL environment!

Key Takeaways

✅ CodePeet works without setup - Practice directly in browser

✅ SQLite - Simplest local option, no server needed

✅ SQL Server + SSMS - Best for Windows and enterprise SQL

✅ PostgreSQL + pgAdmin - Best open-source option for all platforms

✅ MySQL + Workbench - Great for web development focus

✅ Cloud alternatives - No installation required

Setup Checklist

For this course:

• Access CodePeet (✅ already done!)

For local practice (optional):

• Choose a database system
• Download and install database engine
• Download and install management tool
• Connect to your local server
• Create a practice database

What's Next?

You've completed the Introduction to SQL module! You now understand:

• What databases and SQL are
• How DBMS systems work
• Different types of databases
• SQL command categories
• Why SQL skills are valuable
• How to set up a local environment

Continue to Module 2: SELECT Queries to start writing real SQL queries!