SQLize Online / PHPize Online  /  SQLtest Online

CREATE SCHEMA IF NOT EXISTS recruit; CREATE TABLE IF NOT EXISTS recruit.companies ( COMP_ID INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, COMP_NAME VARCHAR(128) NOT NULL , COMP_EMAIL_NO VARCHAR(128) NOT NULL UNIQUE , COMP_INFORM VARCHAR(1024) ); CREATE TABLE IF NOT EXISTS recruit.managers ( MANAGER_ID INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, MANAGER_NAME VARCHAR(128) NOT NULL , MANAGER_EMAIL_NO VARCHAR(128) NOT NULL , MANAGER_COMP_ID INTEGER, FOREIGN KEY (MANAGER_COMP_ID) REFERENCES recruit.companies (COMP_ID) ); CREATE TABLE IF NOT EXISTS recruit.users ( APPL_ID INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, APPL_NAME VARCHAR(128) NOT NULL , APPL_EMAIL_NO VARCHAR(128) NOT NULL UNIQUE , APPL_PERS_INFORM VARCHAR(1024) ); CREATE TABLE IF NOT EXISTS recruit.vacancies ( VACANCY_ID INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, VACANCY_COMP_ID INTEGER, VACANCY_SALARY DECIMAL(18,4), VACANCY_TEXT VARCHAR(1024), FOREIGN KEY (VACANCY_COMP_ID) REFERENCES recruit.companies (COMP_ID) ); CREATE TABLE IF NOT EXISTS recruit.reviews ( COMMENT_ID INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, COMMENT_VACANCY_ID INTEGER, COMMENT_AUTHOR INTEGER, COMMENT_TEXT VARCHAR(1024) NOT NULL , FOREIGN KEY (COMMENT_VACANCY_ID) REFERENCES recruit.vacancies (vacancy_id) ); CREATE TABLE IF NOT EXISTS recruit.responses ( RESP_ID INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, RESP_MANAGER_ID INTEGER, RESP_APPL_ID INTEGER, RESP_VACANCY_ID INTEGER, RESP_STATUS VARCHAR(1024) NOT NULL , CHECK ( RESP_STATUS = 'denied' OR RESP_STATUS = 'during' OR RESP_STATUS = 'approved' ), FOREIGN KEY (RESP_MANAGER_ID) REFERENCES recruit.managers (manager_id), FOREIGN KEY (RESP_APPL_ID) REFERENCES recruit.users (appl_id), FOREIGN KEY (RESP_VACANCY_ID) REFERENCES recruit.vacancies (vacancy_id) ); CREATE TABLE IF NOT EXISTS recruit.summaries ( SUMM_ID INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, SUMM_APPL_ID INTEGER, SUMM_TEXT VARCHAR(1024) NOT NULL , FOREIGN KEY (SUMM_APPL_ID) REFERENCES recruit.users (appl_id) ); CREATE TABLE IF NOT EXISTS recruit.experiences ( EXPER_ID INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, EXPER_SUMM_ID INTEGER, EXPER_COMP_ID INTEGER, EXPER_SPECIALITY VARCHAR(128), EXPER_TEXT VARCHAR(1024), FOREIGN KEY (EXPER_SUMM_ID) REFERENCES recruit.summaries (summ_id), FOREIGN KEY (EXPER_COMP_ID) REFERENCES recruit.companies (comp_id) ); DROP FUNCTION IF EXISTS getUserIdByEmail; CREATE FUNCTION getUserIdByEmail(in user_email text, out user_id int) AS $$ SELECT appl_id FROM recruit.users WHERE appl_email_no = user_email $$ LANGUAGE SQL;