2
I have a problem to recover the teacher who teaches more subjects in this table, in which case I want to recover the code that has more duplicated records in the cod_prof column.
create table aluno_professor_disciplina (
matricula int,
cod_prof int,
cod_disc int,
primary key (matricula, cod_prof, cod_disc),
foreign key (matricula) references aluno(matricula),
foreign key (cod_prof) references professor(cod_prof),
foreign key (cod_disc) references disciplina(cod_disc));
INSERT INTO aluno_professor_disciplina VALUES (1,1,1);
INSERT INTO aluno_professor_disciplina VALUES (1,5,2);
INSERT INTO aluno_professor_disciplina VALUES (2,2,3);
INSERT INTO aluno_professor_disciplina VALUES (3,3,5);
INSERT INTO aluno_professor_disciplina VALUES (3,4,5);
INSERT INTO aluno_professor_disciplina VALUES (1,5,5);
Someone could help me?
I’m tempted to, but it’s not working:
select A.nome, count(B.cod_prof) as QT_DISC from professor as A
inner join aluno_professor_disciplina as B
on A.cod_prof = B.cod_prof
group by (A.nome);
He returns the name of the teachers and the amount of disciplines that each one teaches, wanted to know how I can recover only what teaches more disciplines.
Cool, I understood the logic and it worked here, in your code, missed only put the 'DESC' in order by. Thanks!!
– Vynstus
Oops, my bad. I’ll fix.
– William Okano