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) ); INSERT INTO recruit.companies (comp_name, comp_email_no, comp_inform) VALUES ('apple', 'apple@gmail.com', 'Собираем macbook'), ('google', 'google@gmail.com', 'Создаём google'), ('amazon', 'amazon@gmail.com', 'Отправляем посылки'), ('fpmi', 'fpmi@mail.com', 'Решаем задачки'), ('yandex', 'yandex@mail.com', 'Создаём сервисы'), ('head hunter', 'hh@gmail.com', 'Набираем стажёров'), ('asus', 'asus@gmail.com', 'Конкурируем с apple'), ('netflix', 'netflix@gmail.com', 'Снимаем фильмы'), ('nix', 'nix@gmail.com', 'Создаём отечественое ПО'), ('microsoft', 'microsoft@gmail.com', 'Поддерживаем windows'); INSERT INTO recruit.managers (manager_name, manager_email_no, manager_comp_id) VALUES ('Alena', 'apple@gmail.com', 1), ('Max', 'apple@gmail.com', 1), ('Nik', 'google@gmail.com', 2), ('Azu', 'amazon@gmail.com', 3), ('Artem', 'fpmi@mail.com', 4), ('Yan', 'yandex@mail.com', 5), ('Hun', 'hh@gmail.com', 6), ('Suzan', 'asus@gmail.com', 7), ('Alex', 'netflix@gmail.com', 8), ('Mike', 'microsoft@gmail.com', 10); INSERT INTO recruit.users (appl_name, appl_email_no, appl_pers_inform) VALUES ('Oliver Stoun', 'oliver@gmail.com', ''), ('Tom Hanks', 'hancks@gmail.com', ''), ('Robin Williams', 'will@gmail.com', ''), ('Mel Gibson', 'gibson@gmail.com', ''), ('Oliver Stoun Senior', 'oliver4657@gmail.com', ''), ('Harrison Ford', 'harrison@gmail.com', ''), ('Will Smith', 'will2@gmail.com', ''), ('Keanu Reeves', 'reeves@gmail.com', ''), ('Audrey Hepburn', 'hepburn@gmail.com', ''), ('Bot Botov', 'spam@gmail.com', ''); INSERT INTO recruit.vacancies (vacancy_comp_id, vacancy_salary, vacancy_text) VALUES (1, 310000, 'looking for computer technicians'), (1, NULL, 'looking for c++ backend interns'), (2, 220000, 'looking for c++ backend developers'), (3, 1000000, ''), (4, 35000, 'Ищем дипломированного, признанного учёного в области ДМ'), (5, 290000, 'we want to make devops'), (5, NULL, 'ищем переводчиков на английский'), (6, 110000, 'looking for a head hunter'), (7, 299999, 'Apple analyst. We are cooler than Apple!'), (8, 900000, 'looking for famous actors from the 90s'); INSERT INTO recruit.reviews (comment_vacancy_id, comment_author, comment_text) VALUES (1, 1, 'not bad'), (2, 2, 'I passed even though I don''t know how to code'), (3, 1, 'looks risky'), (4, 4, 'this is for my boss'), (4, 2, 'how to get there'), (7, 9, 'pay little'), (4, 4, 'no way'), (9, 1, 'do not recommend'), (9, 5, 'why?'), (9, 1, 'Because I work at Apple and I know how everything works'); -- RESP_STATUS = 'denied' OR 'during' OR 'approved' INSERT INTO recruit.responses (resp_manager_id, resp_appl_id, resp_vacancy_id, resp_status) VALUES (1, 1, 1, 'denied'), (4, 2, 4, 'during'), (3, 3, 3, 'approved'), (4, 2, 6, 'approved'), (5, 2, 3, 'approved'), (6, 6, 7, 'denied'), (8, 9, 6, 'denied'), (10, 10, 10, 'denied'), (3, 6, 8, 'during'), (1, 7, 2, 'during'); INSERT INTO recruit.summaries (summ_appl_id, summ_text) VALUES (1, 'my GPA is 7.2'), (2, 'My skills:'), (3, 'C++, Python'), (4, 'Git, Gitlab, Git Action'), (1, 'Postgres sql'), (2, 'have internship experience:'), (3, 'Yandex. Backend developer'), (5, 'Created metrics'), (8, 'I study at FPMI'), (7, 'maybe I''ll interest you'); INSERT INTO recruit.experiences (exper_summ_id, exper_comp_id, exper_speciality, exper_text) VALUES (1, 1, 'programmer', ''), (3, 4, 'backend', ''), (2, 6, 'actor', ''), (10, 8, 'frontend', ''), (1, 1, 'gamer', ''), (5, 3, 'backend', ''), (7, 2, 'manager', ''), (3, 4, 'leader', ''), (6, 2, 'front', ''), (9, 10, 'tester', ''); -- ON CONFLICT (exper_id) DO UPDATE -- SET exper_summ_id = excluded.exper_summ_id, -- exper_comp_id = excluded.exper_comp_id, -- exper_speciality = excluded.exper_speciality, -- exper_text = excluded.exper_text; 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; SELECT * FROM getUserIdByEmail()