Apostila de Linguagem SQL, Notas de estudo de Informática

Baixe Apostila de Linguagem SQL e outras Notas de estudo em PDF para Informática, somente na Docsity!

Instituto de informática

Disciplina:

Banco de Dados I

Linguagem SQL

Professor:

Gustavo Belgini Andretta

Puc-Campinas – Banco de Dados I – Projeto de Banco de Dados 1

1

Índice

• 1 Linguagem SQL.....................................................................................................................................................
• 1.1 Notação para Sintaxes..................................................................................................................................................
• 2 Tabelas, Colunas e Linhas...........................................................................................................................................
• 3 O Banco de Dados Exemplo........................................................................................................................................
• 4 Linguagem de Manipulação de Dados Interativa (DML)............................................................................................
• 4.1 SELECT.....................................................................................................................................................
• 4.1.1 Eliminação de linhas duplicadas..................................................................................................................................
• 4.1.2 Constantes e Expressões..............................................................................................................................................
• 4.1.3 Condições de Procura..................................................................................................................................................
• 4.1.4 Predicados Relacionais................................................................................................................................................
• 4.1.5 Predicado Between.....................................................................................................................................................
• 4.1.6 Predicado NULL.....................................................................................................................................................
• 4.1.7 Predicado LIKE.....................................................................................................................................................
• 4.1.8 Predicado Exists.....................................................................................................................................................
• 4.1.9 Predicado IN.....................................................................................................................................................
• 4.1.10 Utilizando mais de uma tabela na cláusula from.........................................................................................................
• 4.1.11 Chaves.....................................................................................................................................................
• 4.1.12 Variáveis Tuplas.....................................................................................................................................................
• 4.1.13 Operações de Conjuntos..............................................................................................................................................
• 4.1.14 Exibindo Resultados Ordenados..................................................................................................................................
• 4.1.15 Funções de agregação..................................................................................................................................................
• 4.1.16 A Cláusula HAVING....................................................................................................................................................
• 4.1.17 Consultas Complexas...................................................................................................................................................
• 5 Funções Built-in.....................................................................................................................................................
• 5.1 Funções para manipulação de strings..........................................................................................................................
• 5.2 Funções para manipulação de números.......................................................................................................................
• 5.3 Funções para manipulação de datas.............................................................................................................................
• 5.4 Funções de conversão..................................................................................................................................................
• 6 Modificando o Banco de Dados...................................................................................................................................
• 6.1 Inserindo Linhas: INSERT...........................................................................................................................................
• 6.2 Removendo Linhas: DELETE.....................................................................................................................................
• 6.3 Modificando Linhas: UPDATE...................................................................................................................................
• 7 Introdução a DDL - Linguagem de Definição de Dados.............................................................................................
• 7.1 Tabelas (Tables) - Criando, Alterando e Excluindo.....................................................................................................
• 7.2 Visões (Views).....................................................................................................................................................
• 7.3 Índices (Indexes).....................................................................................................................................................

Blocos de comandos limitados pelo símbolo “|” (pipe) são opcionais, ou seja, podem ser omitidos sem invalidar a expressão. Neste caso, a seqüência C1, C2 ... Cn pode ser omitida por estar entre pipes: | (<C1, C2 ... Cn>) |. As outras palavras que não se encontram entre “< >” ou entre “| |” são obrigatórias (palavras chaves ). As palavras insert , into e values , neste caso, são palavras-chaves.

Tabelas, Colunas e Linhas

Nos bancos de dados relacionais, os dados são armazenados em relações ou tabelas. Um exemplo de tabela poderia conter os dados básicos dos setores de uma empresa, como seu Código e seu Nome.

Tabela de Setores

Código e Nome, que no Modelo Entidade-Relacionamento (MER) nós chamamos de atributos, aqui são denominados colunas. Uma linha é a denominação dada a cada instância armazenada na tabela de Setores, ou seja, (100, Vendas) constitui uma linha ou registro. Da mesma forma que os dados são armazenados, o resultado de um comando de consulta SQL, também é obtido sob a forma de tabelas, ou seja, um conjunto de linhas e colunas. O banco de dados utilizado para a construção dos exemplos citados nesta apostila foi desenvolvido a partir do exemplo “Controle de Estoque” mostrado a seguir através do seu Projeto Conceitual (Diagrama de Entidades e Relacionamentos) e correspondente diagrama de tabelas.

O Banco de Dados Exemplo

Todos os exemplos desta apostila estão baseados num bancos de dados exemplo, que se propõe a armazenar, simplificadamente, os dados de um controle de estoque.

A seguir, temos o seu Modelo de Entidade-Relacionamento (MER), seguido pelo seu Diagrama de Tabelas (ou Diagrama Relacional).

Linguagem de Manipulação de Dados Interativa (DML)

Como citado anteriormente, esta porção da linguagem SQL cuida de fornecer aos usuários comandos e funções para a recuperação de informações, bem como a atualização destas no banco de dados.

A instrução utilizada para consultas na base de dados é a select , enquanto para atualizações são utilizadas as instruções insert , para inserção de linhas, delete , para remoção de linhas, e update , para alterações nas linhas.

SELECT

A estrutura básica da instrução de consulta select consiste em três cláusulas:

• select: É usada para listar os atributos desejados no resultado da consulta, ou seja, as colunas requisitadas como respostas.Esta lista, além de nomes de colunas, pode conter: - um asterisco (*), indicando que todas as colunas devem ser retornadas; - expressões; - constantes; - funções; - qualquer combinação destes, conectadas por operadores aritméticos e parênteses.
• from: Lista as tabelas a serem examinadas na avaliação da expressão.As colunas indicadas na cláusula select devem constar nas tabelas listadas nesta cláusula.
• where: Consiste de uma condição de procura envolvendo atributos das tabelas que aparecem na cláusula from. É o lugar onde se restringe ou seleciona quais linhas deverão ser retornadas. Uma consulta típica SQL tem a forma:

select < A1, A2, ..., An > from < R1, R2, ..., Rn > | where P |

onde:

• Ai : representa uma coluna (atributo) de uma tabela Ri, uma expressão, constante ou função;
• Ri : representa uma tabela;
• P : uma condição de procura que utiliza os atributos de R1, ..., Rn. A cláusula where pode ser omitida; neste caso, o predicado é tido como sempre verdadeiro e todas as linhas serão retornadas. A lista de colunas na cláusula select (A1, .., An) pode ser substituída por um asterisco (*) para selecionar todos os atributos de todas as tabelas presentes na cláusula from. Considerando uma simples consulta ao banco de dados exemplo: “Quais os códigos de todos os funcionários que fizeram alguma requisição ao estoque ?”; a instrução correspondente será:

2. O operador not é analisado antes do and ;
3. And antes do or ;
4. Operadores do mesmo nível de precedência são analisados da esquerda para a direita. Utilizando a regra de precedência, a ordem de avaliação dos predicados, do exemplo anterior, será:
5. R1 = not P
6. R2 = R1 and P
7. R3 = R2 and P
8. R4 = R3 or P Onde: Ri trata-se um resultado booleano intermediário. Predicados usam operadores, expressões, e constantes para especificar a condição a ser avaliada. Os seguintes tipos de predicados serão discutidos:

• Relacionais
• BETWEEN
• NULL
• LIKE
• EXISTS
• IN

Predicados Relacionais

Os predicados relacionais são aqueles que relacionam duas expressões segundo um operador. Os seguintes operadores são suportados:

Operador Significado = Igual != Diferente <> Diferente

Maior !> Não maior (menor ou igual ) < Menor !< Não menor (maior ou igual ) = Maior ou igual <= Menor ou igual

Um predicado relacional segue a seguinte sintaxe: <expressão> <expressão>. Logo abaixo encontra-se um exemplo utilizando operadores relacionais. Neste exemplo, queremos obter todos os dados dos funcionários cujos salários são maiores ou iguais a R$ 1.000,000.

select * from FUNCIONARIO where SALARIO >= 1000

Lembre que o uso do asterisco (*) na cláusula select indica o desejo de que todas as colunas sejam retornadas.

Predicado Between

O predicado between compara um valor com uma faixa de valores. Os limites da faixa estão inclusos. Ele possui a seguinte sintaxe: <expressão> | not | between <expressão> and <expressão> O exemplo seguinte mostra uma instrução usando between :

select DESCRICAO from PRODUTO where PRECO between 50 and 100

Neste exemplo, recuperamos todas as descrições de produtos, cujos seus preços estão entre R$ 50,00 e R$ 100,00.

Predicado NULL

O predicado NULL testa valores nulos. Verifica, por exemplo, se colunas não contém nenhum valor armazenado. Ele possui a seguinte sintaxe: is | not | NULL Caso seja necessário conhecer os funcionários cujo cadastro do endereço esteja faltando, utilizaríamos a seguinte instrução:

select COD_FUNCIONARIO, NOME from FUNCIONARIO where ENDERECO is NULL

É possível utilizar o operador not juntamente com a palavra-chave is ( is not NULL ) para, por exemplo, montar condições de colunas que não possuem valores nulos.

Predicado LIKE

O predicado LIKE procura por strings que se encontram dentro de um determinado padrão. O predicado LIKE só pode ser usado com tipos de dados CHAR e VARCHAR.

A sintaxe para o predicado é seguinte: | not | like < padrão de pesquisa > O padrão de pesquisa trata-se de uma string a ser comparada com a coluna especificada. Este padrão pode ser formatado com a utilização de coringas, permitindo a procura de substrings. Os dois caracteres coringas existentes são o símbolo porcentagem ‘%’ e o undescore ‘_’.

% Equivale a zero ou mais caracteres _ Equivale a um caracter qualquer

Os exemplos abaixos mostram a utilização do predicado LIKE:

• Verificar todos os funcionários que possuem “Silva” como último sobrenome.

select * from FUNCIONARIO where NOME like ‘%Silva’

• Retornar todos os códigos das requisições dos produtos que os códigos comecem com ‘01’.

from FUNCIONARIO where SALARIO > 1000)

Utilizando mais de uma tabela na cláusula from

Nos exemplos que discutimos até o presente momento, só utilizamos uma tabela na cláusula from , ou seja, pesquisamos apenas em uma tabela individualmente. Entretanto, na maioria dos casos, precisaremos combinar e recuperar dados de diversas tabelas simultaneamente. Para conseguirmos isto, devemos relacionar na cláusula from todas as tabelas necessárias. Caso fosse necessário selecionar, por exemplo, todos os funcionários (código e nome) e seus respectivos setores (código e nome) seria preciso extrair dados de duas tabelas: FUNCIONARIOS e SETORES, pois o código, nome e código do setor do funcionário poderia ser extraído da tabela FUNCIONÄRIOS, mas o nome do setor apenas da tabela setor. Desta maneira a instrução seria a seguinte:

select COD_FUNCIONARIO, FUNCIONARIO.NOME, FUNCIONARIO.COD_SETOR, SETOR.NOME from FUNCIONARIO, SETOR where FUNCIONARIO.COD_SETOR = SETOR.COD_SETOR

A linguagem utiliza-se de um método chamado produto cartesiano para resolver esta questão. O produto cartesiano de tabelas, gera uma nova tabela auxiliar contendo todas as colunas de todas as tabelas listadas na cláusula from , relacionando cada linha de uma tabela com todas as outras da segunda tabela, e assim por diante, gerando uma grande tabela que possui todas as combinações de linhas posssíveis. Para discutirmos melhor este método imaginemos que as nossas tabelas FUNCIONARIO e SETOR tivessem os seguintes dados: FUNCIONARIO :

COD_FUNCIONARIO NOME COD_SETOR Outras Colunas 10 André Pires 100 nononononononononon 11 Roberto Azevedo 100 nononononononononon 20 Carla Campos 200 nononononononononon 30 Cleber Barros 300 nononononononononon

A coluna “Outras Colunas” está representando todas as demais colunas (RG, CPF, ENDERECO, CIDADE, ESTADO, SALARIO) da tabela, apenas para simplificar a demonstração. SETOR:

COD_SETOR NOME

100 Vendas 200 PCP 300 Produção

Aplicando o produto cartesiano entre FUNCIONARIO e SETOR a tabela auxiliar resultante FUNCIONARIO-SETOR seria a seguinte: FUNCIONARIO-SETOR:

COD_FUNCIONARIO FUNCIONARIO.NOME FUNCIONARIO.COD_SETOR Outras Colunas SETOR.COD_SETOR SETOR. NOME

10 André Pires 100 nononon 100 Vendas 10 André Pires 100 nononon 200 PCP 10 André Pires 100 nononon 300 Produção 11 Roberto Azevedo 100 nononon 100 Vendas 11 Roberto Azevedo 100 nononon 200 PCP 11 Roberto Azevedo 100 nononon 300 Produção 20 Carla Campos 200 nononon 100 Vendas 20 Carla Campos 200 nononon 200 PCP 20 Carla Campos 200 nononon 300 Produção 30 Cleber Barros 300 nononon 100 Vendas 30 Cleber Barros 300 nononon 200 PCP 30 Cleber Barros 300 nononon 300 Produção

Verificamos, então, que a tabela gerada possui todas as colunas de FUNCIONARIO adicionadas às colunas de SETOR. Note que para o atributos COD_SETOR e NOME foram necessários qualificadores, representados pelo nome da tabela, para não causar ambigüidades. Sempre que colunas com nomes idênticos existirem será necessário inserir os nomes das tabelas à frente dos nomes das colunas, para fazer a distinção.

A tabela resultante ficou com 12 linhas, resultado do produto entre o número de linhas da tabela FUNCIONARIO (4 linhas) e a tabela SETOR (3 linhas ). No entanto, dentre estas linhas, várias não têm sentido prático, pois FUNCIONARIO.COD_SETOR é diferente de SETOR.COD_SETOR. Deste modo, sempre que utilizarmos várias tabelas numa cláusula from , ou seja, utilizarmos produto cartesiano, devemos fazer o que a álgebra relacional chama de ligação natural, eliminando essas linhas. No caso a restrição foi feita através do predicado “where FUNCIONARIO.COD_SETOR = SETOR.COD_SETOR”.

Chaves

Antes de discutirmos o que vem a ser a ligação natural, devemos discutir o conceito de chaves ( keys ). Num banco de dados relacional, existem dois tipos de chaves: Chave Primária ( primary key ) Uma chave primária é uma coluna ou um conjunto de colunas que identificam unicamente uma linha numa tabela. As colunas que fazem parte da chave primária são os atributos determinantes do MER.Por exemplo, na tabela SETOR, o código (COD_SETOR) identifica unicamente cada linha. Deste modo, uma chave primária é um valor que não pode se repetir dentro de uma mesma tabela. Chave Estrangeira ( foreign key ) Uma chave estrangeira é uma ou mais colunas numa tabela que são chaves primárias em outra tabela, o que significa que qualquer valor para uma coluna que é chave estrangeira deve corresponder a um valor existente na outra tabela onde a coluna é chave primária.Na linguagem dos sistemas gerenciadores de banco de dados, isto é conhecido como integridade referencial. Por exemplo, temos o código do setor (COD_SETOR) na tabela FUNCIONARIO que é uma chave estrangeira para o campo de mesmo nome na tabela SETOR onde este é chave primária. Decorrente do conceito de chave estrangeira, também surge a definição de regras de deleção. Esta regra determina qual processamento deve ser praticado quando da remoção de linhas de uma tabela e que são referenciadas como chaves estrangeiras em outras tabelas. Os dois tipos de regra de deleção mais comuns são:

• Restrita ( restrict ) : este regra determina que uma linha somente pode ser excluída de uma tabela se os seus valores de chave primária não forem referenciados por nenhuma linha na tabela onde ele pode aparecer como chave estrangeira. No nosso exemplo, é o que acontece nas tabelas SETOR e FUNCIONARIO, onde um setor somente pode ser removido caso não exista nenhum funcionário pertencente àquele setor;

Para aqueles que devolveram algum produto:

select COD_FUNCIONARIO from DEVOLUCAO

Agora para fazermos a união destes dois conjuntos de valores, utilizamos o operador union. Assim a instrução resultante é:

select COD_FUNCIONARIO from REQUISICAO

union

select COD_FUNCIONARIO from DEVOLUCAO

Se a consulta fosse os códigos dos funcionários que participaram de uma requisição e de uma devolução, teríamos:

select COD_FUNCIONARIO from REQUISICAO

intersect

select COD_FUNCIONARIO from DEVOLUCAO

O mesmo resultado obteríamos com a seguinte sentença:

select COD_FUNCIONARIO from REQUISICAO where COD_FUNCIONARIO in (select COD_FUNCIONARIO from DEVOLUCAO )

Se a consulta fosse para obter os códigos dos funcionários que participaram de alguma requisição, mas não participaram de nenhuma devolução, teríamos:

select COD_FUNCIONARIO from REQUISICAO

minus

select COD_FUNCIONARIO from DEVOLUCAO

O mesmo resultado obteríamos com a seguinte sentença:

select COD_FUNCIONARIO from REQUISICAO where COD_FUNCIONARIO not in (select COD_FUNCIONARIO from DEVOLUCAO )

Portanto pudemos comprovar que tanto a operação intersect como a operação minus podem ser realizadas em construções análogas utilizando in e not in , respectivamente.

É importante ressaltar que o operador union trata as tabelas como conjuntos, e pela regra de união de conjuntos, valores duplicados são descartados. Assim, se um funcionário requisitou e devolveu algum produto, ele aparecerá apenas uma vez na lista resultante. Caso seja necessário manter as duplicações, deve- se utilizar o operador union all ao invés de union.

Exibindo Resultados Ordenados

SQL oferece controle sobre a ordem na qual as linhas do resultado de uma consulta estão dispostas. A cláusula order by ocasiona o aparecimento das linhas de resultado de uma consulta em uma ordem determinada. Por exemplo para listar em ordem alfabética todos os funcionários do setor 100, escrevemos:

select F.NOME, COD_FUNCIONARIO from FUNCIONARIO F, SETOR S where F.COD_SETOR = S.COD_SETOR and S.COD_SETOR = 100 order by F.NOME

Como padrão, SQL lista itens na ordem ascendente. Para especificar a ordem de classificação, podemos especificar desc para descendente ou asc para ascendente. Além do mais, a ordenação pode ser feita por múltiplos atributos, bastando, para isto listá-los na cláusula order by. Suponha que fosse necessário gerar uma lista contendo as requisições realizadas em ordem descendente, ou seja, primeiro pelos mais recentes. Para as requisições realizadas no mesmo momento, ordenamos em ordem ascendente pelo número da requisição. Neste caso o comando SQL seria:

select * from REQUISICAO order by data desc , COD_REQUISICAO asc

Para atender a cláusula order by , SQL precisa executar uma classificação. Uma vez que uma classificação de um grande número de linhas pode ser custoso, deve-se utilizá-la apenas quando necessário.

Funções de agregação

SQL oferece a possibilidade de computar funções em grupos de linhas usando a cláusula group by. Os atributos, dados na cláusula group by, são usados para formar grupos. Linhas com o mesmo valor em todos os atributos na cláusula group by são colocados em um grupo. SQL inclui funções de agregação para computar:

• média: avg
• mínimo: min
• máximo: max
• total: sum
• contar: count As operações, como a avg , são chamadas funções de agregação, porque operam em agregações de linhas. O resultado de uma função de agregação é um valor único. Para ilustrar, considere a consulta “Selecione o salário médio em cada setor da empresa”. A instrução ficaria assim:

select COD_SETOR, avg (SALARIO) from FUNCIONARIO group by COD_SETOR

Funções de Agregação e seus resultados

where ( select count() from* FUNCIONARIO T where T.COD_SETOR = S.COD_SETOR ) > 15

Funções Built-in Além dos comandos, cláusulas e funções agregadas da linguagem SQL , os gerenciadores de bancos de dados disponibilizam uma série de outras funções para ajudar o usuário na manipulação dos dados. Estas funções podem ser utilizadas normalmente com as instruções SQL comuns. Contudo, essas extensões acarretam uma grande desvantagem quando se pretende desenvolver sistemas multiplataformas, pois estas funções são específicas para cada servidor de banco de dados. Por exemplo, uma função de conversão de inteiro para string, por exemplo, é diferente entre o Oracle e o Sybase.

O Oracle fornece um grande conjunto de funções para manipular strings, datas e números. Dentre elas, podemos destacar:

Funções para manipulação de strings

Função Propósito ASCII(x) Retorna o valor numérico ASCII do caracter x CHR(x) Retorna o caracter ASCII correspondente ao inteiro x CONCAT(x,y) Retorna x concatenado com y INITCAP(x) Retorna a string x com a primeira letra de cada palavra em maiúscula e as restantes em minúscula (nome próprio) LENGTH(x) Retrona o tamanho da string x LENGTHB(x) Retorna o tamanho em bytes da string x LOWER(x) Retorna a string x com todas as letras em minúscula LTRIM(x|,y|) Elimina caracteres à esquerda da string x e retorna a string resultante. Se y não for especificado, caracteres em branco serão removidos. O valor da string y (opcional) é o conjunto de caracteres a ser eliminado REPLACE(x,y|,z|) Retorna a string x com todas as ocorrências de y substituídas por z. Se z for omitido, todas as ocorrências de y serão simplesmente removidas RTRIM(x|,y|) Elimina caracteres à direita da string x e retorna a string resultante. Se y não for especificado, caracteres em branco serão removidos. O valor da string y (opcional) é o conjunto de caracteres a ser eliminado SUBSTR(x,y|,z|) Retorna a parte da string x, iniciando com o caracter y. Se z for especificado, a função retorna z caracteres a partir de y (inclusive); caso contrário, a parte retornada vai até o final da string x UPPER(x) Retorna a string x com todas as letras em maiúscula

Um exemplo, de utilização das funções de manipulação de strings , seria a eliminação de todos os espaços em branco que tenham sido digitados à direita dos nomes dos funcionários:

update FUNCIONARIO set NOME = RTRIM (NOME)

Funções para manipulação de números

Função Propósito ASCII(x) Retorna o valor absoluto de x CEIL(x) Retorna o menor inteiro superior ou igual a x COS(x) Retorna o co-seno de x COSH(x) Retorna o co-seno hiperbólico de x

select distinct TO_NUMBER ( TO_CHAR (DATA,’yyyy’)) from REQUISICAO

Modificando o Banco de Dados

Até o presente momento, restringimos nossa atenção à consulta ao banco de dados. Agora discutiremos as instruções da SQL para atualização do banco, através dos comandos insert, delete e update.

Inserindo Linhas: INSERT

Para inserir um dado numa tabela, podemos fazê-lo de duas formas:

• especificando uma linha a ser inserida, ou seja, os campos e seus valores;
• escrevendo uma consulta cujo o resultado seja um conjunto de linhas a serem inseridas. A primeira maneira é mais simples, pois inserimos uma linha por vez, atribuindo valores as colunas da tabela. Sintaxes: insert into | (<C1, C2 ... Cn>) | values (< V1, V2 ... Vn >) insert into <select ... from ... where ...> onde:
• Ci : nome da coluna
• Vi : valor a ser inserido. Pode ser uma constante ou uma expressão. Os nomes das colunas podem ser omitidos, se a ordem dos valores a serem inseridos corresponder com a ordem de criação dos mesmos. Para incluirmos o setor de Compras no nosso banco de dados exemplo utilizaríamos a seguinte instrução:

insert into SETOR (COD_SETOR, NOME ) values (500, ‘Compras’)

Deve-se notar que ao fazer uma inserção todas as restrições ( constraints ) de integridade referencial declaradas serão processadas, de modo a preservar a consistência dos dados. Na tabela SETOR há apenas a restrição de chave-primária (COD_SETOR), que não permitirá a inserção de um setor com o mesmo código. Entretanto, em outras tabelas, como a FUNCIONARIO, existem restrições de chave estrangeira. Esta restrição não permitirá que um funcionário seja inserido num setor inexistente, ou seja, que o seu COD_SETOR não exista na tabela SETOR. Pode-se também fazer uma inserção através do resultado de uma consulta. Conseguimos assim fazer uma inserção em lote. Imaginemos um caso, onde a requisição de código 1015 realizada pelo funcionário 10, deve ser extornada, ou seja, todos os itens que constam como requisitados deverão ser lançados na devolução de código 5005 com a mesma data da requisição. Deveríamos fazer os seguintes inserts :

insert into DEVOLUCAO select 5005, COD_FUNCIONARIO, DATA from REQUISICAO where COD_REQUISICAO = 1015

insert into ITENS_DEVOLUCAO select 5005, COD_PRODUTO, QUANTIDADE from ITENS_REQUISICAO where COD_REQUISICAO = 1015