Repository navigation
Expand file tree
/
Copy path03_insert_data.sql
More file actions
126 lines (113 loc) · 6.77 KB
/
Copy path03_insert_data.sql
File metadata and controls
126 lines (113 loc) · 6.77 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
-- ===========================================================================
-- Employee Management System (EMS)
-- File: 03_insert_data.sql
-- Description: Populates all tables with realistic sample data
-- ===========================================================================
USE EMS;
-- ===========================================================================
-- Data: departments
-- ===========================================================================
INSERT INTO departments (department_name, location)
VALUES
('IT', 'Pune'),
('HR', 'Mumbai'),
('Finance', 'Bengaluru'),
('Sales', 'Delhi'),
('Marketing', 'Hyderabad'),
('Operations', 'Chennai'),
('Production', 'Nashik'),
('Quality', 'Nagpur'),
('Research & Development', 'Pune'),
('Customer Support', 'Noida');
SELECT * FROM departments;
-- ===========================================================================
-- Data: jobs
-- ===========================================================================
INSERT INTO jobs (job_title, min_salary, max_salary, job_description)
VALUES
('Software Developer', 350000.00, 800000.00, 'Develops and maintains software applications'),
('Data Analyst', 300000.00, 700000.00, 'Analyzes data and prepares reports'),
('HR Executive', 250000.00, 500000.00, 'Manages recruitment and employee relations'),
('Accountant', 300000.00, 600000.00, 'Handles financial records and transactions'),
('Sales Executive', 250000.00, 650000.00, 'Generates sales and manages clients'),
('Marketing Executive', 280000.00, 650000.00, 'Plans and executes marketing campaigns'),
('Production Engineer', 350000.00, 750000.00, 'Supervises production processes'),
('Quality Engineer', 350000.00, 700000.00, 'Ensures product quality standards'),
('Operations Manager', 600000.00, 1200000.00, 'Oversees daily business operations'),
('Project Manager', 700000.00, 1500000.00, 'Plans and manages projects');
SELECT * FROM jobs;
-- ===========================================================================
-- Data: employees
-- ===========================================================================
INSERT INTO employees (first_name, last_name, gender, email, phone, hire_date,
salary, department_id, job_id, manager_id)
VALUES
('Amit', 'Sharma', 'Male', 'amit.sharma@company.com', '9876500001', '2022-01-10', 1200000.00, 1, 10, NULL),
('Priya', 'Patil', 'Female', 'priya.patil@company.com', '9876500002', '2022-02-15', 750000.00, 1, 1, 1),
('Rahul', 'Verma', 'Male', 'rahul.verma@company.com', '9876500003', '2022-03-20', 650000.00, 2, 3, 1),
('Sneha', 'Kulkarni', 'Female', 'sneha.kulkarni@company.com', '9876500004', '2022-04-12', 550000.00, 3, 4, 1),
('Vikas', 'Singh', 'Male', 'vikas.singh@company.com', '9876500005', '2022-05-08', 500000.00, 4, 5, 1),
('Neha', 'Joshi', 'Female', 'neha.joshi@company.com', '9876500006', '2022-06-18', 520000.00, 5, 6, 1),
('Rohan', 'Deshmukh', 'Male', 'rohan.deshmukh@company.com', '9876500007', '2022-07-25', 700000.00, 6, 9, 1),
('Pooja', 'Mehta', 'Female', 'pooja.mehta@company.com', '9876500008', '2022-08-10', 620000.00, 7, 7, 7),
('Karan', 'Yadav', 'Male', 'karan.yadav@company.com', '9876500009', '2022-09-05', 580000.00, 8, 8, 7),
('Anjali', 'Nair', 'Female', 'anjali.nair@company.com', '9876500010', '2022-10-01', 680000.00, 9, 2, 1);
SELECT * FROM employees;
-- ===========================================================================
-- Data: attendance
-- ===========================================================================
INSERT INTO attendance (employee_id, attendance_date, check_in_time, check_out_time, status)
VALUES
(1, '2026-08-01', '09:00:00', '18:00:00', 'Present'),
(2, '2026-08-01', '09:10:00', '18:05:00', 'Present'),
(3, '2026-08-01', '09:15:00', '18:10:00', 'Present'),
(4, '2026-08-01', '09:05:00', '17:55:00', 'Present'),
(5, '2026-08-01', '09:20:00', '18:15:00', 'Present'),
(6, '2026-08-01', '09:00:00', '18:00:00', 'Present'),
(7, '2026-08-01', '09:30:00', '18:20:00', 'Present'),
(8, '2026-08-01', '09:00:00', '17:50:00', 'Present'),
(9, '2026-08-01', '09:40:00', '18:30:00', 'Present'),
(10, '2026-08-01', '09:00:00', '18:00:00', 'Present'),
(1, '2026-08-02', '09:05:00', '18:10:00', 'Present'),
(2, '2026-08-02', '09:00:00', '18:00:00', 'Present'),
(3, '2026-08-02', '09:25:00', '18:15:00', 'Present'),
(4, '2026-08-02', '09:00:00', '18:00:00', 'Present'),
(5, '2026-08-02', '09:15:00', '18:05:00', 'Present'),
(6, '2026-08-02', '09:00:00', '18:00:00', 'Present'),
(7, '2026-08-02', '09:10:00', '18:10:00', 'Present'),
(8, '2026-08-02', '09:35:00', '18:20:00', 'Present'),
(9, '2026-08-02', '09:00:00', '18:00:00', 'Present'),
(10, '2026-08-02', '09:20:00', '18:10:00', 'Present');
SELECT * FROM attendance;
-- ===========================================================================
-- Data: leaves
-- ===========================================================================
INSERT INTO leaves (employee_id, leave_type, start_date, end_date, reason, status)
VALUES
(3, 'Sick', '2026-08-10', '2026-08-11', 'Fever and cold', 'Approved'),
(5, 'Casual', '2026-08-15', '2026-08-15', 'Personal work', 'Approved'),
(2, 'Earned', '2026-09-01', '2026-09-05', 'Family vacation', 'Pending'),
(7, 'Sick', '2026-08-20', '2026-08-22', 'Medical treatment', 'Approved'),
(4, 'Casual', '2026-08-18', '2026-08-18', 'Family function', 'Approved'),
(8, 'Earned', '2026-09-10', '2026-09-12', 'Travel', 'Pending'),
(6, 'Casual', '2026-08-25', '2026-08-25', 'Bank work', 'Rejected'),
(9, 'Sick', '2026-08-28', '2026-08-29', 'Food poisoning', 'Approved'),
(10, 'Earned', '2026-09-15', '2026-09-20', 'Vacation', 'Pending'),
(1, 'Casual', '2026-08-30', '2026-08-30', 'Personal work', 'Approved');
SELECT * FROM leaves;
-- ===========================================================================
-- Data: performance
-- ===========================================================================
INSERT INTO performance (employee_id, review_date, rating, review_comments)
VALUES
(1, '2026-07-31', 5, 'Outstanding leadership and decision making.'),
(2, '2026-07-31', 4, 'Consistently delivers quality work.'),
(3, '2026-07-31', 5, 'Excellent analytical and problem-solving skills.'),
(4, '2026-07-31', 3, 'Needs improvement in meeting deadlines.'),
(5, '2026-07-31', 4, 'Good communication with clients.'),
(6, '2026-07-31', 3, 'Average performance with scope for improvement.'),
(7, '2026-07-31', 5, 'Excellent team management and planning.'),
(8, '2026-07-31', 4, 'Maintains high quality standards.'),
(9, '2026-07-31', 2, 'Needs improvement in productivity.'),
(10, '2026-07-31', 5, 'Exceptional performance throughout the review period.');
SELECT * FROM performance;