Select two tables with different columns in ORACLE

Question

I have three tables, one call ALUNO, a callMATRICULA and another call CLASSE and I need to select all students who are male and who belong to the same class as a student who has a specific RGM.

The tables are :

STUDENT SCHEDULE

  CREATE TABLE ALUNO(
    NR_RGM NUMBER(8),
    NM_NOME VARCHAR2(40) CONSTRAINT NOME_ALUNO_NN NOT NULL,
    NM_PAI VARCHAR2(40) CONSTRAINT PAI_ALUNO_NN NOT NULL,
    NM_MAE VARCHAR2(40) CONSTRAINT MAE_ALUNO_NN NOT NULL,
    DT_NASCIMENTO DATE CONSTRAINT DT_NASC_ALUNO_NN NOT NULL,
    ID_SEXO VARCHAR2(1) CONSTRAINT SEXO_ALUNO_NN NOT NULL,
    CONSTRAINT ALUNO_NR_RGM_PK PRIMARY KEY (NR_RGM));

MATRICULA :

CREATE TABLE MATRICULA(
CD_CLASSE NUMBER(8),
NR_RGM NUMBER(8),
DT_MATRICULA DATE CONSTRAINT DATA_MATRICULA_NN NOT NULL,
CONSTRAINT MATRICULA__CD_CLASSE_NR_RGM_PK PRIMARY KEY (CD_CLASSE,NR_RGM),
CONSTRAINT MATRICULA_CD_RGM_FK FOREIGN KEY(NR_RGM) REFERENCES ALUNO(NR_RGM),
CONSTRAINT MATRICULA_CD_CLASSE_FK FOREIGN KEY(CD_CLASSE) 
    REFERENCES CLASSE(CD_CLASSE));

CLASS TABLE :

CREATE TABLE CLASSE(
CD_CLASSE NUMBER(8),
NR_ANOLETIVO NUMBER(4) CONSTRAINT ANO_LETIVO_CLASSE_NN NOT NULL,
CD_ESCOLA NUMBER(6),
CD_GRAU NUMBER(2),
NR_SERIE NUMBER(2) CONSTRAINT SERIE_CLASSE_NN NOT NULL,
TURMA VARCHAR2(2) CONSTRAINT TURMA_CLASSE_NN NOT NULL,
CD_PERIODO NUMBER(2),
CONSTRAINT CLASSE_PK PRIMARY KEY (CD_CLASSE),
CONSTRAINT GOIABAO FOREIGN KEY(CD_ESCOLA) 
      REFERENCES ESCOLA(CD_ESCOLA),
CONSTRAINT CLASSE_CD_CLASSE_FK FOREIGN KEY(CD_GRAU) REFERENCES GRAU(CD_GRAU),
CONSTRAINT CLASSE_CD_PERIODO_FK FOREIGN KEY(CD_PERIODO) REFERENCES PERIODO(CD_PERIODO),
CONSTRAINT CLASSE_ANO_LETIVO_CK CHECK(NR_ANOLETIVO > 2000));

How could I do this ?

For example, to select students from the same student room as a specific RGM would look like this:

SELECT * FROM Matricula WHERE cd_classe = 
(SELECT cd_classe FROM matricula WHERE nr_rgm = '12150');

But how could I do this with two different columns of two different tables ?

Answer 1

From what I understand, MATRICULA.nr_rgm references STUDENT.nr_rgm and MATRICULA.cd_class references CLASS.cd_class

then to return all students of the same class who own the same NR_RGM and are male, would do something like this:

select a.nome 
from aluno a
inner join matricula ma on (ma.nr_rgm = a.nr_rgm)
inner join classe c on (c.cd_classe = ma.cd_classe)
where a.nr_rgm = 12150 and a.id_sexo = 'M';

not tested, because I do not have the tables created here, see if it helps in any way.