Search in 3 tables

Asked

Viewed 22 times

1

I have the following tables:

CREATE TABLE alunos (
 id_aluno int not null,
 nome VARCHAR(255),
  PRIMARY KEY (id_aluno)
)

CREATE TABLE aulas (
id_aula int not null,
nome VARCHAR(255),
PRIMARY KEY(id_aula)
)

CREATE TABLE matriculas (
id_aluno int not null,
id_aula int not null,
FOREIGN KEY (id_aluno) REFERENCES alunos(id_aluno),
FOREIGN key (id_aula) REFERENCES aulas(id_aula)
)

and with the following data:

INSERT INTO alunos (id_aluno, nome) VALUES ('10', 'JOÃO');
INSERT INTO alunos (id_aluno, nome) VALUES  ('11', 'MARIA');

insert into aulas (id_aula, nome) VALUES ('1', 'Português');
insert into aulas (id_aula, nome) VALUES ('2', 'Matematica');
insert into aulas (id_aula, nome) VALUES ('3', 'História');
insert into aulas (id_aula, nome) VALUES ('4', 'Ciência');

insert into matriculas (id_aluno, id_aula) VALUES ('10','1');
insert into matriculas (id_aluno, id_aula) VALUES ('10','2');
insert into matriculas (id_aluno, id_aula) VALUES ('11','3');
insert into matriculas (id_aluno, id_aula) VALUES ('11','4');
insert into matriculas (id_aluno, id_aula) VALUES ('11','2');

Soon, João has the classes Portuguese and Mathematics and Maria Matematica, History and Science.

Questions:

1- How could I do a query to get the name of the class in which John and Mary are registered? (in the case of Matematica) Or suppose I have 10 students registered and they have at least one class in common with each other and I want to know what it is.

2-This would be the opposite I wanted to know which students are registered in a given class. Portuguese for example.

  • Fundamental to understand: https://answall.com/questions/6441/70

  • I could show a ready answer, but I believe that from a learning point of view, it would be very good if you understand the concepts of joins between tables using language SQL. Adding to what @Bacco mentioned, I suggest this very illustrative reading: https://answall.com/questions/6441/qual-%C3%A9-a-difference%C3%A7a-entre-Inner-Join-e-outer-Join

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.