-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathEmployeePayrollService.sql
233 lines (166 loc) · 8.52 KB
/
EmployeePayrollService.sql
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
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
--UC1-Welcome to Employee Payroll Problem
Create Database Employee_payroll_service
--go to the databse
use Employee_payroll_service
--UC2-Creating the Table
Create Table employee_payroll(
id int primary key identity not null,
name varchar(20) not null,
salary int not null,
startdate date not null)
--to select all / display the table
select * from employee_payroll
--UC3-Inserting the data in to table
INSERT INTO employee_payroll values ('Vaibhav',30000,'2019-07-27'),('Sanjay',40000,'2019-02-10'),('Subham',35000,'2019-11-04'),('Minu',15000,'2020-06-20'),('Sai',18000,'2019-08-01');
--UC4-to select all / display the table
select * from employee_payroll
--UC5-Retrieve salary data from particular employee
SELECT salary FROM employee_payroll
WHERE name = 'Vaibhav'
--UC5-Retrieve Names from the selected date to present date
select name FROM employee_payroll
WHERE startdate BETWEEN CAST('2019-01-01' AS DATE) AND GETDATE()
--UC6--
--Alter table to add gender column
ALTER TABLE employee_payroll
add gender char
--update the gender
update employee_payroll
set gender = 'M'
update employee_payroll
set gender = 'F'
where Name = 'Sai''Minu'
--add field gender field after name field
select id,name,gender,startdate,salary from employee_payroll
--UC7--
--Sum of salary of employee groupby Male & Female employee
select SUM(salary) FROM employee_payroll where gender = 'M' GROUP BY gender
select SUM(salary) FROM employee_payroll where gender = 'F' GROUP BY gender
--Average salary of employee groupby Male & Female employee
select AVG(salary) FROM employee_payroll where gender = 'M' GROUP BY gender
select AVG(salary) FROM employee_payroll where gender = 'F' GROUP BY gender
--Min salary of employee groupby Male & Female employee
select MIN(salary) FROM employee_payroll where gender = 'M' GROUP BY gender
select MIN(salary) FROM employee_payroll where gender = 'F' GROUP BY gender
--Max salary of employee groupby Male & Female employee
select MAX(salary) FROM employee_payroll where gender = 'M' GROUP BY gender
select MAX(salary) FROM employee_payroll where gender = 'F' GROUP BY gender
--Number of employee groupby Male &Female employee
select COUNT(salary) FROM employee_payroll where gender = 'M' GROUP BY gender
select COUNT(salary) FROM employee_payroll where gender = 'F' GROUP BY gender
--Alter the Table
ALTER TABLE employee_payroll
add phone bigint , address varchar(25) default 'Nagpur', department varchar(20)
select * from employee_payroll
--UC8-updating the phone, address and department field
update employee_payroll
set phone = 9764510067, address = 'Nagpur', department = 'Chemical' where name = 'Vaibhav'
update employee_payroll
set phone = 92115363496, address = 'Godia', department = 'Electronics' where name = 'Sanjay'
update employee_payroll
set phone = 8760054592, address = 'Bangalore', department = 'Civil' where name = 'Subham'
update employee_payroll
set phone = 9865327452, address = 'Chitradurga', department = 'Computer Science' where name = 'Minu'
update employee_payroll
set phone = 8762265775, address = 'Humpi', department = 'Electrical' where name = 'Sai'
--Altering the column deparment to not null
ALTER table employee_payroll
alter column department varchar(20) not null
--Altering the column address to not null
ALTER table employee_payroll
alter column address varchar(25) not null
--insert the data into employee_payroll table
insert into employee_payroll values ('Akash',20000,'2019-12-15','M',8742516384,'Kurkumb','Production')
--UC9--
--Altering the table by adding additional information regarding salaryies of employee
ALTER TABLE employee_payroll
add BasicPay float, Deductions float, TaxablePay float, IncomeTax float, NetPay float
--updating the BasicPay, Deduction, TaxablePay, IncomeTax & NetPay
update employee_payroll
set BasicPay = 30000, Deductions = 2000, TaxablePay = 1000, IncomeTax = 500, NetPay= 21500 where name = 'Vaibhav'
update employee_payroll
set BasicPay = 40000, Deductions = 1000, TaxablePay = 1000, IncomeTax = 200, NetPay= 18000 where name = 'Sanjay'
update employee_payroll
set BasicPay = 35000, Deductions = 2500, TaxablePay = 1500, IncomeTax = 500, NetPay= 30000 where name = 'Subham'
update employee_payroll
set BasicPay = 15000, Deductions = 500, TaxablePay = 500, IncomeTax = 100, NetPay= 13500 where name = 'Minu'
update employee_payroll
set BasicPay = 18000, Deductions = 1000, TaxablePay = 500, IncomeTax = 100, NetPay= 16000 where name = 'Sai'
update employee_payroll
set BasicPay = 20000, Deductions = 1000, TaxablePay = 1000, IncomeTax = 200, NetPay= 18000 where name = 'Akash'
select * from employee_payroll
--UC10--
--Insert employee details
insert into employee_payroll values ('Terissa', 35000, '2018-05-06', 'F',98263579510,'Mumbai','Sales',35000, 2500, 1500, 500, 31000),
('Terissa', 35000, '2018-05-06', 'F',98263579510,'Mumbai','Marketing',35000, 2500, 1500, 500, 31000)
--UC11--
--check for composite or multivaled data
select * from employee_payroll where name = 'Terissa'
--UC12--
--Droping the Department entity
ALTER TABLE employee_payroll DROP column department
--Creating departmentdetails table
CREATE TABLE departmentdetails
(departmentID int primary key,
department varchar (20))
--add the departmentid entity
ALTER TABLE employee_payroll add departmentID int foreign key references departmentdetails
select * from employee_payroll
select * from departmentdetails
--re-assigning the department and department id in departmentdetails
insert into departmentdetails values (1,'Sales'),(2,'Marketing'),(3,'HR')
--updating the phone, address and department field
update employee_payroll
set departmentID = 2 where name = 'Vaibhav'
update employee_payroll
set departmentID = 1 where name = 'Sanjay'
update employee_payroll
set departmentID = 1 where name = 'Subham'
update employee_payroll
set departmentID = 3 where name = 'Minu'
update employee_payroll
set departmentID = 3 where name = 'Sai'
update employee_payroll
set departmentID = 2 where name = 'Akash'
--Insert employee details
insert into employee_payroll values ('Terissa', 35000, '2018-05-06', 'F',98263579510,'Mumbai',35000, 2500, 1500, 500, 31000,2),
('Terissa', 35000, '2018-05-06', 'F',98263579510,'Mumbai',35000, 2500, 1500, 500, 31000,1)
--Sum of salary of employee groupby Male & Female employee
select SUM(salary) FROM employee_payroll where gender = 'M' GROUP BY gender
select SUM(salary) FROM employee_payroll where gender = 'F' GROUP BY gender
--Average salary of employee groupby Male & Female employee
select AVG(salary) FROM employee_payroll where gender = 'M' GROUP BY gender
select AVG(salary) FROM employee_payroll where gender = 'F' GROUP BY gender
--Min salary of employee groupby Male & Female employee
select MIN(salary) FROM employee_payroll where gender = 'M' GROUP BY gender
select MIN(salary) FROM employee_payroll where gender = 'F' GROUP BY gender
--Max salary of employee groupby Male & Female employee
select MAX(salary) FROM employee_payroll where gender = 'M' GROUP BY gender
select MAX(salary) FROM employee_payroll where gender = 'F' GROUP BY gender
--Number of employee groupby Male & Female employee
select COUNT(salary) FROM employee_payroll where gender = 'M' GROUP BY gender
select COUNT(salary) FROM employee_payroll where gender = 'F' GROUP BY gender
--Retrieve salary data from particular employee
SELECT salary FROM employee_payroll
WHERE name = 'Terissa'
--to select all / display the table
select * from employee_payroll
--Retrieve Names from the selected date to present date
select name FROM employee_payroll
WHERE startdate BETWEEN CAST('2019-01-01' AS DATE) AND GETDATE()
--Sum of salary of employee groupby Male employee
select SUM(salary) FROM employee_payroll where gender = 'M' GROUP BY gender
select SUM(salary) FROM employee_payroll where gender = 'F' GROUP BY gender
--Average salary of employee groupby Male employee
select AVG(salary) FROM employee_payroll where gender = 'M' GROUP BY gender
select AVG(salary) FROM employee_payroll where gender = 'F' GROUP BY gender
--Min salary of employee groupby Male employee
select MIN(salary) FROM employee_payroll where gender = 'M' GROUP BY gender
select MIN(salary) FROM employee_payroll where gender = 'F' GROUP BY gender
--Max salary of employee groupby Male employee
select MAX(salary) FROM employee_payroll where gender = 'M' GROUP BY gender
select MAX(salary) FROM employee_payroll where gender = 'F' GROUP BY gender
--Number of employee groupby Male employee
select COUNT(salary) FROM employee_payroll where gender = 'M' GROUP BY gender
select COUNT(salary) FROM employee_payroll where gender = 'F' GROUP BY gender
delete from employee_payroll where id = 7 or id = 8