2014 04 28 - Exercitando Junções

(1)

Exercitando Junções

Banco de Dados II

(2)

Problema

1. Suponha o seguinte modelo entidade relacionamento. Pais (0,1) (0,n) Sub-região id nome id nome Cidade (0,1) id nome (0,n)

(3)

Solução

• Transformação para o modelo relacional, por meio da técnica de adição de colunas,

(4)

Preparação da Base de Dados

• Inserir os seguinte registros na tabela Pais.

INSERT INTO pais (nomePais) VALUES ('Brasil'); INSERT INTO pais (nomePais) VALUES ('Argentina');

INSERT INTO pais (nomePais) VALUES ('Estados Unidos'); INSERT INTO pais (nomePais) VALUES ('Itália');

INSERT INTO pais (nomePais) VALUES ('França'); INSERT INTO pais (nomePais) VALUES ('Noruega');

(5)

• Inserir os seguinte registros na tabela Subregião.

INSERT INTO subregiao (nomeSubregiao, idPais) VALUES ('Paraná', 1); INSERT INTO subregiao (nomeSubregiao, idPais) VALUES ('Sao Paulo', 1);

INSERT INTO subregiao (nomeSubregiao, idPais) VALUES ('Rio Grande do Sul',1); INSERT INTO subregiao (nomeSubregiao, idPais) VALUES ('Buenos Aires', 2);

INSERT INTO subregiao (nomeSubregiao, idPais) VALUES ('Cordoba', 2); INSERT INTO subregiao (nomeSubregiao, idPais) VALUES ('Califórnia', 3); INSERT INTO subregiao (nomeSubregiao, idPais) VALUES ('Flórida', 3); INSERT INTO subregiao (nomeSubregiao, idPais) VALUES ('Toscana', 4); INSERT INTO subregiao (nomeSubregiao, idPais) VALUES ('Lombardia', 4); INSERT INTO subregiao (nomeSubregiao, idPais) VALUES ('Aquitania', 5);

(6)

• Inserir os seguinte registros na tabela Cidade.

INSERT INTO cidade (nomeCidade, idSubregiao) VALUES ('Curitiba', 1); INSERT INTO cidade (nomeCidade, idSubregiao) VALUES ('Sao Paulo', 2); INSERT INTO cidade (nomeCidade, idSubregiao) VALUES ('Guarulhos', 2); INSERT INTO cidade (nomeCidade, idSubregiao) VALUES ('Buenos Aires', 4); INSERT INTO cidade (nomeCidade, idSubregiao) VALUES ('La Plata', 4);

INSERT INTO cidade (nomeCidade, idSubregiao) VALUES ('Cordoba', 5);

INSERT INTO cidade (nomeCidade, idSubregiao) VALUES ('Los Angeles', 6); INSERT INTO cidade (nomeCidade, idSubregiao) VALUES ('San Francisco', 6); INSERT INTO cidade (nomeCidade, idSubregiao) VALUES ('Orlando', 7);

INSERT INTO cidade (nomeCidade, idSubregiao) VALUES ('Miami', 7); INSERT INTO cidade (nomeCidade, idSubregiao) VALUES ('Siena', 8); INSERT INTO cidade (nomeCidade, idSubregiao) VALUES ('Florença', 8); INSERT INTO cidade (nomeCidade, idSubregiao) VALUES ('Milao', 9);

(7)

• Quantidade de registros nas tabelas

Pais Sub-região Cidade

(8)

Problema

• Realizar uma consulta que retorne os nomes de sub-regiões com seus respectivas países. Queremos:

Sub-região Pais

Paraná Brasil

Sao Paulo Brasil

Rio Grande do Sul Brasil

Buenos Aires Argentina

Cordoba Argentina

Califórnia Estados Unidos

Flórida Estados Unidos

Toscana Itália

Lombardia Itália

Aquitania França

Borgonha França

Calábria França

(9)

Solução

• Utilização cláusula WHERE, por exemplo:

– SELECT nomeSubregiao, nomePais FROM subregiao s, pais p WHERE s.idPais = p.idPais;

• No entanto existem cláusulas específicas para

esse tipo de operações, as quais guardam íntima relação com o produto cartesiano entre as

(10)

Produto Cartesiano

• Dadas duas tabelas (subregiao, pais), o produto cartesiano consiste na combinação de todos os registros de uma tabela com os da outra tabela. • Vejamos duas formas de realizar essa operação:

– SELECT * FROM subregiao, pais;

(11)

• Resultado:

1 Paraná 1 1 Brasil

1 Paraná 1 2 Argentina

1 Paraná 1 3 Estados Unidos

1 Paraná 1 4 Itália

1 Paraná 1 5 França

1 Paraná 1 6 Noruega

2 Sao Paulo 1 1 Brasil

2 Sao Paulo 1 2 Argentina

2 Sao Paulo 1 3 Estados Unidos

2 Sao Paulo 1 4 Itália

(12)

• O valor 84 é resultante do produto cartesiano da tabela Pais (com 6 registros) pela tabela

Subregiao (com 14 registros). • Assim, 6 * 14 = 84.

• Mas não é isso que nós queremos encontrar. O que precisamos é filtrar esses resultados de

forma que fiquem apenas as Sub-regiões com seus correspondentes Países.

(13)

Filtrando Registros de Interesse

• Temos três formas de realizar essa operação usando INNER JOIN.

• Cláusula ON:

– SELECT nomeSubregiao, nomePais FROM subregiao s INNER JOIN pais p ON s.idPais = p.idPais;

• Cláusula USING:

– SELECT nomeSubregiao, nomePais FROM subregiao INNER JOIN pais USING (idPais);

(14)

• Resultado:

# nomeSubregiao nomePais 1 Paraná Brasil 2 Sao Paulo Brasil 3 Rio Grande do Sul Brasil 4 Buenos Aires Argentina 5 Cordoba Argentina

6 Califórnia Estados Unidos 7 Flórida Estados Unidos 8 Massachussetts Estados Unidos 9 Toscana Itália

10 Lombardia Itália 11 Aquitania França 12 Borgonha França 13 Calábria França

(15)

• A cláusula USING é ainda mais importante

quando o INNER JOIN é realizado entre várias tabelas, que compartilham as chaves

primárias/estrangeiras, evitando utilizar condições dentro da Cláusula ON.

• Por exemplo, se queremos realizar uma consulta que retorne para cada cidade, seus respectivos nomes de sub-região e pais. Temos que filtrar:

(16)

• O que pode ser feito da seguinte forma:

– SELECT nomeCidade, nomeSubregiao, nomePais FROM cidade c

INNER JOIN subregiao s INNER JOIN pais p

ON (c.idSubregiao = s.idSubregiao AND s.idPais = p.idPais);

• Mas também, de uma forma mais clara:

• SELECT nomeCidade, nomeSubregiao, nomePais FROM cidade INNER JOIN subregiao USING (idSubregiao)

INNER JOIN pais USING (idPais); • ou ainda:

SELECT nomeCidade, nomeSubregiao, nomePais FROM cidade NATURAL JOIN subregiao NATURAL JOIN pais;

(17)

• Resultado:

# nomeCidade nomeSubregiao nomePais 1 Curitiba Paraná Brasil 2 Sao Paulo Sao Paulo Brasil 3 Guarulhos Sao Paulo Brasil 4 Buenos Aires Buenos Aires Argentina 5 La Plata Buenos Aires Argentina 6 Cordoba Cordoba Argentina

7 Los Angeles Califórnia Estados Unidos 8 San Francisco Califórnia Estados Unidos 9 Orlando Flórida Estados Unidos

(18)

Problema

• Nas consultas realizadas nos slides anteriores, foram retornados 13 ao invés de 14 registros. • Lembrando que criamos 14 cidades e 14

sub-regiões e 6 países.

• De fato, na nossa base de dados tempos um

registro na tabela Cidades que tem idSubregiao = null e também um registro na tabela Subregião que tem idPais igual a null. Vejamos

(19)

Problema

Tabela Cidade Tabela Subregião

idCidade nomeCidade idSubregiao

1 Curitiba 1 2 Sao Paulo 2 3 Guarulhos 2 4 Buenos Aires 4 5 La Plata 4 6 Cordoba 5 7 Los Angeles 6 8 San Francisco 6 9 Orlando 7 10 Miami 7

idSubregião nomeSubregiao idPais

1 Paraná 1

2 Sao Paulo 1

3 Rio Grande do Sul 1

4 Buenos Aires 2 5 Cordoba 2 6 Califórnia 3 7 Flórida 3 8 Toscana 4 9 Lombardia 4 10 Aquitania 5

(20)

Solução

• As junções externas servem para capturar os registros que foram filtrados por apresentarem valor de id da chave estrangeira igual a null ou outro valor inválido no contexto do campo de outra tabela.

• Essas junções possuem a cláusula OUTER JOIN • Existem duas formas de usá-la:

– LEFT OUTER JOIN – RIGHT OUTER JOIN

(21)

Solução

• Se utilizarmos a cláusula LEFT estaremos indicando que a tabela da esquerda (left)

determinará o número de registros da consulta resposta.

• Se utilizarmos a cláusula RIGHT estaremos

indicando que quem determinará o número de registros da resposta será à tabela da direita. • Efetuando a consulta:

(22)

Solução

• Resultado # nomeSubregiao nomePais

1 Paraná Brasil

2 Sao Paulo Brasil

3 Rio Grande do Sul Brasil

4 Buenos Aires Argentina

5 Cordoba Argentina

6 Califórnia Estados Unidos

7 Flórida Estados Unidos

8 Toscana Itália

9 Lombardia Itália

10 Aquitania França

11 Borgonha França

12 Calábria França

13 Massachussetts Estados Unidos

(23)

Solução

• Ao trocarmos a ordem das tabelas na consulta:

– SELECT nomeSubregiao, nomePais FROM pais LEFT OUTER JOIN subregiao USING (idPais);

(24)

Solução

• Resultado: # nomeSubregiao nomePais

1 Paraná Brasil

2 Sao Paulo Brasil

5 Cordoba Argentina

9 Toscana Itália 10 Lombardia Itália 11 Aquitania França 12 Borgonha França 13 Calábria França 14 null Noruega

(25)

Solução

• A utilizarmos a cláusula RIGHT com a ordem

inicial de tabelas, obteremos o mesmo resultado do slide anterior

– SELECT nomeSubregiao, nomePais FROM subregiao RIGHT OUTER JOIN pais USING (idPais);

(26)

Solução

• Resultado: # nomeSubregiao nomePais

1 Paraná Brasil

2 Sao Paulo Brasil

5 Cordoba Argentina

9 Toscana Itália 10 Lombardia Itália 11 Aquitania França 12 Borgonha França 13 Calábria França 14 null Noruega

(27)

Exercícios

1. Adicione os países: Uruguai, Canadá e Nigéria

2. Adicione as sub-regiões de: Rivera e Montevidéu (Uruguai); Valparíso;

3. Realize uma consulta que retorne todas as sub-regiões contidas na base de dados, com seus respectivos nome de países (se o atributo estiver presente ou não);

4. Realize uma consulta que retorne todos os países, com suas respectivas regiões (se estas existirem);

5. Realize uma consulta que mostre o nome de cidades e sub-regiões, apenas com as cidades que possuem uma sub-região associada;

(28)

Exercícios

6. Realize uma consulta que mostre o nome de cidades, sub-regiões e países, apenas com as cidades que possuem uma sub-região associada, que por sua vez tenha um pais associado;

7. Realize uma consulta que apresente o nome de todos os países que possuem alguma região associada;

8. Realize uma consulta que apresente o nome de todos os paises que possuem alguma região associada, que por sua vez possuam alguma cidade associada.

9. Realize uma consulta que mostre o nome de cidades, sub-regiões e países, apenas com as cidades que possuem ou não uma sub-região associada, que por sua vez tenham ou não um pais