Mysql Help with INNER JOIN

Question

Talk people, good night! I’m breaking my balls here, I wonder if questions 3 and 4 are correct and how the syntax would be structured if I answer them using INNER JOIN and if it is more suitable.

We have a teacher and student management system in a school based on a Mysql database, which has 3 tables (teacher, student and relationship) with the following content:

All tables have a primary index called "id", which uniquely identifies each record. The "relationship" table is the one that relates teachers to students. We need you to build the necessary Mysql commands to get the following information:

1. Name of the science teacher

SELECT * FROM professor WHERE assinatura=ciencias;

2. Count how many students are in school

SELECT COUNT(id) FROM aluno;

3. List of names with all students of the science teacher, taking into account that the only data we have about the teacher is that his signature is "science".

SELECT *
FROM aluno a,
           professor p,
           relacionamento r
WHERE p.assinatura = 'ciencias'
AND p.id = r.professor_id
AND r.aluno_id = a.id

4. List of students who are not in science class, taking into account that the only data we have on the teacher is that his signature is "science".

SELECT *
FROM aluno a,
           professor p,
           relacionamento r
WHERE p.assinatura != 'ciencias'
AND p.id = r.professor_id
AND r.aluno_id = a.id

Answer 1

1. Name of the science teacher

   SELECT * FROM teacher WHERE subscription='ciencias';

The quotes were missing.

3. List of names of all the science teacher’s students, taking into account that the only data we have on the teacher is that his signature is "science". Using joint:

   SELECT * FROM student to INNER JOIN relationship r ON (r.aluno_id = a.id) INNER JOIN teacher p ON (p.id = r.professor_id) WHERE p.signature = 'ciencias'

4. List of students who are not in science class, taking into account that the only data we have on the teacher is that his signature is "science". Using joint:

   SELECT * FROM student to INNER JOIN relationship r ON (r.aluno_id = a.id) INNER JOIN teacher p ON (p.id = r.professor_id) WHERE p.signature = 'science'

Although in your table above there is no record with signature = 'ciencia' (exists with 'ciencias').

Answer 2

No, you are wrong. Until it works, but after 92 started to use INNER Join

3. SELECT * FROM student to INNER JOIN relationship r ON (r.aluno_id = a.id) INNER JOIN teacher p ON (p.id = r.professor_id) WHERE p.signature = 'ciencias'

4. SELECT * FROM student to INNER JOIN relationship r ON (r.aluno_id = a.id) INNER JOIN teacher p ON (p.id = r.professor_id) WHERE p.signature <> 'ciencias'