SqlPhil

--1 ///////////////////// drop tables
DROP TABLE Inputs;
DROP TABLE Questions;
DROP TABLE RegistrationTasks;
DROP TABLE Users;

--2 //////////////////// Create all tables
CREATE TABLE Users (
    id serial PRIMARY KEY,
    firstName text,
    lastName text,
    email text,
    phoneNumber text
);

CREATE TABLE RegistrationTasks (
    id serial PRIMARY KEY,
    captcha text,
    registration_id text,
    fk_id_Users INTEGER REFERENCES Users(id) NOT NULL  --klucz obcy tabeli users
);

CREATE TABLE Questions(
    id serial PRIMARY KEY,
    fk_id_RegistrationTasks INTEGER REFERENCES RegistrationTasks(id),
    questionText text,
    rightAnswer text
);

CREATE TABLE Inputs(
    id serial PRIMARY KEY,
    fk_id_RegistrationTasks INTEGER REFERENCES RegistrationTasks(id),
    inputText text,
    fk_id_Questions INTEGER REFERENCES Questions(id)
);


--3 ///////////////////////Users data
INSERT INTO Users (firstName,lastName,email,phoneNumber) VALUES('Adam', 'Kowalski', 'adam.kowalski@wp.pl', 666454333);
INSERT INTO Users (firstName,lastName,email,phoneNumber) VALUES('Roman', 'Pawłowski', 'roman.pawlowski@gmail.com', 555233413);
INSERT INTO Users (firstName,lastName,email,phoneNumber) VALUES('Katarzyna', 'Górska', 'katarzyna231@interia.pl', 656332231);
INSERT INTO Users (firstName,lastName,email,phoneNumber) VALUES('Anna', 'Małaszyńska', 'ania2323@wp.pl', 600231465);
INSERT INTO Users (firstName,lastName,email,phoneNumber) VALUES('Monika', 'Kowalewska', 'monia231@wp.pl', 34234543);

--select id,firstName,lastName,email,phoneNumber from Users;

--4 ///////////////////////RegistrationTasks data

INSERT INTO RegistrationTasks (captcha,registration_id,fk_id_Users) VALUES('AMBASD231A',1321,1);
INSERT INTO RegistrationTasks (captcha,registration_id,fk_id_Users) VALUES('KSDJFNkjsd',4,2);
INSERT INTO RegistrationTasks (captcha,registration_id,fk_id_Users) VALUES('234fgasdjh',1124,3);
INSERT INTO RegistrationTasks (captcha,registration_id,fk_id_Users) VALUES('LSDIH341as',11,4);
INSERT INTO RegistrationTasks (captcha,registration_id,fk_id_Users) VALUES('AMBdasd322',130,5);

--select id,captcha,registration_id,fk_id_Users from RegistrationTasks;

--5 ///////////////////////Questions data

INSERT INTO Questions (questionText,rightAnswer,fk_id_RegistrationTasks) VALUES('Pytanie1','PrawidlowaOdp',1);
INSERT INTO Questions (questionText,rightAnswer,fk_id_RegistrationTasks) VALUES('Pytanie2','PrawidlowaOdp',1);

INSERT INTO Questions (questionText,rightAnswer,fk_id_RegistrationTasks) VALUES('Pytanie3','PrawidlowaOdp',2);
INSERT INTO Questions (questionText,rightAnswer,fk_id_RegistrationTasks) VALUES('Pytanie1','PrawidlowaOdp',2);

INSERT INTO Questions (questionText,rightAnswer,fk_id_RegistrationTasks) VALUES('Pytanie3','PrawidlowaOdp',3);
INSERT INTO Questions (questionText,rightAnswer,fk_id_RegistrationTasks) VALUES('Pytanie4','PrawidlowaOdp',3);

INSERT INTO Questions (questionText,rightAnswer,fk_id_RegistrationTasks) VALUES('Pytanie5','PrawidlowaOdp',4);
INSERT INTO Questions (questionText,rightAnswer,fk_id_RegistrationTasks) VALUES('Pytanie6','PrawidlowaOdp',4);

INSERT INTO Questions (questionText,rightAnswer,fk_id_RegistrationTasks) VALUES('Pytanie7','PrawidlowaOdp',5);
INSERT INTO Questions (questionText,rightAnswer,fk_id_RegistrationTasks) VALUES('Pytanie8','PrawidlowaOdp',5);

--select id,questionText,rightAnswer,fk_id_RegistrationTasks from Questions;

--6 ///////////////////////Inputs data

INSERT INTO Inputs (inputText,fk_id_RegistrationTasks,fk_id_Questions) VALUES('Odpowiedz1',1,3);
INSERT INTO Inputs (inputText,fk_id_RegistrationTasks,fk_id_Questions) VALUES('Odpowiedz2',1,4);

INSERT INTO Inputs (inputText,fk_id_RegistrationTasks,fk_id_Questions) VALUES('Odpowiedz3',2,5);
INSERT INTO Inputs (inputText,fk_id_RegistrationTasks,fk_id_Questions) VALUES('Odpowiedz4',2,6);

INSERT INTO Inputs (inputText,fk_id_RegistrationTasks,fk_id_Questions) VALUES('Odpowiedz5',3,7);
INSERT INTO Inputs (inputText,fk_id_RegistrationTasks,fk_id_Questions) VALUES('Odpowiedz6',3,8);

INSERT INTO Inputs (inputText,fk_id_RegistrationTasks,fk_id_Questions) VALUES('Odpowiedz7',4,9);
INSERT INTO Inputs (inputText,fk_id_RegistrationTasks,fk_id_Questions) VALUES('Odpowiedz8',4,10);

INSERT INTO Inputs (inputText,fk_id_RegistrationTasks,fk_id_Questions) VALUES('Odpowiedz9',5,1);
INSERT INTO Inputs (inputText,fk_id_RegistrationTasks,fk_id_Questions) VALUES('Odpowiedz10',5,2);

--select id,inputText,fk_id_RegistrationTasks,fk_id_Questions from Inputs;