SQLize Online / PHPize Online  /  SQLtest Online

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, age, weight) VALUES('Kseniia', 'Altynbaeva', 19, 65); INSERT INTO project.patient(first_name, last_name, age, weight) VALUES('Anna', 'Gubareva', 23, 60); INSERT INTO project.patient(first_name, last_name, age, weight) VALUES('Sofya', 'Dergunova', 31, 55); INSERT INTO project.patient(first_name, last_name, age, weight) VALUES('Anna', 'Pchelintseva', 15, 45); INSERT INTO project.patient(first_name, last_name, age, weight) VALUES('Mark', 'Bikbulatov', 19, 60); INSERT INTO project.patient(first_name, last_name, age, weight) VALUES('Mariia', 'Popova', 19, 57); INSERT INTO project.patient(first_name, last_name, age, weight) VALUES('Alena', 'Poyasova', 45, 78); INSERT INTO project.patient(first_name, last_name, age, weight) VALUES('Ivan', 'Ivanov', 65, 82); INSERT INTO project.patient(first_name, last_name, age, weight) VALUES('Yulia', 'Altynbaeva', 10, 35); INSERT INTO project.patient(first_name, last_name, age, weight) VALUES('Darya', 'Sverlova', 10, 25); 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(1, 8); INSERT INTO project.prescription_analysis(prescription_id, analysis_id) VALUES(4, 2); INSERT INTO project.prescription_analysis(prescription_id, analysis_id) VALUES(1, 4); INSERT INTO project.prescription_analysis(prescription_id, analysis_id) VALUES(1, 8); INSERT INTO project.prescription_analysis(prescription_id, analysis_id) VALUES(2, 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(1, 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(1, 4); INSERT INTO project.analysis_result(patient_id, analysis_id) VALUES(6, 8); INSERT INTO project.analysis_result(patient_id, analysis_id) VALUES(2, 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); --Для каждой возрастной группы посчиать средний вес и вывести только тех, чей возраст больше 40 SELECT age, AVG(weight) as average FROM project.Patient GROUP BY age HAVING age > 40; --Вывести фамилии, имена и специализации врачей, отсортированные по фамилии и имени SELECT last_name,first_name, name FROM project.Physician p LEFT JOIN project.Physician_Specialization ps ON p.physician_id = ps.physician_id LEFT JOIN project.specialization s ON ps.specialization_id = s.specialization_id ORDER BY last_name, first_name; --Для каждого человека вывести, какие анализы он сдавал, пронумеровать и отсортировать в алфавитном порядке SELECT last_name, first_name, row_number() OVER (PARTITION BY p.patient_id ORDER BY a.name), a.name FROM project.patient p RIGHT JOIN project.analysis_result pa ON p.patient_id = pa.patient_id LEFT JOIN project.analysis a ON pa.analysis_id = a.analysis_id; --Вывести ФИ врачей, являющихcя дантистами, отсортировать SELECT last_name, first_name, name FROM project.Physician p LEFT JOIN project.Physician_Specialization ps ON p.physician_id = ps.physician_id LEFT JOIN project.specialization s ON ps.specialization_id = s.specialization_id WHERE name='dentist' ORDER BY last_name, first_name; --Из людей, сдававших тест на ковид, вывести тех, чей результат отрицательный SELECT last_name, first_name FROM project.patient p RIGHT JOIN project.analysis_result pa ON p.patient_id = pa.patient_id LEFT JOIN project.analysis a ON pa.analysis_id = a.analysis_id AND a.name='covid-19 test' WHERE pa.analysis_result='Negative' ORDER BY last_name, first_name;