EXP2_CSE2004Lab_20BRS1064

--EXP2 CSE2004Lab 20BRS1064
CREATE TABLE tester_20BRS1064(
testerid varchar2(4) CHECK (testerid LIKE 'T%') PRIMARY KEY,
testername varchar2(20) NOT NULL,
jobband varchar2(1) CHECK (jobband IN ('C','B'))
);

CREATE TABLE developer_20BRS1064(
developerid varchar2(4) PRIMARY KEY CHECK (developerid LIKE 'D%'),
developername varchar2(20) NOT NULL,
developerexperience varchar2(15),
phonenumber NUMBER
);

CREATE TABLE bugreport_20BRS1064(
bugid varchar2(4) PRIMARY KEY CHECK (bugid LIKE 'B%'),
testerid varchar2(4),
bugdescription varchar2(50) NOT NULL,
severity varchar2(1) CHECK (severity IN ('C','M','L')),
reporteddate DATE,
CONSTRAINT FK_testerid FOREIGN KEY (testerid) REFERENCES tester_20BRS1064(testerid)
);

CREATE TABLE bugallocation_20BRS1064(
allocationid varchar2(4) PRIMARY KEY CHECK(allocationid LIKE 'A%'),
bugid varchar2(4),
developerid varchar2(4),
STATUS varchar2(1) CHECK (STATUS IN ('O','C')),
escalationdate DATE,
resolutiondate DATE,
CONSTRAINT FK_bugid FOREIGN KEY (bugid) REFERENCES bugreport_20BRS1064(bugid),
CONSTRAINT FK_developerid FOREIGN KEY (developerid) REFERENCES developer_20BRS1064(developerid)
);

INSERT INTO tester_20BRS1064 VALUES('T101','Arron','B');
INSERT INTO tester_20BRS1064 VALUES('T102','Jacob','C');
INSERT INTO tester_20BRS1064 VALUES('T103','Jerome','B');
INSERT INTO tester_20BRS1064 VALUES('T104','Simonds','B');
INSERT INTO tester_20BRS1064 VALUES('T105','Paul','B');
INSERT INTO tester_20BRS1064 VALUES('T106','Antony','C');
INSERT INTO tester_20BRS1064 VALUES('T107','Nancy','C');
INSERT INTO tester_20BRS1064 VALUES('T108','Gonzalez','B');

INSERT INTO developer_20BRS1064 VALUES('D201','Anson','EXPERT',9785554423);
INSERT INTO developer_20BRS1064 VALUES('D202','Dsouza','COMPETENT',9256743210);
INSERT INTO developer_20BRS1064 VALUES('D203','Sharon','COMPETENT',9886556889);
INSERT INTO developer_20BRS1064 VALUES('D204','Peter','COMPETENT',9298277662);
INSERT INTO developer_20BRS1064 VALUES('D205','Susan','NOVICE',9789175600);
INSERT INTO developer_20BRS1064 VALUES('D206','Sophia','NOVICE',9008134134);

INSERT INTO bugreport_20BRS1064 VALUES('B101','T106','File Not Saved','M',to_date('11-08-2021','DD-MM-YYYY'));
INSERT INTO bugreport_20BRS1064 VALUES('B102','T102','Crash on opening','C',to_date('12-08-2021','DD-MM-YYYY'));
INSERT INTO bugreport_20BRS1064 VALUES('B103','T102','Memory Leak','C',to_date('13-08-2021','DD-MM-YYYY'));
INSERT INTO bugreport_20BRS1064 VALUES('B104','T102','ID Field Editable','L',to_date('14-08-2021','DD-MM-YYYY'));
INSERT INTO bugreport_20BRS1064 VALUES('B105','T101','File corrupted when received','M',to_date('15-08-2021','DD-MM-YYYY'));

INSERT INTO bugallocation_20BRS1064 VALUES('A101','B101','D201','C',to_date('12-08-2021','DD-MM-YYYY'),to_date('21-08-2021','DD-MM-YYYY'));
INSERT INTO bugallocation_20BRS1064 VALUES('A102','B103','D203','C',to_date('13-08-2021','DD-MM-YYYY'),to_date('19-08-2021','DD-MM-YYYY'));
INSERT INTO bugallocation_20BRS1064 VALUES('A103','B102','D204','O',to_date('14-08-2021','DD-MM-YYYY'),to_date('24-08-2021','DD-MM-YYYY'));
INSERT INTO bugallocation_20BRS1064 VALUES('A104','B104','D201','O',to_date('15-08-2021','DD-MM-YYYY'),to_date('22-08-2021','DD-MM-YYYY'));
INSERT INTO bugallocation_20BRS1064 VALUES('A105','B105','D202','O',to_date('16-08-2021','DD-MM-YYYY'),to_date('29-08-2021','DD-MM-YYYY'));

DESC tester_20BRS1064;
DESC developer_20BRS1064;
DESC bugreport_20BRS1064;
DESC bugallocation_20BRS1064;

SELECT * FROM tester_20BRS1064;
SELECT * FROM developer_20BRS1064;
SELECT * FROM bugreport_20BRS1064;
SELECT * FROM bugallocation_20BRS1064;

UPDATE developer_20BRS1064 SET phonenumber= 9998887776 WHERE developerid='D204';
SELECT * FROM developer_20BRS1064;

DELETE FROM bugallocation_20BRS1064 WHERE bugid='B105';
SELECT * FROM bugallocation_20BRS1064;

SELECT * FROM bugallocation_20BRS1064 WHERE STATUS='O';

SELECT * FROM bugreport_20BRS1064,bugallocation_20BRS1064 WHERE resolutiondate - reporteddate >=10 AND STATUS = 'O';

UPDATE tester_20BRS1064 SET testername='John Paul' WHERE testername='Paul';
SELECT * FROM tester_20BRS1064;

DROP TABLE bugallocation_20BRS1064;
DROP TABLE bugreport_20BRS1064;
DROP TABLE developer_20BRS1064;
DROP TABLE tester_20BRS1064;