Write 2 complex queries

1

I have a DB:

CREATE TABLE cabinet
(
    id_cabinet INT PRIMARY KEY,
    area INT NOT NULL
);
 
CREATE TABLE doctor
(
    id_pib INT PRIMARY KEY,
    speciality VARCHAR(50) NOT NULL,
    surname VARCHAR(100) NOT NULL,
    id_cabinet INT NOT NULL,
    FOREIGN KEY (id_cabinet) REFERENCES cabinet(id_cabinet)
);
 
CREATE TABLE nurse
(
    id_pib INT PRIMARY KEY,
    pib VARCHAR(255) NOT NULL,
    work_exp INT NOT NULL,
    fk_nurse_doc INT REFERENCES doctor(id_pib)
);
 
CREATE TABLE patient
(
    id_patient INT PRIMARY KEY,
    age INT NOT NULL DEFAULT 1,
    sex VARCHAR(6) NOT NULL,
    disease VARCHAR(100) NOT NULL
);
 
CREATE TABLE doctor_patient
(
    date_time TIMESTAMP NOT NULL, 
    id_doc INT REFERENCES doctor(id_pib),
    id_pat INT REFERENCES patient(id_patient),
    CONSTRAINT pk_doctor_patient PRIMARY KEY (id_doc, id_pat)
);
 
INSERT INTO cabinet(id_cabinet, area) VALUES (1, 12);
INSERT INTO cabinet(id_cabinet, area) VALUES (2, 10);
INSERT INTO cabinet(id_cabinet, area) VALUES (3, 8);
 
INSERT INTO doctor(id_pib, speciality, surname, id_cabinet) VALUES (1, 'surgery', 'Vacovskiy', 1);
INSERT INTO doctor(id_pib, speciality, surname, id_cabinet) VALUES (2, 'neurology', 'Obama', 1);
INSERT INTO doctor(id_pib, speciality, surname, id_cabinet) VALUES (3, 'dermatology', 'Atano', 2);
INSERT INTO doctor(id_pib, speciality, surname, id_cabinet) VALUES (4, 'pediatrics', 'Yeager', 1);
INSERT INTO doctor(id_pib, speciality, surname, id_cabinet) VALUES (5, 'surgery', 'Reeves', 2);
INSERT INTO doctor(id_pib, speciality, surname, id_cabinet) VALUES (6, 'psychiatry', 'Pines', 3);
 
INSERT INTO nurse(id_pib, pib, work_exp, fk_nurse_doc) VALUES (1, 'B.G.A.', 5, 1);
INSERT INTO nurse(id_pib, pib, work_exp, fk_nurse_doc) VALUES (2, 'J.L.B.', 7, 2);
INSERT INTO nurse(id_pib, pib, work_exp, fk_nurse_doc) VALUES (3, 'R.O.A.', 2, 3);
INSERT INTO nurse(id_pib, pib, work_exp, fk_nurse_doc) VALUES (4, 'S.T.J.', 10, 4);
INSERT INTO nurse(id_pib, pib, work_exp, fk_nurse_doc) VALUES (5, 'V.C.K.', 6, 5);
INSERT INTO nurse(id_pib, pib, work_exp, fk_nurse_doc) VALUES (6, 'M.H.F.', 4, 6);
 
INSERT INTO patient(id_patient, age, sex, disease) VALUES (1, 19, 'male', 'flu');
INSERT INTO patient(id_patient, age, sex, disease) VALUES (2, 24, 'female', 'diabetes');
INSERT INTO patient(id_patient, age, sex, disease) VALUES (3, 36, 'male', 'alergy');
INSERT INTO patient(id_patient, age, sex, disease) VALUES (4, 27, 'female', 'COVID-19');
INSERT INTO patient(id_patient, age, sex, disease) VALUES (5, 50, 'female', 'typhus');
INSERT INTO patient(id_patient, age, sex, disease) VALUES (6, 42, 'male', 'cerebrovascular disease');
INSERT INTO patient(id_patient, age, sex, disease) VALUES (7, 33, 'female', 'tuberculosis');
INSERT INTO patient(id_patient, age, sex, disease) VALUES (8, 21, 'male', 'chickenpox');
INSERT INTO patient(id_patient, age, sex, disease) VALUES (9, 23, 'male', 'flu');
INSERT INTO patient(id_patient, age, sex, disease) VALUES (10, 32, 'female', 'diabetes');
INSERT INTO patient(id_patient, age, sex, disease) VALUES (11, 40, 'male', 'alergy');
INSERT INTO patient(id_patient, age, sex, disease) VALUES (12, 62, 'female', 'COVID-19');
INSERT INTO patient(id_patient, age, sex, disease) VALUES (13, 54, 'female', 'typhus');
INSERT INTO patient(id_patient, age, sex, disease) VALUES (14, 37, 'male', 'cerebrovascular disease');
INSERT INTO patient(id_patient, age, sex, disease) VALUES (15, 20, 'female', 'tuberculosis');
INSERT INTO patient(id_patient, age, sex, disease) VALUES (16, 17, 'male', 'chickenpox');
 
 
INSERT INTO doctor_patient(date_time, id_doc, id_pat) 
VALUES ('2021-06-01 08:15:00', 1, 2), ('2021-06-01 09:10:00', 1, 3), 
('2021-06-03 10:00:00', 2, 1), ('2021-06-03 12:00:00', 3, 4),
('2021-06-04 07:45:00', 4, 6), ('2021-06-06 11:30:00', 5, 5),
('2021-06-07 07:15:00', 5, 7), ('2021-06-09 16:00:00', 6, 8),
('2021-06-07 08:15:00', 1, 9), ('2021-06-01 09:45:00', 6, 10), 
('2021-06-03 10:45:00', 2, 11), ('2021-06-01 12:00:00', 3, 12),
('2021-06-01 09:50:00', 4, 13), ('2021-06-06 13:30:00', 5, 14),
('2021-06-04 10:00:00', 5, 15), ('2021-06-09 15:00:00', 6, 16);

And for it I need to write the following queries:

  1. Find all patients who visited the doctor on the same day as patient "A"(example).
  2. Determine how many times each doctor has seen each patient.

Since I'm new to SQL, I spent a lot of time trying to make these requests, so I would be very grateful if someone would write these requests, so to speak, deadlines are burning. Thank you in advance <3

1 Like
2

Welcome @GeraltZRivii to this forum. Great way to provide sample data! What have you tried? And what is this deadline you speak of?

3

Your Sample data .... does not ... provide the scenario

select 
      id_pat 
from 
     doctor_patient 
where 
    date_time   in     (select date_time  from doctor_patient where id_pat = 3 )
4

Need to change the where clause to this to get it to work, since we are storing time in the date_time column.

cast(date_time as date) in (select cast(date_time as date) from doctor_patient where id_pat = 3 )