Programação de Banco de Dados

Este é o terceiro artigo relacionado com programação de Banco de Dados, sairemos um pouco de teoria e vamos abordar mais a prática explicando a linguagem SQL. No segundo artigo o enfoque foi explicar a arquitetura cliente servidor com os modelos de 2 e 3 camadas.

SQL (Structured Query Language)

Quando os Bancos de Dados Relacionais estavam sendo desenvolvidos, foram criadas linguagens destinadas à sua manipulação. O departamento de Pesquisa da IBM desenvolveu a SQL como forma de interface para o sistema de BD relacional denominado SYSREM R, início dos anos 70. Em 1986 o American National Standard Institute ( ANSI) , publicou um padrão SQL.A SQL estabeleceu-se como linguagem padrão de Banco de Dados Relacional.

SQL apresenta uma série de comandos que permitem a definição dos dados, chamada de DDL ( Data Definition Language) , composta entre outros pelos comandos Create, que é destinado a criação do Banco de Dados, das tabelas que o compõe, além das relações existentes entre as tabelas. Como exemplo de comandos da classe DDL temos os comandos Create, Alter e Drop.

Os comandos da série DML ( Data Manipulation Language), destinados a consultas, inserções, exclusões e alterações em um ou mais registros de uma ou mais tabelas de maneira simultânea. Com exemplo de comandos da classe DML temos os comandos Select, Insert, Update e Delete.

Um subclasse de comandos DML, a DCL ( Data Control Language), dispõe de comandos de controle como Grant e Revoke.

A linguagem SQL tem como grandes virtudes sua capacidade de gerenciar índices, sem necessidade de controle individualizado de índice corrente, algo muito comum nas linguagens de manipulação de dados do tipo registro a registro.Outra característica muito importante disponível em SQL é sua capacidade de construção de visões, que são formas de visualizarmos os dados na forma de listagens independente das tabelas e organização lógica dos dados.

Outra característica interessante da linguagem SQL é a sua capacidade que dispomos de cancelar uma série de atualizações ou de as gravarmos, depois de iniciarmos uma seqüência de atualizações. Os comandos Commit e Rollback são responsáveis por estas facilidades.

Devemos notar que a linguagem SQL consegue implementar estas soluções, somente pelo fato de estar baseada em Banco de Dados, que garante por si mesmo a integridade das relações existentes entre as tabelas e seus índices.

» COMANDOS SQL

Comando	Descrição
SELECT	Recupera dados do Banco de Dados
INSERT UPDATE DELETE	Insere novas linhas, altera linhas existentes e remove linhas de tabelas do banco de dados, respectivamente. Estes comandos são conhecidos como comandos DML (Data Manipulation Language).
CREATE ALTER DROP RENAME TRUNCATE	Cria, altera e remove objetos do banco de dados. São conhecidos como comandos DDL (Data Definition Language).
COMMIT ROLLBACK SAVEPOINT	Gerenciam as modificações realizadas pelos comandos DML. As modificações efetuadas pelos comandos DML podem ser agrupadas em transações lógicas.
GRANT REVOKE	Atribuem e removem direitos de acesso ao banco de dados e aos objetos a ele pertencentes. São conhecidos como comandos DCL (Data Control Language).

» DML ( Data Manipulation Languagem)

Veremos agora os comandos SQL DML destinados a manipulação dos dados.Comandos:

COMANDO SELECT

Sintaxe: Select <campos da tabela> From <nome da tabela> Where <condição>

Exemplo 1: Selecionar linhas de uma tabela

SELECT NUMERO, NOME FROM EMPREGADOS;

Exemplo 2: Utilizando a cláusula DISTINCT para suprimir linhas duplicatas.

SELECT DISTINCT CARGO FROM EMPREGADOS;

Exemplo 3: Utilizando a cláusula DISTINCT com várias colunas.

SELECT DISTINCT CARGO, NUMERO_DEPT FROM EMPREGADOS;

Exemplo 4: Utilizando a cláusula * para listar todas as colunas da tabela.

SELECT * FROM EMPREGADOS

Exemplo 5: Utilizando operadores aritméticos (+, -,*, / ) na cláusula Select.

SELECT NOME, SALARIO * 12, DT_ADMISSAO FROM EMPREGADOS;

Exemplo 6: Utilizando Alias.

SELECT NOME, SALARIO * 12 AS SAL_ANUAL,DT_ADMISSAO FROM EMPREGADOS;
OU
SELECT NOME, SALARIO * 12 "SAL ANUAL", DT_ADMISSAO FROM EMPREGADOS;

Exemplo 7: Utilizando operador de concatenação.

SELECT NOME||" "||SOBRENOME FROM EMPREGADOS;

Como Ordenar e Limitar as Linhas Selecionadas

Exemplo 1: Ordenando as linhas selecionadas com a cláusula ORDER BY

SELECT NOME, SALARIO FROM EMPREGADOS ORDER BY SALARIO;
OU
SELECT NOME, SALARIO FROM EMPREGADOS ORDER BY SALARIO DESC;

Obs: Na ordenação ascendente, valores NULOS aparecem no final.

Exemplo 2: Ordenando pela posição da coluna selecionada

SELECT NOME, SALARIO * 12
FROM EMPREGADOS ORDER BY SALARIO * 12;
OU
SELECT NOME, SALARIO * 12 FROM EMPREGADOS ORDER BY 2; Exemplo 3: Selecionando apenas os empregados lotados no departamento 20.

SELECT NOME, NUMERO_DEPT, SALARIO FROM EMPREGADOS
WHERE NUMERO_DEPT = 20 ORDER BY SALARIO;

Exemplo 4: Selecionando apenas o empregado denominado SMITH.

SELECT NOME, NUMERO_DEPT, SALARIO FROM EMPREGADOS WHERE NOME = "CELIO";

Exemplo 5: Selecionando com operadores lógicos.

SELECT NOME, NUMERO_DEPT, SALARIO FROM EMPREGADOS
WHERE SALARIO> 1000;

Observações:

1. Operadores para comparações lógicas: = > >= <= <> 2. Outros comparadores:

BETWEEN ... AND ...	ou	NOT BETWEEN
IN (Lista)	ou	NOT IN
LIKE	ou	NOT LIKE
IS NULL	ou	IS NOT NULL

3. Operadores lógicos: AND
OR
NOT

Exemplo 6: Selecionando linhas com BETWEEN ... AND ...

SELECT NOME, DT_ADMISSAO FROM EMPREGADOS
WHERE DT_ADMISSAO BETWEEN "28-SEP-90" AND "30-JAN-91";

Exemplo 7: Selecionando linhas com a cláusula IN.

SELECT NOME, NUMERO_DEPT, SALARIO FROM EMPREGADOS
WHERE NUMERO_DEPT IN (10, 20) ORDER BY NUMERO_DEPT, SALARIO;

Exemplo 8: Selecionando linhas com a cláusula LIKE.

SELECT NOME, SALARIO FROM EMPREGADOS WHERE NOME LIKE "S%"
ORDER BY NOME;
OU
SELECT NOME, SALARIO FROM EMPREGADOS
WHERE NOME NOT LIKE "%I%"
ORDER BY NOME;
OU
SELECT NOME, SALARIO FROM EMPREGADOS
WHERE NOME LIKE "_A%";

Observações:

- "%" representa nenhum ou muitos caracteres.
- "_" representa um único caracter.

Exemplo 9: Selecionando linhas com a cláusula IS NULL.

A coluna Num_supervisor contém o número do empregado que supervisiona o empregado corrente.

SELECT NUMERO, NOME, CARGO, NUM_SUPERVISOR
FROM EMPREGADOS;

Com esta Query recuperamos o único empregado da empresa que não é gerenciado por ninguém, isto é, o Presidente da empresa.

SELECT NOME, CARGO, NUM_SUPERVISOR FROM EMPREGADOS
WHERE NUM_SUPERVISOR IS NULL;

Observação: O resultado da cláusula Where abaixo é sempre falso pois um valor nulo não pode ser igual ou diferente de outro valor, mesmo que este outro valor seja nulo. Se valores nulos forem comparados por operadores que não o "IS NULL" o resultado será sempre falso.

SELECT NOME, CARGO, NUM_SUPERVISOR FROM EMPREGADOS
WHERE NUM_SUPERVISOR = NULL;

Exemplo 10: Selecionando linhas com operadores lógicos.

SELECT NOME, SALARIO, NUMERO_DEPT FROM EMPREGADOS
WHERE SALARIO >= 3000 AND (NUMERO_DEPT = 10
OR NUMERO_DEPT = 30);

Funções Aplicadas a Linhas

Há dois tipos de funções:

- Funções de linhas
- Funções de grupos

Um função de linha retorna um resultado por linha da tabela acessada. Já uma função de grupo retorna um resultado por grupo de registros.

Exemplos de Funções de Linha:

- LOWER ("UFF") » uff
- UPPER ("uff") » UFF
- INITCAP ("UNIVERSIDADE FEDERAL") » Universidade Federal
- CONCAT ("String1", "String2") » String1String2
- SUBSTR ("String", 1, 3) » Str
- LENGTH ("UFF") » 3
- NVL (SAL, 0) » Se SAL for NULO seu valor será convertido para zero.
- ROUND (78.731, 2) » 78.73 (Até 4 p/ baixo, Acima de 4 p/ cima)
- ROUND (78.731, 0) » 79
- TRUNC (78.731, 2) » 78.73
- TRUNC (78.731) » 78
- MOD (100, 30) » 10

Exemplo 1: Utilização da função UPPER em uma sentença SQL.

SELECT NOME, SALARIO, NUMERO_DEPT FROM EMPREGADOS
WHERE UPPER (NOME) = "CELIO";

Exemplo 2: Utilização das funções SUBSTR e LENGTH em uma sentença SQL.

SELECT NOME, LENGTH (NOME) FROM EMPREGADOS
WHERE SUBSTR (NOME, 1, 3) = "CEL";

Como selecionar dados de mais de uma tabela

Para se exibir dados de mais de uma tabela, através de um comando SQL, é preciso definir condições de junção. (Joins)

Os joins geralmente ocorrem entre valores de chave primária e de chave estrangeira. Tipos de Joins:

Equijoin
Non-equijoin
Outer join
Self Join
Set operators

Um produto cartesiano geralmente ocorre quando a condição de junção é omitida ou inválida. Para evitar produtos cartesianos é preciso incluir, na cláusula Where, condições de junção válidas.

Exemplo 1:Uma junção simples entre a Tabela de Empregados (Emp) e a tabela de Departamentos (Dept).

SELECT EMPREGADOS.NOME, NUMERO_DEPT, DEPARTAMENTOS.NOME
FROM EMPREGADOS, DEPARTAMENTOS
WHERE EMPREGADOS.NUMERO_DEPT = DEPARTAMENTOS.NUMERO;

Obs: Haverá um ganho de desempenho e de clareza se você sempre qualificar as colunas com o nome das tabelas às quais elas pertencem.

Exemplo 2: Uma junção simples entre a Tabela de Empregados e a tabela de Departamentos considerando apenas aqueles empregados que ganham mais de 2500,00.

SELECT EMPREGADOS.NOME, EMPREGADOS.NUMERO_DEPT,
DEPARTAMENTOS.NOME
FROM EMPREGADOS, DEPARTAMENTOS
WHERE EMPREGADOS.NUMERO_DEPT = DEPARTAMENTOS.NUMERO
AND EMPREGADOS.SALARIO> 2500;
OU
SELECT E.NOME, E.NUMERO_DEPT, D.NOME
FROM EMPREGADOS E, DEPARTAMENTOS D
WHERE E.NUMERO_DEPT = D.NUMERO
AND E.SALARIO> 2500;

Exemplo 3: Uma junção entre a tabela de Empregados, a tabela de Departamentos e a tabela de Dependentes.

SELECT E.NOME, E.NUMERO_DEPT, DPT.NOME, DEP.NOME
FROM EMPREGADOS E, DEPARTAMENTOS DPT, DEPENDENTES DEP
WHERE E.NUMERO_DEPT = DPT.NUMERO AND E.NUMERO = DEP.NUMERO_EMP; Funções de Grupo

Funções de grupo operam com um conjunto de linhas para dar um resultado por grupo de linhas. Um conjunto de linhas pode ser uma tabela inteira ou linhas desta tabela divididas em grupos.

Funções de grupo podem aparecer tanto na cláusula Select quanto na cláusula Having.
A cláusula Group By divide as linhas de uma ou mais tabelas em grupos de linhas.
A cláusula Having seleciona os grupos que serão aceitos.

Funções de Grupo Existentes:

AVG
COUNT
MAX
MIN
STDDEV
SUM
VARIANCE

Observações:

A cláusula Distinct pode ser utilizada para que sejam considerados apenas valores não duplicatas. Todas as funções de grupo ignoram valores nulos. Para substituir um valor nulo por outro valor utilize a função NVL.

Exemplo 1: Utilização de funções de grupo, considerando todas as linhas de uma tabela um único grupo.

SELECT AVG(SALARIO), MAX(SALARIO), MIN(SALARIO), SUM(SALARIO)
FROM EMPREGADOS;
OU
SELECT MIN(NOME), MAX(NOME)
FROM EMPREGADOS;

Exemplo 2: Um único grupo definido através da cláusula Where.

SELECT AVG(SALARIO), MAX(SALARIO), MIN(SALARIO), SUM(SALARIO)
FROM EMPREGADOS WHERE CARGO LIKE "VEND%";

Exemplo 3: Utilização da função COUNT para contar o número de empregados lotados no departamento número 10.

SELECT COUNT(*) FROM EMPREGADOS WHERE NUMERO_DEPT = 10;

Exemplo 4: Utilização da função COUNT para contar o número de empregados que possuem percentual de comissão diferente de nulo.

SELECT COUNT(PERC_COMISSAO) FROM EMPREGADOS;

Exemplo 5: Utilização da função COUNT para contar o número de empregados na tabela.

SELECT COUNT(NVL(PERC_COMISSAO, 0)) FROM EMPREGADOS;

Observações:

COUNT(*) conta o número de linhas na tabela.
COUNT(PERC_COMISSAO) conta o número de linhas com percentual de comissão diferente de nulo.
COUNT(NUMERO) conta o número de linhas na tabela uma vez que a coluna NUMERO é a chave primária da tabela e toda chave primária não pode conter valores nulos.

A cláusula Group By

Exemplo 6: Utilização da cláusula GROUP BY e da função COUNT para se contar quantos empregados estão lotados em cada departamento.

SELECT NUMERO_DEPT, COUNT(*) FROM EMPREGADOS
GROUP BY NUMERO_DEPT;

Observações:

Qualquer coluna incluída na cláusula SELECT, se não estiver em uma função de grupo, deverá constar da cláusula GROUP BY.

Com a cláusula WHERE é possível excluir determinadas linhas dos grupos.

Por default as linhas são ordenadas ascendentemente conforme a lista de colunas especificada na cláusula GROUP BY. Para modificar este comportamento é preciso utilizar a cláusula ORDER BY.

Exemplo 7: Utilização da cláusula Group By, da função COUNT e de um JOIN para se contar quantos empregados estão lotados em cada departamento.

SELECT D.NOME "DEPARTAMENTO", COUNT(*) "QTD"
FROM EMPREGADOS E, DEPARTAMENTOS D
WHERE E.NUMERO_DEPT = D.NUMERO
GROUP BY D.NOME;

Exemplo 8: A query abaixo está correta? A intenção é listar o número dos departamentos seguido da média salarial. No entanto, deseja-se listar apenas aqueles departamentos cuja média salarial é superior a 2000.

SELECT NUMERO_DEPT, AVG(SALARIO) FROM EMPREGADOS
WHERE AVG(SALARIO)> 2000 GROUP BY NUMERO_DEPT;

A cláusula Having

Para se restringir a inclusão de grupos não se pode utilizar a cláusula WHERE.

A cláusula WHERE deve ser utilizada para se restringir a inclusão de linhas em um grupo. Para se omitir a inclusão de grupos inteiros do resultado de uma query deve-se utilizar a cláusula HAVING.

Exemplo 9: A utilização da cláusula HAVING para listar o número dos departamentos seguido da média salarial de seus empregados. No entanto, deseja-se listar apenas aqueles departamentos cuja média salarial é superior a 2000.

SELECT NUMERO_DEPT, AVG(SALARIO) FROM EMPREGADOS
GROUP BY NUMERO_DEPT
HAVING AVG(SALARIO) > 2000;

Exemplo 10: A utilização da cláusula GROUP BY para listar a quantidade de empregados por departamento/cargo. Isto é, grupos dentro de grupos. Não deve ser exibido Número de Departamento NULO.

SELECT NUMERO_DEPT, CARGO, COUNT(*) FROM EMPREGADOS
GROUP BY NUMERO_DEPT, CARGO HAVING NUMERO_DEPT IS NOT NULL; As cláusulas do comando Select são avaliadas na seguinte ordem:

Se o comando SQL contem a cláusula WHERE, o SGBD seleciona as linhas candidatas.
O SGBD identifica os grupos especificados pela cláusula GROUP BY.
A cláusula HAVING restringe os grupos resultantes que não estão de acordo com os critérios especificados nesta cláusula.

A recuperação de dados com subconsultas (Subqueries)

Uma subconsulta é um comando SELECT embutido em uma cláusula de outro comando SQL.

Quando e como utilizar:

Escreva subconsultas para recuperar dados baseados em critérios desconhecidos.
Pode ser muito útil quando se necessita selecionar linhas de uma tabela com uma condição que depende dos dados que estão na própria ou em outra tabela.
Subconsultas não podem conter a cláusula ORDER BY.
Duas classes de operadores de comparações são utilizadas em subconsultas:
Operadores de uma única linha: >, =, >=, <, <=, <>
Operadores de multiplas linhas: IN e NOT IN.
Como uma subconsulta é processada?
Primeiramente é executado o comando SELECT aninhado.
Em seguida o resultado é utilizado em uma condição da consulta principal.

Exemplo 1: A utilização de uma subconsulta aninhada para recuperar o nome e salário de todos os empregados que trabalham no mesmo departamento que o JOSE trabalha.

SELECT NOME, SALARIO FROM EMPREGADOS
WHERE NUMERO_DEPT = (SELECT NUMERO_DEPT
FROM EMPREGADOS
WHERE NOME = "JOSE");

Exemplo 2: A utilização de uma subconsulta aninhada para recuperar o nome e salário de todos os empregados que ganham mais do que a média salarial da empresa.

SELECT NOME, SALARIO FROM EMPREGADOS
WHERE SALARIO > (SELECT AVG(SALARIO)
FROM EMPREGADOS);

Exemplo 3: A utilização de uma subconsulta aninhada para recuperar o nome, número do departamento e salário de todos os empregados que trabalham no departamento situado no RIO ou no departamento denominado VENDAS.

SELECT NOME, NUMERO_DEPT, SALARIO
FROM EMPREGADOS
WHERE NUMERO_DEPT = (SELECT NUMERO
FROM DEPARTAMENTOS
WHERE LOCAL = "RIO" OR
NOME = "VENDAS");

Exemplo 4: A utilização de uma subconsulta aninhada para recuperar o nome, número do departamento e salário de todos os empregados que trabalham no departamento situado no RIO ou no departamento denominado VENDAS.

Correção da query anterior com a utilização da cláusula IN uma vez que esta subconsulta retorna mais de uma linha.

SELECT NOME, NUMERO_DEPT, SALARIO FROM EMPREGADOS
WHERE NUMERO_DEPT IN (SELECT NUMERO
FROM DEPARTAMENTOS
WHERE LOCAL = "RIO" OR
NOME = "VENDAS");

Exemplo 5: A utilização de uma subconsulta aninhada para recuperar o número do departamento e sua média salarial. Devem ser recuperados apenas os departamentos cuja média salarial é maior do que a média salarial do departamento número 30.

SELECT NUMERO_DEPT, AVG(SALARIO) FROM EMPREGADOS
GROUP BY NUMERO_DEPT
HAVING AVG(SALARIO) >= (SELECT AVG(SALARIO)
FROM EMPREGADOS
WHERE NUMERO_DEPT = 30);

A recuperação de dados com subconsultas correlacionadas

No exemplo abaixo a subconsulta é executada uma vez para cada linha da tabela de empregados na consulta mais externa.

Exemplo 1: A utilização de uma subconsulta correlacionada para recuperar o nome dos empregados que trabalham no projeto numero 2.

SELECT NOME FROM EMPREGADOS
WHERE 2 IN ( SELECT NUMERO_PROJ
FROM TRABALHAM
WHERE NUMERO_EMP = E.NUMERO);

Exemplo 2: A mesma query acima sem a utilização de subconsulta correlacionada.

SELECT NOME FROM EMPREGADOS E, TRABALHAM T
WHERE E.NUMERO = T.NUMERO_EMP
AND T.NUMERO_PROJ = 2;

Quantificador Existencial

Exists representa o quantificador existencial, uma noção emprestada da lógica formal. Em SQL, um predicado existencialmente quantificado é representado pela expressão da forma EXISTS (SELECT * FROM ... ).

Essa expressão será verdadeira se o resultado do cálculo da subconsulta representado por "SELECT * FROM ..." não estiver vazio, isto é, se existir pelo menos um registro na tabela FROM da subconsulta que satisfaz a condição WHERE dessa mesma subconsulta.

Qualquer consulta que utilize IN pode alternativamente ser formulada com EXISTS, porém o inverso não é verdadeiro.

Exemplo 1: Obter o nome dos empregados que trabalham no projeto nº 2.

SELECT NOME FROM EMPREGADOS E
WHERE EXISTS (SELECT *
FROM TRABALHAM
WHERE NUMERO_EMP = E.NUMERO
AND NUMERO_PROJ = 2);

OU

SELECT NOME
FROM EMPREGADOS E, TRABALHAM T
WHERE E.NUMERO = T.NUMERO_EMP
AND T.NUMERO_PROJ = 2;

Exemplo 2: Obter o nome dos empregados que não trabalham no projeto nº 2.

SELECT NOME FROM EMPREGADOS E
WHERE NOT EXISTS
(SELECT *
FROM TRABALHAM
WHERE NUMERO_EMP = E.NUMERO
AND NUMERO_PROJ = 2);

OU

SELECT NOME FROM EMPREGADOS MINUS SELECT NOME
FROM EMPREGADOS E, TRABALHAM T
WHERE T.NUMERO_EMP = E.NUMERO AND T.NUMERO_PROJ = 2;

União (Union e Union All)

Linhas duplicatas são eliminadas do resultado de uma união a não ser que o operador UNION inclua explicitamente o quantificador ALL. Assim, no exemplo nº 1, o projeto nº 3 é selecionado em ambos os SELECTS, mas só aparece uma vez no resultado final.

Já o exemplo nº 2 retornará os números de projeto 2, 3 e 3.

Qualquer número de SELECTS pode ser unido pelo UNION.

Quando sabemos que não haverá elementos duplicados no resultado é conveniente utilizarmos UNION ALL para que o sistema não seja forçado a eliminar duplicidades, desnecessariamente.

Exemplo 1: Obter o número dos projetos que, ou se iniciaram após 31-JUL-97, ou possuem o empregado 7566 nele trabalhando. União sem repetição.

SELECT NUMERO FROM PROJETOS WHERE DT_INICIO > "31-JUL-97"
UNION
SELECT NUMERO_PROJ FROM TRABALHAM
WHERE NUMERO_EMP = 7566;

Exemplo 2: Obter o número dos projetos que, ou se iniciaram após 31-JUL-97, ou possuem o empregado 7566 nele trabalhando. União com repetição.

SELECT NUMERO FROM PROJETOS WHERE DT_INICIO > "31-JUL-97"
UNION ALL
SELECT NUMERO_PROJ FROM TRABALHAM WHERE NUMERO_EMP = 7566;

Exemplo 3: A possibilidade de incluirmos constantes numa cláusula SELECT é frequentemente útil quando a cláusula UNION é utilizada. Por exemplo, para indicar qual das duas condições WHERE foi atendida para a inclusão do elemento no resultado final.

SELECT NUMERO, "DT_INICIO> 07-JAN-90" CONDIÇÃO
FROM PROJETOS
WHERE DT_INICIO > "07-JAN-90"
UNION ALL
SELECT NUMERO_PROJ, "NUMERO_EMP = 7566"
FROM TRABALHAM
WHERE NUMERO_EMP = 7566;

Resultado:

NUMERO          CONDIÇÃO
------------ 	---------------------
          2     DT_INICIO> 07-JAN-90
          3     DT_INICIO> 07-JAN-90
          4     DT_INICIO> 07-JAN-90
          3     NUMERO_EMP  =  7566

No próximo artigo vamos continuar a falar sobre os comandos SQL DML (Insert, Delete e Update).

Banco de Dados - SQL Server

Leia também