Query SQL to select student and student name

Asked

Viewed 876 times

2

I have this simple relational model, and I need a query that selects the student and the student’s name.

Modelo relacional

mysql sql database postgresql oracle

2 answers

1


Try it this way:

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

  • Show! Long time no working with sql! : ) I am downloading a course here to get back active!

  • 1

    hehe, I’m also in this was a while without messing with SQL to re-do a query now.

  • Is that right ? It works ? From which table did the field come matricula_aluno ? This type of query is running a CROSS JOIN between tables and does not answer the question. See only: http://sqlfiddle.com/#! 9/43afd8/20

1

Translating your model 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);

Register of Students:

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 );

Register of Teachers:

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 );

Exit:

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

Teacher consultation:

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

Exit:

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

General consultation of persons:

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 )

Exit:

| 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: http://sqlfiddle.com/#! 9/43afd8/18

Browser other questions tagged mysql sql database postgresql oracle

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