C-PROG C-PROG - 7 months ago 26
SQL Question

SQL Queries in Oracle

I have created a database of a hospital and "management would like to know how many people got diagnosed with cancer in the last year".

CREATE TABLE patients (
ID_patients INTEGER NOT NULL,
Name VARCHAR NOT NULL
);

CREATE TABLE visit(
ID_visit INTEGER NOT NULL,
DATE_visit DATE NOT NULL,
FK_patients INTEGER NOT NULL,
FK_diagnosis INTEGER
);

CREATE TABLE Diagnosis(
ID_Diagnosis INTEGER NOT NULL,
FK_disease INTEGER NOT NULL
);

CREATE TABLE Disease(
ID_disease INTEGER NOT NULL,
Name_disease VARCHAR NOT NULL
);


Now we need to find out: which patients got diagnosed with cancer last year.
I used query below to get patients that have visited last year, but I do not know how to target those with cancer ? I think I should use "VIEW AS" but I'm not sure.

SELECT *
FROM Visit
WHERE Date_Visit BETWEEN
(CURRENT_DATE - interval '2' year) AND CURRENT_DATE - INTERVAL '1' YEAR;

Answer

Assuming you only need a patient count and you already know how to define cancer, you'll want to use a JOIN to connect these tables together:

SELECT COUNT(v.FK_patients)
FROM visit v
JOIN Diagnosis d on d.ID_Diagnosis = v.FK_diagnosis --Here is where you connect the tables
WHERE v.Date_Visit BETWEEN
      (CURRENT_DATE - interval '2' year) AND CURRENT_DATE - INTERVAL '1' YEAR
AND FK_disease IN(--Your list of cancer ids);