Oracle Live SQL - Script: hkjh

Statement 1

CREATE TABLE Users 
( 
USER_ID NUMBER(8) PRIMARY KEY,  
Subscriber_Type char(1) Not null, 
User_Name VARCHAR2(20) NOT NULL,
firstName VARCHAR2(20) NOT NULL,
lastName VARCHAR2(20) NOT NULL,
User_Email VARCHAR2(20) NOT NULL 
)

Statement 2

CREATE TABLE Projects 
( 
Project_Id NUMBER(8) CONSTRAINT project_id_pk PRIMARY KEY, 
Project_Dependency NUMBER(8) CONSTRAINT project_dependency_nn NOT NULL, 
Project_Status VARCHAR2(1) CONSTRAINT project_status_nn NOT NULL,  
Project_Description VARCHAR2(256) CONSTRAINT project_description_nn NOT NULL,  
User_Id NUMBER(8),  
FOREIGN KEY(User_Id) REFERENCES USERS(USER_ID), 
Transaction_Id NUMBER(6) 
)

Statement 3

CREATE TABLE TRANSACTIONS ( 
TRANSACTION_ID NUMBER(6) CONSTRAINT transaction_id_pk PRIMARY KEY, 
TRANSACTION_DATE DATE, 
Project_Id NUMBER(8), 
FOREIGN KEY(Project_Id) REFERENCES Projects(Project_Id) 
)

Statement 4

ALTER TABLE Projects 
ADD FOREIGN KEY (Transaction_Id) REFERENCES TRANSACTIONS(TRANSACTION_ID)

Statement 5

CREATE TABLE Subscriber 
( 
Subscriber_ID number(8) Primary key, 
USER_ID number (8), 
FOREIGN KEY(USER_ID) REFERENCES USERS(USER_ID),  
Subscription_Date DATE NOT NULL, 
Pwd number(8) Not Null, 
Dev_Type char(1) not null 
)

Statement 6

CREATE TABLE Developer ( 
Developer_Id number(8) CONSTRAINT developer_id_pk PRIMARY KEY, 
USER_ID number(8), 
FOREIGN KEY(USER_ID) REFERENCES USERS(USER_ID) 
)

Statement 7

CREATE TABLE Download ( 
Download_Id number(8) CONSTRAINT download_id_pk PRIMARY KEY, 
USER_ID number(8), 
FOREIGN KEY(USER_ID) REFERENCES USERS(USER_ID), 
Project_Id number(8), 
FOREIGN KEY(Project_Id) REFERENCES PROJECTS(Project_Id) 
)

Statement 8

CREATE TABLE Categories ( 
CAT_ID CHAR(1) CONSTRAINT cat_id_pk PRIMARY KEY, 
Project_Id NUMBER(8), 
FOREIGN KEY(Project_Id) REFERENCES Projects(Project_Id) 
)

Statement 9

CREATE TABLE UPDATETRAN ( 
UPDATE_DATE DATE CONSTRAINT update_date_pk PRIMARY KEY, 
TRANSACTION_ID NUMBER(6), 
FOREIGN KEY(TRANSACTION_ID) REFERENCES Transactions(TRANSACTION_ID), 
Project_Id NUMBER(8), 
FOREIGN KEY(Project_Id) REFERENCES projects(Project_Id) 
)

Statement 10

CREATE TABLE Patches 
( 
Patch_ID number(8) Primary KEY, 
Project_ID number (8), 
Transaction_ID number(6), 
Patch_Name Varchar(20) Not Null, 
Patch_Status Varchar(1) Not Null, 
Patch_Description Varchar(256)Not Null, 
Patch_Type Varchar(1) Not Null, 
Patch_DATE DATE,
FOREIGN KEY(Transaction_ID) REFERENCES Transactions(Transaction_ID), 
Foreign key (Project_ID) References projects(Project_ID) 
)

Statement 11

CREATE TABLE BugReport 
( 
Bug_ID number(8) , 
Project_ID number (8), 
User_ID number(8), 
Primary key(Bug_ID, Project_ID), 
FOREIGN KEY(Project_ID) REFERENCES Projects(Project_ID), 
Foreign key (User_ID) References Users(User_ID) 
)

Statement 12

CREATE TABLE Bug 
( 
Bug_ID number(8), 
Project_ID number(8), 
Bug_Description Varchar(256) Not Null, 
Bug_Date Date Not Null, 
Primary key(Bug_ID, Project_ID) 
)

Statement 13

INSERT INTO Users (USER_ID,SUBSCRIBER_TYPE, USER_NAME, firstName, lastName, USER_EMAIL) VALUES(00000011,'G','MOEEZ', 'Moeez', 'Mo', 'maq@utdallas.edu')

Statement 14

INSERT INTO Users VALUES(00000012,'G','SHY','Shai', 'Ayy', 'jed@utdallas.edu')

Statement 15

INSERT INTO Users VALUES(00000013,'S','HAMZA', 'Ham', 'Gab', 'huj@utdallas.edu')

Statement 16

INSERT INTO Users VALUES(00000014,'S','YAMAAN', 'Yam', 'Sha', 'def@utdallas.edu')

Statement 17

INSERT INTO Users VALUES(00000015,'S','KIRK', 'Kri', 'Shsa', 'uyu@utdallas.edu')

Statement 18

insert into subscriber(subscriber_id,user_id,subscription_date,PWD,dev_type) 
values (22222222,00000011,'01-JAN-21',21212121,'N')

Statement 19

insert into subscriber(subscriber_id,user_id,subscription_date,PWD,dev_type) 
values (22222223,00000012,'03-JAN-21',21212123,'D')

Statement 20

insert into subscriber(subscriber_id,user_id,subscription_date,PWD,dev_type) 
values (22222224,00000013,'05-JAN-21',21212124,'N')

Statement 21

insert into subscriber(subscriber_id,user_id,subscription_date,PWD,dev_type) 
values (22222225,00000013,'04-JAN-21',21212123,'D')

Statement 22

insert into subscriber(subscriber_id,user_id,subscription_date,PWD,dev_type) 
values (22222226,00000014,'04-JAN-21',21212124,'N')

Statement 23

INSERT INTO PROJECTS (PROJECT_ID,USER_ID,PROJECT_STATUS,PROJECT_DESCRIPTION, 
PROJECT_DEPENDENCY) 
VALUES(11111110,00000011,'D','This is Query project meant to strengthen the skills  
for those learning SQL',00000000)

Statement 24

INSERT INTO TRANSACTIONS(TRANSACTION_ID,TRANSACTION_DATE,project_id) 
VALUES (333334,'11-FEB-21',11111110)

Statement 25

INSERT INTO PROJECTS (transaction_id,project_id) 
VALUES(33333334,11111110)

Statement 26

INSERT INTO PROJECTS (PROJECT_ID,USER_ID,TRANSACTION_ID,PROJECT_STATUS,PROJECT_DESCRIPTION, 
PROJECT_DEPENDENCY) 
VALUES(11111110,00000011,333334,'D','This is Query project meant to strengthen the skills  
for those learning SQL',00000000)

Statement 27

INSERT INTO PROJECTS (PROJECT_ID,USER_ID,TRANSACTION_ID,PROJECT_STATUS,PROJECT_DESCRIPTION, 
PROJECT_DEPENDENCY) 
VALUES(21111110,00000011,333334,'D','This is Query project meant to strengthen the skills  
for those learning SQL',00000000)

Statement 28

INSERT INTO TRANSACTIONS(TRANSACTION_ID,TRANSACTION_DATE,project_id) 
VALUES (333334,'12-FEB-21',21111110)

Statement 29

INSERT INTO TRANSACTIONS(TRANSACTION_ID,TRANSACTION_DATE,project_id) 
VALUES (333335,'12-FEB-21',21111110)

Statement 30

INSERT INTO TRANSACTIONS(TRANSACTION_ID,TRANSACTION_DATE,project_id) 
VALUES (333335,'12-FEB-21',21111110)

Statement 31

INSERT INTO PROJECTS (PROJECT_ID,USER_ID,TRANSACTION_ID,PROJECT_STATUS,PROJECT_DESCRIPTION, 
PROJECT_DEPENDENCY) 
VALUES(21111110,00000012,333335,'D','This is Query project meant to strengthen the skills  
for those learning JAVA',11111110)

Statement 32

INSERT INTO PROJECTS (PROJECT_ID,USER_ID,TRANSACTION_ID,PROJECT_STATUS,PROJECT_DESCRIPTION, 
PROJECT_DEPENDENCY) 
VALUES(11111110,00000012,333335,'D','This is Query project meant to strengthen the skills  
for those learning JAVA',11111110)

Statement 33

INSERT INTO PROJECTS (PROJECT_ID,USER_ID,TRANSACTION_ID,PROJECT_STATUS,PROJECT_DESCRIPTION, 
PROJECT_DEPENDENCY) 
VALUES(21111110,00000012,333334,'D','This is Query project meant to strengthen the skills  
for those learning JAVA',11111110)

Statement 34

INSERT INTO PROJECTS (PROJECT_ID,USER_ID,TRANSACTION_ID,PROJECT_STATUS,PROJECT_DESCRIPTION, 
PROJECT_DEPENDENCY) 
VALUES(31111110,00000013,333336,'D','This is Query project meant to strengthen the skills  
for those learning JAVA',21111110)

Statement 35

INSERT INTO PROJECTS (PROJECT_ID,USER_ID,TRANSACTION_ID,PROJECT_STATUS,PROJECT_DESCRIPTION, 
PROJECT_DEPENDENCY) 
VALUES(31111110,00000013,333335,'D','This is Query project meant to strengthen the skills  
for those learning JAVA',21111110)

Statement 36

INSERT INTO TRANSACTIONS(TRANSACTION_ID,TRANSACTION_DATE,project_id) 
VALUES (333336,'13-FEB-21',31111110)

Statement 37

INSERT INTO TRANSACTIONS(TRANSACTION_ID,TRANSACTION_DATE,project_id) 
VALUES (333336,'13-FEB-21',31111110)

Statement 38

INSERT INTO PROJECTS (PROJECT_ID,USER_ID,TRANSACTION_ID,PROJECT_STATUS,PROJECT_DESCRIPTION, 
PROJECT_DEPENDENCY) 
VALUES(41111110,00000014,333336,'D','This is Query project meant to strengthen the skills  
for those learning C++',31111110)

Statement 39

INSERT INTO TRANSACTIONS(TRANSACTION_ID,TRANSACTION_DATE,project_id) 
VALUES (333337,'14-FEB-21',41111110)

Statement 40

INSERT INTO PROJECTS (PROJECT_ID,USER_ID,TRANSACTION_ID,PROJECT_STATUS,PROJECT_DESCRIPTION, 
PROJECT_DEPENDENCY) 
VALUES(51111110,00000015,333337,'D','This is Query project meant to strengthen the skills  
for those learning R',41111110)

Statement 41

INSERT INTO TRANSACTIONS(TRANSACTION_ID,TRANSACTION_DATE,project_id) 
VALUES (333338,'15-FEB-21',51111110)

Statement 42

INSERT INTO UPDATETRAN (UPDATE_DATE,TRANSACTION_ID,PROJECT_ID) 
VALUES ('11-MAY-20',333333,11111110)

Statement 43

INSERT INTO UPDATETRAN (UPDATE_DATE,TRANSACTION_ID,PROJECT_ID) 
VALUES ('11-MAY-20',333333,11111110)

Statement 44

INSERT INTO UPDATETRAN (UPDATE_DATE,TRANSACTION_ID,PROJECT_ID) 
VALUES ('11-MAY-20',333334,11111110)

Statement 45

INSERT INTO UPDATETRAN (UPDATE_DATE,TRANSACTION_ID,PROJECT_ID) 
VALUES ('13-JUN-20',333335,21111110)

Statement 46

INSERT INTO UPDATETRAN (UPDATE_DATE,TRANSACTION_ID,PROJECT_ID) 
VALUES ('13-JUN-20',333335,21111110)

Statement 47

INSERT INTO UPDATETRAN (UPDATE_DATE,TRANSACTION_ID,PROJECT_ID) 
VALUES ('17-MAR-20',333336,31111110)

Statement 48

INSERT INTO UPDATETRAN (UPDATE_DATE,TRANSACTION_ID,PROJECT_ID) 
VALUES ('21-MAR-20',333337,41111110)

Statement 49

INSERT INTO UPDATETRAN (UPDATE_DATE,TRANSACTION_ID,PROJECT_ID) 
VALUES ('12-MAY-20',333338,51111110)

Statement 50

INSERT INTO PATCHES (PATCH_ID,PROJECT_ID,TRANSACTION_ID,PATCH_NAME, 
PATCH_DESCRIPTION,PATCH_TYPE, Patch_DATE, PATCH_STATUS) 
VALUES(12121212,11111110,333334,'data security','ensures that all data is not being leaked  
to unauthorized parties',1,'12-MAY-00','U')

Statement 51

INSERT INTO PATCHES (PATCH_ID,PROJECT_ID,TRANSACTION_ID,PATCH_NAME, 
PATCH_DESCRIPTION,PATCH_TYPE, Patch_DATE, PATCH_STATUS) 
VALUES(12121213,21111110,333335,'BINARY','Radare allows for assembly code to be written inline, compiled and inserted into the binary without any hassle',3,'12-MAY-01','A')

Statement 52

INSERT INTO PATCHES (PATCH_ID,PROJECT_ID,TRANSACTION_ID,PATCH_NAME, 
PATCH_DESCRIPTION,PATCH_TYPE, Patch_DATE, PATCH_STATUS) 
VALUES(12121214,31111110,333336,'HOTFIX','Software update designed to fix a bug or security hole in a program.',2,'12-MAY-03','A')

Statement 53

INSERT INTO PATCHES (PATCH_ID,PROJECT_ID,TRANSACTION_ID,PATCH_NAME, 
PATCH_DESCRIPTION,PATCH_TYPE, Patch_DATE, PATCH_STATUS) 
VALUES(12121214,31111110,333336,'HOTFIX','Software update designed to fix a bug or security hole in a program.',2,'12-MAY-05','A')

Statement 54

INSERT INTO PATCHES (PATCH_ID,PROJECT_ID,TRANSACTION_ID,PATCH_NAME, 
PATCH_DESCRIPTION,PATCH_TYPE, Patch_DATE, PATCH_STATUS) 
VALUES(12121215,41111110,333337,'SERVICE','rolls together all the patches that have come along over a given period of time','12-MAY-07',1,'A')

Statement 55

INSERT INTO PATCHES (PATCH_ID,PROJECT_ID,TRANSACTION_ID,PATCH_NAME, 
PATCH_DESCRIPTION,PATCH_TYPE, Patch_DATE, PATCH_STATUS) 
VALUES(12121215,41111110,333337,'SERVICE','rolls together all 
the patches that have come along over a given period of time',1,'12-MAY-09','A')

Statement 56

INSERT INTO PATCHES (PATCH_ID,PROJECT_ID,TRANSACTION_ID,PATCH_NAME, 
PATCH_DESCRIPTION,PATCH_TYPE, Patch_DATE, PATCH_STATUS) 
VALUES(12121215,41111110,333337,'SERVICE','rolls together all 
the patches that have come along over a given period of time.', 1, '12-MAY-10','A')

Statement 57

INSERT INTO PATCHES (PATCH_ID,PROJECT_ID,TRANSACTION_ID,PATCH_NAME, 
PATCH_DESCRIPTION,PATCH_TYPE, Patch_DATE, PATCH_STATUS) 
VALUES(12121216,51111110,333338,'SERVICE','This is a bulky patch that serve as 
update to the software to fix any previous bugs.',3,'12-MAY-11','A')

Statement 58

INSERT INTO BUGREPORT(BUG_ID,PROJECT_ID,USER_ID) 
VALUES (45454545,11111110,00000011)

Statement 59

INSERT INTO BUGREPORT(BUG_ID,PROJECT_ID,USER_ID) 
VALUES (55454545,21111110,00000012)  
 
 
INSERT INTO BUGREPORT(BUG_ID,PROJECT_ID,USER_ID) 
VALUES (65454545,31111110,00000013)  
 
INSERT INTO BUGREPORT(BUG_ID,PROJECT_ID,USER_ID) 
VALUES (75454545,41111110,00000014)  
 
 
INSERT INTO BUGREPORT(BUG_ID,PROJECT_ID,USER_ID) 
VALUES (85454545,51111110,00000015)

Statement 60

INSERT INTO BUGREPORT(BUG_ID,PROJECT_ID,USER_ID) 
VALUES (55454545,21111110,00000012)

Statement 61

INSERT INTO BUGREPORT(BUG_ID,PROJECT_ID,USER_ID) 
VALUES (65454545,31111110,00000013)

Statement 62

INSERT INTO BUGREPORT(BUG_ID,PROJECT_ID,USER_ID) 
VALUES (75454545,41111110,00000014)

Statement 63

INSERT INTO BUGREPORT(BUG_ID,PROJECT_ID,USER_ID) 
VALUES (85454545,51111110,00000015)

Statement 64

INSERT INTO BUG(BUGREPORT_ID,BUG_ID,PROJECT_ID,USER_ID) 
VALUES (23232323,45454545,11111110,00000011)

Statement 65

INSERT INTO BUG(BUGREPORT_ID,BUG_ID,PROJECT_ID,USER_ID) 
VALUES (23232323,45454545,11111110,00000011)

Statement 66

INSERT INTO BUG(BUGREPORT_ID,BUG_ID,PROJECT_ID) 
VALUES (23232323,45454545,11111110)

Statement 67

INSERT INTO BUG(BUG_ID,PROJECT_ID,USER_ID) 
VALUES (85454545,51111110,00000015)

Statement 68

INSERT INTO DOWNLOAD(DOWNLOAD_ID,USER_ID,PROJECT_ID) 
VALUES (56565656,00000011,11000000)

Statement 69

INSERT INTO DOWNLOAD(DOWNLOAD_ID,USER_ID,PROJECT_ID) 
VALUES (56565657,00000011,11000000)

Statement 70

INSERT INTO DOWNLOAD(DOWNLOAD_ID, PROJECT_ID) 
VALUES (56565658,11000000)

Statement 71

INSERT INTO DOWNLOAD(DOWNLOAD_ID,PROJECT_ID) 
VALUES (56565659,11000000)

Statement 72

INSERT INTO CATEGORIES(CAT_ID,PROJECT_ID) 
VALUES ('A',11111110)

Statement 73

INSERT INTO CATEGORIES(CAT_ID,PROJECT_ID) 
VALUES ('B',21111110)

Statement 74

INSERT INTO CATEGORIES(CAT_ID,PROJECT_ID) 
VALUES ('C',31111110)

Statement 75

INSERT INTO CATEGORIES(CAT_ID,PROJECT_ID) 
VALUES ('D',41111110)

Statement 76

INSERT INTO CATEGORIES(CAT_ID,PROJECT_ID) 
VALUES ('A',51111110)

Statement 77

INSERT INTO CATEGORIES(CAT_ID,PROJECT_ID) 
VALUES ('B',51111110)

Statement 78

INSERT INTO CATEGORIES(CAT_ID,PROJECT_ID) 
VALUES ('P',51111110)

Statement 79

INSERT INTO DEVELOPER(DEVELOPER_ID,USER_ID) 
VALUES(99999991,00000011)

Statement 80

INSERT INTO DEVELOPER(DEVELOPER_ID,USER_ID) 
VALUES(99999992,00000012)

Statement 81

INSERT INTO DEVELOPER(DEVELOPER_ID,USER_ID) 
VALUES(99999993,00000013)

Statement 82

INSERT INTO DEVELOPER(DEVELOPER_ID,USER_ID) 
VALUES(99999994,00000014)

Statement 83

INSERT INTO DEVELOPER(DEVELOPER_ID,USER_ID) 
VALUES(99999995,00000015)

hkjh

Additional Information