How to find the name and address of students and teachers in São Paulo

Question

Hello, I have a question in the question that my teacher passed to train. The database has 5 tables being: a alunos, professores and historico in the exercise asks to consult name and address of students and teachers São Paulo, but the way I am solving returns all students of the teacher who lives in São Paulo and if the student lives in São Paulo return teachers who live in another city.

Information on the tables:

create table Alunos ( 
ra number(9) constraint ra_pk primary key, 
nm_aluno varchar(30) constraint nm_aluno_nn not null, 
endereco varchar(50), 
cidade varchar(30) 
);


create table Professores ( 
cod_prof number(9) constraint cod_prod_pk primary key, 
nm_prof varchar(50) constraint nm_prof_nn not null,  
endereco_prof varchar(50), 
cidade_prof varchar(30) 
);

create table Historico ( 
ra number(9) constraint ra_fk references Alunos 
             constraint ra_nn not null, 
cod_disc number(5) constraint cod_disc_fk_tr references Disciplinas 
                   constraint cod_disc_nn_tr not null, 
cod_turma number(5) constraint cod_turma_fk references Turma, 
cod_prof number(9) constraint cod_prof_fk_tr references Professores 
                   constraint cod_prof_nn_tr not null, 
ano_tr number(4), 
frequencia number(4), 
nota number(2) 
);

insert into Alunos values (945441422,'Wesley Junior Francisco','Rua Renato Marques Jr','Minas Gerais');
insert into Alunos values (955655481,'Gustavo Dias Rodrigue','Rua Mato Grosso','São Paulo');
insert into Alunos values (458586521,'Lucas Ferreira Silva','Rua Benedicto de Souza Branco','Rio de Janeiro');
insert into Alunos values (686955654,'Ana Vicari Beatriz','Rua Rangel Pestana', 'São Paulo');
insert into Alunos values (326546985,'Yasmim Santos','Rua Macapá','São Paulo');
insert into Alunos values (963258741,'Geraldo Francisco Farias','Rua Cisne','Guarulhos');
insert into Alunos values (654987321,'Clarice Jaqueline','Avenida Contorno','Guarulhos');
insert into Alunos values (845621789,'Vinicius Murilo Gomes','SES 801 Lote 05','Brasília'); 
insert into Alunos values (541258963,'Bryan Arthur Gonçalves','Rua 3 de Setembro','Manaus'); 
insert into Alunos values (852654951,'Aurora Heloisa','Rua Antonio de Godoi','São Paulo'); 
insert into Alunos values (741489951,'Cauã Eduardo Vicente Peixoto','Rua A','Palmeira dos Índios');
insert into Alunos values (951654753,'Adriana Laura','Rua Rio Brilhante','São Luís');
insert into Alunos values (357963369,'Miguel Gustavo','Rua Carolina Lopes de Faria','Garulhos');
insert into Alunos values (963987951,'Alana Rayssa Caroline Fogaça','Quadra Orla 14 Alameda 7','Santos');
insert into Alunos values (456456456,'Elza Daiane Assunção','Quadra SHCES Quadra 611 Área Especial s/n','Palmas');
insert into Alunos values (541523587,'Luzia Clara Cavalcanti','Praça Humberto Mendes','Goiânia');

insert into Professores values(789456123,'Vania Cristina','Rua Brás Pina','São Paulo');
insert into Professores values(852963741,'Marcel Thomé','1ª Travessa Beira Rio','Rio de Janeiro');
insert into Professores values(147852369,'Alice Flora','Avenida Brasil','Rio de Janeiro');
insert into Professores values(578951258,'Wagner Luiz','Travessa Treze de Abril','São Paulo');
insert into Professores values(951753698,'Maria Cristina','Avenida Jundiaí','Jundaí');  

insert into Historico values(945441422,12366,14785,789456123,2010,15,4);
insert into Historico values(955655481,32145,14765,852963741,2010,100,5);
insert into Historico values(458586521,45612,25851,147852369,2011,25,7);
insert into Historico values(686955654,45612,32145,578951258,2000,35,5);
insert into Historico values(326546985,51234,32154,951753698,2018,55,5);
insert into Historico values(963258741,44566,14785,789456123,2015,6,6);
insert into Historico values(654987321,44566,14765,852963741,2010,45,8);
insert into Historico values(845621789,45612,25851,147852369,2011,8,10);
insert into Historico values(541258963,51234,32145,578951258,2000,1,10);
insert into Historico values(852654951,51234,32154,951753698,2018,10,6);
insert into Historico values(741489951,44566,14785,789456123,2010,5,1);
insert into Historico values(951654753,12366,14765,852963741,2011,6,3);
insert into Historico values(357963369,32145,25851,147852369,2012,52,6);
insert into Historico values(963987951,45612,32145,578951258,2013,92,9);
insert into Historico values(456456456,51234,32154,951753698,2014,65,2);
insert into Historico values(541523587,44566,14785,789456123,2017,4,8);

---

Method I used, but not correct :

select nm_prof, endereco_prof,cidade_prof nm_aluno,endereco from historico h inner join alunos a on h.ra=a.ra inner join professores p on h.cod_prof=p.cod_prof where cidade = 'São Paulo' or cidade_prof='São Paulo'

Result is attached as image.

I hope I was able to express my doubt and praise whoever gets to Ana-la.

Answer 1

Welcome to the Stackoverflow!

From what I understood by analyzing your question and the result of your query added as an image, I believe you want the result of only students residing in São Paulo and also only teachers residing in São Paulo.

Your query currently looks like this:

SELECT nm_prof,
       endereco_prof,
       cidade_prof nm_aluno,
       endereco 
FROM historico h 
INNER JOIN alunos a
    ON h.ra = a.ra 
INNER JOIN professores p
    ON h.cod_prof = p.cod_prof 
WHERE cidade = 'São Paulo' or cidade_prof='São Paulo'

Analyzing your query I saw 3 inconsistent situations:

1. Note that in its attachment the column NM_ALUNO is bringing the state of the teacher, this is because when declaring in the above query you brought the value of the column and gave an alias to it in the following code: ... cidade_prof nm_aluno, .... This code is without comma separation, so DBMS understands that the values for the citade_prof column will be named at the output prompt as nm_student.
2. If you just want to bring the students and teachers in that BOTH São Paulo, you will use the clause AND instead of OR in that part WHERE cidade = 'São Paulo' AND cidade_prof='São Paulo'
3. The third and last situation is a tip I give you: After SELECT, note that you are referencing columns from the teacher table (the first 3 columns) and student table (last column), however, to facilitate the maintenance of the code add the table alias as a call prefix, as an example: SELECT p.nm_prof, p.endereco_prof, p.cidade_prof, a.m_aluno, a.endereco ... this facilitates your life or the next one that will do the maintenance in the query.

Good studies.