-
Notifications
You must be signed in to change notification settings - Fork 0
/
DBScripts.sql
83 lines (59 loc) · 1.26 KB
/
DBScripts.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
create table employee (
emp_no int not null,
emp_name char(100),
dept_id int ,
salary int,
constraint emp_no_pk primary key (emp_no),
constraint dept_id_fk foreign key (dept_id) references department (dept_id)
);
create table department (
dept_id int,
dept_name char(50) not null,
constraint dept_id_pk primary key (dept_id)
);
--select * from employee
insert into employee
values(
100, 'Alex White', 10, 60000
);
insert into employee
values(
101, 'James Hunter', 10, 65000
);
insert into employee
values(
102, 'Olivia Ditcher', 10, 70000
);
insert into employee
values(
103, 'Lisa Ray', 10, 40000
);
insert into employee
values(
104, 'Naveen Mishra', 20, 50000
);
insert into employee
values(
105, 'Kelly Evans', 30, 90000
);
insert into employee
values(
106, 'Christine Fox', 40, 100000
);
commit;
--select * from employee
--select * from department;
insert into department
values(10, 'ACCOUNTING');
insert into department
values(20, 'RESEARCH');
insert into department
values(30, 'SALES');
insert into department
values(40, 'OPERATIONS');
commit;
-- to get average salary department wise
select dept_id, AVG(salary) from employee
group by dept_id;
-- to get current date and time
select CURRENT_TIMESTAMP AS current_date_time;