By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE aeronave (
id_aeronave NUMBER(10,0) PRIMARY KEY,
matricula VARCHAR2(20) NOT NULL
);
CREATE TABLE ocorrencia (
id_ocorrencia NUMBER(10,0) PRIMARY KEY,
data_utc DATE NOT NULL
);
CREATE TABLE aeronave_ocorrencia (
id_aeronave NUMBER(10,0) NOT NULL,
id_ocorrencia NUMBER(10,0) NOT NULL,
PRIMARY KEY (id_aeronave, id_ocorrencia),
FOREIGN KEY (id_aeronave) REFERENCES aeronave (id_aeronave),
FOREIGN KEY (id_ocorrencia) REFERENCES ocorrencia (id_ocorrencia)
);
INSERT INTO aeronave (id_aeronave, matricula)
VALUES (1, 'Teco-teco');
1 rows affected
INSERT INTO aeronave (id_aeronave, matricula)
VALUES (2, 'Boeing 747');
1 rows affected
INSERT INTO aeronave (id_aeronave, matricula)
VALUES (3, 'Embraer Tucano');
1 rows affected
INSERT INTO ocorrencia (id_ocorrencia, data_utc)
VALUES (1, TO_DATE('2020-08-13', 'YYYY-MM-DD'));
1 rows affected
INSERT INTO ocorrencia (id_ocorrencia, data_utc)
VALUES (2, TO_DATE('2020-07-12', 'YYYY-MM-DD'));
1 rows affected
INSERT INTO ocorrencia (id_ocorrencia, data_utc)
VALUES (3, TO_DATE('2020-06-11', 'YYYY-MM-DD'));
1 rows affected
INSERT INTO ocorrencia (id_ocorrencia, data_utc)
VALUES (4, TO_DATE('2020-05-10', 'YYYY-MM-DD'));
1 rows affected
INSERT INTO ocorrencia (id_ocorrencia, data_utc)
VALUES (5, TO_DATE('2020-04-09', 'YYYY-MM-DD'));
1 rows affected
INSERT INTO ocorrencia (id_ocorrencia, data_utc)
VALUES (6, TO_DATE('2020-03-08', 'YYYY-MM-DD'));
1 rows affected
INSERT INTO aeronave_ocorrencia (id_aeronave, id_ocorrencia)
VALUES (1, 6);
1 rows affected
INSERT INTO aeronave_ocorrencia (id_aeronave, id_ocorrencia)
VALUES (1, 5);
1 rows affected
INSERT INTO aeronave_ocorrencia (id_aeronave, id_ocorrencia)
VALUES (1, 4);
1 rows affected
INSERT INTO aeronave_ocorrencia (id_aeronave, id_ocorrencia)
VALUES (1, 3);
1 rows affected
INSERT INTO aeronave_ocorrencia (id_aeronave, id_ocorrencia)
VALUES (1, 2);
1 rows affected
INSERT INTO aeronave_ocorrencia (id_aeronave, id_ocorrencia)
VALUES (1, 1);
1 rows affected
INSERT INTO aeronave_ocorrencia (id_aeronave, id_ocorrencia)
VALUES (3, 1);
1 rows affected
INSERT INTO aeronave_ocorrencia (id_aeronave, id_ocorrencia)
VALUES (2, 6);
1 rows affected
INSERT INTO aeronave_ocorrencia (id_aeronave, id_ocorrencia)
VALUES (3, 2);
1 rows affected
INSERT INTO aeronave_ocorrencia (id_aeronave, id_ocorrencia)
VALUES (2, 5);
1 rows affected
SELECT
a.matricula
FROM
aeronave a
JOIN
aeronave_ocorrencia ao ON a.id_aeronave = ao.id_aeronave
JOIN
ocorrencia o ON ao.id_ocorrencia = o.id_ocorrencia
WHERE
o.data_utc > ADD_MONTHS(CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE), -2)
GROUP BY
a.matricula;
| MATRICULA |
|---|
| Teco-teco |
| Embraer Tucano |