Database Setup: company_db
title: “Database Setup: company_db” part: 0 topic_number: 0 slug: “setup-company-db” difficulty: “Beginner” prerequisites: “none” —
Database Setup: company_db
What You’re Building
Throughout this entire course, you’ll work with a realistic database called company_db. It models a mid-sized company with employees, departments, projects, and assignments. This database is designed with deliberate variety — NULLs, salary ranges, different hire dates, and realistic relationships — so your practice queries return meaningful results.
Think of this as your sandbox. You’ll query it, modify it, and learn from it across all three parts of this course.
Full Setup Script
Copy and paste this entire script into your MySQL command line or workbench. Run it all at once.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
-- ============================================
-- COMPANY_DB SETUP SCRIPT
-- MySQL 8+ Training Database
-- ============================================
-- Start fresh: drop the database if it already exists
DROP DATABASE IF EXISTS company_db;
-- Create the database
CREATE DATABASE company_db;
-- Switch to using the new database
USE company_db;
-- ============================================
-- TABLE 1: departments
-- ============================================
CREATE TABLE departments (
department_id INT PRIMARY KEY AUTO_INCREMENT, -- Unique ID for each department
department_name VARCHAR(100) NOT NULL, -- Department name (required)
location VARCHAR(100) -- Office location (can be NULL)
);
-- ============================================
-- TABLE 2: employees
-- ============================================
CREATE TABLE employees (
employee_id INT PRIMARY KEY AUTO_INCREMENT, -- Unique ID for each employee
first_name VARCHAR(50) NOT NULL, -- First name (required)
last_name VARCHAR(50) NOT NULL, -- Last name (required)
department_id INT, -- Which department? (can be NULL if unassigned)
salary DECIMAL(10, 2), -- Annual salary (can be NULL)
hire_date DATE, -- When they were hired (can be NULL)
manager_id INT, -- Who is their manager? (can be NULL if top-level)
FOREIGN KEY (department_id) REFERENCES departments(department_id),
FOREIGN KEY (manager_id) REFERENCES employees(employee_id) -- Self-referencing for hierarchy
);
-- ============================================
-- TABLE 3: projects
-- ============================================
CREATE TABLE projects (
project_id INT PRIMARY KEY AUTO_INCREMENT, -- Unique ID for each project
project_name VARCHAR(150) NOT NULL, -- Project name (required)
start_date DATE, -- When project started (can be NULL)
end_date DATE, -- When project ended/will end (can be NULL)
budget DECIMAL(12, 2) -- Project budget (can be NULL)
);
-- ============================================
-- TABLE 4: employee_projects
-- ============================================
-- This is a "junction table" that connects employees to projects (many-to-many relationship)
CREATE TABLE employee_projects (
employee_id INT, -- Which employee?
project_id INT, -- Which project?
role VARCHAR(100), -- What role do they have on this project?
PRIMARY KEY (employee_id, project_id), -- Composite key: one employee can't have duplicate role on same project
FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
FOREIGN KEY (project_id) REFERENCES projects(project_id)
);
-- ============================================
-- POPULATE: departments
-- ============================================
INSERT INTO departments (department_name, location) VALUES
('Engineering', 'San Francisco'),
('Marketing', 'New York'),
('Sales', 'Austin'),
('Human Resources', 'Chicago'),
('Finance', 'Boston'),
('Customer Support', 'Remote'),
('Product', 'San Francisco'),
('Legal', 'New York'),
('Operations', NULL), -- No location assigned yet
('Data Science', 'Seattle');
-- ============================================
-- POPULATE: employees
-- ============================================
INSERT INTO employees (first_name, last_name, department_id, salary, hire_date, manager_id) VALUES
('Alice', 'Johnson', 1, 120000.00, '2019-03-15', NULL), -- CEO, no manager
('Bob', 'Smith', 1, 95000.00, '2020-06-01', 1), -- Reports to Alice
('Carol', 'Williams', 2, 78000.00, '2021-01-10', 1), -- Reports to Alice
('David', 'Brown', 2, 72000.00, '2021-05-20', 3), -- Reports to Carol
('Eve', 'Davis', 3, 85000.00, '2018-11-05', 1), -- Reports to Alice
('Frank', 'Miller', 3, 68000.00, '2022-02-14', 5), -- Reports to Eve
('Grace', 'Wilson', 4, 65000.00, '2020-09-30', 1), -- Reports to Alice
('Hank', 'Moore', 5, 90000.00, '2019-07-12', 1), -- Reports to Alice
('Ivy', 'Taylor', 6, 55000.00, '2022-08-01', 7), -- Reports to Grace
('Jack', 'Anderson', 1, 110000.00, '2017-04-22', 1), -- Senior engineer, reports to Alice
('Karen', 'Thomas', 7, 105000.00, '2020-03-18', 1), -- Reports to Alice
('Leo', 'Jackson', 1, 88000.00, '2021-10-05', 10), -- Reports to Jack
('Mia', 'White', 8, 115000.00, '2019-12-01', 1), -- Reports to Alice
('Noah', 'Harris', 9, NULL, '2023-01-15', 1), -- New hire, salary not set
('Olivia', 'Martin', 10, 98000.00, '2020-11-20', 1), -- Reports to Alice
('Paul', 'Garcia', NULL, 60000.00, '2023-03-10', NULL), -- Contractor, no department or manager
('Quinn', 'Martinez', 1, 92000.00, '2021-07-07', 10), -- Reports to Jack
('Rachel', 'Robinson', 2, 71000.00, '2022-04-12', 3), -- Reports to Carol
('Sam', 'Clark', 3, 67000.00, '2022-09-25', 5), -- Reports to Eve
('Tina', 'Rodriguez', 6, 58000.00, '2023-02-03', 7); -- Reports to Grace
-- ============================================
-- POPULATE: projects
-- ============================================
INSERT INTO projects (project_name, start_date, end_date, budget) VALUES
('Website Redesign', '2022-01-10', '2022-06-30', 150000.00),
('Mobile App Launch', '2022-03-01', '2023-03-01', 500000.00),
('Data Migration', '2021-11-01', '2022-05-15', 200000.00),
('Marketing Campaign Q1', '2023-01-01', '2023-03-31', 80000.00),
('Customer Portal', '2022-07-01', NULL, 300000.00), -- Still ongoing
('AI Chatbot', '2023-02-15', NULL, 250000.00), -- Still ongoing
('Office Expansion', '2022-05-01', '2022-12-31', 1000000.00),
('Security Audit', '2022-09-01', '2022-11-30', 75000.00),
('Product Rebranding', '2021-08-01', '2022-02-28', 120000.00),
('Cloud Infrastructure', '2020-06-01', '2021-06-01', 450000.00),
('Employee Training Portal', '2023-03-01', NULL, NULL), -- Budget not approved yet
('Sales CRM Upgrade', '2022-10-01', '2023-01-31', 180000.00),
('Legal Compliance Review', '2021-12-01', '2022-03-31', 90000.00),
('Customer Feedback System', '2023-01-15', '2023-06-30', 110000.00),
('Data Analytics Dashboard', '2022-04-01', '2022-09-30', 160000.00);
-- ============================================
-- POPULATE: employee_projects
-- ============================================
INSERT INTO employee_projects (employee_id, project_id, role) VALUES
(2, 1, 'Lead Developer'),
(12, 1, 'Frontend Developer'),
(17, 1, 'Backend Developer'),
(2, 2, 'Technical Lead'),
(10, 2, 'Senior Developer'),
(12, 2, 'Mobile Developer'),
(2, 3, 'Database Architect'),
(10, 3, 'Migration Specialist'),
(3, 4, 'Campaign Manager'),
(4, 4, 'Content Strategist'),
(18, 4, 'Social Media Coordinator'),
(2, 5, 'Tech Lead'),
(10, 5, 'Full Stack Developer'),
(17, 5, 'Frontend Developer'),
(15, 6, 'Data Scientist'),
(2, 6, 'Integration Engineer'),
(8, 7, 'Financial Analyst'),
(7, 7, 'HR Coordinator'),
(2, 8, 'Security Engineer'),
(10, 8, 'Code Reviewer'),
(3, 9, 'Brand Manager'),
(4, 9, 'Designer'),
(2, 10, 'Cloud Architect'),
(10, 10, 'DevOps Engineer'),
(7, 11, 'Project Manager'),
(9, 11, 'Content Developer'),
(5, 12, 'Sales Lead'),
(6, 12, 'CRM Administrator'),
(19, 12, 'Sales Analyst'),
(13, 13, 'Legal Advisor'),
(9, 14, 'Support Lead'),
(20, 14, 'Feedback Analyst'),
(15, 15, 'Lead Data Analyst'),
(11, 15, 'Product Manager');
-- ============================================
-- VERIFICATION: View all tables
-- ============================================
Verify Your Setup
After running the script above, run these queries to confirm everything loaded correctly:
Check departments:
1
SELECT * FROM departments;
You should see 10 departments.
Check employees:
1
SELECT * FROM employees;
You should see 20 employees with varied salaries, hire dates, and some NULLs.
Check projects:
1
SELECT * FROM projects;
You should see 15 projects with different statuses (some completed, some ongoing).
Check employee_projects:
1
SELECT * FROM employee_projects;
You should see 35 assignment records showing who works on which project.
What’s Special About This Database?
• Realistic variety — Not every employee has a manager, not every project has an end date, not everyone has been assigned a department yet.
• Hierarchies — Employees have managers (the manager_id column references another employee). You’ll use this later to learn self-joins and recursive queries.
• Many-to-many relationships — Employees work on multiple projects, and projects have multiple employees. The employee_projects table models this.
• Salary ranges — From $55,000 to $120,000. Perfect for aggregate function practice.
• Date variety — Hire dates span from 2017 to 2023. Great for sorting, filtering, and date function practice.
You’re Ready!
Your database is live. Every topic from here on will use company_db. Keep this setup script handy — if you ever need to reset your database, just re-run it.
Up Next
Ready to learn? Start by understanding the structure you just created:
Next topic: CREATE TABLE → part1_01_create_table.md
You’ll learn what those CREATE TABLE statements actually mean, then move on to adding data with INSERT!