SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
DROP SCHEMA IF EXISTS project CASCADE; CREATE SCHEMA project; DROP TABLE IF EXISTS project.Patient; CREATE TABLE project.Patient ( PATIENT_ID INTEGER PRIMARY KEY generated always as identity, FIRST_NAME VARCHAR(30) NOT NULL, MIDDLE_NAME VARCHAR(30), LAST_NAME VARCHAR(50) NOT NULL, AGE INTEGER CHECK(AGE >= 0), BIRTH_DATE DATE, GENDER VARCHAR(1) CHECK( gender = 'f' OR gender = 'm' ), BLOOD_TYPE INTEGER CHECK( blood_type > 0 AND blood_type < 5 ), RH_FACTOR VARCHAR(1) CHECK( rh_factor = '+' OR rh_factor = '-' ), HEIGHT REAL CHECK(height > 0), WEIGHT REAL CHECK(weight > 0), INSURANCE_POLICY INTEGER ); DROP TABLE IF EXISTS project.Physician; CREATE TABLE project.Physician ( PHYSICIAN_ID INTEGER PRIMARY KEY generated always as identity, FIRST_NAME VARCHAR(30) NOT NULL, MIDDLE_NAME VARCHAR(30), LAST_NAME VARCHAR(50) NOT NULL ); DROP TABLE IF EXISTS project.Appointment; CREATE TABLE project.Appointment ( APPOINTMENT_ID INTEGER PRIMARY KEY generated always as identity, PATIENT_ID INTEGER REFERENCES project.Patient(patient_id), PHYSICIAN_ID INTEGER REFERENCES project.Physician(physician_id), APPOINTMENT_DATE TIMESTAMP ); DROP TABLE IF EXISTS project.Prescription; CREATE TABLE project.Prescription ( PRESCRIPTION_ID INTEGER PRIMARY KEY generated always as identity, APPOINTMENT_ID INTEGER REFERENCES project.Appointment(appointment_id), RECOMMENDATIONS TEXT ); DROP TABLE IF EXISTS project.Specialization; CREATE TABLE project.Specialization ( SPECIALIZATION_ID INTEGER PRIMARY KEY generated always as identity, NAME VARCHAR(30) ); DROP TABLE IF EXISTS project.Physician_specialization; CREATE TABLE project.Physician_specialization ( PHYSICIAN_SPECIALIZATION_ID INTEGER PRIMARY KEY generated always as identity, PHYSICIAN_ID INTEGER REFERENCES project.physician(physician_id), SPECIALIZATION_ID INTEGER REFERENCES project.specialization(specialization_id) ); DROP TABLE IF EXISTS project.Analysis; CREATE TABLE project.Analysis ( ANALYSIS_ID INTEGER PRIMARY KEY generated always as identity, NAME VARCHAR(50) NOT NULL, DESCRIPTION VARCHAR(250) ); DROP TABLE IF EXISTS project.Analysis_result; CREATE TABLE project.Analysis_result ( ANALYSIS_RESULT_ID INTEGER PRIMARY KEY generated always as identity, PATIENT_ID INTEGER REFERENCES project.patient(patient_id), ANALYSIS_ID INTEGER REFERENCES project.analysis(analysis_id), ANALYSIS_DATE TIMESTAMP, ANALYSIS_RESULT TEXT ); DROP TABLE IF EXISTS project.Prescription_analysis; CREATE TABLE project.Prescription_analysis ( PRESCRIPTION_ANALYSIS_ID INTEGER PRIMARY KEY generated always as identity, PRESCRIPTION_ID INTEGER REFERENCES project.prescription(prescription_id), ANALYSIS_ID INTEGER REFERENCES project.analysis(analysis_id) ); INSERT INTO project.patient(first_name, last_name) VALUES ('Kseniia', 'Altynbaeva'); INSERT INTO project.patient(first_name, last_name) VALUES ('Anna', 'Gubareva'); INSERT INTO project.patient(first_name, last_name) VALUES ('Sofya', 'Dergunova'); INSERT INTO project.patient(first_name, last_name) VALUES ('Anna', 'Pchelintseva'); INSERT INTO project.patient(first_name, last_name) VALUES ('Mark', 'Bikbulatov'); INSERT INTO project.patient(first_name, last_name) VALUES ('Mariia', 'Popova'); INSERT INTO project.patient(first_name, last_name) VALUES ('Alena', 'Poyasova'); INSERT INTO project.patient(first_name, last_name) VALUES ('Ivan', 'Ivanov'); INSERT INTO project.patient(first_name, last_name) VALUES ('Yulia', 'Altynbaeva'); INSERT INTO project.patient(first_name, last_name) VALUES ('Darya', 'Sverlova'); INSERT INTO project.physician(first_name, last_name) VALUES ('Irina', 'Afanasyeva'); INSERT INTO project.physician(first_name, last_name) VALUES ('Alla', 'Iguminova'); INSERT INTO project.physician(first_name, last_name) VALUES ('Petr', 'Ivanov'); INSERT INTO project.physician(first_name, last_name) VALUES ('Ulyana', 'Povysheva'); INSERT INTO project.physician(first_name, last_name) VALUES ('Anastasiya', 'Petrova'); INSERT INTO project.physician(first_name, last_name) VALUES ('Ekaterina', 'Romanova'); INSERT INTO project.physician(first_name, last_name) VALUES ('Tatyana', 'Malevich'); INSERT INTO project.physician(first_name, last_name) VALUES ('Aleksandra', 'Novikova'); INSERT INTO project.physician(first_name, last_name) VALUES ('Sofya', 'Kovalevskaya'); INSERT INTO project.physician(first_name, last_name) VALUES ('Nikolay', 'Nikiphorov'); INSERT INTO project.appointment( patient_id, physician_id, appointment_date ) VALUES (1, 2, '2020-10-19 10:30:00'); INSERT INTO project.appointment( patient_id, physician_id, appointment_date ) VALUES (2, 4, '2020-10-12 11:00:00'); INSERT INTO project.appointment( patient_id, physician_id, appointment_date ) VALUES (3, 3, '2020-11-01 12:00:00'); INSERT INTO project.appointment( patient_id, physician_id, appointment_date ) VALUES (4, 5, '2020-11-07 13:20:00'); INSERT INTO project.appointment( patient_id, physician_id, appointment_date ) VALUES (5, 6, '2020-11-20 15:10:00'); INSERT INTO project.appointment( patient_id, physician_id, appointment_date ) VALUES (6, 9, '2020-12-04 16:00:00'); INSERT INTO project.appointment( patient_id, physician_id, appointment_date ) VALUES (7, 8, '2020-12-21 9:30:00'); INSERT INTO project.appointment( patient_id, physician_id, appointment_date ) VALUES (8, 7, '2020-09-05 10:00:00'); INSERT INTO project.appointment( patient_id, physician_id, appointment_date ) VALUES (9, 1, '2020-10-16 14:00:00'); INSERT INTO project.appointment( patient_id, physician_id, appointment_date ) VALUES (1, 3, '2020-10-26 12:30:00'); INSERT INTO project.specialization(name) VALUES ('dentist'); INSERT INTO project.specialization(name) VALUES ('surgeon'); INSERT INTO project.specialization(name) VALUES ('neurologist'); INSERT INTO project.specialization(name) VALUES ('dermatologist'); INSERT INTO project.specialization(name) VALUES ('otolaryngologist'); INSERT INTO project.specialization(name) VALUES ('cardiologist'); INSERT INTO project.specialization(name) VALUES ('ophthalmologist'); INSERT INTO project.specialization(name) VALUES ('endocrinologist'); INSERT INTO project.specialization(name) VALUES ('psychiatrist'); INSERT INTO project.specialization(name) VALUES ('immunologist'); INSERT INTO project.physician_specialization(physician_id, specialization_id) VALUES (1, 3); INSERT INTO project.physician_specialization(physician_id, specialization_id) VALUES (2, 1); INSERT INTO project.physician_specialization(physician_id, specialization_id) VALUES (3, 7); INSERT INTO project.physician_specialization(physician_id, specialization_id) VALUES (4, 8); INSERT INTO project.physician_specialization(physician_id, specialization_id) VALUES (5, 4); INSERT INTO project.physician_specialization(physician_id, specialization_id) VALUES (6, 5); INSERT INTO project.physician_specialization(physician_id, specialization_id) VALUES (7, 3); INSERT INTO project.physician_specialization(physician_id, specialization_id) VALUES (8, 2); INSERT INTO project.physician_specialization(physician_id, specialization_id) VALUES (9, 9); INSERT INTO project.physician_specialization(physician_id, specialization_id) VALUES (10, 10); INSERT INTO project.analysis(name) VALUES ('ultrasonography'); INSERT INTO project.analysis(name) VALUES ('covid-19 test'); INSERT INTO project.analysis(name) VALUES ('Urinalysis'); INSERT INTO project.analysis(name) VALUES ('Stool analysis'); INSERT INTO project.analysis(name) VALUES ('lumbar puncture'); INSERT INTO project.analysis(name) VALUES ('gastric fluid analysis'); INSERT INTO project.analysis(name) VALUES ('computed tomography'); INSERT INTO project.analysis(name) VALUES ('blood analysis'); INSERT INTO project.analysis(name) VALUES ('echocardiography'); INSERT INTO project.analysis(name) VALUES ('echoencephalography'); INSERT INTO project.prescription(appointment_id, recommendations) VALUES (1, 'take pills'); INSERT INTO project.prescription(appointment_id, recommendations) VALUES (2, 'take pills'); INSERT INTO project.prescription(appointment_id, recommendations) VALUES (3, 'take pills'); INSERT INTO project.prescription(appointment_id, recommendations) VALUES (4, 'take pills'); INSERT INTO project.prescription(appointment_id, recommendations) VALUES (5, 'take pills'); INSERT INTO project.prescription(appointment_id, recommendations) VALUES (6, 'take pills'); INSERT INTO project.prescription(appointment_id, recommendations) VALUES (7, 'take pills'); INSERT INTO project.prescription(appointment_id, recommendations) VALUES (8, 'take pills'); INSERT INTO project.prescription(appointment_id, recommendations) VALUES (9, 'take pills'); INSERT INTO project.prescription(appointment_id, recommendations) VALUES (10, 'take pills'); INSERT INTO project.prescription_analysis(prescription_id, analysis_id) VALUES (1, 2); INSERT INTO project.prescription_analysis(prescription_id, analysis_id) VALUES (2, 2); INSERT INTO project.prescription_analysis(prescription_id, analysis_id) VALUES (3, 8); INSERT INTO project.prescription_analysis(prescription_id, analysis_id) VALUES (4, 2); INSERT INTO project.prescription_analysis(prescription_id, analysis_id) VALUES (5, 4); INSERT INTO project.prescription_analysis(prescription_id, analysis_id) VALUES (1, 8); INSERT INTO project.prescription_analysis(prescription_id, analysis_id) VALUES (7, 3); INSERT INTO project.prescription_analysis(prescription_id, analysis_id) VALUES (8, 4); INSERT INTO project.prescription_analysis(prescription_id, analysis_id) VALUES (9, 2); INSERT INTO project.prescription_analysis(prescription_id, analysis_id) VALUES (10, 8); INSERT INTO project.analysis_result( patient_id, analysis_id, analysis_result ) VALUES (1, 2, 'Negative'); INSERT INTO project.analysis_result( patient_id, analysis_id, analysis_result ) VALUES (2, 2, 'Negative'); INSERT INTO project.analysis_result(patient_id, analysis_id) VALUES (3, 8); INSERT INTO project.analysis_result( patient_id, analysis_id, analysis_result ) VALUES (4, 2, 'Negative'); INSERT INTO project.analysis_result(patient_id, analysis_id) VALUES (5, 4); INSERT INTO project.analysis_result(patient_id, analysis_id) VALUES (6, 8); INSERT INTO project.analysis_result(patient_id, analysis_id) VALUES (7, 3); INSERT INTO project.analysis_result(patient_id, analysis_id) VALUES (8, 4); INSERT INTO project.analysis_result(patient_id, analysis_id) VALUES (9, 2); INSERT INTO project.analysis_result(patient_id, analysis_id) VALUES (10, 8);
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear