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
– Bacco
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
– Ricardo Pontual