-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathseed_data.sql
More file actions
155 lines (145 loc) · 7.33 KB
/
seed_data.sql
File metadata and controls
155 lines (145 loc) · 7.33 KB
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
-- ============================================================
-- Workforce Operations Analytics
-- Seed Data: Tables + Sample Records
-- ============================================================
-- -------------------------------------------------------
-- DEPARTMENTS
-- -------------------------------------------------------
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50),
location VARCHAR(50),
manager_id INT
);
INSERT INTO departments VALUES
(1, 'Data Operations', 'Seattle', 101),
(2, 'Quality Assurance', 'Seattle', 102),
(3, 'Research', 'Kirkland', 103),
(4, 'Engineering', 'Kirkland', 104),
(5, 'Logistics', 'Remote', 105);
-- -------------------------------------------------------
-- EMPLOYEES
-- -------------------------------------------------------
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department_id INT,
job_title VARCHAR(60),
hire_date DATE,
hourly_rate DECIMAL(6,2),
employment_type VARCHAR(20), -- Full-time, Part-time, Contract
is_active BOOLEAN
);
INSERT INTO employees VALUES
(101,'Jordan','Kim', 1,'Senior Data Analyst', '2021-03-15', 48.00,'Full-time', TRUE),
(102,'Sam', 'Patel', 2,'QA Lead', '2020-07-01', 45.00,'Full-time', TRUE),
(103,'Alex', 'Torres', 3,'Research Coordinator', '2022-01-10', 38.00,'Full-time', TRUE),
(104,'Casey', 'Nguyen', 4,'Data Engineer', '2021-09-20', 52.00,'Full-time', TRUE),
(105,'Morgan','Lee', 5,'Logistics Analyst', '2022-06-01', 35.00,'Full-time', TRUE),
(106,'Riley', 'Chen', 1,'Data Analyst', '2023-02-14', 40.00,'Full-time', TRUE),
(107,'Drew', 'Williams', 2,'QA Analyst', '2023-05-01', 36.00,'Full-time', TRUE),
(108,'Jamie', 'Brown', 1,'Data Operations Coord.', '2022-11-07', 32.00,'Contract', TRUE),
(109,'Taylor','Johnson', 3,'Research Analyst', '2023-08-15', 34.00,'Part-time', TRUE),
(110,'Avery', 'Martinez', 4,'Junior Data Engineer', '2024-01-10', 38.00,'Full-time', TRUE),
(111,'Quinn', 'Davis', 1,'Data Analyst', '2022-04-20', 41.00,'Full-time', TRUE),
(112,'Sage', 'Wilson', 2,'QA Specialist', '2021-12-01', 37.00,'Full-time', TRUE),
(113,'Blake', 'Anderson', 5,'Logistics Coord.', '2023-03-17', 30.00,'Part-time', FALSE),
(114,'Peyton','Thomas', 4,'Data Engineer', '2020-10-05', 50.00,'Full-time', TRUE),
(115,'Reese', 'Jackson', 3,'Research Lead', '2019-06-22', 55.00,'Full-time', TRUE);
-- -------------------------------------------------------
-- SHIFTS (scheduled)
-- -------------------------------------------------------
CREATE TABLE shifts (
shift_id INT PRIMARY KEY,
employee_id INT,
shift_date DATE,
scheduled_start TIME,
scheduled_end TIME,
shift_type VARCHAR(20) -- Morning, Afternoon, Evening
);
INSERT INTO shifts VALUES
(1001,101,'2024-01-08','08:00','16:00','Morning'),
(1002,102,'2024-01-08','09:00','17:00','Morning'),
(1003,103,'2024-01-08','12:00','20:00','Afternoon'),
(1004,106,'2024-01-09','08:00','16:00','Morning'),
(1005,107,'2024-01-09','09:00','17:00','Morning'),
(1006,108,'2024-01-09','13:00','21:00','Afternoon'),
(1007,101,'2024-01-10','08:00','16:00','Morning'),
(1008,104,'2024-01-10','10:00','18:00','Morning'),
(1009,111,'2024-01-10','08:00','16:00','Morning'),
(1010,112,'2024-01-11','09:00','17:00','Morning'),
(1011,109,'2024-01-11','12:00','16:00','Afternoon'),
(1012,106,'2024-01-11','08:00','16:00','Morning'),
(1013,114,'2024-01-12','08:00','16:00','Morning'),
(1014,115,'2024-01-12','08:00','16:00','Morning'),
(1015,103,'2024-01-12','12:00','20:00','Afternoon'),
(1016,110,'2024-01-13','09:00','17:00','Morning'),
(1017,101,'2024-01-14','08:00','16:00','Morning'),
(1018,106,'2024-01-14','08:00','16:00','Morning'),
(1019,107,'2024-01-15','09:00','17:00','Morning'),
(1020,108,'2024-01-15','13:00','21:00','Afternoon');
-- -------------------------------------------------------
-- ATTENDANCE (actual clock-in/out)
-- -------------------------------------------------------
CREATE TABLE attendance (
attendance_id INT PRIMARY KEY,
shift_id INT,
employee_id INT,
clock_in TIMESTAMP,
clock_out TIMESTAMP,
status VARCHAR(20) -- Present, Late, Absent, Left Early
);
INSERT INTO attendance VALUES
(2001,1001,101,'2024-01-08 08:02:00','2024-01-08 16:05:00','Present'),
(2002,1002,102,'2024-01-08 09:14:00','2024-01-08 17:00:00','Late'),
(2003,1003,103,'2024-01-08 12:00:00','2024-01-08 20:00:00','Present'),
(2004,1004,106,'2024-01-09 08:00:00','2024-01-09 16:00:00','Present'),
(2005,1005,107,'2024-01-09 09:32:00','2024-01-09 17:00:00','Late'),
(2006,1006,108,'2024-01-09 13:00:00','2024-01-09 21:00:00','Present'),
(2007,1007,101,'2024-01-10 08:05:00','2024-01-10 16:00:00','Present'),
(2008,1008,104,'2024-01-10 10:00:00','2024-01-10 18:00:00','Present'),
(2009,1009,111,'2024-01-10 08:45:00','2024-01-10 16:00:00','Late'),
(2010,1010,112,'2024-01-11 09:00:00','2024-01-11 17:00:00','Present'),
-- Absent (no attendance record for shift 1011 / employee 109)
(2011,1012,106,'2024-01-11 08:00:00','2024-01-11 14:30:00','Left Early'),
(2012,1013,114,'2024-01-12 08:00:00','2024-01-12 16:00:00','Present'),
(2013,1014,115,'2024-01-12 08:00:00','2024-01-12 16:00:00','Present'),
(2014,1015,103,'2024-01-12 12:10:00','2024-01-12 20:00:00','Present'),
(2015,1016,110,'2024-01-13 09:00:00','2024-01-13 17:00:00','Present'),
(2016,1017,101,'2024-01-14 08:00:00','2024-01-14 16:00:00','Present'),
(2017,1018,106,'2024-01-14 08:22:00','2024-01-14 16:00:00','Late'),
(2018,1019,107,'2024-01-15 09:00:00','2024-01-15 17:00:00','Present'),
(2019,1020,108,'2024-01-15 13:55:00','2024-01-15 21:00:00','Late');
-- -------------------------------------------------------
-- TASKS (work output / productivity)
-- -------------------------------------------------------
CREATE TABLE tasks (
task_id INT PRIMARY KEY,
employee_id INT,
task_date DATE,
task_type VARCHAR(50),
tasks_assigned INT,
tasks_completed INT,
quality_score DECIMAL(4,2) -- 0.00 to 100.00
);
INSERT INTO tasks VALUES
(3001,101,'2024-01-08','Data Validation', 20, 20, 98.5),
(3002,102,'2024-01-08','QA Review', 15, 14, 93.0),
(3003,103,'2024-01-08','Data Collection', 25, 25, 97.0),
(3004,106,'2024-01-09','Data Validation', 18, 18, 96.0),
(3005,107,'2024-01-09','QA Review', 12, 10, 88.5),
(3006,108,'2024-01-09','Data Entry', 30, 28, 91.0),
(3007,101,'2024-01-10','Pipeline Review', 10, 10, 99.0),
(3008,104,'2024-01-10','ETL Validation', 15, 15, 97.5),
(3009,111,'2024-01-10','Data Validation', 20, 17, 85.0),
(3010,112,'2024-01-11','QA Review', 18, 18, 94.5),
(3011,106,'2024-01-11','Data Validation', 10, 7, 82.0),
(3012,114,'2024-01-12','ETL Validation', 12, 12, 98.0),
(3013,115,'2024-01-12','Research Review', 20, 20, 99.5),
(3014,103,'2024-01-12','Data Collection', 22, 21, 95.0),
(3015,110,'2024-01-13','ETL Validation', 10, 10, 93.5),
(3016,101,'2024-01-14','Data Validation', 22, 22, 97.0),
(3017,106,'2024-01-14','Data Validation', 15, 14, 90.0),
(3018,107,'2024-01-15','QA Review', 14, 14, 95.5),
(3019,108,'2024-01-15','Data Entry', 28, 25, 89.0);