SQLTeam.com | Weblogs | Forums

Write 2 complex queries

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

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

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 )

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 )