Projeto e Administração de Banco de Dados

Pesquisas em Tabelas

Projeto e Manutenção de Banco de Dados 2

2

Objetivo

Nesta unidade você vai estudar sobre pesquisas em linguagem SQL incluindo comandos

de junção de tabelas.

Introdução

Agora que temos o Banco de Dados criado, as tabelas corretamente definidas e inclusive

já sabemos como inserir, alterar e excluir dados dentro dele, poderemos trabalhar com os dados

de maneira mais específica. Agora poderemos trazer relatórios das nossas tabelas, usando o

comando SELECT (com todas as suas opções).

Comando Select

O Comando SELECT serve para fazer uma pesquisa no banco de dados, resultando em

uma listagem de dados. Esta listagem pode ser para consultar algum dado específico, para gerar

um relatório

Inicialmente, poderíamos fazer a seguinte sintaxe básica deste comando:

SELECT campos FROM tabela WHERE critérios ORDER BY campos

Vamos agora entender cada uma das partes que compõem o comando SELECT.

Inicialmente vale destacar a parte em azul! Ela é opcional. Tanto que, até a aula passada, nós

usávamos select sem esta parte!

O primeiro elemento que vamos ver é a definição dos campos, que vem logo após a palavra

SELECT. Possibilidades:

Campo1 - coloque o nome da coluna que deseja exibir

Campo1, campo2, campo3 - para inserir mais de uma coluna, basta separar elas

com vírgula!

* - o sinal asterisco serve para trazer todas as colunas disponíveis. Cuidado, este

comando pode gastar muitos recursos desnecessariamente

É por esta razão que desde a aula passada já testávamos o comando select com esta

sintaxe básica, usando apenas o asterisco (*).

Depois da palavra FROM devemos obrigatoriamente dizer o nome de uma (e somente uma)

tabela, de onde os dados virão. Se desejar obter dados de mais de uma tabela ao mesmo tempo

existem outras técnicas para isto (que veremos mais adiante).

Projeto e Manutenção de Banco de Dados 3

3

A clausula WHERE define uma condição que deve ser atendida para que a linha seja

exibida na consulta. Este WHERE é utilizado da mesma forma que usamos quando aprendemos

UPDATE e DELETE, lembra? Veja alguns exemplos:

SELECT * FROM CLIENTES WHERE IDADE > 18

o Retorna todas as colunas da tabela CLIENTES e apenas as linhas onde o

cliente tenha idade maior que 18 anos

SELECT NOME FROM ANIMAIS WHERE TIPO = 'GATO'

o Seleciona o nome de todos os animais cujo tipo é gato

SELECT FUN_CODIGO, FUN_NOME FROM TB_FUNCIONARIOS

o Seleciona o código e o nome de todos os funcionários (sem WHERE)

SELECT * FROM TB_BAIRROS WHERE BAI_CID_CODIGO = 1

o Seleciona todas as colunas da tabela de bairros apenas dos bairros que

tenham a coluna BAI_CID_CODIGO com valor 1

SELECT CLI_NOME FROM TB_CLIENTES WHERE CLI_BAI_CODIGO = 1

o Seleciona nome dos clientes que moram no bairro 1:

SELECT FUN_NOME FROM TB_FUNCIONARIOS WHERE FUN_DTNASC > ’1985-01-01’

o Selecionar nome dos funcionários que nasceram a partir de 1985

A clausula ORDER BY serve para ordenar uma consulta por uma determinada coluna. Veja

exemplos de uso:

SELECT * FROM TB_CLIENTES ORDER BY CLI_NOME

o Clientes em ordem alfabética

SELECT FUN_NOME FROM TB_FUNCIONARIOS WHERE FUN_DTNASC > ’1985-01-01’

ORDER BY FUN_DTNASC DESC

o Funcionários que nasceram depois de 1985 em ordem decrescente de idade

A clausula WHERE pode receber algumas condições especiais. As 3 principais são:

IN()

WHERE coluna1 IN

(valor1, valor2,

...valorN)

BETWEEN ... AND ...

WHERE coluna1 BETWEEN

valorInicial AND

valorFinal

LIKE

WHERE coluna1 LIKE

‘%TEXTO%’

Verifica se a coluna é algum

dos valores da lista

Verifica se o valor coluna está

entre os dois valores

Verifica se a coluna contém

um texto. O % serve para

indicar qualquer coisa!

Projeto e Manutenção de Banco de Dados 4

4

Veja alguns exemplos de uso destas condições especiais listadas acima:

SELECT CLI_NOME FROM TB_CLIENTES WHERE CLI_BAI_CODIGO in (1, 2, 3)

o Nome dos clientes dos bairros 1, 2 ou 3

SELECT CLI_NOME FROM TB_CLIENTES WHERE CLI_DTNASC BETWEEN ’1990-01-

01’ AND ‘2000-12-31’

o Nome dos clientes nascidos entre 1990 e 2000

SELECT CLI_NOME FROM TB_CLIENTES WHERE CLI_NOME LIKE ‘%João%’

o Nome dos clientes que se chamam João. Não importa o sobrenome ou

mesmo se João não é seu primeiro nome. Caso deseje buscar apenas por

nomes que se iniciam com João, busque por: 'João%'

Variações no SELECT

Existem alguns comandos que podemos usar para alterar o SELECT.

A primeira delas é a clausula DISTINCT. Ela é digitada logo após a palavra SELECT,

quando deseje que sua pesquisa não exiba linhas totalmente iguais (exibir apenas linhas

distintas). Veja o exemplo:

SELECT DISTINCT FUN_FUNCAO FROM TB_FUNCIONARIOS

o Selecione as funções dos funcionários, sem repetir

Outro parâmetro é o LIMIT. Este é um componente específico do MySQL. Para SQL Server

usamos o comando TOP (que tem outra sintaxe). O comando LIMIT é colocado no final do

comando, geralmente depois do ORDER BY (caso existe). Veja os exemplos:

SELECT FUN_NOME FROM TB_FUNCIONARIOS ORDER BY FUN_NOME LIMIT 5

o Selecione os 5 primeiros funcionários, em ordem alfabética

SELECT FUN_NOME FROM TB_FUNCIONARIOS ORDER BY FUN_NOME LIMIT 10,100

o Selecione 100 funcionários, em ordem alfabética, começando a partir do 11º (até o

110º)

SELECT FUN_NOME FROM TB_FUNCIONARIOS LIMIT 0,30

o Selecione 30 funcionários, começando a partir do 1º

É isso, pessoal! Esse é o SELECT básico, e com estas opções podemos praticar bastante.

Projeto e Manutenção de Banco de Dados 5

5

Prática de SELECT

Para praticar os conteúdos até agora ministrados, use o banco de dados fornecido na pasta

da Aula 3 (DB_LOJA), e tente criar as seguintes consultas:

Nome e o desconto de todos os departamentos

1) Descrição, valor para venda e quantidade em estoque de todos os produtos

2) Descrição, valor para venda, quantidade em estoque e valor mínimo em estoque de

todos os produtos que estão abaixo do estoque mínimo (caso não exista nenhum

produto nessa situação, altere alguns dados para que exista)

3) Descrição, valor para venda, quantidade em estoque e valor mínimo em estoque de

todos os produtos que estão acima do estoque mínimo

4) Matrícula, nome e data de admissão de todas as vendedoras (mulheres)

5) Matrícula, nome e data de admissão de todos os vendedores homens, admitidos

entre 01/01/2005 e 31/12/2008

6) Nome de todos os clientes que possuem “SANTOS” no nome

7) Descrição e preço dos dois produtos mais baratos

8) Descrição e preço dos dois produtos mais caros

9) Matrícula e data de admissão dos 3 vendedores mais antigos

Caso não encontre alguma coluna ou dados correspondentes a sua pesquisa, insira-os

(modificando a tabela ou inserindo linhas que atendam o critério) e tente novamente!

SELECT com JOIN

Às vezes é necessário usar duas ou mais tabelas em uma só pesquisa. O objetivo seria

exibir dados como por exemplo, o nome do cliente juntamente com o nome do bairro onde ele

mora (não apenas o código do bairro). Para resolver estes problemas é que usamos o comando

JOIN.

A sintaxe padrão desde comando é:

SELECT campos FROM tabela1

JOIN tabela2 ON estrangeiratabela1 = primariatabela2

WHERE condição

Projeto e Manutenção de Banco de Dados 6

6

Vamos imaginar o caso que eu deseje mostrar dados que estão em uma tabela de alunos

e uma tabela de bairros. Veja a imagem ilustrativa ao lado (modelo físico). O primeiro passo, que

já foi feito, é identificar quais colunas existem em comum entre

estas duas tabelas. Elas geralmente são a chave primária de uma

delas (BAI_CODIGO na tabela TB_BAIRROS) e uma chave

estrangeira da outra (ALU_BAI_CODIGO na tabela TB_ALUNOS).

Uma vez que identificamos isto, podemos criar o nosso código. Vai

ficar assim:

SELECT ALU_NOME, BAI_BAIRRO

FROM TB_ALUNOS

JOIN TB_BAIRROS ON ALU_BAI_CODIGO = BAI_CODIGO

Perceba que os campos que foram selecionados pertencem

a tabelas diferentes (ALU_NOME e BAI_BAIRRO)! Isso só vai ser

possível de executar quando fazemos o JOIN corretamente. Neste

caso, ao executar isto em meu computador obtive o seguinte

resultado, conforme a imagem a seguir:

Veja alguns outros exemplos de código: na locadora, desejo mostrar o código da cópia

(tabela de cópias) e o nome do filme que esta contém (tabela filmes)

O que une estas duas tabelas são as colunas FIL_CODIGO e COP_FIL_CODIGO,

portanto:

SELECT COP_CODIGO, FIL_TITULO

FROM TB_COPIAS

JOIN TB_FILMES ON COP_FIL_CODIGO = FIL_CODIGO

Projeto e Manutenção de Banco de Dados 7

7

Prática de SELECT com JOIN

Para entender corretamente este assunto, só mesmo praticando! Use o banco de dados

fornecido na pasta da Aula 3 (DB_LOJA), e tente criar as seguintes consultas:

1) Nome do departamento, descrição do produto, valor de venda e desconto de todos os produtos com estoque acima do mínimo, por ordem de nome do departamento.

2) Nome do departamento, descrição do produto, valor de venda, desconto de todos os produtos do departamento de calçados.

3) Nome e telefone de todos os clientes dos bairros TIROL, ALECRIM, ou RIBEIRA, por ordem de nome do cliente.

4) Nome e telefone de todos os fornecedores das cidades NATAL ou PARNAMIRIM, por ordem de nome do fornecedor.

5) Nota fiscal, data, descrição das formas de pagamento e nome do vendedor de todas as vendas já realizadas.

6) Nota fiscal, data, descrição das formas de pagamento e nome do vendedor de todas as vendas realizadas no mês de março de 2012.

7) O nome e departamento de todos os produtos que foram comprados pela loja em março de 2012 (elimine eventuais repetições).

8) O nome e o departamento de todos os produtos que foram vendidos por um determinado departamento.

9) O nome e o departamento de todos os produtos que foram vendidos por um determinado vendedor no primeiro semestre de 2012 (elimine eventuais repetições).

10) DESAFIO: O nome dos vendedores que nunca venderam nenhum produto.

Caso não encontre alguma coluna ou dados correspondentes a sua pesquisa, insira-os

(modificando a tabela ou inserindo linhas que atendam o critério) e tente novamente!

Projeto e Manutenção de Banco de Dados 8

8

Tipos de JOIN

Existem várias formas de usar o comando JOIN, além da que vimos anteriormente. Desta

forma, neste capítulo vamos apresentar os tipos mais usados de JOIN. São eles:

INNER JOIN

LEFT JOIN

RIGHT JOIN

Para exemplificar melhor, usaremos um caso um banco de dados de funcionários, de

acordo com o modelo a seguir:

Podemos notar pelo modelo que pode existir no banco de dados funcionários sem cargos

e cargos sem funcionários. Na imagem a seguir vemos o conteúdo das tabelas. Perceba que

temos cargos que não tem nenhum funcionário, como também temos funcionários sem cargo!

INNER JOIN

O comando INNER JOIN serve para juntar duas ou mais tabelas por coincidência. Para

cada linha da tabela FUNCIONÁRIOS queremos o CARGO correspondente que internamente

(INNER), em seus valores de atributos, coincidam. No caso de FUNCIONÁRIO e CARGO os

TB_CARGOS

TB_FUNCIONÁRIOS

Projeto e Manutenção de Banco de Dados 9

9

atributos internos coincidentes são CAR_CODIGO na tabela TB_CARGOS e FUN_CAR_CODIGO

na tabela TB_FUNCIONARIOS.

CAR_CODIGO é chave primária da tabela TB_CARGOS e chave estrangeira na

tabela TB_FUNCIONARIOS. Para efetivarmos a junção das duas tabelas se fará necessário ligar

(ON) as duas tabelas por seus atributos internos (INNER) coincidentes.

Este comando é igual ao JOIN natural, ou seja, tanto faz usar apenas JOIN quanto INNER

JOIN! Veja o exemplo a seguir:

LEFT JOIN

Observando a tabela TB_FUNCIONARIOS notamos que o funcionário Gonofredo não

possui cargo. Se desejarmos listar todos os funcionários com seus respectivos cargos, incluindo

os funcionários sem cargos, a exemplo de Gonofredo, poderíamos usar todo o poder da junção

INNER JOIN adicionando ainda outros funcionários que não fazem parte do INNER JOIN.

Podemos conseguir esse feito com a junção FUNCIONARIO/CARGO através da

declaração FUNCIONARIO LEFT JOIN CARGO, que promove a junção interna (INNER) de todos

os funcionários a cargos e lista ainda outros (EXTERNOS) não associados. Veja o exemplo a

seguir:

RIGHT JOIN

Observando a tabela CARGOS notamos que o cargo GERENTE, com código 3, não é

referenciado/associado por/a nenhum funcionário na tabela FUNCIONARIOS. Se desejarmos

Projeto e Manutenção de Banco de Dados 10

10

listar todos os CARGOS e seus respectivos FUNCIONARIOS, incluindo os CARGOS sem

FUNCIONÁRIOS, poderíamos usar a junção RIGTH JOIN.

Se você ficou confuso quanto ao uso do LEFT ou RIGHT, temos uma dica para você, para

facilitar seu uso. Veja o exemplo de código a seguir:

SELECT * FROM TB_FUNCIONARIOS JOIN TB_CARGOS ON CAR_CODIGO = FUN_CAR_CODIGO

A dica é a seguinte: sabemos que o JOIN natural (inner join) sempre vai trazer apenas os

relacionamentos perfeitos. Ou seja, quem está presente tanto em um lado como no outro. Quando

usamos o LEFT ou RIGHT podemos apontar para que lado queremos trazer também os "órfãos",

ou seja, os registros não relacionados. Veja o exemplo

TB_FUNCIONARIOS LEFT JOIN TB_CARGOS

Ou seja, além de trazer todos os funcionários e seus cargos, também pedimos que sejam

trazidos os FUNCIONÁRIOS que não tem cargo (valorizando a tabela TB_FUNCIONÁRIOS). Veja

este outro exemplo:

TB_FUNCIONARIOS RIGHT JOIN TB_CARGOS

Ou seja, além de trazer todos os funcionários e seus cargos, também pedimos que sejam

trazidos os CARGOS que não tem funcionários (valorizando a tabela TB_CARGOS).

Projeto e Manutenção de Banco de Dados 11

11

Prática de Tipos de JOIN

E aqui vem nossa última prática! Use o banco de dados fornecido na pasta da Aula 3

(DB_LOJA), e tente criar as seguintes consultas:

1) Nome de todas as cidades e, se existirem, seus respectivos bairros.

2) Nome de todas as cidades sem nenhum bairro cadastrado (te ajudará usar a

seguinte condição: WHERE colunaX IS NULL).

3) Nome de todas as marcas sem nenhum produto cadastrado.

4) Nome de todos os produtos que não tiveram nenhuma unidade vendida.

5) Nome e fone de todos os vendedores que não venderam nada.

6) Nome dos clientes, pessoa física, sem telefone cadastrado.

7) Nome e fone de todos os fornecedores que já forneceram produtos.

8) Nome e fone de todos os fornecedores que nunca forneceram.

Projeto e Manutenção de Banco de Dados 12

12

Bibliografia

[1] Tipos de JOIN. <http://www.devmedia.com.br/inner-cross-left-rigth-e-full-joins/21016/>. Acessado em julho de 2015.

[2] Material didático do professor Ari Oliveira. <http://www.arioliveira.com>. Acessado em julho de 2015.