Comandos SQL para consultas em banco de dados de clínica médica

Prévia do material em texto

BANCO DE DADOS 
9a aula 
 
 
Lupa 
 
 
 
Vídeo 
 
PPT 
 
MP3 
 
 
 
Exercício: CCT0782_EX_A9_1_V2 07/05/2019 (Finaliz.) 
Aluno(a): 
Disciplina: CCT0782 - BANCO DE DADOS 
 
 
 
 
 1a Questão 
 
 
- Considere o banco de dados relacional de uma clínica médica, 
no qual as chaves primárias (incrementada automaticamente) e 
estrangeiras estão sublinhadas. 
 
*Obs. A numeração automática iniciará com 1. 
 
PACIENTE (id_pac, nome_pac, sexo, fone_pac) 
ESPECIALIDADE (id_espec, nome_espec) 
MEDICO (id_med, nome_med, fone_med, id_espec) 
CONSULTA (id_cons, dt_cons, vl_cons, id_pac, id_med) 
 
Dê o comando correspondente de Selecionar nome do paciente, sexo do paciente, 
valor da consulta para todos os pacientes. 
 
 
 Select T1.nome_pac, T1.sexo, T2.vl_cons From PACIENTE T1, CONSULTA T2 
Where T1. id_pac >= T2. id_pac 
 Select T1.nome_pac, T1.sexo, T2.vl_cons From PACIENTE T1, CONSULTA T2 
Where T1. id_pac <= T2. id_pac 
 Select T1.nome_pac, T1.sexo, T2.vl_cons From PACIENTE T1, CONSULTA T2 
Where T1. id_pac = T2. id_pac 
 Select T1.nome_pac, T1.sexo, T2.vl_cons From PACIENTE T1, CONSULTA T2 
Where T1. id_pac > T2. id_pac 
 Select T1.nome_pac, T1.sexo, T2.vl_cons From PACIENTE T1, CONSULTA T2 
Where T1. id_pac <> T2. id_pac 
 
 
 
 
 
 2a Questão 
 
 
Considere as seguintes tabelas: 
 CLIENTE (idCli, NomeCli) 
 PEDIDO (idPEDIDO, Data, idCli, ValorPedido) 
Qual alternativa apresenta os clientes que NÃO tiveram nenhum pedido? 
 
 
 
SELECT NomeCli FROM CLIENTE C RIGHT JOIN PEDIDO P ON C.idCli = P.idCli 
 
SELECT NomeCli FROM CLIENTE C LEFT JOIN PEDIDO P ON C.idCli = P.idCli 
 
SELECT * FROM CLIENTE 
 
SELECT * FROM CLIENTE WHERE idCli IN (SELECT idCli FROM PEDIDO) 
 SELECT * FROM CLIENTE WHERE idCli NOT IN (SELECT idCli FROM PEDIDO) 
 
 
Explicação: sub-select listando clientes com pedido 
 
 
 
 
 
 3a Questão 
 
 
- Considere o banco de dados relacional de uma clínica médica, no qual as chaves primárias 
(incrementada automaticamente) e estrangeiras estão sublinhadas. 
 
*Obs. A numeração automática iniciará com 1. 
 
 PACIENTE (id_pac, nome_pac, sexo, fone_pac) 
 ESPECIALIDADE (id_espec, nome_espec) 
 MEDICO (id_med, nome_med, fone_med, id_espec) 
 CONSULTA (id_cons, dt_cons, vl_cons, id_pac, id_med) 
 
Dê o comando correspondente de Selecionar nome do paciente, nome da especialidade, nome 
do médico, data de consulta para todos os pacientes. 
 
 
 Select T3.nome_med, T1.nome_pac, T2.nome_espec, T4.dt_cons, T4.vl_cons From PACIENTE 
T4, ESPECIALIDADE T3, MEDICO T2, CONSULTA T1 Where T1.id_pac = T4.id_pac And 
T4.id_med = T3.id_med And T3.id_espec = T2.id_espec 
 Select T1.nome_pac, T2.nome_espec, T3.nome_med, T4.dt_cons, T4.vl_cons From PACIENTE 
T4, ESPECIALIDADE T3, MEDICO T2, CONSULTA T1 Where T1.id_pac = T4.id_pac And 
T4.id_med = T3.id_med And T3.id_espec = T2.id_espec 
 Select T2.nome_espec, T1.nome_pac, T3.nome_med, T4.dt_cons, T4.vl_cons From PACIENTE 
T4, ESPECIALIDADE T3, MEDICO T2, CONSULTA T1 Where T1.id_pac = T4.id_pac And 
T4.id_med = T3.id_med And T3.id_espec = T2.id_espec 
 Select T1.nome_pac, T2.nome_espec, T3.nome_med, T4.dt_cons From PACIENTE T4, 
ESPECIALIDADE T3, MEDICO T2, CONSULTA T1 Where T1.id_pac = T4.id_pac And T4.id_med = 
T3.id_med And T3.id_espec = T2.id_espec 
 Select T1.nome_pac, T3.nome_med, T2.nome_espec, T4.dt_cons, T4.vl_cons From PACIENTE 
T4, ESPECIALIDADE T3, MEDICO T2, CONSULTA T1 Where T1.id_pac = T4.id_pac And 
T4.id_med = T3.id_med And T3.id_espec = T2.id_espec 
 
 
 
 
 
 4a Questão 
 
 
Qual comando abaixo representa o seguinte texto: Especifica a junção entre duas tabelas com uma cláusula de junção 
explícita. 
 
 
 
Select 
 
Order by 
 
Group by 
 
Where 
 Inner Join 
 
 
Explicação: 
Where: A cláusula Where estabelece uma condição que a linha deverá obedecer para que faça parte do conjunto 
resposta da consulta. No caso apenas retornam as linhas cujo deste da condição de como resposta verdadeiro. 
Select: Comando utilizado para recuperação dos dados de uma ou mais tabelas. 
Group by: A clausula GROUP BY da declaração do SELECT é usada para agrupar as linhas em menores grupos. 
Inner Join: Conecta as duas tabelas e retorna apenas as linhas que satisfazem a condição de junção. 
Order by: A clausula permite ordenar qualquer resultado de uma consulta. 
 
 
 
 
 
 5a Questão 
 
 
- Considere o banco de dados relacional de uma clínica médica, 
no qual as chaves primárias (incrementada automaticamente) e 
estrangeiras estão sublinhadas. 
 
*Obs. A numeração automática iniciará com 1. 
 
PACIENTE (id_pac, nome_pac, sexo, fone_pac) 
ESPECIALIDADE (id_espec, nome_espec) 
MEDICO (id_med, nome_med, fone_med, id_espec) 
CONSULTA (id_cons, dt_cons, vl_cons, id_pac, id_med) 
 
Dê o comando correspondente de Selecionar nome do paciente, nome do médico 
onde a data da consulta é igual à data de hoje. 
 
 
 Select T1.nome_pac, T2.nome_med, T2.dt_cons From PACIENTE T1, MEDICO T2, 
CONSULTA T3 Where T3.dt_cons < GETDATE() And T3.id_pac = T1.id_pac And T3.id_med 
= T2.id_med 
 Select T1.nome_pac, T2.nome_med, T2.dt_cons From PACIENTE T1, MEDICO T2, 
CONSULTA T3 Where T3.dt_cons = GETDATE() And T3.id_pac = T1.id_pac And T3.id_med 
= T2.id_med 
 Select T1.nome_pac, T2.nome_med, T2.dt_cons From PACIENTE T3, MEDICO T1, 
CONSULTA T2 Where T3.dt_cons != GETDATE() And T3.id_pac = T1.id_pac And T3.id_med 
= T2.id_med 
 Select T1.nome_pac, T2.nome_med, T2.dt_cons From PACIENTE T3, MEDICO T2, 
CONSULTA T1 Where T3.dt_cons = GETDATE() And T3.id_pac = T1.id_pac And T3.id_med 
= T2.id_med 
 Select T1.nome_pac, T2.nome_med, T2.dt_cons From PACIENTE T2, MEDICO T1, 
CONSULTA T3 Where T3.dt_cons = GETDATE() And T3.id_pac = T1.id_pac And T3.id_med 
= T2.id_med 
 
 
 
 
 
 6a Questão 
 
 
(PRODEB 2008) Analise as assertivas e assinale a alternativa que apresenta as corretas. 
I. O comando create table é usado para especificar uma nova relação, dando-lhe um nome e especificando seus 
atributos e restrições iniciais. 
II. É possível definir um valor default para um atributo por meio da adição da cláusula default seguida de seu valor na 
definição de um atributo. 
III. Pode-se limitar os valores do atributo ou de seu domínio pelo uso da cláusula check, seguida da definição do 
atributo ou do domínio. 
IV. O conceito de junção de tabelas (ou junção de relações) " join " foi incorporado à SQL para que os usuários 
pudessem especificar uma tabela que fosse resultado da aplicação da operação de junção na cláusula where de uma 
consulta. 
 
 
 Apenas III está incorreta. 
 
Apenas IV está correta. 
 Apenas I, II e III estão corretas. 
 
Nenhuma assertiva está correta. 
 
Apenas I, II, III e IV estão corretas. 
 
 
Explicação: 
IV. Um comando SELECT pode fazer uma consulta que traz dados de duas ou mais tabelas. Esse é um processo 
chamado de Junção (join). As tabelas têm uma coluna em comum que é usado para fazer as junções. 
 
 
 
 
 
 7a Questão 
 
 
 
 
 
 
SELECT DISTINCT NOMEP "Nome do Professor" FROM PROFESSOR P , TCC T, ALUNO A; 
 SELECT DISTINCT NOMEP "Nome do Professor" FROM PROFESSOR P , TCC T, ALUNO A WHERE P.MATP = A.MATP 
AND A.MATA = T.MATA; 
 
SELECT NOMEP "Nome do Professor" FROM PROFESSOR P , TCC T, ALUNO A WHERE P.MATP = A.MATP AND 
A.MATA = T.MATA; 
 
SELECT DISTINCT NOMEP FROM PROFESSOR P , TCC T, ALUNO A; 
 SELECT NOMEP FROM PROFESSOR P , TCCT, ALUNO A WHERE P.MATP = A.MATP AND A.MATA = T.MATA; 
 
 
Gabarito 
Coment. 
 
 
 
 
 8a Questão 
 
 
De acordo com a estrutura das tabelas abaixo, escolha a opção que mostre o nome de cada empregado e o seu salário, 
daqueles que ganham o maior salário do seu departamento, através da linguagem SQL. O exemplo do banco de dados 
é composto por 2 tabelas: a tabela EMP que contém dados dos empregados, cuja chave primária é EMPNO (matrícula 
do empregado) e a tabela DEPT que contém dados dos departamentos, cuja a chave primária é DEPTNO. A coluna 
GERENTE é chave estrangeira na tabela EMP, referente à própria tabela EMP. A coluna DEPTNO na tabela EMP é chave 
estrangeira, referente à tabela DEPT. 
 
ESTRUTURA DAS TABELAS DO BANCO DE DADOS 
TABELA EMP 
Name Null Type 
------------------------------------------ 
EMPNO NOT NULL NUMBER(4) 
NOME_EMP CHAR(10) 
CARGO CHAR (9) 
GERENTE NUMBER(4) 
SALARIO NUMBER(7,2) 
COMISSAO NUMBER(7,2) 
DEPTNO NUMBER(2) 
TABELA DEPT 
Name Null Type 
------------------------------------------ 
DEPTNO NOT NULL NUMBER(2) 
NOME_DEPT CHAR(14) 
LOCALIZACAO CHAR(13) 
 
 
 
SELECT E.NOME_EMP, MAX(E.SALARIO) 
 FROM EMP E 
 GROUP BY E.ENOME; 
 
SELECT E.NOME_EMP, E.SALARIO 
 FROM EMP E 
 WHERE E.SALARIO=(SELECT MAX(SALARIO) 
 FROM EMP); 
 SELECT E.NOME_EMP, E.SALARIO 
FROM EMP E 
WHERE E.SALARIO=(SELECT MAX(SALARIO) 
 FROM EMP 
 WHERE DEPTNO=E.DEPTNO); 
 
 
SELECT E.NOME_EMP, MAX(E.SALARIO) 
 FROM EMP E 
 GROUP BY E.ENOME 
 HAVING SALARIO=MAX(SALARIO); 
 
SELECT E.NOME_EMP, E.SALARIO 
 FROM EMP E 
 WHERE E.SALARIO=(SELECT MAX(SALARIO) 
 FROM DEPT 
 WHERE DEPTNO=E.DEPTNO);