SQL Query to select student and student name

Try this:

SELECT A.matricula_aluno,P.nome FROM aluno AS A,pessoa AS P

Translating your template to MySQL :

CREATE TABLE pessoa
(
    matricula BIGINT NOT NULL,
    senha VARCHAR(32),
    nome VARCHAR(255),
    email VARCHAR(255),
    logradouro VARCHAR(255),
    bairro BIGINT,
    cidade BIGINT,
    uf  VARCHAR(2),
    PRIMARY KEY(matricula)
);

CREATE TABLE aluno
(
    matricula BIGINT NOT NULL,
    PRIMARY KEY(matricula)
);

CREATE TABLE professor
(
    matricula BIGINT NOT NULL,
    PRIMARY KEY(matricula)
);

ALTER TABLE aluno ADD CONSTRAINT aluno_matricula_fk FOREIGN KEY (matricula) REFERENCES pessoa (matricula);

ALTER TABLE professor ADD CONSTRAINT professor_matricula_fk FOREIGN KEY (matricula) REFERENCES pessoa (matricula);

Student Registration:

INSERT INTO pessoa ( matricula, nome ) VALUES ( 1234, 'JOAO' );
INSERT INTO aluno ( matricula ) VALUES ( 1234 );

INSERT INTO pessoa ( matricula, nome ) VALUES ( 9876, 'MARIA' );
INSERT INTO aluno ( matricula ) VALUES ( 9876 );

INSERT INTO pessoa ( matricula, nome ) VALUES ( 1000, 'JOSE' );
INSERT INTO aluno ( matricula ) VALUES ( 1000 );

INSERT INTO pessoa ( matricula, nome ) VALUES ( 7777, 'JESUS' );
INSERT INTO aluno ( matricula ) VALUES ( 7777 );

Teacher Registration:

INSERT INTO pessoa ( matricula, nome ) VALUES ( 2222, 'ALBERT EINSTEIN' );
INSERT INTO professor ( matricula ) VALUES ( 2222 );

INSERT INTO pessoa ( matricula, nome ) VALUES ( 5555, 'ISAAC NEWTON' );
INSERT INTO professor ( matricula ) VALUES ( 5555 );

Student Consultation:

SELECT
    p.matricula AS matricula_aluno,
    p.nome AS nome_aluno
FROM
    aluno AS a
JOIN
    pessoa AS p ON ( p.matricula = a.matricula );

Output:

| matricula_aluno | nome_aluno |
|-----------------|------------|
|            1000 |       JOSE |
|            1234 |       JOAO |
|            7777 |      JESUS |
|            9876 |      MARIA |

Teacher Inquiry:

SELECT
    pes.matricula AS matricula_professor,
    pes.nome AS nome_professor
FROM
    professor AS prof
JOIN
    pessoa AS pes ON ( pes.matricula = prof.matricula );

Output:

| matricula_professor |  nome_professor |
|---------------------|-----------------|
|                2222 | ALBERT EINSTEIN |
|                5555 |    ISAAC NEWTON |

General people consultation:

SELECT
    pes.matricula AS matricula_pessoa,
    pes.nome AS nome_pessoa,
    (CASE
       WHEN prof.matricula IS NOT NULL THEN 'PROFESSOR'
       WHEN a.matricula IS NOT NULL THEN 'ALUNO'
       ELSE ''
     END) AS tipo_pessoa
FROM
    pessoa AS pes
LEFT JOIN
    aluno AS a ON ( pes.matricula = a.matricula )
LEFT JOIN
    professor AS prof ON ( pes.matricula = prof.matricula )

Output:

| matricula_pessoa |     nome_pessoa | tipo_pessoa |
|------------------|-----------------|-------------|
|             1000 |            JOSE |       ALUNO |
|             1234 |            JOAO |       ALUNO |
|             2222 | ALBERT EINSTEIN |   PROFESSOR |
|             5555 |    ISAAC NEWTON |   PROFESSOR |
|             7777 |           JESUS |       ALUNO |
|             9876 |           MARIA |       ALUNO |

SQLFiddle: link