BANDO DE DADOS II - ATPS

BANDO DE DADOS II

ATPS - Terceira Etapa

1) FAÇA UMA CONSULTA QUE APRESENTE UMA LISTAGEM PARA TODOS OS ATENDIMENTOS REGISTRADOS. APRESENTE O NOME DO PACIENTE, CIDADE, NOME DO MÉDICO, CRM, DIAGNÓSTICO, TRATAMENTO E DATA DO ATENDIMENTO. APRESENTE AS COLUNAS DA TABELA DE FORMA A DIFERENCIAR OS NOMES DOS MÉDICOS DOS PACIENTES. ORDENE APRESENTANDO NO TOPO DA TABELA OS ATENDIMENTOS MAIS RECENTES.

SELECT p.nome AS 'Nome Paciente', p.cidade, m.nome AS 'Nome Medico', m.CRM, a.diagnostico, a.tratamento, a.data

FROM paciente p, medico m, atendimento a

ORDER BY a.data ASC;

2) FAÇA UMA CONSULTA QUE RETORNE UMA LISTAGEM COM O NOME DE TODAS AS ESPECIALIDADES MÉDICAS E A QUANTIDADE DE MÉDICOS PARA CADA UMA. APRESENTE NO TOPO AS ESPECIALIDADES MAIS FREQUENTES. NENHUMA ESPECIALIDADE MÉDICA DEVE FICAR DE FORA DESSA LISTAGEM.

SELECT e.nome_especialidade, COUNT(m.id_medico) AS 'Quantidade de Medicos Cadastrados'

FROM especialidade_medica e LEFT OUTER JOIN medico_especialidade y on e.id_especialidade = y.especialidade_fk

left join medico m on y.medico_fk = m.id_medico

GROUP BY m.id_medico, e.nome_especialidade

3) FAÇA UMA CONSULTA QUE RETORNE OS MÉDICOS QUE ATENDERAM PACIENTES DO ESTADO DO RIO DE JANEIRO. A CONSULTA DEVE GARANTIR QUE O NOME E O CRM DO MÉDICO NÃO IRÃO APARECER MAIS DE UMA VEZ.

SELECT m.nome, m.crm

FROM medico m, atendimento a, paciente p

WHERE m.id_medico = a.medico_fk

AND a.paciente_fk = p.id_paciente

GROUP BY m.nome, m.crm

4) FAÇA UMA CONSULTA QUE APRESENTE UMA LISTAGEM COM O NOME DE TODAS AS ESPECIALIDADES MÉDICAS QUE NÃO SE ENCONTRAM ASSOCIADAS A NENHUM MÉDICO.

SELECT e.nome_especialidade, COUNT(m.id_medico)

FROM especialidade_medica e LEFT OUTER JOIN medico_especialidade y on e.id_especialidade = y.especialidade_fk

left join medico m on y.medico_fk = m.id_medico

where y.especialidade_fk = null

GROUP BY m.id_medico, e.nome_especialidade

5) FAÇA UMA CONSULTA QUE APRESENTE A LISTAGEM DE TODOS OS PLANOS DE SAÚDE E SUA QUANTIDADE DE CLIENTES. A QUANTIDADE DE CLIENTES QUE NÃO POSSUEM PLANO DE SAÚDE DEVE ESTAR NESSA LISTAGEM ASSOCIADA À NULL. A PRIMEIRA COLUNA DEVE SER O NOME DA EMPRESA DO PLANO, A SEGUNDA COLUNA O TIPO DO PLANO, E A TERCEIRA COLUNA DEVE SER A QUANTIDADE DE CLIENTES QUE O PLANO POSSUI. ORDENE DE FORMA A APRESENTAR NO TOPO DA TABELA OS PLANOS QUE POSSUEM MAIS CLIENTES.

select p.nome_empresa, p.tipo, count(y.id_paciente) AS 'Quantidade de clientes'

from