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

Question

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

Navigation

#1 by (1 votes)

2

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

Information about 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 that I used, but not the correct one:

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 have been able to express my doubt and I thank anyone who can heal it.

sql oracle

asked by anonymous 13.11.2018 / 19:11

1 answer

Linear Regression in Various Products Error generating report in production - ReportViewer Version 11

score 1 · Answer 1

Welcome to StackOverFlow!

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

Your query is currently 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:

Note that in your attachment the column NM_ALUNO is bringing the state of the teacher, this is because when declaring in the above query you have 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 the DBMS understands that the values referring to the city_prof column will be named at the output prompt as nm_aluno.

If you just want to bring students and teachers that are BOTH from São Paulo, you'll use the AND clause instead of OR in this part WHERE cidade = 'São Paulo' AND cidade_prof='São Paulo'

The third and last situation is a tip I give you: After SELECT, note that you are referencing columns of the teacher table (the first 3 columns) and student (last column), however, to facilitate code maintenance add the table alias as a prefix of the call, for example: SELECT p.nm_prof, p.endereco_prof, p.cidade_prof, a.m_aluno, a.endereco ... this makes your life easier or the next one that will perform the query maintenance.

Good studies.