Query em SQL

06/11/2019

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.

Daniel

Curtir t�pico + 0

Responder

Posts

06/11/2019

Emerson Nascimento

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

Gostei + 0

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!

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

Gostei + 0

07/11/2019

Emerson Nascimento

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

Gostei + 0

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!

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

Gostei + 0

11/11/2019

Emerson Nascimento

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

Gostei + 0