InitialScripts.sql

CREATE TABLE "LRDS_USER" (
   ID NUMBER(10) PRIMARY KEY,
   CREATION_DATE TIMESTAMP(6),
   "PASSWORD" VARCHAR2(255),
   "USERNAME" VARCHAR2(255),
   SESSION_START TIMESTAMP(6),
   CURRENT_SESSION_TOKEN VARCHAR2(255)
);

CREATE SEQUENCE SEQ_LRDSUSER;

----------------------
CREATE TABLE "STUDENT" (
   "ID" NUMBER(10) PRIMARY KEY,
   "USER_ID" NUMBER(10),
   "SURNAME" VARCHAR2(255),
   "FIRST_NAME" VARCHAR2(255),
   MAIL VARCHAR2(255)   
);
CREATE SEQUENCE SEQ_STUDENT;

-----------------------
CREATE TABLE SUBJECT(
    "ID" NUMBER(10) PRIMARY KEY,
    "DEGREE" NUMBER(2),
    "NAME" VARCHAR2(255),
    "SCHEDULE" VARCHAR2(255),
    NEXT_FINAL_DATE TIMESTAMP(6)
);
CREATE SEQUENCE SEQ_SUBJECT;
------------------------
CREATE TABLE STUDENT_SUBJECT(    
    "ID" NUMBER(10) PRIMARY KEY,
    "STUDENT_ID" NUMBER(10),
    "SUBJECT_ID" NUMBER(10),
    IS_REGISTERED  NUMBER(1,0) DEFAULT 0
);
ALTER TABLE STUDENT_SUBJECT ADD CONSTRAINT FK_STUDENT_SUBJECT FOREIGN KEY ("STUDENT_ID") REFERENCES STUDENT;
ALTER TABLE STUDENT_SUBJECT ADD CONSTRAINT FK_SUBJECT_STUDENT FOREIGN KEY ("SUBJECT_ID") REFERENCES SUBJECT;
CREATE SEQUENCE SEQ_STUDENT_SUBJECT;


INSERT ALL 
    INTO LRDS_USER (ID, CREATION_DATE, "PASSWORD", "USERNAME", SESSION_START, CURRENT_SESSION_TOKEN) VALUES (
        SEQ_LRDSUSER.NEXTVAL, SYSDATE, '123456', 'MyUser', NULL, NULL)
    INTO STUDENT (ID, "USER_ID", "SURNAME", "FIRST_NAME", MAIL) VALUES (
        SEQ_STUDENT.NEXTVAL, (SELECT "ID" FROM LRDS_USER WHERE ROWNUM = 1), 'SURNAME', 'FIRST_NAME', 'MyMAIL')
    INTO SUBJECT (ID, DEGREE, "NAME", "SCHEDULE", NEXT_FINAL_DATE) VALUES (
        SEQ_SUBJECT.NEXTVAL, 3, 'NAME', '19-21HS',SYSDATE + 10)        
    SELECT * FROM dual;
    COMMIT;