Write 2 complex queries

Question

Post reply

Write 2 complex queries

GeraltZRiviii

Valued Member

Points: 62

December 5, 2021 at 11:22 pm

#3960632

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:

Find all patients who visited the doctor on the same day as patient "A"(example).
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

December 13, 2021 at 11:27 am

This was removed by the editor as SPAM
December 16, 2021 at 4:28 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

The dead giveaway that you're new to SQL is that you said these were complex.

What did you try? Post the SQL. People will help you if you do that. If you're asking for someone to do your homework for you, that's basically plagiarism. At least if you try it and get it wrong, you can explain your reasoning.

So post your attempt for each first. Unfortunately, the way you learn anything is by trying it, making mistakes, and figuring out why your answer isn't quite right, and then fixing it.

ScottPletcher SSC Guru Points: 101095 More actions · Answer 2

The patient's name is not present anywhere. That is, there's no way to know who patient "A", let alone to know what doctor(s) he/she saw.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".