Queries SQL

[danielcruz733]

Boa tarde a todos,

estou fazendo um trabalho de banco de dados para a faculdade e devo dizer que BD não é o meu forte. Modelei um banco de dados referente a uma clínica odontológica e como parte do trabalho preciso responder a algumas queries, mas estou esbarrando em duas delas. Talvez vocês possam me ajudar (acredito que sim pois é algo a nível básico).

1. A primeira query é a seguinte:

Preciso listar o nome de todos os médicos trainees cujos testes de avaliação contem o termo "insuficiente". Além do nome do médico trainee, a consulta deve me retornar o seu ID, sua pontuação, a descrição do teste (suficiente, insuficiente...) e o nome do médico que fez a avaliação.

Segue as tabelas que estou trabalhando para esta query:

►empregado [id, nome] - esta tabela contém todos os funcionários da clínica, incluindo os médicos e médicos trainees
►medico_trainee [id_medico_trainee, id_medico_supervisor] - os dois atributos atuam como chaves estrangeiras, a primeira referencia o id da tabela médico e a segunda referencia o id da tabela médico_permanente
►medico [id] - id é chave primária e estrangeira que referencia id de empregado
►medico_permanente [id] - id é chave primária e estrangeira que referencia id de medico
►avaliacao [id, descricao, pontuacao] - id é chave primária e estrangeira que referencia id de medico_trainee

Na query abaixo eu consigo listar tudo o que preciso exceto o nome do médico que fez a avaliação (essa parte não consegui fazer)

select e.nome, e.id, a.pontuacao, a.descricao
from empregado e
join avaliacao a on a.id=e.id
where descricao=''insuficiente'' order by a.avaliacao desc;

2. A segunda query é a seguinte:

Preciso listar o nome, cidade e o id de todos os clientes da clínica cuja consulta o médico tenha diagnosticado "gingivitis" ou "periodontitis". Esta não me parece muito difícil mas também estou batendo cabeça. Segue abaixo as tabelas que estou trabalhando para esta query:

►cliente [id, nome, cidade]
►consulta [id_medico, id_cliente]
►resultado_consulta [id_medico, descricao] - em descrição é que o médico anota o diagnóstico: "gingivitis", "periodontitis", etc.

Se alguém tiver alguma dica agradeço.

[Leonardo Persan]

Eu não aconselho manter as tabelas medico e medico_permanente e sim criar um campo cargo ou funcao na tabela empregado para fazer essa diferença

Além de que chaves primárias devem ser só primárias, chaves estrangeiras só estrangeiras, mesmo que isso signifique ter mais dados e ocupar mais espaço no Banco de Dados.

O relacionamento cliente => consulta => resultado_consulta está errado, porque você fez vez de 1 para 1 em todos os casos e isso não é verdade.

Um Cliente pode não ter nenhuma ou ter muitas Consultas.
Um Médico pode não ter feito nenhuma ou ter muitas Consultas.

Mas desconsiderando essas alterações as consultas seriam...

Em 06/11/2019 em 14:02, danielcruz733 disse:

1. A primeira query é a seguinte:

Preciso listar o nome de todos os médicos trainees cujos testes de avaliação contem o termo "insuficiente". Além do nome do médico trainee, a consulta deve me retornar o seu ID, sua pontuação, a descrição do teste (suficiente, insuficiente...) e o nome do médico que fez a avaliação.

SELECT trainee.id , trainee.nome as trainee, avaliacao.pontuacao, avaliacao.descricao, supervisor.nome as supervisor

FROM medico_trainee mt

INNER JOIN empregado trainee ON mt.id_medico_trainee = trainee.id

INNER JOIN empregado supervisor ON mt.id_medico_supervisor = supervisor.id

INNER JOIN avaliacao ON trainee.id = avaliacao.id

WHERE avaliacao.descricao = "insuficiente"

Em 06/11/2019 em 14:02, danielcruz733 disse:

2. A segunda query é a seguinte:

Preciso listar o nome, cidade e o id de todos os clientes da clínica cuja consulta o médico tenha diagnosticado "gingivitis" ou "periodontitis". Esta não me parece muito difícil mas também estou batendo cabeça. Segue abaixo as tabelas que estou trabalhando para esta query:

SELECT cli.*

FROM cliente cli

INNER JOIN consulta con ON cli.id = con.id_cliente

INNER JOIN resultado_consulta res ON con.id_medico = res.id_medico

WHERE descricao IN ("gingivitis", "periodontitis")

Editado Novembro 13, 2019 por Leonardo Persan