Programação de Banco de Dados - parte 4

Este é o quarto artigo relacionado com programação de Banco de Dados, neste artigo vamos continuar abordar os comandos SQL. No terceiro falamos mais do comando SELECT agora vamos falar sobre os demais comandos fechando o assunto SQL.

por Renato Mattos

Este é o quarto artigo relacionado com programação de Banco de Dados, neste artigo vamos continuar abordar os comandos SQL. No terceiro falamos mais do comando SELECT agora vamos falar sobre os demais comandos fechando o assunto SQL.

Achei que no terceiro artigo ficou faltando falar melhor na recuperação de dados de várias tabelas (JOINS) vamos então fechar o SELECT com esse assunto.

JOINS

A cláusula WHERE permite que você conecte duas ou mais tabelas, com base nos valores de duas colunas nas duas tabelas.A cláusula WHERE, no entanto, deve ser usada para expressar restrições envolvendo uma ou mais tabelas, e não para conectar tabelas, embora seja comum fazermos isso. O método apropriado para vincular tabelas é a operação JOIN.

Vou citar 2 exemplos onde ocorre problemas ao usar a clausula WHERE em junções:

Exemplo 1: Consideremos duas Tabelas uma Tabela FUNCIONARIOS (idfuncionario,nome) e outra DEPENDENTES (idfuncionario,nome,grauparentesco). Onde o idfuncionario na tabela FUNCIONARIOS é a chave primária e na tabela DEPENDENTES é a chame estrangeira, a chave primária da tabela DEPENDENTES seria a concatenação de idfuncinario + nome. Sendo que a tabela DEPENDESTES é tabela fraca de FUNCIONARIOS ( isso quer dizer que só existe um registro em Dependentes a partir da tabela funcionário, isso também ocorre quando na chave primaria da tabela participa a chave primária de outra tabela), bem voltando ao caso, suponha que queremos retornar todos os funcionários e caso o funcionário tenha dependente retornamos também. A consulta com WHERE ficaria assim

SELECT F.Idfuncionario , F.nome,D.Nome From FUNCIONARIOS as F, DEPENDENTES as D Where F.Idfuncionario= D.Idfuncionario

Bem nesta consulta somente iria retornar os funcionários que possuem dependentes, os que não tivessem dependentes ficaria de fora.

Exemplo 2: Consideremos ainda a tabela FUNCIONARIO e outra tabela TELFUNCIONARIO(idfuncionario,numero), muito comum , pois o campo telefone é multivalorado ( permite vários telefones) por isso foi criado uma tabela somente para os telefones. Bem neste caso temos o mesmo problema os funcionários sem telefone não iriam ser retornados usando a clausula WHERE.

A operação JOIN combina colunas de duas tabelas se as linhas possuírem campos de correspondência. Sua sintaxe é:

FROM tabela1 INNER JOIN tabela2 ON tabela1.col = tabela2.col

As duas colunas não precisam ser correspondidas com o operador igual, embora este seja o método mais comum de correspondência de várias tabelas.Você pode usar qualquer um dos operadores relacionais ( >,>=,<,<= e <>). Além disso você pode combinar várias retrições com operadores lógicos.Por exemplo:

FROM tabela1 INNER JOIN tabela2 ON tabela1.col1 = tabela2.col1 AND tabela1.col2 <> tabela2.col2

TIPOS DE JUNÇÕES

O SQL suporta dois tipos de junções:

INNER JOIN esta junção retorna todos os pares com correspondentes de linhas nas duas tabelas e descartam as linhas sem correspondentes de ambas as tabelas.

OUTER JOIN esta junção retorna todas as linhas normalmente retornadas pela operação INNER JOIN, mas as linhas da tabela esquerda ou da direita que não atendam à condição.

CROSS JOIN incluímos cada uma das combinações de todas as linhas entre as tabelas.

Na sintaxe MS-SQL Server, são comparadas as tabelas por uma coluna específica para cada tabela (chave estrangeira), linha por linha, e são listadas as linhas em que a comparação é verdadeira.

INNER JOIN

Considere as tabelas:

CLIENTE: Cod_cliente, Nome,Endereço
PEDIDO: Num_Pedido,Prazo_Entrega Cod_Cliente, Cod_Vendedor,Data
ITEMPEDIDO: num_pedido,Cod_produto,Quantidade
PRODUTO: Cód_produto,Descrição,Unidade,ValUnidade.
VENDEDOR: Cód_Vendedor,Nome, Comissão,Salario

Problema: ver os pedidos de cada cliente:

SELECT Cliente.nome,Pedido.cod_cliente,pedido.num_pedido
FROM Cliente INNER JOIN Pedido 
ON Cliente.Cod_cliente = Pedido.Cod_cliente

Problema: Mostre os clienter (ordenados) que têm prazo de entrega maior que 15 dias para o produto "ARROZ" e sejam do Rio de Janeiro.

SELECT Cliente.Nome
FROM Cliente INNER JOIN Pedido
ON Cliente.Cod_cliente=Pedido.Cod_Cliente
INNER JOIN ItemPedido
ON pedido.num_pedido = itempedido.num_pedido
INNER JOIN Produto
ON itempedido.Cód_produto= Produto.Cod_Produto
WHERE Pedido.Prazo_Entrega > 15 AND
Produto.Descrição="ARROZ" AND
Cliente.UF = "RJ"
ORDER BY Cliente.Nome

Problema: Mostre todos os vendedores que venderam chocolate em quantidade superior a 5 Kg.

SELECT DISTINCT Vendedor.Nome
FROM Vendedor INNER JOIN Pedido
ON Vendedor.Cod_Vendedor=Pedido.Cod_Vendedor
INNER JOIN ItemPedido
ON pedido.num_pedido = itempedido.num_pedido
INNER JOIN Produto
ON itempedido.Cód_produto= Produto.Cod_Produto
WHERE Quantidade > 5 AND
Produto.Descrição="CHOCOLATE"
ORDER BY Vendedor.Nome

Problema: Quantos clientes da cidade do Rio de Janeiro e de Niterói tiveram seus pedidos tirados pelo vendedor "PAULO" fez no mês de janeiro.

SELECT cidade,COUNT (nome_cliente), 
FROM Cliente INNER JOIN Pedido
ON Cliente.Cod_Cliente=Pedido.Cod_Cliente
INNER JOIN Vendedor
ON pedido.Cód_Vendedor = vendedor.Cód_Vendedor
WHERE Cidade In("Rio de Janeiro","Niteroi") AND
Data BETWEEN #01/01/2004# AND #31/01/2004#
GROUP BY Cidade

OUTER JOIN

É a seleção em que são restritas as linhas que interessam em uma tabela, mas são consideradas todas as linhas de outra tabela.

Ou seja, queremos ver quais linhas de uma tabela estão relacionadas com a outra tabela e quais as linhas não estão.

Poderíamos dizer, que queremos ver quais clientes tem pedidos e quais clientes não tem pedidos.

Um OUTER JOIN somente pode ser realizado entre duas tabelas, não mais que duas tabelas.

O Outer Join possui 3 tipos:

LEFT OUTER JOIN - são incluidas todas as linhas da primeira tabela na expressão.

RIGHT JOIN - são incluídas todas as linhas da segunda tabela na expressão.

FULL OUTER JOIN - são incluidas todas as linhas de ambas as tabelas, as que satisfazem a expressão e as que não satisfazem.

INSERT, UPDATE E DELETE

Adicionado Registro na Tabela - INSERT

Sintaxe: INSERT INTO nome Tabela (nome das colunas ) VALUES ( valores )

Exemplo:

INSERT INTO Clientes (nome,endereco) VALUES ("LUCIANA", "AV ATLANTICA").

Podemos omitir a lista dos nomes das colunas, neste caso a lista de valores deve estar na mesma ordem que na tabela no banco, e a tabela não pode ter nenhum campo AutoNumeric, pois não pode ser omitido nenhum valor.

Outro mecanismo para passar valores de colunas para a instrução INSERT é seleciona-las de outra tabela.

Exemplo:

INSERT INTO PhoneBook Select ContactName,Phone,Fax From Customers.

Esse tipo de inserção permite a inclusão de várias linhas de uma só vez.

Alterando Registros - UPDATE

Sintaxe: UPDATE nome_tabela SET coluna1=valor1, coluna2=valor2... Where condição

Problema: Alterar o valor unitário do produto "parafuso"

UPDATE Produto
Set val_unit = 2.00
Where Descrição= "parafuso"

Problema: atualizar o salario fixo de todos os vendedores em 30% mais bonificação de 100

UPDATE Vendedor
Set Salário = (Salário * 1.30) + 100

Neste comando não foi usado a clausula WHERE neste caso todos os registros da tabela Vendedor foram atualizados.

Problema: Acrescentar 2,5% ao preço dos produtos que estejam abaixo da média dos preços.

UPDATE Produto
Set Val_Unit = Val_Unit * 1.25
Where Val_Unit < (Select AVG(Val_unit) From Produto)

Apagando Registros - DELETE

Sintaxe: DELETE From nome_tabela Where condição.

Exemplo: DELETE FROM Cliente Where IdCliente = 1.

Se omitir a clausula WHERE todos os registros da tabela são deletados.

OBS: Tanto no comando UPDATE como no DELETE na clausula WHERE pode-se usar SELECT com todos as suas possibilidades.

Terminamos aqui de falar sobre os comandos DML, passaremos agora a abordar os comandos DDL mais precisamente o comando CREATE TABLE.

CRIAÇÃO DE TABELAS - CREATE TABLE

Estruturas de dados que podem ser criadas com um SGBD.

Estruturas de Dados	Descrição
Tabela	Armazena dados.
Visão	Representa logicamente subconjuntos de dados de uma ou mais tabelas.
Seqüência	Gera valores de chaves primárias.
Índice	Melhora o desempenho de algumas consultas.

Tabelas

• Uma tabela pode ser criada a qualquer momento.
• Não é necessário especificar seu tamanho, no momento da sua criação, embora seja possível.
• A estrutura de uma tabela pode ser modificada a qualquer momento, sem a necessidade de se tirar o banco do ar.
• Quando uma tabela é criada sua definição é armazenada no dicionário de dados.
• Para se poder criar tabelas é preciso ter o privilégio de CREATE TABLE e o direito de utilizar algum espaço em disco, alocado para o banco de dados.

Quem concede estes direitos para os usuários do banco é o Administrador de Banco de Dados. (DBA) Comando Create Table

Exemplo:

CREATE  TABLE  FORNECEDORES
(NUMERO		NUMBER(2)  PRIMARY  KEY,
NOME			VARCHAR2(25)  NOT NULL,
TELEFONE		CHAR(7),
ENDERECO		VARCHAR2(20),
VALOR_FORNEC	NUMBER (8,2));

Observações:

- O nome de uma tabela deve começar por uma letra.
- Pode ter até 30 caracteres.
- Deve conter apenas: A-Z, a-z, 0-9, _, $ e #.
- Não pode ter o mesmo nome de qualquer outro objeto existente no esquema do usuário.

Tipos de Dados

- NUMBER
- NUMBER(p,s)
- DATE
- CHAR(s)
- VARCHAR2(s)
- LONG

Tipos de Constraints - PRIMARY KEY
- FOREIGN KEY
- NOT NULL
- UNIQUE
- CHECK

Observações:

- É possível criar uma constraint após a criação da tabela.
- Uma constraint pode ser definida a nível de coluna ou a nível de tabela.
- Constraints são armazenadas no Dicionário de Dados e podem ser facilmente recuperadas se possuírem nomes razoáveis.

Como dar Nome às Constraints

Exemplo 1: Constraints Primary Key e Not Null.

CREATE  TABLE  FORNECEDORES
(NUMERO		NUMBER(2)  
	  	CONSTRAINT   FORNECEDORES_NUMERO_PK  PRIMARY  KEY,
NOME		VARCHAR2(25)
	      	CONSTRAINT   FORNECEDORES_NOME_NN  NOT  NULL,
TELEFONE		CHAR(7)
	      	CONSTRAINT   FORNECEDORES_TELEFONE_NN  NOT  NULL,
ENDERECO		VARCHAR2(20),
VALOR_FORNEC	NUMBER (8,2));

Exemplo 2: Constraints Primary Key e Not Null.

CREATE TABLE DEPARTAMENTOS
(NUMERO	      	NUMBER(2)
	      CONSTRAINT DEPARTAMENTOS_NUMBER_PK  PRIMARY KEY,
NOME        		VARCHAR2(14)
	      CONSTRAINT DEPARTAMENTOS_NOME_NN  NOT  NULL,
LOCAL       		VARCHAR2(13));

Exemplo 3: Constraint Check e Integridade Referecial com a própria tabela de Empregados e com a tabela de Departamentos.

  CREATE TABLE EMPREGADOS 
	(NUMERO              NUMBER(4)
	      CONSTRAINT EMPREGADOS_NUMBER_PK  PRIMARY KEY,
 	NOME        	VARCHAR2(10),
	SOBRENOME        	VARCHAR2(10),
	CPF          	CHAR(11)
	      CONSTRAINT EMPREGADOS_CPF_UN  UNIQUE,
 	CARGO       	VARCHAR2(9),
 	NUM_SUPERVISOR     NUMBER(4)
    	      CONSTRAINT  EMP_EMP_NUM_SUPERVISOR_FK    
    	      REFERENCES  EMPREGADOS (NUMERO),
 	DT_ADMISSAO 	DATE,
 	SALARIO     	NUMBER(7,2),
 	PERC_COMISSAO  	NUMBER(4,2)
	      CONSTRAINT  EMPREGADOS_PERC_COMISSAO_CK  
	      CHECK (PERC_COMISSAO  IN  (10, 12.5, 15, 17.5, 20)),
 	NUMERO_DEPT	NUMBER(2)
    	      CONSTRAINT  EMPR_DEPARTAMENTOS_NUMERO_DEPT_FK  
    	      REFERENCES  DEPARTAMENTOS (NUMERO)
	      ON  DELETE  CASCADE);

Exemplo 4:

   CREATE TABLE DEPENDENTES 
	(NUMERO_EMP  	NUMBER(4)
	      CONSTRAINT  DEPENDENTES_EMP_NUMERO_EMP_FK    
	      REFERENCES  EMPREGADOS (NUMERO),
	NUM_ORDEM   	NUMBER(2),
	NOME        	VARCHAR2(10),
           CONSTRAINT DEPENDENTES_NUM_EMP_NUM_ORD_PK  
PRIMARY KEY(NUMERO_EMP, NUM_ORDEM));

Observações sobre a Constraint Primary Key:

- A constraint Primary Key é uma combinação das constraints Unique e Not Null.
- Um índice único é automaticamente criado.

Observações sobre a Constraint Unique:

- Designa uma coluna ou uma combinação de colunas de tal forma que duas linhas não possam ter o mesmo valor.
- Valores nulos são aceitos.
- Automaticamente é criado um índice único para a(s) coluna(s) especificada(s).

Observações sobre a Constraint Foreign Key:

- Estabelece um relacionamento com a chave primária ou única da mesma ou de outra tabela.
- Deve referenciar um valor existente na tabela pai ou ser nulo.
- Chaves estrangeiras são baseadas em dados e são puramente lógicas, isto é, não são ponteiros físicos.
- Uma chave estrangeira, parte de uma chave primária, não pode ser nula pois uma chave primária não pode ser nula, nem parcialmente nula.
- Havendo a cláusula ON DELETE CASCADE, uma deleção na tabela pai causa a deleção das linhas relacionadas na tabela filho.

Outras Formas de se Validar uma Restrição de Integridade

- Triggers
- Procedimentos ou funções armazenados no servidor de banco de dados
- Através do código na própria aplicação.

Como Criar uma Tabela Através de uma Subconsulta

      CREATE  TABLE  EMPREGADOS_VENDAS
           AS  SELECT  *
           FROM  EMPREGADOS
           WHERE  CARGO  =  "VENDEDOR";

Observação:

- A tabela Empregados_Vendas é criada contendo todos os empregados no cargo de vendedores.
- Apenas a constraint NOT NULL é copiada.

Bem o assunto SQL não termina aqui, não abordamos os comando DCL responsável pelo acesso e permissão de usuários.Também não falamos sobre Trigers, Procedures, Function e View. Devido a pedidos para falarmos logo do componente ADO nos próximos artigos vamos "cair" em cima da prática com acesso a banco de dados pelo ADO, com exemplos de códigos realizando: consultas, inserções, deleções, alterações, transações, carregamento de listas, controle de concorrência, backup e etc, enfim tudo com exemplos e feito via código, não usarei componentes para acesso como o Data Control, que considero muito ruim e limitado.Até a próxima.