Query em SQL

06/11/2019

22

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.
Responder

Posts

1. em algum lugar (deveria ser na tabela avaliacao) você precisa gravar o id do médico avaliador. não tem como listar se não foi gravado em lugar nenhum.
►avaliacao [id_trainee, descricao, pontuacao, id_medico] - id é chave primária e estrangeira que referencia id de medico_trainee

2.
►cliente [id, nome, cidade]
►consulta [id_medico, id_cliente] -- aqui é necessário um id, para referenciar na tabela de resultados
►resultado_consulta [id_medico, descricao] -- em descrição é que o médico anota o diagnóstico: "gingivitis", "periodontitis", etc. -- aqui falta uma referência à consulta (na verdade só precisa dessa referência, porque a partir daí chego na consulta e encontro o cliente e o médico
select distinct
	cli.id, cli.nome, cli.cidade
from
	consulta con
inner join cliente cli -- encontro o cliente a partir da consulta
	on cli.id = con.id_cliente
inner join medico med -- encontro o médico a partir da consulta
	on med.id = con.id_medico
inner join resultado_consulta res -- encontro o resultado a partir da consulta
	on res.id_consulta = con.id
where
	upper(res.descricao) like '%GINGIVITIS%' or upper(res.descricao) like '%PERIODONTITIS%'



Responder

07/11/2019

Daniel

Olá Emerson, obrigado pela resposta.
Sobre a primeira query, o id do médico avaliador está gravado na tabela medico_trainee, na coluna supervisor.
Para ter uma melhor visualização do meu banco e ajudar no problema, tirei prints de algumas tabelas e coloquei no link abaixo.

http://ovoscaldas.com.br/bd.html

Eu realmente travei nessa queries, muito em parte por eu ter um perfil iniciante em SQL.

Obrigado!

1. em algum lugar (deveria ser na tabela avaliacao) você precisa gravar o id do médico avaliador. não tem como listar se não foi gravado em lugar nenhum.
►avaliacao [id_trainee, descricao, pontuacao, id_medico] - id é chave primária e estrangeira que referencia id de medico_trainee

2.
►cliente [id, nome, cidade]
►consulta [id_medico, id_cliente] -- aqui é necessário um id, para referenciar na tabela de resultados
►resultado_consulta [id_medico, descricao] -- em descrição é que o médico anota o diagnóstico: "gingivitis", "periodontitis", etc. -- aqui falta uma referência à consulta (na verdade só precisa dessa referência, porque a partir daí chego na consulta e encontro o cliente e o médico
select distinct
	cli.id, cli.nome, cli.cidade
from
	consulta con
inner join cliente cli -- encontro o cliente a partir da consulta
	on cli.id = con.id_cliente
inner join medico med -- encontro o médico a partir da consulta
	on med.id = con.id_medico
inner join resultado_consulta res -- encontro o resultado a partir da consulta
	on res.id_consulta = con.id
where
	upper(res.descricao) like '%GINGIVITIS%' or upper(res.descricao) like '%PERIODONTITIS%'



Responder
1.
select
	tr.nome Trainee, tr.id IdTrainee,
	a.pontuacao, a.descricao,
	med.nome Avaliador, med.id idAvaliador
from
	empregado tr
join avaliacao a on
	a.id = tr.id
join medico_trainee mt on
	mt.id_medico_trainee = tr.id
join empregado med on
	med.id = mt.id_medico_supervisor
where
	a.descricao = 'insuficiente'
order by
	a.avaliacao desc


Responder

08/11/2019

Daniel

Obrigado Emerson! A query funcionou corretamente. Também consegui fazer a outra. Muito obrigado.

Para finalizar, tem apenas mais uma query que eu não consegui fazer (são 10). Esta pede para eu apresentar o número médio de enfermeiras/assistentes, procedimentos, códigos de diagnóstico e prescrições envolvidas nas consultas do ano de 2019, respectivamente para os clientes pertencentes a dois grupos: menor ou igual a 18 anos e maiores

Para ajudar, coloquei as tabelas que estou usando em http://ovoscaldas.com.br/bd.html

Eu tentei fazer como abaixo, mas a média não está saindo. Se tiver alguma dica fico agradecido.

select count(distinct consultation_assistant.VAT_nurse) as nurses, 
count(distinct procedure_in_consultation.name) as procedures, 
count(distinct prescription.ID) 
as IDprescriptions, 
count(distinct prescription.name) as nameprescriptions 
from consultation_assistant, procedure_in_consultation, prescription, client, appointment, consultation 
where consultation_assistant.date_timestamp=procedure_in_consultation.date_timestamp 
and prescription.date_timestamp=consultation_assistant.date_timestamp 
and year(consultation_assistant.date_timestamp)=2019
and consultation_assistant.VAT_doctor=procedure_in_consultation.VAT_doctor 
and prescription.VAT_doctor=procedure_in_consultation.VAT_doctor 
and consultation_assistant.date_timestamp=appointment.date_timestamp and appointment.VAT_client=client.VAT 
and consultation.date_timestamp=appointment.date_timestamp group by client.age>18;


Abs!

1.
select
	tr.nome Trainee, tr.id IdTrainee,
	a.pontuacao, a.descricao,
	med.nome Avaliador, med.id idAvaliador
from
	empregado tr
join avaliacao a on
	a.id = tr.id
join medico_trainee mt on
	mt.id_medico_trainee = tr.id
join empregado med on
	med.id = mt.id_medico_supervisor
where
	a.descricao = 'insuficiente'
order by
	a.avaliacao desc


Responder
acho que você pode se orientar por esta instrução:
select 
	(nurses / consultations) avg_nurses,
	(procedures / consultations) avg_procedures,
	(IDprescriptions / consultations) avg_IDprescriptions,
	(nameprescriptions / consultations) avg_nameprescriptions
from
	(select
		count(distinct ca.VAT_nurse) as nurses,
		count(distinct pic.name) as procedures,
		count(distinct p.ID) as IDprescriptions,
		count(distinct p.name) as nameprescriptions,
		count(distinct c.campo_id_consulta) consultations
	from
		consultation_assistant ca
	inner join
		procedure_in_consultation pic on pic.date_timestamp = ca.date_timestamp
			and pic.VAT_doctor = ca.VAT_doctor
	inner join
		prescription p on p.date_timestamp = ca.date_timestamp
			and p.VAT_doctor = pic.VAT_doctor 
	inner join
		appointment a on a.date_timestamp = ca.date_timestamp
	inner join
		client cl on cl.VAT = a.VAT_client
	inner join
		consultation c on c.date_timestamp = a.date_timestamp
	where
		year(ca.date_timestamp) = 2019
		and cl.age > 18
) tabela
Responder