-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathemployeescript.sql
More file actions
100 lines (84 loc) · 5.2 KB
/
Copy pathemployeescript.sql
File metadata and controls
100 lines (84 loc) · 5.2 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
create table Employee(
ID int Identity,
Firstname varchar(100),
Lastname varchar(100),
Salary int,
streetaddress varchar(100),
zipcode int,
DepartmentID int
Foreign Key(DepartmentID) references Department(DepartmentID));
create table Department(
DepartmentID int Not Null,
Departmentname varchar(100) Not null,
)
Alter table Department add Primary key(DepartmentID);
insert into Department(DepartmentID,Departmentname) values (1001, 'tools');
insert into Department(DepartmentID,Departmentname) values (1002, 'medicine');
insert into Department(DepartmentID,Departmentname) values (1003, 'Home');
insert into Department(DepartmentID,Departmentname) values (1004, 'Construction');
insert into Department(DepartmentID,Departmentname) values (1005, 'Food');
insert into Department(DepartmentID,Departmentname) values (1006, 'Utils');
insert into Employee values ('jim','wcam',60000,'401 trolly rd',23441,1003);
insert into Employee values ('asam','scam',100000,'404 trolly rd',23421,1002);
insert into Employee values ('qsam','wcam',100000,'404 trolly rd',23421,1001);
insert into Employee values ('sam','rcam',100000,'404 trolly rd',23421,1006);
insert into Employee values ('sam','ecam',100000,'404 trolly rd',23421,1005);
insert into Employee values ('sam','ecam',100000,'404 trolly rd',23421,1004);
insert into Employee values ('sam','gcam',100000,'404 trolly rd',23421,1003);
insert into Employee values ('sam','fcam',100000,'404 trolly rd',23421,1002);
insert into Employee values ('sam','fcam',100000,'404 trolly rd',23421,1001);
insert into Employee values ('sam','tcam',100000,'404 trolly rd',23421,1005);
insert into Employee values ('sam','ycam',100000,'404 trolly rd',23421,1006);
insert into Employee values ('sam','rcam',100000,'404 trolly rd',23421,1004);
insert into Employee values ('sam','hcam',100000,'404 trolly rd',23421,1003);
insert into Employee values ('sam','jcam',100000,'404 trolly rd',23421,1002);
insert into Employee values ('sam','ycam',100000,'404 trolly rd',23421,1001);
insert into Employee values ('sam','icam',100000,'404 trolly rd',23421,1003);
insert into Employee values ('sam','ocam',100000,'404 trolly rd',23421,1002);
insert into Employee values ('sam','cpam',100000,'404 trolly rd',23421,1001);
insert into Employee values ('sam','fgcam',100000,'404 trolly rd',23421,1006);
insert into Employee values ('sam','fghcam',100000,'404 trolly rd',23421,1003);
insert into Employee values ('sam','gbcam',100000,'404 trolly rd',23421,1005);
insert into Employee values ('sam','acam',100000,'404 trolly rd',23421,1003);
insert into Employee values ('sam','gvcam',100000,'404 trolly rd',23421,1002);
insert into Employee values ('sam','yrcam',100000,'404 trolly rd',23421,1003);
insert into Employee values ('sam','nhcam',100000,'404 trolly rd',23421,1001);
insert into Employee values ('sam','jicam',100000,'404 trolly rd',23421,1005);
insert into Employee values ('sam','jhcam',100000,'404 trolly rd',23421,1006);
insert into Employee values ('sam','kucam',100000,'404 trolly rd',23421,1004);
insert into Employee values ('sam','ytcam',100000,'404 trolly rd',23421,1003);
insert into Employee values ('sam','hcam',100000,'404 trolly rd',23421,1005);
insert into Employee values ('sam','ytecam',100000,'404 trolly rd',23421,1004);
insert into Employee values ('sam','htrecam',100000,'404 trolly rd',23421,1003);
insert into Employee values ('sam','hcam',100000,'404 trolly rd',23421,1002);
insert into Employee values ('sam','fcam',100000,'404 trolly rd',23421,1004);
insert into Employee values ('sam','rtyrcam',100000,'404 trolly rd',23421,1005);
insert into Employee values ('sam','recam',100000,'404 trolly rd',23421,1002);
insert into Employee values ('sfam','ygcam',100000,'404 trolly rd',23421,1003);
insert into Employee values ('shtam','htrcam',100000,'404 trolly rd',23421,1004);
insert into Employee values ('sjgfham','jrrcam',100000,'404 trolly rd',23421,1002);
insert into Employee values ('sdfgam','trwcam',100000,'404 trolly rd',23421,1004);
insert into Employee values ('shgfam','uhgcam',100000,'404 trolly rd',23421,1003);
insert into Employee values ('sdsam','rtecam',100000,'404 trolly rd',23421,1005);
insert into Employee values ('sfam','iytcam',100000,'404 trolly rd',23421,1006);
insert into Employee values ('syam','grcam',100000,'404 trolly rd',23421,1003);
insert into Employee values ('suam','hfdcam',100000,'404 trolly rd',23421,1004);
insert into Employee values ('sjam','gvcam',100000,'404 trolly rd',23421,1005);
insert into Employee values ('stam','ygcam',100000,'404 trolly rd',23421,1003);
insert into Employee values ('sam','cam',100000,'404 trolly rd',23421,1001);
insert into Employee values ('sam','cam',100000,'404 trolly rd',23421,1001);
insert into Employee values ('sam','cam',100000,'404 trolly rd',23421,1001);
insert into Employee values ('sam','cam',100000,'404 trolly rd',23421,1001);
insert into Employee values ('sam','cam',100000,'404 trolly rd',23421,1001);
SELECT Departmentname ,SUM(SALARY) as 'Salary'
from Employee inner join Department on
Employee.DepartmentID = Department.DepartmentID
Group by Departmentname
Order by Salary asc;
Alter table Employee Add Date_Hired Date;
Update Employee
set Salary = 100000
where ID = 20;
Alter table Employee Add Active bit;
Update Employee
set Active = 1;