Create Demo Database

🗄️ Building a Demo Database

Create a realistic sample database to practice PostgreSQL skills. This comprehensive example includes multiple related tables, sample data, and demonstrates real-world database design patterns for learning.


-- Create demo database
CREATE DATABASE company_demo;
\c company_demo

Output:

CREATE DATABASE
You are now connected to database "company_demo"

Database Overview

👥

Employees

Store employee information and details

🏢

Departments

Organize employees by department

📋

Projects

Track company projects and assignments

💰

Salaries

Manage employee compensation history

🔹 Step 1: Create Departments Table

Start with the departments table since other tables will reference it. This table stores basic information about company departments and their locations.


-- Create departments table
CREATE TABLE departments (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL UNIQUE,
    location VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample departments
INSERT INTO departments (dept_name, location) VALUES
    ('Engineering', 'San Francisco'),
    ('Marketing', 'New York'),
    ('Sales', 'Chicago'),
    ('Human Resources', 'Boston'),
    ('Finance', 'New York');

-- View departments
SELECT * FROM departments;

Output:

dept_id	dept_name	location
1	Engineering	San Francisco
2	Marketing	New York

🔹 Step 2: Create Employees Table

The employees table links to departments using a foreign key. This relationship ensures every employee belongs to a valid department and maintains data integrity.


-- Create employees table
CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    hire_date DATE NOT NULL,
    dept_id INTEGER REFERENCES departments(dept_id),
    job_title VARCHAR(100),
    is_active BOOLEAN DEFAULT true
);

-- Insert sample employees
INSERT INTO employees (first_name, last_name, email, phone, hire_date, dept_id, job_title) VALUES
    ('John', 'Doe', '[email protected]', '555-0101', '2020-01-15', 1, 'Software Engineer'),
    ('Jane', 'Smith', '[email protected]', '555-0102', '2019-03-20', 1, 'Senior Developer'),
    ('Mike', 'Johnson', '[email protected]', '555-0103', '2021-06-10', 2, 'Marketing Manager'),
    ('Sarah', 'Williams', '[email protected]', '555-0104', '2020-09-05', 3, 'Sales Representative'),
    ('Tom', 'Brown', '[email protected]', '555-0105', '2018-11-12', 4, 'HR Specialist');

-- View employees with department names
SELECT e.emp_id, e.first_name, e.last_name, e.job_title, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

Output:

emp_id	first_name	last_name	job_title	dept_name
1	John	Doe	Software Engineer	Engineering

🔹 Step 3: Create Projects Table

Track company projects with start and end dates, budgets, and status. This table demonstrates how to store project information independently of employees.


-- Create projects table
CREATE TABLE projects (
    project_id SERIAL PRIMARY KEY,
    project_name VARCHAR(200) NOT NULL,
    description TEXT,
    start_date DATE NOT NULL,
    end_date DATE,
    budget NUMERIC(12, 2),
    status VARCHAR(20) DEFAULT 'Planning',
    dept_id INTEGER REFERENCES departments(dept_id)
);

-- Insert sample projects
INSERT INTO projects (project_name, description, start_date, end_date, budget, status, dept_id) VALUES
    ('Website Redesign', 'Complete overhaul of company website', '2024-01-01', '2024-06-30', 150000.00, 'In Progress', 1),
    ('Marketing Campaign Q1', 'Social media and digital ads', '2024-01-15', '2024-03-31', 75000.00, 'In Progress', 2),
    ('Sales Training Program', 'Quarterly sales team training', '2024-02-01', '2024-02-28', 25000.00, 'Completed', 3),
    ('HR System Upgrade', 'New HRIS implementation', '2024-03-01', '2024-08-31', 200000.00, 'Planning', 4);

-- View projects
SELECT project_name, budget, status, dept_name
FROM projects p
JOIN departments d ON p.dept_id = d.dept_id;

🔹 Step 4: Create Project Assignments

Link employees to projects using a junction table. This many-to-many relationship allows employees to work on multiple projects and projects to have multiple team members.


-- Create project assignments table
CREATE TABLE project_assignments (
    assignment_id SERIAL PRIMARY KEY,
    emp_id INTEGER REFERENCES employees(emp_id),
    project_id INTEGER REFERENCES projects(project_id),
    role VARCHAR(100),
    hours_allocated INTEGER,
    assigned_date DATE DEFAULT CURRENT_DATE,
    UNIQUE(emp_id, project_id)
);

-- Assign employees to projects
INSERT INTO project_assignments (emp_id, project_id, role, hours_allocated) VALUES
    (1, 1, 'Lead Developer', 160),
    (2, 1, 'Senior Developer', 160),
    (3, 2, 'Project Manager', 120),
    (4, 3, 'Trainer', 80),
    (5, 4, 'Project Coordinator', 100);

-- View assignments
SELECT e.first_name, e.last_name, p.project_name, pa.role
FROM project_assignments pa
JOIN employees e ON pa.emp_id = e.emp_id
JOIN projects p ON pa.project_id = p.project_id;

Output:

first_name	last_name	project_name	role
John	Doe	Website Redesign	Lead Developer

🔹 Step 5: Create Salary History

Track employee salary changes over time. This table maintains a complete history of compensation adjustments for auditing and reporting purposes.


-- Create salary history table
CREATE TABLE salary_history (
    salary_id SERIAL PRIMARY KEY,
    emp_id INTEGER REFERENCES employees(emp_id),
    salary_amount NUMERIC(10, 2) NOT NULL,
    effective_date DATE NOT NULL,
    end_date DATE,
    currency VARCHAR(3) DEFAULT 'USD'
);

-- Insert salary records
INSERT INTO salary_history (emp_id, salary_amount, effective_date) VALUES
    (1, 85000.00, '2020-01-15'),
    (1, 95000.00, '2022-01-15'),
    (2, 110000.00, '2019-03-20'),
    (2, 125000.00, '2021-03-20'),
    (3, 75000.00, '2021-06-10'),
    (4, 65000.00, '2020-09-05'),
    (5, 70000.00, '2018-11-12');

-- View current salaries
SELECT e.first_name, e.last_name, e.job_title, sh.salary_amount
FROM employees e
JOIN salary_history sh ON e.emp_id = sh.emp_id
WHERE sh.end_date IS NULL OR sh.end_date > CURRENT_DATE
ORDER BY sh.salary_amount DESC;

🔹 Useful Queries for Demo Database

Practice these common queries to explore relationships and aggregate data. These examples demonstrate real-world database operations you'll use frequently in applications.


-- 1. Count employees per department
SELECT d.dept_name, COUNT(e.emp_id) as employee_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name;

-- 2. Total budget by department
SELECT d.dept_name, SUM(p.budget) as total_budget
FROM departments d
JOIN projects p ON d.dept_id = p.dept_id
GROUP BY d.dept_name;

-- 3. Employees with their current salary
SELECT e.first_name, e.last_name, e.job_title, 
       sh.salary_amount, d.dept_name
FROM employees e
JOIN salary_history sh ON e.emp_id = sh.emp_id
JOIN departments d ON e.dept_id = d.dept_id
WHERE sh.end_date IS NULL
ORDER BY sh.salary_amount DESC;

-- 4. Projects with team size
SELECT p.project_name, p.status, 
       COUNT(pa.emp_id) as team_size
FROM projects p
LEFT JOIN project_assignments pa ON p.project_id = pa.project_id
GROUP BY p.project_id, p.project_name, p.status;

-- 5. Average salary by department
SELECT d.dept_name, 
       ROUND(AVG(sh.salary_amount), 2) as avg_salary
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
JOIN salary_history sh ON e.emp_id = sh.emp_id
WHERE sh.end_date IS NULL
GROUP BY d.dept_name;

🔹 Complete Database Script

Here's the complete script to create the entire demo database in one go. Copy and paste this into pgAdmin or psql to set up everything at once.


-- Complete Demo Database Setup Script
CREATE DATABASE company_demo;
\c company_demo

-- 1. Departments
CREATE TABLE departments (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL UNIQUE,
    location VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. Employees
CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    hire_date DATE NOT NULL,
    dept_id INTEGER REFERENCES departments(dept_id),
    job_title VARCHAR(100),
    is_active BOOLEAN DEFAULT true
);

-- 3. Projects
CREATE TABLE projects (
    project_id SERIAL PRIMARY KEY,
    project_name VARCHAR(200) NOT NULL,
    description TEXT,
    start_date DATE NOT NULL,
    end_date DATE,
    budget NUMERIC(12, 2),
    status VARCHAR(20) DEFAULT 'Planning',
    dept_id INTEGER REFERENCES departments(dept_id)
);

-- 4. Project Assignments
CREATE TABLE project_assignments (
    assignment_id SERIAL PRIMARY KEY,
    emp_id INTEGER REFERENCES employees(emp_id),
    project_id INTEGER REFERENCES projects(project_id),
    role VARCHAR(100),
    hours_allocated INTEGER,
    assigned_date DATE DEFAULT CURRENT_DATE,
    UNIQUE(emp_id, project_id)
);

-- 5. Salary History
CREATE TABLE salary_history (
    salary_id SERIAL PRIMARY KEY,
    emp_id INTEGER REFERENCES employees(emp_id),
    salary_amount NUMERIC(10, 2) NOT NULL,
    effective_date DATE NOT NULL,
    end_date DATE,
    currency VARCHAR(3) DEFAULT 'USD'
);

-- Insert all sample data (see previous sections)

🗄️ Building a Demo Database

Output:

Database Overview

Employees

Departments

Projects

Salaries

🔹 Step 1: Create Departments Table

Output:

🔹 Step 2: Create Employees Table

Output:

🔹 Step 3: Create Projects Table

🔹 Step 4: Create Project Assignments

Output:

🔹 Step 5: Create Salary History

🔹 Useful Queries for Demo Database

🔹 Complete Database Script

🧠 Test Your Knowledge

What type of relationship exists between employees and projects?