Centro Federal de Educação Tecnológica de Mato Grosso

UAB-CEFET-MT

CURSO DE TECNÓLOGO EM SISTEMAS PARA

INTERNET
MODALIDADE A DISTÂNCIA

DISCIPLINA: FUNDAMENTOS DE BANCO DE DADOS

Professor Autor: Inara Aparecida Ferrer Silva

Material Didático: Fundamentos de Banco de Dados

 Centro Federal de Educação Tecnológica de Mato Grosso
UAB-CEFET-MT

Unidade VIII – RECUPERANDO DADOS DE DUAS OU MAIS

TABELAS

• VISÃO GERAL DA UNIDADE

Até agora trabalhamos a recuperação de dados em uma única tabela,
mas o conceito de banco de dados reúne várias tabelas.
O objetivo nesta unidade é auxiliar o aluno a compreender como é
realizada a manipulação dos dados em duas ou mais tabelas e realizar
cálculos utilizando funções específica no comando select.
Ao final desta unidade, para finalizar nossos estudos na disciplina de
Fundamentos de Banco de Dados, faremos um pequeno estudo de caso
envolvendo o conhecimento adquirido nesta. Esperamos que você tenha
aproveitado este material e desejamos que continue firme nos estudos!

Objetivo Específico

Propomos, nesta unidade, levar você a:

• Entender a busca de dados, com os comandos da DML- Linguagem de

Manipulação de Dados, acessando duas ou mais tabelas.
• Realizar cálculos utilizando funções no comando select
• Estudo de caso : cadastro de aulas

Unidade VIII – RECUPERANDO DADOS DE DUAS OU MAIS

TABELAS

8. Recuperando dados de duas ou mais tabelas

8.1 utilizando consultas encadeadas (subqueries)
8.2 funções no comando select
8.3 Estudo de Caso

8. RECUPERANDO DADOS DE DUAS OU MAIS TABELAS

Material Didático: Fundamentos de Banco de Dados

 Centro Federal de Educação Tecnológica de Mato Grosso
UAB-CEFET-MT

Até o momento, vimos como selecionar um conjunto de registros em uma

tabela, atendendo a uma determinada condição definida pelo comando ou não.
A condição é opcional, podem-se selecionar todos os registros sem nenhuma
condição.

Exemplos:

Selecione todos os alunos da tabela aluno:

SELECT *
FROM aluno;

Selecione os alunos que residem a ‘Rua 1 bairro cpa’:

SELECT *
FROM aluno
WHERE endereço=‘Rua 1 bairro cpa’;

Selecione o nome das disciplinas cuja carga horária é maior que ‘40
horas’:

SELECT nomedisc, cargahoraria

FROM disciplina
WHERE cargahoraria >’40 horas’;

A partir daqui vamos utilizar o conjunto de tabelas a seguir para ilustrar os

exemplos do comando select e introduzir novas funcionalidades. Faremos a
consulta dos dados envolvendo várias tabelas.

Tabela cliente
ccliente nomecliente Endereço Crédito
110 Alicia Rua G 10

Material Didático: Fundamentos de Banco de Dados

 Centro Federal de Educação Tecnológica de Mato Grosso
UAB-CEFET-MT

111 Marta Rua 3 5

112 Ivan Rua 4 8
113 Celso Rua 2 9
Tabela item
citem nomeitem preço
1 Caderno R$4,00
2 Pasta R$3,00
3 Lápis cor R$ 3,50
4 caneta R$ 1,50
Tabela pedido
npedido ccliente citem quantidade total
10 110 1 4 R$ 16,00
11 110 2 10 R$ 30,00
12 111 2 10 R$ 30,00
13 113 3 5 R$ 7,50

Para consultar o nome do cliente e todos os seus pedidos acrescentamos

neste comando a comparação da chave primária de uma das tabelas com a
chave estrangeira da outra, ou seja, só podemos consultar dados de tabelas
relacionadas. Para isto utiliza-se o qualificador de nome.

O qualificador de nome consiste no nome da tabela seguido de um ponto

e o nome da coluna na tabela. Exemplo, o qualificador de nome para a coluna
npedido da tabela pedido será:

Sintaxe: <nome Tabela>.<nome coluna na tabela especificada>

• [Link]

• [Link]

Exemplo de uma consulta usando qualificador para acessar duas tabelas:

SELECT nomecliente, npedido, ccliente

Material Didático: Fundamentos de Banco de Dados
 Centro Federal de Educação Tecnológica de Mato Grosso
UAB-CEFET-MT

FROM pedido, cliente

WHERE [Link]=[Link];

Exemplo de uma consulta para acessar os pedidos de um cliente com

total maior que R$10,00 reais.

SELECT nomecliente, npedido, ccliente, total

FROM pedido, cliente
WHERE [Link]=[Link] and total >10;

Outra opção nesta consulta é a possibilidade de incluir aliases

(sinônimos) para o nome das tabelas para evitar a digitação a todo momento e
facilitar o trabalho. Eles são inseridos na cláusula FROM.

SELECT nomecliente, npedido, ccliente

FROM Pedido P, Cliente C
WHERE [Link]=[Link];

SELECT citem, nomeitem, npedido, quantidade

FROM Pedido P, Item I
WHERE [Link]=[Link] and quantidade>5;

8.1 UTILIZANDO CONSULTAS ENCADEADAS (subqueries)

Uma subquery , de forma simples, é quando o resultado de uma consulta

é usado por outra no mesmo comando SQL. Imagine a busca dos itens dos
pedidos cuja quantidade vendida é maior que 4.

SELECT citem, nomeitem

FROM Item
WHERE citem IN
(select citem
From pedido
Material Didático: Fundamentos de Banco de Dados
 Centro Federal de Educação Tecnológica de Mato Grosso
UAB-CEFET-MT

WHERE quantidade>5);

EX: Quais os nomes dos itens que não estão em nenhum pedido?

SELECT citem, nitem

FROM item
WHERE citem NOT IN
(SELECT * FROM PEDIDO
WHERE [Link]=[Link]);

Selecione os clientes que não tem feito pedido na empresa:

SELECT ccliente, ncliente

FROM cliente
WHERE ccliente NOT IN
(SELECT npedido, ccliente FROM PEDIDO
WHERE [Link]=[Link]);

Material Didático: Fundamentos de Banco de Dados

 Centro Federal de Educação Tecnológica de Mato Grosso
UAB-CEFET-MT

8.2 FUNÇÕES NO COMANDO SELECT

a) COUNT(*)

COUNT(DISTINCT <nome-campo>)

Retorna a quantidade de registros existentes no campo especificado.

Quando a opção * é utilizada o resultado é a quantidade de registros
existentes.

Quando se coloca o nome de um campo, a função count retorna a

quantidade de valores existentes na coluna do campo.

Exemplo:

Conte o número de clientes da empresa.

SELECT COUNT(*)
FROM cliente;

A função Count (distinct <nome campo>) conta os registros diferentes,

sem repetição. Neste exemplo desejamos saber quem são os clientes que
realmente têm realizado pedido na empresa.

SELECT Count (distinct ccliente)

FROM pedido;

Selecionem os itens que aparecem mais de duas vezes nos pedidos.

SELECT citem, npedido
FROM pedido
GROUP BY citem
HAVING count(*) >2;

b) SUM <nome-campo>

Material Didático: Fundamentos de Banco de Dados

 Centro Federal de Educação Tecnológica de Mato Grosso
UAB-CEFET-MT

Retorna a soma dos valores existentes no campo especificado. Quando a

opção DISTINCT é utilizada são considerados apenas os diferentes valores
existentes no campo.

Exemplos:

Selecione a soma dos pedidos por cliente.

SELECT ccliente, npedido, sum(total)

FROM pedido
Group by ccliente;

Mostre o cliente, o item e a quantidade dos produtos comprados.

SELECT ccliente, citem, sum(quantidade)
FROM pedido;

c) AVG <nome-campo>

Retorna a média aritmética dos valores existentes no campo

especificado. Quando a opção DISTINCT é utilizada são considerados apenas
os valores distintos existentes no campo.

Exemplo:

Selecione os clientes que possuem número de crédito acima da média

dos clientes.

SELECT ccliente, ncliente

FROM cliente
WHERE credito > (SELECT AVG(credito) FROM cliente);

Selecionar os itens que vendem mais do que a média das quantidades

vendidas.

SELECT citem, nomeitem

FROM pedido, item
Material Didático: Fundamentos de Banco de Dados
 Centro Federal de Educação Tecnológica de Mato Grosso
UAB-CEFET-MT

WHERE ([Link]=[Link]) and quantidade >

(SELECT AVG(quantidade) FROM pedido);

SELECT ccliente, npedido

FROM pedido
WHERE total >=(SELECT avg(total) FROM PEDIDO );

d) MAX <nome-campo>

Retorna o maior valor existente no campo especificado. Quando a opção

DISTINCT é utilizada são considerados apenas os valores sem repetição
existentes no campo.

Exemplo:

Selecione o cliente que tem o maior número de crédito

SELECT ccliente, ncliente, Max(credito)
FROM cliente;

Selecione o pedido com maior quantidade de item comprado

SELECT npedido, citem, Max(quantidade)
FROM pedido;

Selecione o cliente que efetuou o maior valor de pedido

SELECT ccliente, citem, Max(total)
FROM pedido;

e) MIN <nome-campo>

Retorna o menor valor existente no campo especificado. Quando a

opção DISTINCT é utilizada são considerados apenas os diferentes valores
existentes no campo.

Exemplo:

Material Didático: Fundamentos de Banco de Dados

 Centro Federal de Educação Tecnológica de Mato Grosso
UAB-CEFET-MT

Selecionar o cliente com menor valor de crédito

SELECT ccliente, ncliente, Min(credito)

FROM cliente;

Selecionar o pedido com menor quantidade de item vendido

SELECT npedido, ccliente, citem, Min(quantidade)
FROM pedido;

Selecionar o pedido com menor valor total

SELECT ccliente, citem, Min(total)
FROM pedido;

8.3 ESTUDO DE CASO: Cadastro de aulas

Vamos agora ao final do nosso módulo resumir o projeto de BD com todas as

suas fases. Faremos um estudo de caso: um cadastro de aulas para uma sala
de aula.
Uma sala de aula está ocupada o dia inteiro. Cada aula é ministrada por um
professor em uma respectiva sala. Um aluno pode assistir aula em várias salas,
de disciplinas diferentes e em dias diferentes também. Cada sala pode ter
vários alunos assistindo aula nela. Segue o projeto conceitual, lógico e físico.
No projeto físico mostraremos apenas os códigos da linguagem SQL, que pode
ser utilizado em vários SGBD.

PROJETO CONCEITUAL

Material Didático: Fundamentos de Banco de Dados

 Centro Federal de Educação Tecnológica de Mato Grosso
UAB-CEFET-MT

Para a situação descrita anteriormente elaboramos o DER na ferramenta

DBdesigner e escolhemos a notação pé-de–galinha ou crows-foot.

As notações na ferramenta podem ser modificadas em Display Notation,

existem várias opções( ER, Tradicional, Crow foot ) depois que o modelo está
pronto podem ser alteradas sem problema. A notação Crow-foot ou pés-de-
galinha representando 1 :N como :

Lado 1

Lado N
Material Didático: Fundamentos de Banco de Dados
 Centro Federal de Educação Tecnológica de Mato Grosso
UAB-CEFET-MT

Projeto Lógico
Para o projeto lógico vamos mostrar todas as tabelas transformadas (segundo
as regras que aprendemos) e posteriormente aplicar as formas normais.
Tabela aluno Tabela aula Tabela professor Tabela sala
Nome(CP) Numeroaula(CE) Nomeprofessor(CP Numesala (CP)
)
Idade Data Telefone
Sexo Modalidade
Instituição Nomeprofessor(CE
)
Telefone Numerosala(CE)

Tabela aluno_sala
Nome(CP)
Numero aula(CP)
Os dois campos juntos são a chave primária desta tabela que surge do
relacionamento N:N(muitos-para-muitos). Isoladamente eles são chave
estrangeira vindos de outra tabela.

Aplicando as 3 (três) FN percebe-se que as tabelas já se encontram de acordo

com as regras.

Projeto Físico
O projeto físico neste exemplo resume-se aos códigos da linguagem SQL.

CREATE TABLE aluno (

nome VARCHAR(20) NOT NULL AUTO_INCREMENT,
idade VARCHAR(20) NULL,
sexo VARCHAR(20) NULL,
instituicao VARCHAR(20) NULL,
telefone VARCHAR(20) NULL,
PRIMARY KEY(nome));

CREATE TABLE aluno_Aula (

aluno_nome VARCHAR(20) NOT NULL,
Aula_numerosala INTEGER NOT NULL,
PRIMARY KEY(aluno_nome, Aula_numeroaula),
INDEX aluno_has_Aula_FKIndex1(aluno_nome),
Material Didático: Fundamentos de Banco de Dados
 Centro Federal de Educação Tecnológica de Mato Grosso
UAB-CEFET-MT

INDEX aluno_has_Aula_FKIndex2(Aula_numerosala));

CREATE TABLE professor (

nomeprof VARCHAR(20) NOT NULL AUTO_INCREMENT,
telefone VARCHAR(20) NULL,
PRIMARY KEY(nomeprof));

CREATE TABLE sala (

numerosala INTEGER NOT NULL AUTO_INCREMENT,
PRIMARY KEY(numerosala));

CREATE TABLE Aula (

numeroaula INTEGER NOT NULL AUTO_INCREMENT,
professor_nomeprof VARCHAR(20) NOT NULL,
sala_numerosala INTEGER NOT NULL,
dataaula DATE NULL, modalidade VARCHAR(20) NULL,
PRIMARY KEY(numerosala),
INDEX Aula_FKIndex1(sala_numerosala),
INDEX Aula_FKIndex2(professor_nomeprof));

Exercícios de auto-avaliação:
1) Com relação a DML-Data Manipulation Language escreva os códigos para:

Tabela Aluno Tabela Curso Tabela Instituição

*Codaluno *Codcurso *Codinstituicao
Nomealuno Nomecurso Nomeinstituicao
Codcurso (assist. Pesquisa) valorcurso
Anoconclusao
Codinstituicao (assist. Pesquisa)
Faixaetaria

1.1) Selecione todos os cursos cujos nomes terminem com a letra ‘a’.
1.2) Selecione todos os alunos na faixa etária de 15 a 18 anos.
1.3)Selecione os alunos, o ano de conclusão do curso e o nome da instituição
onde concluiu seus estudos.
1.4) Selecione todos os alunos que terminam o curso entre o ano de 2000 e
2005.
1.5) Selecione todos alunos que fazem cursos cujo valor é superior a R$
300,00.

2) Dadas as tabelas Funcionários (codfuncionario, nomefuncionario, endereço,

cidade, salario, codempresa) e Empresa (codempresa, nomeempresa,
endempresa, cidempresa), faça os exercícios a seguir.

Material Didático: Fundamentos de Banco de Dados

 Centro Federal de Educação Tecnológica de Mato Grosso
UAB-CEFET-MT

2.1) Dê o nome, a rua e a cidade de todos os funcionários que trabalham para

BBB Bank Corporation e ganham mais do que R$10.000,00 reais.

2.2) Dê o nome dos funcionários que não trabalham para a BBB Bank
Corporation.
2.3) Selecione o nome do funcionário, a empresa em que trabalha e o seu
salário.
2.4)Selecione o nome do funcionário que possui o maior salário da BBB Bank
Corporation.
2.5) Selecione os funcionários que ganham mais do que a média salarial da
BBB Bank Corporation.
2.6) Conte o número de funcionários que ganham mais do que R$ 2000,00
reais.
2.7) Selecione os funcionários que moram na cidade de Cuiabá ou trabalham
na empresa ‘FG Corporation’.
2.8) Selecione o nome das empresas que atuam na cidade de Cuiabá.
2.9) Selecione os funcionários que moram na cidade de Cuiabá, trabalham na
empresa ‘FG Corporation’ e recebem menos de R$1000,00 reais.
2.10) Selecione os funcionários que moram na cidade de Cuiabá, não
trabalham na empresa ‘BBB Bank Corporation’ e recebem mais de R$ 4000,00
reais.

BIBLIOGRAFIA

MACHADO, Nery R.;ABREU, Maurício P. Projeto de Banco de Dados: uma

visão prática. São Paulo: Editora Érica, 1996.
SILBERCHATZ, A.; KORTH, H. F.; SUDARSHAN, S. Sistema de Banco de
Dados- Tradução Daniel Vieira. Rio de Janeiro. Editora Elsevier, 2006.
MACHADO, Felipe N. R. Banco de Dados: Projeto e Implementação. São
Paulo, Érica : 2004.
TOREY, Toby J.; LIGHTSTONE, S.; NADEAU, T. Projeto e Modelagem de
Banco de Dados – Tradução de Daniel Vieira. Rio de Janeiro: Elsevier,
2007.

Material Didático: Fundamentos de Banco de Dados