-
Notifications
You must be signed in to change notification settings - Fork 0
/
Assignment 1.sql
139 lines (97 loc) · 4.68 KB
/
Assignment 1.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
CREATE TABLE PROJECT(
PROJECT_CODE VARCHAR(255),
PROJECT_DESCRIPTION VARCHAR(50) UNIQUE,
PROJECT_START_DATE DATE NOT NULL,
PROJECT_END_DATE DATE
);
CREATE TABLE PROJECT_ALLOCATION(
PROJECT_CODE VARCHAR(255),
EMPNO VARCHAR(10),
EMP_PROJ_ALLOC_DATE DATE,
EMP_PROJ_RELEASE_DATE DATE
);
CREATE TABLE EMPLOYEE(
EMPNO INT PRIMARY KEY,
EMP_NAME VARCHAR(25) NOT NULL,
EMP_JOIN_DATE DATE DEFAULT(SYSDATE),
EMP_STATUS CHAR(1) CHECK(EMP_STATUS = 'C' OR EMP_STATUS = 'P' OR EMP_STATUS = 'R')
);
INSERT INTO EMPLOYEE(EMPNO, EMP_NAME, EMP_JOIN_DATE, EMP_STATUS)
VALUES ('101', 'Jhonny', '01-Jul-2005', 'C');
INSERT INTO EMPLOYEE(EMPNO, EMP_NAME, EMP_JOIN_DATE, EMP_STATUS)
VALUES ('116', 'Nayak' , '16-Aug-2005', 'C');
INSERT INTO EMPLOYEE(EMPNO, EMP_NAME, EMP_JOIN_DATE, EMP_STATUS)
VALUES ('202', 'Meera' , '30-Jan-2006', 'C');
INSERT INTO EMPLOYEE(EMPNO, EMP_NAME, EMP_JOIN_DATE, EMP_STATUS)
VALUES ('205', 'Ravi' , '11-Feb-2006', 'C');
INSERT INTO EMPLOYEE(EMPNO, EMP_NAME, EMP_JOIN_DATE, EMP_STATUS)
VALUES ('304', 'Hari' , '25-Nov-2006', 'P');
INSERT INTO EMPLOYEE(EMPNO, EMP_NAME, EMP_JOIN_DATE, EMP_STATUS)
VALUES ('307', 'Nancy' , '15-Jan-2007', 'P');
INSERT INTO EMPLOYEE(EMPNO, EMP_NAME, EMP_JOIN_DATE, EMP_STATUS)
VALUES ('403', 'Nick' , '21-Jan-2007', 'P');
INSERT INTO PROJECT(PROJECT_CODE, PROJECT_DESCRIPTION, PROJECT_START_DATE, PROJECT_END_DATE)
VALUES('P001','Environment Pollution', '02-Aug-2005', '11-Dec-2006' );
INSERT INTO PROJECT(PROJECT_CODE, PROJECT_DESCRIPTION, PROJECT_START_DATE, PROJECT_END_DATE)
VALUES('P002','Learning Curve', '01-Feb-2006', NULL );
INSERT INTO PROJECT(PROJECT_CODE, PROJECT_DESCRIPTION, PROJECT_START_DATE, PROJECT_END_DATE)
VALUES('P003','Effects of IT', '03-Jan-2007', NULL );
INSERT INTO PROJECT_ALLOCATION(PROJECT_CODE, EMPNO, EMP_PROJ_ALLOC_DATE, EMP_PROJ_RELEASE_DATE)
VALUES('P001', '101', '01-Aug-2005', '11-Dec-2006');
INSERT INTO PROJECT_ALLOCATION(PROJECT_CODE, EMPNO, EMP_PROJ_ALLOC_DATE, EMP_PROJ_RELEASE_DATE)
VALUES('P001', '116', '16-Aug-2005', '11-Dec-2006');
INSERT INTO PROJECT_ALLOCATION(PROJECT_CODE, EMPNO, EMP_PROJ_ALLOC_DATE, EMP_PROJ_RELEASE_DATE)
VALUES('P002', '202', '01-Feb-2006', '14-Jan-2007');
INSERT INTO PROJECT_ALLOCATION(PROJECT_CODE, EMPNO, EMP_PROJ_ALLOC_DATE, EMP_PROJ_RELEASE_DATE)
VALUES('P002', '307', '15-Jan-2007', NULL);
INSERT INTO PROJECT_ALLOCATION(PROJECT_CODE, EMPNO, EMP_PROJ_ALLOC_DATE, EMP_PROJ_RELEASE_DATE)
VALUES('P002', '205', '11-Feb-2006', NULL);
INSERT INTO PROJECT_ALLOCATION(PROJECT_CODE, EMPNO, EMP_PROJ_ALLOC_DATE, EMP_PROJ_RELEASE_DATE)
VALUES('P003', '403', '21-Jan-2007', NULL);
INSERT INTO PROJECT_ALLOCATION(PROJECT_CODE, EMPNO, EMP_PROJ_ALLOC_DATE, EMP_PROJ_RELEASE_DATE)
VALUES('P003', '304', '03-Jan-2007', NULL);
INSERT INTO PROJECT_ALLOCATION(PROJECT_CODE, EMPNO, EMP_PROJ_ALLOC_DATE, EMP_PROJ_RELEASE_DATE)
VALUES('P003', '101', '03-Jan-2007', NULL);
INSERT INTO PROJECT_ALLOCATION(PROJECT_CODE, EMPNO, EMP_PROJ_ALLOC_DATE, EMP_PROJ_RELEASE_DATE)
VALUES('P003', '116', '03-Jan-2007', NULL);
INSERT INTO PROJECT_ALLOCATION(PROJECT_CODE, EMPNO, EMP_PROJ_ALLOC_DATE, EMP_PROJ_RELEASE_DATE)
VALUES('P003', '202', '15-Jan-2007', NULL);
SELECT * FROM PROJECT;
SELECT EMP_NAME FROM EMPLOYEE;
SELECT EMP_NAME FROM EMPLOYEE WHERE EMP_STATUS = 'C';
SELECT * FROM EMPLOYEE;
SELECT * FROM EMPLOYEE WHERE EMP_JOIN_DATE >= '01-Aug-2006';
SELECT * FROM PROJECT WHERE PROJECT_START_DATE >= '01-Jan-2006';
SELECT * FROM PROJECT_ALLOCATION
JOIN EMPLOYEE
ON PROJECT_ALLOCATION.EMPNO = EMPLOYEE.EMPNO
WHERE PROJECT_ALLOCATION.PROJECT_CODE = 'P003' ;
SELECT * FROM PROJECT
WHERE PROJECT_END_DATE IS NULL;
SELECT * FROM EMPLOYEE
JOIN PROJECT_ALLOCATION
ON EMPLOYEE.EMPNO = PROJECT_ALLOCATION.EMPNO
WHERE PROJECT_ALLOCATION.EMP_PROJ_RELEASE_DATE IS NOT NULL AND PROJECT_ALLOCATION.PROJECT_CODE = 'P002';
SELECT PROJECT_END_DATE - PROJECT_START_DATE
FROM PROJECT
WHERE PROJECT_CODE = 'P001';
SELECT EMPLOYEE.EMP_NAME, TRUNC(NVL(PROJECT_ALLOCATION.EMP_PROJ_RELEASE_DATE,SYSDATE)) - PROJECT_ALLOCATION.EMP_PROJ_ALLOC_DATE
FROM PROJECT_ALLOCATION
JOIN EMPLOYEE
ON PROJECT_ALLOCATION.EMPNO = EMPLOYEE.EMPNO;
ALTER TABLE PROJECT
ADD PROJECT_MANAGER VARCHAR(255);
UPDATE PROJECT
SET PROJECT_MANAGER = '101'
WHERE PROJECT_CODE = 'P001';
UPDATE PROJECT
SET PROJECT_MANAGER = '202'
WHERE PROJECT_CODE = 'P002';
UPDATE PROJECT
SET PROJECT_MANAGER = '116'
WHERE PROJECT_CODE = 'P003';
INSERT INTO PROJECT(PROJECT_DESCRIPTION, PROJECT_START_DATE)
VALUES('Election Rage', '01-Mar-2007');
SELECT * FROM PROJECT;
DELETE FROM PROJECT
WHERE PROJECT_DESCRIPTION = 'Election Rage';