100
CURSO DE SQL - APOSTILA

Curso SQL - Apostila

Embed Size (px)

Citation preview

Page 1: Curso SQL - Apostila

CURSO DE SQL - APOSTILA

Page 2: Curso SQL - Apostila

2

Índice Pag

1. Introdução a SQL 04 1.1. O que é SQL 04 1.2. O que é um Banco de Dados Relacional 04 1.3. Regras de um Banco de Dados Relacional 05

2. Introdução a QUERY – O comando SELECT 07 3. Expressões, condições e operadores 11

3.1. Expressões 11 3.2. Condições 11 3.3. Operadores 11

3.3.1.Operadores aritméticos 11 3.3.2.Operadores de comparação 17 3.3.3.Operadores de caractere 22 3.3.4.Operadores lógicos 25 3.3.5.Operadores SET 26 3.3.6.Outros operadores 30

4. Funções - Modelando os dados capturados 33 4.1. Funções de agregação 33 4.2. Funções de data/hora 36 4.3. Funções aritméticas 39 4.4. Funções de caractere 42 4.5. Funções de Conversão 48 4.6. Outras funções 48

5. Clausulas do SQL 50 5.1. A clausula WHERE 50 5.2. A clausula STARTING WITH 51 5.3. A clausula ORDER BY 51 5.4. A clausula GROUP BY 53 5.5. A clausula HAVING 54

6. Unindo Tabelas – JOIN 56 6.1. INNER JOIN 57 6.2. OUTER JOIN 60

7. SUBQUERIES – Aprofundando o comando SELECT 62 8. Manipulando dados 68

8.1. O comando INSERT 68 8.2. O comando UPDATE 70 8.3. O comando DELETE 73

9. Criando e mantendo Tabelas 74 9.1. Criando um Banco de Dados 74 9.2. Criando Tabelas 74 9.3. Alterando a estrutura de uma Tabela 77 9.4. Excluindo Tabelas e Banco de Dados 77

10. VIEW 78

Page 3: Curso SQL - Apostila

3

10.1. WITH CHECK OPTION 79 10.2. WITH ENCRYPTION 79 10.3. VIEW de VIEW 80

11. STORED PROCEDURE 81 11.1. Regras para criar STORED PROCEDURE 81

12. Transação 84 12.1. Comandos de Transação 84 12.2. @@ERROR 85

13. Tópicos avançados de SQL – CURSOR 87 13.1. Criando um CURSOR 87 13.2. Abrindo um CURSOR 88 13.3. Acessando um CURSOR 88 13.4. Fechando um CURSOR 89

14. Erros comuns em SQL e suas soluções 93 15. Exercícios propostos 95

Page 4: Curso SQL - Apostila

4

1 – Introdução a SQL. 1.1 – O que é SQL A história do SQL se inicia no fim da década de 70, nos laboratórios da IBM em San Jose, Califórnia. Inicialmente. No início, a linguagem era chamada de SEQUEL (Structured English QUery Language) pois o objetivo era fornecer comandos mais intuitivos, próximos à língua inglesa, depois, este protótipo sofreu várias revisões, até que em 1977 a linguagem passa a se chamar SQL (Structured Query Language). O SQL é uma linguagem não-procedural, o que significa que os procedimentos desta são menos detalhados que os de uma linguagem procedural. Uma linguagem procedural implica em algoritmos mais complexos para realizar uma tarefa, como ocorre, por exemplo, no Cobol, onde devemos especificar o passo a passo de um procedimento. As linguagens procedurais, como C, Pascal e Cobol são as linguagens de terceira geração. Linguagens para processamento de Banco de Dados são chamadas de linguagens de quarta geração. O SQL foi desenvolvido como uma ferramenta pertencente ao Banco de Dados Relacional DB2, criado pelo Dr. E. F. Cood, para a IBM, e foi a ferramenta que tornou realmente possível a tecnologia de Banco de Dados Relacionais. Devido ao grande sucesso do modelo relacional, surgem várias versões sobre a linguagem SQL. Devido a isto, em 1982 o ANSI propôs uma padronização, que foi aceita em 1986. Em 1987 o ISO aceita esta padronização e a linguagem passa a ser conhecida como SQL/86. Após duas novas padronizações, a linguagem é formalmente chamada de SQL2 ou SQL/92. Atualmente está em curso o desenvolvimento do SQL/3, visando transformar o SQL em uma linguagem de programação completa e orientada a objetos. 1.2 – O que é um Banco de Dados Relacional. Em um banco de dados relacional as bases de dados são vistas como um conjunto de tabelas onde cada uma representa uma relação, onde a integridade de entidade com as chaves primárias é mantida, juntamente com a integridade referencial com as chaves estrangeiras. Isto significa que se tivermos duas tabelas e em uma delas nos referenciarmos à chave primária da outra, o relacionamento entre as duas garantirá a integridade dos dados de ambas.

Page 5: Curso SQL - Apostila

5

1.3 – Regras de um Banco de Dados Relacional. O Dr. E. F. Cood definiu as seguintes regras para o Banco de Dados Relacional:

• Um Banco de Dados Relacional deve estar habilitado para gerenciar bases de dados inteiramente através de suas capacidades relacionais;

• Toda informação dentro de um Banco de Dados Relacional, incluindo as tabelas e os seus campos são representados explicitamente como valores dentro de tabelas;

• Qualquer valor em uma tabela pode ser acessado através da combinação entre: nome da tabela, valor da chave primária e nome da coluna ou campo;

• Um Banco de Dados Relacional deve ter capacidade de tratar valores nulos – NULL – desconhecidos ou inaplicáveis;

• O BD deve possuir um catálogo relacional ativo e on-line, para tratar as descrições de campos no nível lógico, possibilitando a sua consulta através das linguagens para tratamento de banco de dados;

• O Banco de Dados Relacional deve suportar ao menos uma linguagem bem definida, que: permita a definição e manipulação de dados, possua regras de integridade e possibilite trabalhar com níveis de autorização e transações;

• Todas as VIEWS - visões lógicas que permitem a representação de dados de uma ou mais tabelas como se fosse outra tabela - devem ser atualizáveis pelo sistema;

• Um Banco de Dados relacional deve possibilitar não só a recuperação dos dados, mas também a inserção, exclusão e alteração dos mesmos;

• A alteração de um método de acesso ou da estrutura de armazenamento físico dos dados não deve afetar logicamente as aplicações que utilizam o Banco de Dados Relacional;

• Alterações na estrutura de uma tabela não devem afetar logicamente - na medida do possível - os aplicativos que utilizam o BD;

• As linguagens suportadas pelo Banco de Dados Relacional devem ser capazes de definir e garantir regras de integridade para suas entidades. Estas regras devem estar disponíveis através do catálogo relacional on-line e não deve existir a possibilidade de serem dribladas;

• As aplicações não devem ser afetadas na redistribuição dos dados; • As regras de integridade definidas pela linguagem da base de dados

não podem ser dribladas ou alteradas utilizando linguagens de baixo nível.

Page 6: Curso SQL - Apostila

6

A maioria dos Bancos de Dados possui a estrutura de relacionamentos pai-filho, onde a entidade-pai possui ponteiros apontando para as entidades-filho. Este método possui muitas vantagens e algumas desvantagens. A seu favor temos o fato de que a estrutura física do banco de dados não é mais importante, pois o programador passa a armazenar somente a localização lógica, permitindo que os dados possam ser acessados independente da estrutura de armazenamento física. Assim, qualquer acesso aos dados pode ser facilmente executado. Por outro lado, pode não ser fácil juntar grupos de dados distintos para formar novas estruturas. O formato dos dados no disco não pode ser arbitrariamente mudado após a criação do Banco de Dados. Devido ao fato de que a informação poder ser naturalmente agrupada, Dr E. F. Cood organizou seu sistema de dados em torno deste conceito (teoria dos conjuntos). Dentro do modelo relacional, o Banco de Dados é separado em estruturas, chamadas de Tabelas. As Tabelas, por sua vez, são compostas por elementos de dados individuais, chamados de campos ou colunas. Um conjunto único de campos é chamado de registro ou linha. A estrutura das tabelas, depois da escolha da plataforma, é a mais importante decisão no desenvolvimento de uma aplicação com Banco de Dados. A estrutura das tabelas afeta tanto o desenvolvimento quanto a performance da aplicação. O processo de estruturação das tabelas é chamado de normalização.

Page 7: Curso SQL - Apostila

7

2 – Introdução a QUERY – O comando SELECT. O comando SELECT é de longe a instrução mais comum do SQL. Uma instrução SELECT retorna um conjunto de registros que atendam à condição especificada no SELECT. Ao executar este comando, o programa principal do Banco de Dados procura a tabela ou tabelas especificadas, extrai as colunas escolhidas, seleciona as linhas que satisfazem o critério e classifica as linhas resultantes na ordem especificada. O SELECT não altera o conteúdo de um Banco de Dados. A sintaxe mínima de uma instrução SELECT é: SELECT * FROM Tabela; Onde * especifica que todos os campos da Tabela serão retornados, obedecendo a ordem dos mesmos na estrutura da tabela. Para selecionar alguma coluna ou colunas especificamente, a sintaxe é: SELECT campo1, campo2, campo3 FROM Tabela; Neste caso os campos selecionados retornarão na ordem especificada. Comandos SQL não são case-sensitive. Podemos ainda atribuir um nome à coluna diferente daquele que consta no banco de dados, usando a clausula AS: Tabela Campo1 campo2 campo3 XXX AAA BBB CCC AAA BBB XXX BBB AAA DDD BBB AAA

Page 8: Curso SQL - Apostila

8

SELECT campo1, campo2, campo3 AS teste FROM Tabela; Saída: campo1 campo2 teste ---------- ---------- ---------- XXX AAA BBB CCC AAA BBB XXX BBB AAA DDD BBB AAA 4 rows selected Neste exemplo, as colunas mostradas serão: campo1, campo2 e teste, onde teste corresponde a campo3. A tabela também pode ser referenciada através de um ALIAS, bastando para isto colocar o ALIAS desejado após o nome da tabela na clausula FROM: SELECT campo1, campo2, campo3 FROM Tabela T; Assim, a tabela passa a ser referenciada por T. Quando selecionamos campos de mais de uma tabela, freqüentemente teremos colunas com o mesmo nome. Nestes casos torna-se necessário explicitar a relação da coluna com a Tabela. Fazemos isto colocando o nome da tabela seguido de ponto mais o nome da coluna: SELECT Tabela1.campo1, Tabela1.campo2, Tabela2.campo1 FROM Tabela1, Tabela2; Ou: SELECT T1.campo1, T1.campo2, T2.campo1 FROM Tabela1 T1, Tabela2 T2; A instrução SELECT...FROM retorna a lista completa de campos pedidos, ou seja, os campos pertencentes a todas as linhas da tabela selecionada. Caso desejemos selecionar apenas alguns registros utilizamos a clausula WHERE, mais uma condição. Neste caso, o SELECT retornará somente os registros que satisfaçam esta condição:

Page 9: Curso SQL - Apostila

9

SELECT * FROM Tabela WHERE campo1=“XXX”; Saída: campo1 campo2 campo3 ---------- ---------- ---------- XXX AAA BBB XXX BBB AAA 2 rows selected Neste caso são mostrados todos os campos (*) dos registros onde o campo1 tenha o valor de XXX. Ao efetuar uma instrução: SELECT campo1 FROM Tabela; O retorno será: campo1 ---------- XXX CCC XXX DDD 4 rows selected Observamos que ocorrem repetições no valor de campo1. Caso desejemos selecionar somente as ocorrências sem repetir os valores, usamos a clausula DISTINCT: SELECT DISTINCT campo1 FROM Tabela; Saída: campo1 ---------- XXX CCC DDD 3 rows selected

Page 10: Curso SQL - Apostila

10

Assim serão listados todos os valores de campo1, sem repetições. Esta clausula só pode ser aplicada a uma coluna.

Page 11: Curso SQL - Apostila

11

3 – Expressões, condições e operadores. Como vimos acima, para selecionar determinadas linhas de uma tabela utilizamos a clausula WHERE, seguida de uma expressão ou condição. Neste segmento veremos a diferença entre uma expressão e uma condição, assim como a utilização de operadores e outras clausulas do WHERE. 3.1 – Expressões. Uma expressão retorna um valor booleano: True ou False. No uso de uma expressão, o SELECT retornará as linhas nas quais a expressão retorne True. Em: SELECT * FROM Tabela WHERE campo1=“XXX”; A expressão campo1=“XXX” retornará True quando o conteúdo de campo1 for igual a XXX, neste caso então a linha será mostrada após o SELECT. 3.2 – Condições. Uma condição envolve ao menos 4 elementos: a clausula WHERE, uma variável, um operador e uma constante. No exemplo acima, a condição seria: WHERE campo1=“XXX” Onde campo1 é a variável, o sinal = é o operador e “XXX” a constante. 3.3 – Operadores. Operadores são elementos que fazem parte das expressões e especificam a condição para o SELECT. Os operadores podem ser aritméticos, de comparação, de caractere, lógicos, do tipo SET e variados. 3.3.1 – Operadores aritméticos. Os operadores aritméticos são: +, -, *, / e % Onde % representa o resto inteiro de uma divisão. Da mesma forma que em qualquer operação aritmética, a precedência dos operadores pode ser manipulada através de parênteses.

Page 12: Curso SQL - Apostila

12

Operador +: Podemos usar o operador + para somar duas colunas da tabela ou para somar uma constante a uma coluna da tabela: tab_frutas nome valor_varejo valor_atacado Mamão 1.0 1.5 Laranja .50 .75 Maçã 1.2 1.8 Banana 2.0 2.5 Exemplo 1: Somando duas colunas de uma tabela. SELECT F.nome, F.valor_varejo + F.valor_atacado AS soma_coluna FROM tab_frutas F; Saída: nome soma_coluna ------------ -------------------------------- Mamão 2.5 Laranja 1.25 Maçã 3.0 Banana 4.5 4 rows selected

Page 13: Curso SQL - Apostila

13

Exemplo 2: Somando uma constante a uma coluna de uma tabela. SELECT F.nome, F.valor_varejo + 1.0 FROM tab_frutas F; Saída: nome valor_varejo+1.0 ------------ -------------------- Mamão 2.0 Laranja 1.5 Maçã 2.2 Banana 3.0 4 rows selected Operador –: Assim como o operador +, podemos diminuir uma coluna da outra, ou diminuir uma constante de uma coluna. Além disso, o operador – pode ser usado para mudar o sinal de uma coluna: Exemplo 1: Diminuindo duas colunas de uma tabela. SELECT F.nome, F.valor_varejo – F.valor_atacado AS diferença FROM tab_frutas F; Saída: nome diferenca -------------- ----------- Mamão 0.5 Laranja 0.25 Maçã 0.4 Banana 0.5 4 rows selected

Page 14: Curso SQL - Apostila

14

Exemplo 2: Diminuindo uma constante de uma coluna de uma tabela. SELECT F.nome, F.valor_varejo – 0.15 AS valor_desconto FROM tab_frutas F; Saída: nome valor_desconto ---------------- ----------------- Mamão 0.85 Laranja 0.35 Maçã 1.05 Banana 1.85 4 rows selected Exemplo 3: Mudando o sinal de uma coluna de uma tabela. SELECT F.nome, – F.valor_varejo AS sinal_trocado FROM tab_frutas; Saída: nome sinal_trocado ------------- --------------- Mamão -1.0 Laranja -0.5 Maçã -1.2 Banana -2.0 4 rows selected Operador *: O operador * processa uma multiplicação entre duas colunas ou entre uma coluna e uma constante: Exemplo 1: Multiplicando duas colunas de uma tabela.

Page 15: Curso SQL - Apostila

15

SELECT F.nome, F.valor_varejo * F.valor_atacado AS produto FROM tab_frutas F; Saída: nome produto ------------ ---------- Mamão 1.5 Laranja 0.375 Maçã 2.16 Banana 5.0 4 rows selected Exemplo 2: Multiplicando uma constante por uma coluna de uma tabela. SELECT F.nome, F.valor_varejo * 2 AS dobro FROM tab_frutas F; Saída: nome dobro --------------- ------ Mamão 2.0 Laranja 1.0 Maçã 2.4 Banana 4.0 4 rows selected Operador /: O operador / faz uma divisão entre duas colunas ou entre uma coluna e uma constante:

Page 16: Curso SQL - Apostila

16

Exemplo 1: Dividindo duas colunas de uma tabela. SELECT F.nome, F.valor_varejo / F.valor_atacado AS divisão FROM tab_frutas F; Saída: nome divisão -------------- --------- Mamão 0.66666 Laranja 0.66666 Maçã 0.66666 Banana 0.8 4 rows selected Exemplo 2: Dividindo uma coluna de uma tabela por uma constante. SELECT F.nome, F.valor_varejo /2 AS metade FROM tab_frutas F; Saída: nome metade -------------- ---------- Mamão 0.5 Laranja 0.25 Maçã 0.6 Banana 1.0 4 rows selected Operador %: O operador % retorna o resto inteiro de uma divisão:

Page 17: Curso SQL - Apostila

17

SELECT F.nome, F.valor_varejo % 3 AS resto FROM tab_frutas F; Saída: nome resto ------------- ------ Mamão 0.0 Laranja 0.0 Maçã 0.0 Banana 0.0 4 rows selected Na verdade a operação de divisão retorna um resto fracionado entre 0 e 0,9 que é então arredondado para o número inteiro abaixo. 3.3.2 – Operadores de comparação. Operadores de comparação, assim como expressões, retornam os valores TRUE e FALSE. Além destes, um operador de comparação pode retornar um valor desconhecido. Um retorno de valor desconhecido está associado ao conceito de nulidade, o NULL. Dizer que o valor de um campo é igual a NULL significa que não existe nenhum valor armazenado no elemento. Quando dizemos nenhum valor, é importante diferenciar isto de zeros ou espaços. Quando temos um elemento numérico cujo valor é zero, ou um alfanumérico contendo espaço vazio, os elementos contêm um valor, já quando o conteúdo é especificado como NULL, nenhum valor está associado ao elemento. Ao executar uma busca em uma tabela que tenha campos com valor NULL, estes retornarão como colunas vazias. Por isso é fácil perceber quando um campo numérico tem o valor igual a NULL, mas se o campo for alfanumérico, o valor NULL pode ser confundido com espaços. tab_itens Nome valor_venda Vassoura 10.0 Pá 5.0 Lixeira Null Null 6.0 Escovão 4.5

Page 18: Curso SQL - Apostila

18

Ao aplicar o comando: SELECT * FROM tab_itens; Teremos a seguinte saída: nome valor_venda ---------------- -------------- Vassoura 10.0 Pá 5.0 Lixeira 6.0 Escovão 4.5 4 rows selected Observando a saída do SELECT percebemos facilmente que a terceira linha possui um valor NULL, pois o campo valor_venda é numérico e aparece em branco, já na quarta linha, não é tão claro que o campo nome possui um valor nulo, pois como o campo é do tipo texto, o NULL pode facilmente ser interpretado como espaço. No momento de processar uma comparação, se o campo contiver um valor NULL o retorno da comparação será desconhecido (nem verdadeiro nem falso). Levando isto em conta, se quisermos selecionar da tabela os itens que tenham o campo valor_venda igual a NULL, usamos a seguinte sintaxe: SELECT * FROM tab_itens WHERE valor_venda IS NULL; Saída: nome valor_venda ---------------- -------------- Lixeira 1 row selected Se ao invés disto usarmos o seguinte comando: SELECT * FROM tab_itens WHERE valor_venda = NULL; Saída: nome valor_venda ---------------- --------------

Page 19: Curso SQL - Apostila

19

Isto ocorre por que a comparação utilizando o operador = retorna um valor desconhecido, como somente listamos as linhas que retornam TRUE, nenhuma linha será listada. Operador =: O operador = retorna as linhas cujo campo comparado possui exatamente o valor especificado após o operador. Considerando a tabela tab_itens: SELECT * FROM tab_itens WHERE nome = ‘Vassoura’; Saída: nome valor_venda ------------------ -------------- Vassoura 10.0 1 row selected Se ao invés disto escrevermos: SELECT * FROM tab_itens WHERE nome = ‘Vasoura’; A saída será: nome valor_venda ------------------ -------------- Operador > ou >=: Os operadores > e >= indicam que a seleção se dará nas linhas cujo campo comparado seja maior que, ou maior ou igual ao elemento comparado. Considerando a tabela: tab_carro: Nome valor_venda Gol 15000 Fusca 3000 Mercedes Benz 45000 Clio 25000 Fiat Uno 9000 Exemplo 1 - selecionando valores maiores que uma constante:

Page 20: Curso SQL - Apostila

20

SELECT * FROM tab_carro WHERE valor_venda > 9000; Saída: nome valor_venda --------------------- -------------- Gol 15000 Mercedes Benz 45000 Clio 25000 3 rows selected Exemplo 2 - selecionando valores maiores ou iguais a uma constante: SELECT * FROM tab_carro WHERE valor_venda >= 9000; Saída: nome valor_venda ----------------------- -------------- Gol 15000 Mercedes Benz 45000 Clio 25000 Fiat Uno 9000 4 rows selected Operador < ou <=: Seleciona as linhas que contenham um valor menor que, ou menor ou igual ao valor indicado para o campo comparado.

Page 21: Curso SQL - Apostila

21

Exemplo 1 - selecionando valores menores que uma constante: SELECT * FROM tab_carro WHERE nome < ‘Gol’; Saída: nome valor_venda -------------------- -------------- Fusca 3000 Clio 25000 Fiat Uno 9000 3 rows selected Exemplo 2 - selecionando valores menores ou iguais a uma constante: SELECT * FROM tab_carro WHERE nome <= ‘Gol’; Saída: nome valor_venda -------------------- -------------- Gol 15000 Fusca 3000 Clio 25000 Fiat Uno 9000 4 rows selected Operadores <>(Sql-Server) ou =!: Assim como podemos selecionar linhas cujo conteúdo campos seja igual, maior ou menor que um determinado valor, existem situações em que é necessário selecionar as linhas cujo campo de comparação seja diferente do valor especificado. Fazemos isto utilizando os operadores <> ou =!:

Page 22: Curso SQL - Apostila

22

SELECT * FROM tab_carro WHERE valor_venda <> 3000 Saída: nome valor_venda ------------------------- -------------- Gol 15000 Mercedes Benz 45000 Clio 25000 Fiat Uno 9000 4 rows selected 3.3.3 – Operadores de caractere. Operador LIKE: O operador LIKE indica que selecionaremos as linhas cujo campo comparado contenha algo semelhante ao valor indicado na condição. tab_aluno: Nome Endereco Fernando Rua Amaral nº

30 Alex Av Oxford nº 123 Sheila Rua June nº 7

apto 802 Ana Maria Rua Bastos nº 10 Luiza Av London nº 2

apto 17 Se quisermos selecionar as linhas cujo campo endereço comece por ‘Rua’, o comando usado será: SELECT * FROM tab_aluno WHERE endereco LIKE ‘Rua%’;(Sql Server) Ou: SELECT * FROM tab_aluno WHERE endereco LIKE ‘Rua*’;(Access)

Page 23: Curso SQL - Apostila

23

Saída: nome endereco ------------------------- ------------------------------ Fernando Rua Amaral nº 30 Carla Rua June nº 7 apto 802 Ana Maria Rua Bastos nº 10 3 rows selected O sinal % ou o *, utilizado em ‘Rua%’ - ou ‘Rua*’ - indica que listaremos todas as linhas cujos campos de comparação comecem pela constante ‘Rua’. Podemos usar os sinais % ou * antes da constante, depois, ou em ambos os casos: SELECT * FROM tab_aluno WHERE endereco LIKE ‘%apto%’; Saída: nome endereco ------------------------ ----------------------------- Carla Rua June nº 7 apto 802 Luiza Av London nº 2 apto 17 2 rows selected Existem casos em que desejamos selecionar linhas cujo campo de comparação contenha o valor comparado com variação em um ou outro caractere, nestes casos usamos o sinal _ ou ?, que indica que serão selecionadas as linhas cujos campos de comparação contiverem o valor comparado, podendo substituir o valor do caractere na posição onde encontrarmos o _. Considerando a tabela abaixo: tab_aluno Nome Estado Fernando RN Alex MG Carla XRJX Ana Maria GO Luiza SP

Page 24: Curso SQL - Apostila

24

SELECT * FROM tab_aluno WHERE estado LIKE ‘R?’;(Access) Saída: nome estado ----------------- -------- Fernando RN 1 row selected Podemos ainda utilizar ambos os sinais: SELECT * FROM tab_aluno WHERE estado LIKE ‘_R%’;(Sql Server) Saída: nome estado --------------------- ------ Carla XRJX 1 row selected Operador || ou + ou &: Estes operadores indicam a concatenação de duas colunas em uma: SELECT nome + estado AS NomEst FROM tab_aluno; Saída: NomEst -------------------------------- Fernando RN Alex MG Carla XRJX Ana Maria GO Luiza SP 5 rows selected Podemos inserir caracteres fixos entre as colunas: SELECT nome + ‘ - ’ + estado NomEst FROM tab_aluno;

Page 25: Curso SQL - Apostila

25

Saída: NomEst -------------------------------- Fernando - RN Alex - MG Carla - XRJX Ana Maria - GO Luiza - SP 5 rows selected 3.3.4 – Operadores lógicos. Os operadores lógicos são usados para separar duas condições da clausula WHERE, formando condições compostas ou complexas. Os operadores lógicos são: AND, OR e NOT. tab_curso: Nome Professor horario Cobol Rui manhã C+ Ricardo tarde VB Rui tarde Java Fabrício manhã Pascal Rui manhã SELECT * FROM tab_curso WHERE professor = ‘Rui’ AND horario = ‘manhã’; Saída: nome professor horario ------------------ --------------------- ------------- Cobol Rui manhã Pascal Rui manhã 2 rows selected Neste caso, as linhas mostradas são aquelas cujos campos de comparação atendem a ambas as condições.

Page 26: Curso SQL - Apostila

26

SELECT * FROM tab_curso WHERE professor = ‘Rui’ OR horario = ‘manhã’; Saída: nome professor horario ------------------ --------------------- ------------- Cobol Rui manhã Java Fabrício manhã Pascal Rui manhã 3 rows selected Ao trocarmos o AND pelo OR no SELECT, passamos a listar as linhas que atendam a uma ou outra condição, como na linha 2 da consulta, que atende somente a uma das condições. SELECT * FROM tab_curso WHERE professor NOT = ‘Rui’; Saída: nome professor horario ------------------ --------------------- ------------- C+ Ricardo tarde Java Fabrício manhã 2 rows selected Aqui o operador NOT funciona transformando o resultado da condição em FALSE, se a condição for satisfeita e em TRUE se a condição não for satisfeita. 3.3.5 – Operadores SET. Operador UNION: Este operador retorna o resultado de duas consultas excluindo as linhas duplicadas.

Page 27: Curso SQL - Apostila

27

tab_futebol Clube Cruzeiro Grêmio Flamengo Fluminense Vasco tab_volei Clube Flamengo Minas Gás Parmalat Vasco SELECT * FROM tab_futebol UNION SELECT * FROM tab_volei; Saída: clube ------------------ Cruzeiro Grêmio Flamengo Fluminense Minas Gás Parmalat Vasco 7 rows selected As duas consultas, se executadas separadamente, retornariam um total de 9 linhas, porém existem duas linhas com o mesmo conteúdo em ambas as consultas. Estas linhas aparecerão apenas uma vez na consulta utilizando UNION. Se desejarmos incluir também as linhas duplicadas, usamos o UNION ALL:

Page 28: Curso SQL - Apostila

28

SELECT * FROM tab_futebol UNION ALL SELECT * FROM tab_volei; Saída: clube ------------------ Cruzeiro Grêmio Flamengo Fluminense Vasco Flamengo Minas Gás Parmalat Vasco 9 rows selected Operador INTERSECT: (Oracle) Este operador retorna a interseção entre as duas consultas: SELECT * FROM tab_futebol INTERSECT SELECT * FROM tab_volei; Saída: clube ------------------ Flamengo Vasco 2 rows selected Operador EXISTS (INTERSEÇÃO - Sql Server): Da mesma forma que o INTERSEC, retorna a interseção entre duas consultas. Este exemplo retorna a interseção entre os países das tabelas Customers e Employees:

Page 29: Curso SQL - Apostila

29

SELECT DISTINCT Country FROM Customers WHERE EXISTS (SELECT Country FROM Employees WHERE Customers.Country = Employees.Country) Saída: Country ----------------------- UK USA Operador MINUS: (Oracle) Retorna as linhas da primeira consulta que não estão presentes na segunda: SELECT * FROM tab_futebol MINUS SELECT * FROM tab_volei; Saída: clube ------------------ Cruzeiro Grêmio Fluminense 3 rows selected SELECT * FROM tab_volei MINUS SELECT * FROM tab_futebol; Saída: clube ------------------ Minas Gás Parmalat 2 rows selected Operador NOT EXISTS (Diferença - Sql Server) Igual ao MINUS, retorna as linhas da que não estão presentes na segunda consulta.

Page 30: Curso SQL - Apostila

30

O exemplo a seguir retorna os países não comuns nas tabelas Customers e Employees: SELECT DISTINCT Country FROM Customers WHERE NOT EXISTS (SELECT Country FROM Employees WHERE Customers.Country = Employees.Country) Saída --------------- Argentina Austria Belgium Brazil Canada Denmark Finland France Germany Ireland Italy Mexico Norway Poland Portugal Spain Sweden Switzerland Venezuela 3.3.6 – Outros operadores: Operador IN: Este operador retorna as linhas que estiverem contidas em uma lista de argumentos.

Page 31: Curso SQL - Apostila

31

tab_cidades Cidade Estado Rio de Janeiro RJ Cabo Frio RJ Santos SP Salvador BA Ribeirão Preto SP Se quisermos listar as linhas cujo campo estado sejam iguais a RJ ou SP: SELECT * FROM tab_cidades WHERE estado = ‘RJ’ OR estado = ‘SP’; Ou, usando o operador IN: SELECT * FROM tab_cidades WHERE estado IN (‘RJ’, ‘SP’); Saída: cidade estado ---------------------- -------- Rio de Janeiro RJ Cabo Frio RJ Santos SP Ribeirão Preto SP 4 rows selected Operador BETWEEN: tab_livros nome valor Senhor dos Anéis 120.0 Cem anos de solidão 40.0 Ensaio sobre a cegueira 35.0 O iluminado 60.0 A casa dos espíritos 30.0 Selecionando as linhas cujo campo valor seja maior a 35 e menor que 100:

Page 32: Curso SQL - Apostila

32

SELECT * FROM tab_livros WHERE valor > 35 AND valor < 100; Ou, usando o operador BETWEEN: SELECT * FROM tab_livros WHERE valor BETWEEN 35 AND 100; Saída: nome valor ------------------------ ----------- Cem anos de Solidão 40.0 Ensaio sobre a cegue 35.0 O iluminado 60.0 3 rows selected O operador BETWEEN lista as linhas contidas entre o intervalo definido pelos parâmetros, incluindo os valores iguais aos parâmetros. Ambos os comandos produzem o mesmo resultado que quando usamos condições compostas, porém o modo de escrever os comandos fica mais enxuto e claro.

Page 33: Curso SQL - Apostila

33

4 – Funções - Modelando os dados capturados. As funções do SQL auxiliam na manipulação da informação que retorna de uma instrução SELECT. 4.1 – Funções de agregação. Estas funções retornam valores baseados em valores das colunas. Elas são conhecidas também como funções de grupo ou de agrupamento. Função COUNT: Esta função retorna o número de linhas que satisfazem a condição especificada na clausula WHERE. tab_livros Nome Preço autor Sonhos de Robô 42.0 Isaac Assimov O primo Basílio 40.0 Eça de Queiroz Fundação 35.0 Isaac Assimov Água Viva 30.0 Clarice Lispector SELECT COUNT (*) AS qtd_livros_autor FROM tab_livros WHERE tab_livros.autor = ‘Isaac Assimov’; Saída: qtd_livros_autor ---------------------- 2 Como o COUNT conta as linhas que satisfazem a condição, usamos o *. Poderíamos utilizar também o nome de um dos campos, que o resultado seria o mesmo. Se desejarmos saber quantas linhas tem a tabela, podemos usar o seguinte formato: SELECT COUNT (*) AS total_linhas FROM tab_livros; Saída: total_linhas ---------------------- 4

Page 34: Curso SQL - Apostila

34

Função SUM: Esta função retorna o somatório dos campos de uma coluna: SELECT SUM(L.preço) AS total_preço FROM tab_livros L; Saída: total_preço --------------------- 147.0 Como neste caso o somatório é associado a uma coluna específica, o nome do campo deve estar explicitado. Se desejarmos realizar o somatório de mais de um campo, devemos especificar uma função SUM para cada campo separadamente. Esta função somente é válida para campos numéricos. Função AVG: Esta função retorna a média aritmética de uma coluna: SELECT AVG(L.preço) AS media_total_preço FROM tab_livros L; Saída: media_total_preço --------------------- 36.75 Assim como a função SUM, esta função somente é válida para campos numéricos. Função MAX: Retorna o valor máximo presente em uma coluna, tenha esta o conteúdo numérico ou alfanumérico:

Page 35: Curso SQL - Apostila

35

SELECT MAX(L.preço) AS maior_preço FROM tab_livros L; Saída: maior_preço -------------------- 42.0 Função MIN: Ao contrário da função MAX, retorna o menor valor presente em uma coluna: SELECT MIN(L.preço) AS menor_preço FROM tab_livros L; Saída: menor_preço -------------------- 30.0 Podemos ainda combinar as duas funções: SELECT MIN(L.preço) AS menor_preço , MAX(L.preço) AS maior_preço FROM tab_livros L; Saída: menor_preço maior_preço -------------------- -------------------- 30.0 42.0 Função VARIANCE:(Oracle) Retorna a variação do desvio-padrão, muito utilizado para cálculos de estatística. SELECT VARIANCE(tab_livros.preço) AS variação FROM tab_livros; Saída: variação -------------------- 10.333

Page 36: Curso SQL - Apostila

36

Esta função somente pode ser usada em colunas numéricas. Função STDDEV: Esta função retorna o desvio-padrão do conteúdo de uma coluna. SELECT STDDEV(tab_livros.preço) AS desvio_padrão FROM tab_livros; Saída: desvio_padrão --------------------- 2 Assim como a função VARIANCE, somente pode ser utilizado em campos numéricos. 4.2 – Funções de data/hora. Nesta seção estudaremos funções que facilitam a manipulação de datas nas aplicações. Função ADD_MONTHS:(Oracle Esta função adiciona a quantidade de meses especificados no parâmetro à coluna a ela associada. tab_projeto Nome Data_inicio data_final Agenda 02-Ago-04 02-Set-04 Cadastro Aluno 03-Set-04 03-Nov-04 Microsoft 02-Ago-04 03-Nov-04 Projeto Final 04-Nov-04 03-Dez-04

Page 37: Curso SQL - Apostila

37

SELECT P.nome, P.data_inicio, P.data_final AS data_prevista ADD_MONTHS(P.data_final, 2) AS nova_data_fina FROM tab_projeto P; Saída: nome data_inicio data_prevista nova_data_final ----------------- ------------ ---------------- ------------------ Agenda 02-Ago-04 02-Set-04 02-Nov-04 Cadastro Aluno 03-Set-04 03-Nov-04 03-Jan-05 Microsoft 02-Ago-04 03-Nov-04 03-Jan-05 Projeto Final 04-Nov-04 03-Dez-04 03-Fev-05 4 rows selected Os parâmetros desta função são o nome do campo e a quantidade de meses a serem acrescidos. Função DATEADD(): (Sql Server) Acrescenta um valor, na prte da data especificada, e mostra a nova data resultante. DateAdd(unidade,valorAcrescimo,DataOrigem) Sintaxe : Select DateAdd(dd,01,DATA_PRECO) AS DATANOVA FROM TAB_FRUTAS WHERE NOME = 'MAMAO' Saída : retorna a data acrescida de 01 dia. DATANOVA ------------- 2004-11-02 Função LAST_DAY: (Oracle) A função LAST_DAY retorna o último dia do mês:

Page 38: Curso SQL - Apostila

38

SELECT P.data_final, LAST_DAY(P.data_final) AS ultimo_dia_mes FROM tab_projeto P; data_final ultimo_dia_mes ---------------- ------------------ 02-Set-04 30-Set-04 03-Nov-04 30-Nov-04 03-Nov-04 30-Nov-04 03-Dez-04 31-Dez-04 4 rows selected Função MONTHS_BETWEEN: (Oracle) Retorna a quantidade de meses entre duas datas. Retorna um número que pode ser fracionado: SELECT P.nome,MONTHS_BETWEEN(P.data_inicio – P.data_final) AS duração_projeto FROM tab_projeto P; Saída: nome duração_projeto ---------------------- --------------------- Agenda 1.033333 Cadastro Aluno 2.033333 Shell 3.933333 Projeto_final 0.967741 4 rows selected Função NEXT_DAY: (Oracle) Esta função retorna a data do próximo dia da semana a partir da data passada pelo parâmetro:

Page 39: Curso SQL - Apostila

39

SELECT P.data_inicio NEXT_DAY(P.data_inicio, ‘monday’) AS prox_segunda_feira FROM tab_projeto P; Saída: data_inicio prox_segunda_feira ------------- ----------------------- 02-Ago-04 09-Ago-04 03-Set-04 06-Set-05 02-Ago-04 09-Ago-05 04-Nov-04 05-Nov-05 4 rows selected Função SYSDATE: (Oracle) Retorna a data e hora do sistema: SELECT DISTINCT SYSDATE FROM tab_projeto; Saída: SYSDATE ---------------------- 29-Jul-04 1030AM Usamos a clausula DISTINCT para que apenas uma linha seja mostrada, pois teríamos a mesma resposta para cada linha da tabela. 4.3 – Funções aritméticas. Muitas das operações a serem realizadas com dados de uma tabela envolvem operações aritméticas, por isso, muitas das implementações de SQL possuem funções para isto. Função ABS: Esta função retorna o valor absoluto de uma coluna (o valor absoluto é o número sem considerar a sinalização):

Page 40: Curso SQL - Apostila

40

tab_saldo saldo_anterior Movimento saldo_atual

500.90 -600.00 -99.10 -100.30 1000.00 899.70 300.00 -450.00 -150.00 251.20 100.00 351.20

SELECT ABS(tab_saldo.movimento) AS movimento_absoluto FROM tab_saldo; movimento_absoluto ------------------------

600.00 1000.00 450.00 100.00

4 rows selected Funções CEIL(CEILING -> Sql Server) e FLOOR: A função CEIL retorna o menor número inteiro maior que ou igual ao parâmetro: SELECT S.saldo_atual, CEIL(S.saldo_atual) AS aprox_cima FROM tab_saldo S; Saída: saldo_atual aprox_cima ---------------- ------------------

-99.10 -98.00 899.70 900.00

-150.00 -150.00 351.20 352.00

4 rows selected A função FLOOR retorna o maior número inteiro menor que ou igual ao parâmetro:

Page 41: Curso SQL - Apostila

41

SELECT S.saldo_atual, FLOOR(S.saldo_atual) AS aprox_baixo FROM tab_saldo S; Saída: saldo_atual aprox_baixo ---------------- ------------------

-99.10 -100.00 899.70 898.00

-150.00 -150.00 351.21 351.00

4 rows selected Funções COS, COSH, SIN, SINH, TAN e TANH: (Oracle) COS(),ACOS(),SIN(), ASIN(),TAN(),ATAN(): (Sql Server) As funções COS, SIN e TAN retornam respectivamente Co-seno, seno e tangente de um ângulo em radianos. As funções COSH, SINH e TANH retornam os mesmos valores em graus. O formato geral é: SELECT campo1, FUNÇÃO(campo1) FROM tabela; Função EXP: Eleva o campo do parâmetro a um valor correspondente a uma constante matemática: SELECT campo1, EXP(campo1) FROM tabela; Função LN e LOG:(Oracle) LOG(),LOG10(): (Sql Server) Ambas as funções retornam o logaritmo do parâmetro: SELECT campo1, LOG(campo1) FROM tabela; SELECT campo1, LN(campo1) FROM tabela; Função MOD: (Oracle) Retorna o resto da divisão do primeiro pelo segundo parâmetro:

Page 42: Curso SQL - Apostila

42

SELECT campo1, campo2, MOD(campo1, campo2) FROM tabela; Função POWER: Retorna o valor correspondente ao primeiro parâmetro elevado pelo segundo: SELECT campo1, campo2, POWER(campo1, campo2) FROM tabela; Função SIGNS:(Oracle) SIGN() : (Sql Server) Retorna –1 quando o parâmetro é um número negativo: SELECT campo1, SIGNS(campo1) FROM tabela; Função SQRT: Retorna a raiz quadrada do parâmetro: SELECT campo1, SQRT(campo1) FROM tabela; 4.4 – Funções de caractere. Estas funções são utilizadas para manipulação de caracteres ou cadeias de caracteres. Função CHR (Oracle), CHAR (Sql Server): Retorna o caractere equivalente a um número usado como parâmetro na tabela ASCII: tab_caracteres Nome Sobrenome codigo Isabel ALLENDE 73 Garcia MARQUES 77 Isaac ASIMOV 65 Carlos CASTAÑEDA 67

Page 43: Curso SQL - Apostila

43

SELECT C.codigo ,CHR(C.codigo) AS caractere FROM tab_caracteres C; Ou: SELECT C.codigo ,CHAR(C.codigo) AS caractere FROM tab_caracteres C; Saída codigo caractere -------- -----------

73 I 77 M 65 A 67 C

4 rows selected Função CONCAT (Oracle) + (Sql Server): Concatena duas ou mais colunas em uma só: SELECT CONCAT(C.nome, C.sobrenome) AS nome_sobrenome FROM tab_caracteres C; Saída: nome_sobrenome ------------------------------- Isabel ALLENDE Garcia MARQUES Isaac ASIMOV Carlos CASTAÑEDA 4 rows selected Função INITCAP: (Oracle) Transforma em maiúscula a primeira letra da coluna selecionada e o restante em minúscula:

Page 44: Curso SQL - Apostila

44

SELECT C.sobrenome AS antes, INITCAP(C.sobrenome) AS depois FROM tab_caracteres C; Saída: antes depois --------------- --------------- ALLENDE Allende MARQUES Marques ASIMOV Asimov CASTAÑEDA Castañeda 4 rows selected Função LOWER e UPPER. Afunção LOWER retorna o parâmetro em minúsculas e a função UPPER, em maiúsculas: SELECT C.nome, LOWER(C.nome) AS minusculas, UPPER(C.nome) AS maiusculas FROM tab_caractere C; Saída: nome minusculas maiusculas ----------------- ----------------- ------------------- Isabel isabel ISABEL Garcia garcia GARCIA Isaac isaac ISAAC Carlos carlos CARLOS 4 rows selected Funções LPAD e RPAD: (Oracle) Estas funções possuem um mínimo de dois e um máximo de três parâmetros. O primeiro é o nome do campo, o segundo é o tamanho da máscara que definiremos e o terceiro é o caractere de preenchimento a ser utilizado. Se nenhum caractere de preenchimento for especificado este será feito por espaços. Por exemplo, suponhamos que o campo nome foi especificado como tendo um tamanho de 10 caracteres. A função LPAD preenche os campos à esquerda, o RPAD preenche à direita:

Page 45: Curso SQL - Apostila

45

SELECT LPAD(C.nome, 15, ‘*’) AS esquerda, RPAD(C.nome, 15, ‘*’) AS direita FROM tab_caractere C; Saída: esquerda direita --------------------- --------------------- *****Isabel Isabel***** *****Garcia Garcia***** *****Isaac Isaac***** *****Carlos Carlos***** 4 rows selected Funções LTRIM e RTRIM: Estas funções retiram os espaços em branco à esquerda (LTRIM) e à direita (RTRIM). Podemos utilizar estas funções em conjunto com as funções LPAD e RPAD: SELECT RPAD(RTRIM(C.nome), 15, ‘*’) AS esquerda, LPAD(LTRIM(C.nome) , 15, ‘*’) AS direita FROM tab_caractere C; Saída: esquerda direita --------------------- --------------------- **********Isabel Isabel********* **********Garcia Garcia********* **********Isaac Isaac********** **********Carlos Carlos********* 4 rows selected Combinamos duas funções (LPAD e LTRIM, RPAD e RTRIM) para que o efeito da retirada dos espaços em branco fosse visível. Função REPLACE: Substitui o caractere especificado do conteúdo da coluna passada no parâmetro pelo terceiro argumento do parâmetro, quando este existir. Se não, faz a substituição por espaços:

Page 46: Curso SQL - Apostila

46

SELECT REPLACE(C.nome, ‘a’, ‘*’) AS subst_a FROM tab_caractere C; Saída: subst_a ----------------- Is*bel G*rci* Is**c C*rlos 4 rows selected Função SUBSTR (Oracle) SUBSTRING (Sql Server): Esta função retorna um pedaço da string do campo selecionado. Recebe três parâmetros: o nome do campo, a posição inicial e a quantidade de caracteres a serem mostrados: SELECT SUBSTR(C.nome, 3,2) FROM tab_caractere C; nome -------------------- ab rc aa rl 4 rows selected Função TRANSLATE:(Oracle) Transforma os caracteres especificados em outros, ambos passados por parâmetro juntamente com o nome do campo a ser transformado. Esta função é case-sensitive:

Page 47: Curso SQL - Apostila

47

SELECT TRANSLATE(C.nome, ‘abcdefghijklmnopqrstuvwxyz’, ‘@@@@@@@@&&&&&&&&**********’) FROM tab_caractere C; Saída: nome --------------------- I*@@@& G@*@&@ I*@@@ C@*&** 4 rows selected Função INSTR (Oracle) CHARINDEX (Sql Server): Retorna a posição em que se encontra o caractere buscado na string do campo utilizado no parâmetro: SELECT INSTR(C.nome, ‘c’) AS posição_C FROM tab_caractere C; SELECT CHARINDEX(‘c’, C.nome) AS posição C FROM tab_caractere C Saída: posicao_C ------------ 0 4 5 1 4 rows selected Função LENGHT (Oracle) LEN (Sql Server): Retorna o número de caracteres de uma string:

Page 48: Curso SQL - Apostila

48

SELECT LENGHT(C.sobrenome) AS tam_sobrenome FROM tab_caractere C; Ou: SELECT LEN(C.sobrenome) AS tam_sobrenome FROM tab_caractere C; Saída: tam_sobrenome ------------------ 7 6 6 9 4 rows selected 4.5 – Funções de conversão. Estas funções convertem um tipo de dado em outro. São elas: Função TO_CHAR (Oracle): converte um campo numérico em um campo do tipo CHAR. Função TO_NUMBER (Oracle): converte um campo alfabético em um campo numérico. A sintaxe é: SELECT TO_CHAR(campo_num) FROM tabela; SELECT TO_NUMBER(campo_alfa) FROM tabela; Funções CAST() e CONVERT() (Sql Server): convertem uma expressão de um tipo de dado para outro. Sintaxe : SELECT CAST(Expressão as DataType) FROM tabela SELECT CONVERT(DataType, Expressão) FROM tabela 4.6 – Outras funções. Funções GREATEST() e LEAST() (Oracle):

Page 49: Curso SQL - Apostila

49

Estas funções retornam respectivamente o maior e o menor de uma lista de argumentos. SELECT GREATEST(campo1, campo2, campo3) FROM tabela; SELECT LEAST(campo1, campo2, campo3) FROM tabela; Funções : MAX() e MIN() (Sql Server): Estas funções retornam respectivamente o maior e o menor valor existente em uma determinada coluna. Sintaxe : SELECT MAX(Coluna) FROM Tabela SELECT MIN(Coluna) FROM Tabela Função TOP n (Sql Server): Esta função retorna um número n de linhas, a partir da linha inicial. n deve ser um número inteiro. Sintaxe : SELECT TOP n * FROM Tabela Função TOP n PERCENT : A cláusula PERCENT indica que deve ser retornado um valor percentual de quantidade de linhas da consulta base. SELECT TOP n PERCENT * FROM Tabela

Page 50: Curso SQL - Apostila

50

5 – Clausulas do SQL. tab_curso curso professor turno C+ João Manhã Cobol Ana Manhã Fortran João Tarde C++ Guilherme Noite Delphi Lucia Tarde 5.1 – A clausula WHERE. A cláusula WHERE é utilizada para restringir as linhas que serão afetadas pelos comandos SELECT, UPDATE E DELETE. Ao usarmos o comando SELECT no seu formato mais básico: SELECT * FROM tab_curso; Saída: curso professor turno ------------- -------------- --------------- C+ João Manhã Cobol Ana Manhã Fortran João Tarde C++ Guilherme Noite Delphi Lucia Tarde 5 rows selected Trazemos todas as linhas que compõem a tabela. Caso desejemos selecionar as linhas que satisfaçam uma condição, devemos utilizar a clausula WHERE:

Page 51: Curso SQL - Apostila

51

SELECT * FROM tab_curso C WHERE C.curso = ‘C+’; Saída: curso professor turno ------------- -------------- --------------- C+ João Manhã 1 row selected Neste caso somente serão incluídas no resultado da consulta as linhas em que o campo curso satisfaça a condição. 5.2 – A clausula STARTING WITH. (Oracle) Esta clausula faz exatamente o que o operador LIKE seguido do operador % ou *: Seleciona as linhas cujo campo de comparação começa com o caractere ou cadeia de caracteres indicados: SELECT * FROM tab_curso WHERE tab_curso.curso STARTING WITH ‘C+’; Saída: curso professor turno ------------- -------------- --------------- C+ João Manhã C++ Guilherme Noite 2 rows selected O resultado da consulta retorna as linhas em que o campo curso contenha C+ no início. 5.3 – A clausula ORDER BY. A clausula ORDER BY ordena as linhas da consulta por um campo da tabela. Podemos usar qualquer campo ou uma combinação de campos. Podemos ordenar as linhas de forma ascendente ou descendente: SELECT * FROM Tabela ORDER BY campo1 ASC/DESC; A clausula ASC (ascendente) é opcional, pois é o default do comando.

Page 52: Curso SQL - Apostila

52

SELECT * FROM tab_curso C ORDER BY C.curso; Saída: curso professor turno ------------- -------------- --------------- C+ João Manhã C++ Guilherme Noite Cobol Ana Manhã Delphi Lucia Tarde Fortran João Tarde 5 rows selected SELECT * FROM tab_curso C ORDER BY C.curso DESC; Saída: curso professor turno ------------- -------------- --------------- Fortran João Tarde Delphi Lucia Tarde Cobol Ana Manhã C++ Guilherme Noite C+ João Manhã 5 rows selected Ordenando a tabela por mais de um campo: SELECT * FROM tab_curso C ORDER BY C.professor ASC, C.turno DESC; curso professor turno ------------- -------------- --------------- Cobol Ana Manhã C++ Guilherme Noite Fortran João Tarde C+ João Manhã Delphi Lucia Tarde 5 rows selected O resultado da consulta será ordenado por professor de modo ascendente e por turno de modo descendente

Page 53: Curso SQL - Apostila

53

5.4 – A clausula GROUP BY. Já estudamos as funções de agregação, tais como SUM e COUNT, porém, usando o formato geral, somente podemos contar ou totalizar todas as linhas de uma consulta. Às vezes precisamos agrupar estes resultados a partir de um determinado campo, ou combinação de campos. Uma solução seria elaborar diversas instruções SELECT, cada uma delas com a clausula WHERE selecionando o grupo desejado, o que geraria vários processamentos e várias consultas. Felizmente, o SQL possui uma clausula que realiza este agrupamento sem necessidade de executarmos uma instrução SELECT para cada grupo, agregando todos os resultados em uma única consulta. tab_livros Nome Autor editora qtd O Banquete Platão Ouro 100 A idade da razão J.P.Sartre Brasil 300 A Náusea J.P.Sartre Ouro 350 A genealogia da moral

Nietzsche Ouro 30

A república Platão Brasil 200 Na tabela, desejamos saber quantos livros existem para cada autor, ou quantos volumes existem por editora. Se simplesmente utilizarmos as clausulas COUNT e SUM, teríamos: SELECT COUNT(tab_livros.autor) AS QtdLivrosAutor FROM tab_livros; Saída: QtdLivrosAutor ------------------- 5 SELECT SUM(tab_livros.qtd) AS QtdLivrosEditora FROM tab_livros; Saída: QtdLivrosEditora ------------------- 980

Page 54: Curso SQL - Apostila

54

A informação assim apresentada não nos diz o que realmente queremos saber. Se fossemos utilizar as funções de agregação combinadas com a clausula WHERE, teríamos que realizar várias instruções de SQL, além de termos que conhecer antecipadamente o conteúdo da tabela, para assim poder selecionar os dados que desejamos. Podemos ao invés disto utilizar a clausula GROUP BY. Esta clausula agrupa os resultados obtidos em um SELECT a partir do campo ou lista de campos apresentada. Assim, se quisermos saber a quantidade de livros por autor na tabela, podemos simplesmente dizer: SELECT L.autor, COUNT(L.autor) AS QtdLivrosAutor FROM tab_livros L GROUP BY L.autor; Saída: Autor QtdLivrosAutor ------------------- --------------------- Platão 2 J.P. Sartre 2 Nietzsche 1 Para selecionar o somatório da quantidade de livros por editora: SELECT L.editora, SUM(L.qtd) AS QtdLivrosEditora FROM tab_livros L GROUP BY L.editora; Saída: editora QtdLivrosEditora ------------------- -------------------- Brasil 500 Ouro 480 Certamente, este formato é muito mais claro e eficiente. Podemos agregar por qualquer campo, e todos os campos presentes no SELECT, com exceção dos campos agregados, devem estar presentes no GROUP BY. 5.5 – A clausula HAVING. A clausula HAVING indica quais linhas serão mostradas em uma consulta que utilizou a clausula GROUP BY. Esta clausula se assemelha a clausula WHERE, com a diferença de que o WHERE se refere a colunas da tabela e

Page 55: Curso SQL - Apostila

55

o HAVING se refere a colunas que trazem resultados de funções, ou seja, não pertencem à estrutura da tabela: SELECT L.autor, COUNT(L.autor) AS QtdLivrosAutor FROM tab_livros L GROUP BY L.autor HAVING COUNT(L.autor) = 2; Saída: autor QtdLivrosAutor -------------- ------------------ Platão 2 J.P.Sartre 2 Finalmente, podemos combinar tantas quantas clausulas desejemos: SELECT L.autor, COUNT(L.autor) AS QtdLivrosAutor FROM tab_livros L WHERE L.autor STARTING WITH 'P' GROUP BY L.autor HAVING COUNT(L.autor) = 2; Saída: autor QtdLivrosAutor -------------- ------------------ Platão 2 A seqüência para combinação de clausulas do SELECT é: SELECT campo(s) FROM tabela(s) WHERE condição/expressão(ões) para os campos GROUP BY campo(s) a serem agrupados HAVING condição(ões) do grupo ORDER BY campo(s)

Page 56: Curso SQL - Apostila

56

6. Unindo Tabelas – JOIN. Um dos aspectos mais interessantes do SQL é a capacidade de realizar pesquisas em mais de uma tabela. Sem esta característica teríamos que armazenar todos os dados necessários para uma aplicação na mesma tabela, dificultando a manutenção. Graças a esta capacidade do SQL podemos trabalhar com tabelas comuns, menores e mais específicas. Existem duas formas básicas de associar dados de tabelas, dependendo do resultado que se deseja obter: • INNER JOIN: Esta clausula seleciona de duas ou mais tabelas apenas os

dados relacionados entre todas elas; • OUTER JOIN: Seleciona em duas ou mais tabelas tanto os dados

relacionados quanto os não relacionados entre elas. tab_livro Codigo nome Autor codigo_editora 001 O Rei do Inverno Bernard Cornwell 001 002 1984 George Orwell 001 003 Neuromancer William Gibson 002 004 Excalibur Bernard Cornwell 001 005 Admirável Mundo

Novo Aldous Huxley 003

006 O caçador de andróides

Philip K. Dick 002

tab_editora Codigo nome Uf 001 Ouro RJ 002 Brasil SP 003 Europa RJ tab_vendas codigo_editora codigo_livro qtd_vendida valor_unit 001 002 15 25.00 001 004 20 42.00 002 006 40 35.00 001 001 22 42.00 003 005 10 26.00 002 003 15 32.00

Page 57: Curso SQL - Apostila

57

6.1 – INNER JOIN. Uma associação do tipo INNER JOIN tem por objetivo selecionar de duas ou mais tabelas apenas os dados relacionados entre todas elas. Um INNER JOIN pode ser:

• NATURAL JOIN: Neste tipo de associação relacionamos as duas tabelas através de uma coluna, que contém dados em comum, como por exemplo, nas tabelas acima apresentadas, a coluna codigo_editora, da tabela tab_livro corresponde à coluna código da tabela tab_editora, e ambas se relacionam com a tabela tab_vendas através das colunas codigo_editora e codigo_livro respectivamente. Um NATURAL JOIN se caracteriza por selecionar campos de duas ou mais tabelas que se relacionam através de um ou mais campos:

SELECT L.nome AS NomeLivro, E.nome AS NomeEditora, V.qtd_vendida AS QTD, V.valor_venda AS Valor, V.qtd_vendida*V.valor_venda AS Total FROM tab_livro L, tab_editora E, tab_vendas V WHERE L.codigo=V.codigo_livro And E.codigo=V.codigo_editora; Saída: NomeLivro NomeEditora QTD Valor Total -------------- ---------------- --------- ---------- ----------- O Rei do Inv Ouro 22 42 924 1984 Ouro 15 25 375 Excalibur Ouro 20 42 840 Neuromance Brasil 15 32 480 O caçador de Brasil 40 35 1400 Admirável Mu Europa 10 26 260 6 rows selected O exemplo acima é um INNER JOIN através da clausula WHERE, com a qual realizamos a junção entre as tabelas. Podemos usar também a sintaxe como demonstrado a seguir, para isto, as tabelas devem ter chaves especificadas com seus relacionamentos:

Page 58: Curso SQL - Apostila

58

SELECT E.nome, L.nome, V.qtd_vendida, V.valor_venda, V.qtd_vendida*V.valor_venda AS TOTAL FROM (tab_editora E INNER JOIN tab_livro L ON E.codigo=L.codigo_editora INNER JOIN tab_vendas V ON L.codigo=V.codigo_livro AND E.codigo=V.codigo_editora; O resultado produzido é o mesmo, porém existe uma diferença na performance da consulta, que é muito mais rápida ao utilizarmos o JOIN. • EQUI JOIN: Semelhante ao NATURAL JOIN, porém retorna todos os

campos das tabelas envolvidas no relacionamento, inclusive os campos através dos quais as tabelas se relacionam, ocasionando repetição de dados (produto cartesiano):

Page 59: Curso SQL - Apostila

59

SELECT * FROM tab_editora, tab_livro; Saída: Editora.codigo Editora.nome estado livro.codigo livro.nome autor codigo_editora

1 OURO RJ 1 O REI DO INVERNO

BERNARD CORNWELL

1

2 BRASIL SP 1 O REI DO INVERNO

BERNARD CORNWELL

1

3 EUROPA RJ 1 O REI DO INVERNO

BERNARD CORNWELL

1

1 OURO RJ 2 1984 GEORGE ORWELL

1

2 BRASIL SP 2 1984 GEORGE ORWELL

1

3 EUROPA RJ 2 1984 GEORGE ORWELL

1

1 OURO RJ 3 NEUROMANCER WILLIAM GIBSON

2

2 BRASIL SP 3 NEUROMANCER WILLIAM GIBSON

2

3 EUROPA RJ 3 NEUROMANCER WILLIAM GIBSON

2

1 OURO RJ 4 EXCALIBUR BERNARD CORNWELL

1

2 BRASIL SP 4 EXCALIBUR BERNARD CORNWELL

1

3 EUROPA RJ 4 EXCALIBUR BERNARD CORNWELL

1

1 OURO RJ 5 ADMIRÁVEL MUNDO NOVO

ALDOUS HUXLEY

3

2 BRASIL SP 5 ADMIRÁVEL MUNDO NOVO

ALDOUS HUXLEY

3

3 EUROPA RJ 5 ADMIRÁVEL MUNDO NOVO

ALDOUS HUXLEY

3

1 OURO RJ 6 O CAÇADOR DE ANDRÓIDES

PHILIP K. DICK

2

2 BRASIL SP 6 O CAÇADOR DE ANDRÓIDES

PHILIP K. DICK

2

3 EUROPA RJ 6 O CAÇADOR DE ANDRÓIDES

PHILIP K. DICK

2

18 rows selected Neste caso, listaremos 18 linhas, ou seja, o produto cartesiano entre as linhas das tabelas envolvidas. Este tipo de JOIN é também chamado de CROSS JOIN e não é muito utilizado. Outra sintaxe possível é: SELECT * FROM tab_editora CROSS JOIN tab_livros;

Page 60: Curso SQL - Apostila

60

• SELF JOIN: É um INNER JOIN de uma tabela com ela mesma. 6.2 – OUTER JOIN. Um OUTER JOIN os dados relacionados e os não relacionados também.

• LEFT JOIN: lista todas as linhas da tabela à esquerda do JOIN, independente do relacionamento:

Considerando as tabelas tab_editora e tab_livro, adicionando uma linha a tab_editora: tab_editora Código Nome Uf 001 Ouro RJ 002 Brasil SP 003 Europa RJ 004 África SP Se quisermos listar todas as editoras com seus respectivos livros, escrevemos: SELECT E.nome AS Editora, L.nome AS Livro, L.autor AS Autor FROM tab_editora E, tab_livro L WHERE L.codigo_editora=E.codigo; Saída: Editora Livro Autor ---------- ---------- ------------- Ouro O Rei do Bernard Co Ouro 1984 George Orw Ouro Excalibur Bernard Co Brasil Neuroma William Gib Brasil O caçado Philip K Dic Europa Admirave Aldoux Hux 6 rows selected Neste caso, a linha contendo a editora África não aparece, pois não existem livros com esta editora. Se quisermos listar todas as editoras e seus livros, mesmo aquelas que não tenham livros editados, usamos um OUTER LEFT JOIN, assim listaremos todas as linhas da tabela à esquerda

Page 61: Curso SQL - Apostila

61

do JOIN, independente delas terem ou não dados correspondentes na segunda tabela: SELECT E.nome, L.nome, L.autor FROM tab_editora E LEFT JOIN tab_livro L ON E.codigo=L.codigo_editora; Saída: Editora Livro Autor ---------- ---------- ------------- Ouro O Rei do Bernard Co Ouro 1984 George Orw Ouro Excalibur Bernard Co Brasil Neuroma William Gib Brasil O caçado Philip K Dic Europa Admirave Aldoux Hux África 7 rows selected Agora aparecem na consulta todas as linhas de tab_editora, mesmo aquela que não encontra correspondência em tab_livros.

• RIGHT JOIN: lista todas as linhas da tabela à direita do JOIN, independente do relacionamento:

SELECT L.nome AS Livro, L.autor AS Autor, E.nome AS Editora FROM tab_livro L RIGHT JOIN tab_editora E ON E.codigo=L.codigo_editora; Saída: Livro Autor Editora ---------- ------------ ------------ O Rei do Bernard Co Ouro 1984 George Orw Ouro Excalibur Bernard Co Ouro Neuroma William Gib Brasil O caçado Philip K Dic Brasil Admirave Aldoux Hux Europa

África 7 rows selected

Page 62: Curso SQL - Apostila

62

7 – SUBQUERIES – Aprofundando o comando SELECT. As SUBQUERIES permitem a obtenção de dados de uma tabela com base na existência desses dados em outra consulta ou consultas. O primeiro SELECT do comando é chamado de OUTER QUERY ou simplesmente QUERY. Ao segundo SELECT chamamos de SUBQUERY. Considerando as tabelas tab_editora e tab_livro: SELECT L.nome AS Livro FROM tab_livro L WHERE L.codigo_editora= (SELECT E.codigo FROM tab_editora E WHERE E.nome='OURO'); Saída: Livro ------------------- O Rei do Inverno 1984 Excalibur 3 rows selected No exemplo acima seria a mesma coisa se escrevêssemos: SELECT tab_livro.nome AS Livro FROM tab_livro WHERE tab_livro.codigo_editora=001; Ou seja, testamos o resultado da SUBQUERY. Quando usamos uma SUBQUERY utilizando operadores de comparação, estas devem retornar sempre um único valor (uma única linha). Podemos usar todos os operadores de comparação: >, >=, <, <=, =, <> ou !=. Uma SUBQUERY deve selecionar o valor de somente uma coluna, e deve ser escrita entre parênteses. Podemos usar as funções de agregação em uma SUBQUERY:

Page 63: Curso SQL - Apostila

63

tab_filmes Nome qtd_espectadores ET 1500000 Pulp Fiction 500000 Homem Aranha 2000000 O homem que copiava

750000

SELECT F.nome AS Filme FROM tab_filmes F WHERE F.qtd_espectadores = (SELECT MAX(tab_filmes.qtd_espectadores) FROM tab_filmes); Saída: Filme ----------------- Homem Aranha 1 row selected No caso de utilizarmos uma SUBQUERY que retorne mais de uma linha (mas somente de uma coluna) não podemos usar os operadores de comparação, pois estes comparam somente a um argumento. Para fazer uma comparação em uma lista de argumentos, ou seja em SUBQUERIES que retornem mais de uma linha, usamos os operadores IN e NOT IN: SELECT F.nome AS Filme FROM tab_filmes F WHERE F.qtd_espectadores IN (SELECT tab_filmes.qtd_espectadores FROM tab_filmes WHERE tab_filmes.qtd_espectadores >= 750000 AND tab_filmes.qtd_espectadores < 2000000); Saída: Filme --------------------- ET O homem que copi 2 rows selected

Page 64: Curso SQL - Apostila

64

A SUBQUERY: (SELECT tab_filmes.qtd_espectadores FROM tab_filmes WHERE tab_filmes.qtd_espectadores >= 750000 AND tab_filmes.qtd_espectadores < 2000000); Retorna as seguintes linhas: Saída: tab_filmes.qtd_espectadores -------------------------------- 750000 2000000 Ao realizarmos a QUERY principal, comparamos o campo tab_filmes.qtd_espectadores aos valores selecionados na SUBQUERY. Se, ao contrário desejarmos selecionar os dados que não estejam contidos no conjunto de linhas selecionado na SUBQUERY, usamos: SELECT F.nome AS Filme FROM tab_filmes F WHERE F.qtd_espectadores NOT IN (SELECT tab_filmes.qtd_espectadores FROM tab_filmes WHERE tab_filmes.qtd_espectadores >= 750000 AND tab_filmes.qtd_espectadores < 2000000); Saída: Filme --------------------- Pulp Fiction Homem Aranha 2 rows selected Agora selecionamos as linhas que não encontram correspondência no conjunto obtido na SUBQUERY.

Page 65: Curso SQL - Apostila

65

tab_bilheteria Nome bilheteria ET 2000000 Homem Aranha 2500000 Considerando as tabelas tab_filmes e tab_bilheteria, usaremos os operadores EXISTS e NOT EXISTS para obter dados relacionados entre as duas tabelas: SELECT tab_filmes.nome AS Filme FROM tab_filmes WHERE EXISTS (SELECT tab_bilheteria.nome FROM tab_bilheteria WHERE tab_bilheteria.nome = tab_filmes.nome); Saída: Filme ----------------- ET Homem Aranha 2 rows selected Aqui selecionamos apenas os filmes que tem correspondentes em tab_bilheteria. Se, ao contrário, quisermos selecionar os filmes que não tem correspondência em tab_bilheteria, usamos o NOT EXISTS: SELECT tab_filmes.nome AS Filme FROM tab_filmes WHERE NOT EXISTS (SELECT tab_bilheteria.nome FROM tab_bilheteria WHERE tab_bilheteria.nome = tab_filmes.nome); Saída: Filme ------------------ Pulp Fiction O homem que c 2 rows selected

Page 66: Curso SQL - Apostila

66

Ao utilizarmos este formato, para cada linha selecionada na tabela tab_filmes o SQL executa uma vez a SUBQUERY que obtém os dados da tabela tab_bilheteria, assim a SUBQUERY é executada 4 vezes, uma para cada linha da tabela tab_filmes. Pelo fato destas SUBQUERIES estarem amarradas à QUERY, são chamadas de SUBQUERIES correlacionadas. Este tipo de SUBQUERY consome muitos recursos do sistema no seu processamento, portanto seu uso deve ser cuidadoso. Podemos utilizar uma técnica de aninhamento de SUBQUERIES. SUBQUERIES aninhadas são compostas de vários comandos SELECT: Considerando as tabelas: tab_editora Codigo Nome Uf 001 Ouro RJ 002 Brasil SP 003 Europa RJ 004 África SP tab_livro Codigo Nome autor codigo_editora 001 O Rei do Inverno Bernard Cornwell 001 002 1984 George Orwell 001 003 Neuromancer William Gibson 002 004 Excalibur Bernard Cornwell 001 005 Admirável Mundo

Novo Aldous Huxley 003

006 O caçador de andróides

Philip K. Dick 002

tab_vendas Código_editora codigo_livro qtd_vendida valor_unit 001 002 15 25.00 001 004 20 42.00 002 006 40 35.00 001 001 22 42.00 003 005 10 26.00 002 003 15 32.00

Page 67: Curso SQL - Apostila

67

Aninhando SUBQUERIES temos: SELECT tab_editora.nome AS EditoraLivrosVendidos FROM tab_editora WHERE tab_editora.codigo IN (SELECT tab_livro.codigo_editora FROM tab_livro WHERE tab_livro.codigo_editora IN (SELECT tab_vendas.codigo_editora FROM tab_vendas)); Saída EditoraLivrosVendidos ---------------------------- Ouro Brasil Europa 3 rows selected Aqui selecionamos as linhas de tab_editora que tenham livros vendidos, utilizando o conjunto obtido na seleção em tab_livro em relação ao conjunto obtido na seleção realizada em tab_vendas.

Page 68: Curso SQL - Apostila

68

8 – Manipulando dados. Até agora, aprendemos como recuperar dados ou listas de dados, sem em momento algum alterarmos o conteúdo do banco de dados. Neste capítulo, estudaremos os comandos utilizados para inserir, alterar ou deletar linhas nas tabelas. 8.1 – O comando INSERT. Este comando habilita uma inclusão no banco de dados. A sintaxe geral é: INSERT INTO Tabela (campo1, campo2...) VALUES (valor1, valor2...); Este formato, que adiciona à tabela os valores especificados na lista de valores nos campos correspondentes, é chamado de INSERT DECLARATIVO. Observamos que:

• Os valores devem ser do mesmo tipo dos especificados na declaração dos campos no banco de dados;

• Os valores inseridos devem respeitar o tamanho máximo do campo ao qual serão inseridos;

• Os valores inseridos após a clausula VALUES entrarão na seqüência especificada na lista de campos, ou seja, no exemplo acima, campo1 assume o valor de valor1, campo2 de valor2 e assim sucessivamente.

Se quisermos inserir os valores de todos os campos na ordem em que estes estão posicionados não precisamos listar os campos. Este formato é chamado de INSERT POSICIONAL. Assim o comando será: INSERT INTO Tabela VALUES (valor1, valor2...); Neste caso, todos os valores correspondentes aos campos da tabela deverão ser especificados na ordem em que estão declarados:

Page 69: Curso SQL - Apostila

69

tab_filmes Nome qtd_espectadores ET 1500000 Pulp Fiction 500000 Homem Aranha 2000000 O homem que copiava

750000

INSERT INTO tab_filmes VALUES (‘Homem Aranha II’, 2500000); Ou: INSERT INTO tab_filmes (qtd_espectadores, nome) VALUES (2500000, ‘Homem Aranha II’); Após qualquer um dos comandos, a tabela passa a ter: tab_filmes Nome qtd_espectadores ET 1500000 Pulp Fiction 500000 Homem Aranha 2000000 O homem que copiava

750000

Homem Aranha II

2500000

Podemos utilizar uma combinação de INSERT e SELECT para preencher uma tabela a partir de outra, como no exemplo: INSERT INTO tab_filmes_tmp SELECT * FROM tab_filmes; Neste caso teríamos como Resultado uma cópia de tab_filmes em tab_filmes_tmp:

Page 70: Curso SQL - Apostila

70

tab_filmes_tmp Nome qtd_espectadores ET 1500000 Pulp Fiction 500000 Homem Aranha 2000000 O homem que copiava

750000

Homem Aranha II

2500000

Este formato é muito mais eficiente do que realizar um comando INSERT para cada linha, mas somente pode ser utilizado quando e se as colunas da tabela receptora forem do mesmo tipo, tamanho e aparecerem na mesma ordem das colunas na tabela original. Podemos usar um comando SELECT mais elaborado: INSERT INTO tab_filmes_tmp SELECT tab_filmes.nome, tab_filmes.qtd_espectadores FROM tab_filmes WHERE tab_filmes.qtd_espectadores > 1500000; tab_filmes_tmp Nome qtd_espectadores Homem Aranha 2000000 Homem AranhaII 2500000 Neste caso obtemos como resultado uma tabela contendo exatamente as linhas do SELECT. Este formato de combinação do INSERT com SELECT pode ser usado sempre que os campos da tabela destino estejam contidos na tabela origem. 8.2 – O comando UPDATE. Este comando nos permite alterar o conteúdo de uma linha existente na tabela. A sintaxe geral do comando é: UPDATE Tabela SET campo1=valor1, campo2=valor2... WHERE condição;

Page 71: Curso SQL - Apostila

71

Onde a primeira clausula executada é o WHERE, e os valores serão alterados sempre que a condição for verdadeira. Se a clausula WHERE não existir todas as linhas da tabela serão alteradas: tab_filmes Nome qtd_espectadores ET 1500000 Pulp Fiction 500000 Homem Aranha 2000000 O homem que copiava

750000

Homem Aranha II

2500000

UPDATE tab_filmes SET tab_filmes.nome= ‘Teste’ WHERE tab_filmes.nome=‘ET’; Resultado: tab_filmes Nome qtd_espectadores Teste 1500000 Pulp Fiction 500000 Homem Aranha 2000000 O homem que copiava

750000

Homem Aranha II

2500000

UPDATE tab_filmes SET tab_filmes.nome= ‘Teste’; Resultado: tab_filmes Nome qtd_espectadores Teste 1500000 Teste 500000 Teste 2000000 Teste 750000 Teste 2500000

Page 72: Curso SQL - Apostila

72

Podemos usar qualquer tipo de condição no WHERE, inclusive uma SUBQUERY: tab_filmes nome qtd_espectadores ET 1500000 Pulp Fiction 500000 Homem Aranha 2000000 O homem que copiava

750000

Homem Aranha II

2500000

tab_bilheteria nome Bilheteria ET 25000000 Pulp Fiction 1000000 Homem Aranha 4000000 O homem que copiava

1500000

Homem Aranha II

5000000

UPDATE tab_filmes SET tab_filmes.qtd_espectadores=0 WHERE tab_filmes.nome IN (SELECT tab_bilheteria.nome FROM tab_bilheteria WHERE tab_bilheteria.bilheteria > 2000000); Resultado: tab_filmes nome qtd_espectadores ET 0 Pulp Fiction 500000 Homem Aranha 0 O homem que copiava

750000

Homem Aranha II

0

Page 73: Curso SQL - Apostila

73

8.3 – O comando DELETE. O DELETE nos permite excluir uma linha de uma tabela. A sintaxe do comando é: DELETE FROM Tabela WHERE condição; Da mesma forma que o UPDATE, o comando DELETE analisa primeiro a clausula WHERE, e as linhas que obedecerem a condição serão excluídas: tab_filmes Nome qtd_espectadores ET 0 Pulp Fiction 500000 Homem Aranha 0 O homem que copiava

750000

Homem Aranha II

0

DELETE FROM tab_filmes WHERE tab_filmes.qtd_espectadores=0 ; Resultado: tab_filmes Nome qtd_espectadores Pulp Fiction 500000 O homem que copiava

750000

Podemos usar qualquer tipo de condição após o WHERE, sejam elas simples ou SUBQUERIES. Atenção: se nenhuma clausula WHERE for especificada, todas as linhas da tabela serão excluídas.

Page 74: Curso SQL - Apostila

74

9 – Criando e mantendo Tabelas. Até o momento, utilizamos tabelas já criadas ou as geramos através de ferramentas próprias do banco de dados, mas o SQL possui comandos próprios para a criação e manutenção do banco de dados e das tabelas. 9.1 – Criando um Banco de Dados. O comando usado para a criação de um BD é: CREATE DATABASE NomeBD; Muitos gerenciadores de Banco de Dados não aceitam esta sintaxe, somente permitem criar tabelas via SQL. 9.2 – Criando Tabelas. O comando para criação de uma tabela via comando SQL é um pouco mais complexo do que o usado para criar o Banco de Dados. Além do comando CREATE TABLE, devemos especificar os campos, com o tipo e o tamanho de cada um: CREATE TABLE NomeBD.Tabela (”campo1” CHAR(30) NOT NULL, ”campo2” Number... PRIMARY KEY ("campo1") ); Na criação de uma tabela além do tipo de dados declaramos se um campo aceita ou não valores nulos e as suas chaves. Os tipos de dados que podemos usar em uma declaração de tabela são:

Page 75: Curso SQL - Apostila

75

Para o Oracle: Tipo Descrição

CHAR Dados alfanuméricos com tamanho suportado entre 1 e 255 caracteres.

DATE Data/hora, incluindo século, ano, mês, dia, horas minutos e segundos.

LONG Dados alfanuméricos de até 2 Gb. Também chamado de MEMO

LONG RAW

Dados binaries até 2 Gb, também chamado de BINARY LARGE OBJECT, ou BLOB

NUMBER Numérico, positivo ou negativo inteiro ou com casas decimais.

RAW Dado Binário de até 255 bytes.

ROWID Hexadecimal representando o endereço da linha na tabela. Também chamado de chave primária ou IDENTFY FIELD

VARCHAR2 Alfanumérico cujo tamanho pode variar entre 1 e 2.000 caracteres.

Para o Sql Server Numéricos Inteiros bigint Armazena dados numa faixa de valores entre -2^63 (-

9223372036854775808) e 2^63-1 (9223372036854775807), utiliza 8 bytes.

int

Armazena dados numa faixa de valores entre -2^31 (-2,147,483,648) e 2^31 - 1 (2,147,483,647), utiliza 4 bytes.

smallint

Armazena dados numa faixa de valores entre 2^15 (-32,768) e 2^15 - 1 (32,767), utiliza 2 bytes

tinyint

Armazena dados numa faixa de valores entre 0 e 255, utiliza 1 bytes.

Bin bit Datatype utilizado para armazenar os valores 1 ou 0, utiliza 1 byte. decimal e numeric decimal Precisao fixa e faixa de valores vai de -10^38 +1 a 10^38 –1. numeric Funcionalidade igual ao decimal. Numéricos Monetários money

Representa valor na moeda norte-americana, que podem ter até quatro dígitos para as casas decimais e, portanto, terão o arredondamento acontecendo na quarta casa decimal. Ocupa 8 bytes armazena uma faixa de valor que vai de -2^63 (-922,337,203,685,477.5808) a 2^63 - 1 (+922,337,203,685,477.5807).

smallmoney

Representa valor na moeda norte-americana, que podem ter até quatro dígitos para as casas decimais e, portanto, terão o arredondamento acontecendo na quarta casa decimal. Ocupa 4 bytes armazena uma faixa de valor que vai de -214,748.3648 e +214,748.3647.

Page 76: Curso SQL - Apostila

76

Numéricos Aproximados float

Datatype para armazenar números que não têm uma quantidade certa de dígitos para as casas decimais, e justamente por isso são chamados de ponto flutuante, armazena valores na faixa de -1.79E + 308 a 1.79E + 308, ocupa 8 bytes.

real

Datatype para armazenar números que não têm uma quantidade certa de dígitos para as casas decimais, e justamente por isso são chamados de ponto flutuante, armazena valores na faixa de -3.40E + 38 through 3.40E + 38, ocupa 4 bytes.

Data/Hora datetime

Armazena uma data que vai de 1/1/1753 a 31/12/9999, com uma precisao de 3.33 milissegundos. Ocupa 8 bytes. Os primeiros 4 são usados pra data(mês/dia/ano), e os outros 4 bytes são usados pro horário(HH:MM:SS:MS).

smalldatetime

Armazena uma data que vai de 1/1/1900 até 6/6/2079 com uma precisão de 1 minuto. Os 2 primeiros bytes são usados pra data(mês/dia/ano), e outros 2 pro horário(HH:MM:SS).

Caracteres char

Datatype de tamanho fixo que armazena até 8.000 caracteres, cada caracter ocupa 1 byte.

varchar

Datatype de tamanho variável que armazena até 8.000 caracteres, cada caracter ocupa 1 byte.

text

Datatype de tamanho variável que armazena até 2^31 - 1 (2,147,483,647) caracteres, cada caracter ocupa 1 byte.

Caracteres Unicode - O unicode foi inventado para resolver o problema de um único dígito ser armazenado com um determinado valor em um sistema de códigos e ser armazenado com outro valor em outro sistema de códigos diferente. O unicode atribui apenas um único número a cada caractere existente. nchar

Datatype de tamanho fixo que armazena dados no formato caractere unicode, pode armazenar até 4.000 caracteres, cada caracter ocupa 2 bytes.

nvarchar

Datatype de tamanho variável que armazena dados no formato caractere unicode, pode armazenar até 4.000 caracteres, cada caracter ocupa 2 bytes.

ntext

Datatype de tamanho variável que armazena dados no formato caractere unicode, pode armazenar até 2^30 - 1 (1,073,741,823) characters, cada caracter ocupa 2 bytes.

Datatypes Binários binary

Datatype de tamanho fixo que armazena dados binarios no formato hexadecimal, armazena até 8,000 bytes.

varbinary Datatype de tamanho variável que armazena dados binarios no formato hexadecimal, armazena até 8,000 bytes.

image Datatype de tamanho variável que armazena dados binarios no formato hexadecimal, armazena até 2^31 - 1 (2,147,483,647) bytes.

Alguns gerenciadores permitem a criação e preenchimento da tabela ao mesmo tempo, utilizando a sintaxe: CREATE TABLE Tabela2 (campo1 CHAR(30), campo2 NUMBER) AS (SELECT campo1, campo2 FROM Tabela1 [WHERE condição]); Assim criamos a Tabela2 com o conteúdo trazido a partir da seleção efetuada na Tabela1.

Page 77: Curso SQL - Apostila

77

9.3 – Alterando a estrutura de uma Tabela. Muitas vezes é necessário alterar a estrutura de uma tabela, seja criando novos campos ou alterando seu formato ou tamanho. Para isto temos o comando ALTER TABLE. Para adicionar um novo campo para a tabela, usamos o seguinte formato: ALTER TABLE Tabela ADD campo3 CHAR(20), campo4 VARCHAR2; A clausula ADD adiciona o(s) campo(s) no formato e tamanho indicado. Se quisermos alterar o formato ou tamanho de um dos campos, a sintaxe utilizada é: ALTER TABLE Tabela MODIFY campo3 CHAR(40);(Oracle) ALTER TABLE Tabela ALTER COLUMN campo3 CHAR(40) (Sql Server) Ao usar o comando ALTER TABLE com a clausula MODIFY ou ALTER COLUMN, devemos observar que os campos que tiverem seu tamanho modificado não poderão conter dados ocupando um tamanho maior do que o novo tamanho especificado. A clausula MODIFY não é aceita para todos os gerenciadores. 9.4 – Excluindo Tabelas e Banco de Dados. Para excluir um Banco de Dados ou uma Tabela, temos o comando DROP DATABASE e DROP TABLE respectivamente. A sintaxe dos comandos é: DROP DATABASE NomeBD; Usado para excluir uma base de dados, não é aceito em todos os gerenciadores. DROP TABLE Tabela; Este comando exclui definitivamente a tabela e seus índices, por isso é importante seu uso após acabar de utilizar uma tabela temporária, por exemplo, evitando sobrecarregar o Banco de Dados com índices que não serão mais usados. Como o comando não exige nenhum tipo de confirmação para sua execução, devemos ter cuidado ao utiliza-lo, pois uma vez apagada a tabela através deste recurso, não poderemos recupera-la.

Page 78: Curso SQL - Apostila

78

10 – VIEW É um objeto que permite que os dados de uma ou mais tabelas sejam visualizados de uma forma diferente da maneira de como foram criados. Este objeto permite que essa “visualização lógica” seja compartilhada por uma ou mais aplicações. É um objeto que os gerenciadores de banco de dados utilizam para gravar os resultados de uma consulta como se fosse uma tabela. Uma VIEW é uma consulta, portanto dentro dela só podemos escrever o comando SELECT. Os comandos SELECT dentro de uma VIEW podem ser combinados com:

• JOIN; • UNION; • GROUP BY - com função de totalização; • SUBQUERY; • ORDER BY - apenas com TOP.

Em uma VIEW podemos ocultar linhas e/ou colunas de uma ou mais tabelas. Sintaxe: CREATE VIEW Nome_da_VIEW AS SELECT Campo1 as Alias1, Campo2 as Alias2,... FROM Tabela WHERE Campo3 = valor Depois disso podemos acessar os dados da tabela através da VIEW: SELECT * FROM Nome_da_VIEW É possível ainda manipular dados de uma tabela por meio de VIEWS, com os comandos INPUT, UPDATE E DELETE: Sintaxe: INSERT INTO Nome_da_View VALUES(Dado1,Dado2,...) UPDATE Nome_da_View SET Alias1 = Dado1 WHERE Alias2 = Dados2 DELETE Nome_da_View WHERE Alias1 = Dado1

Page 79: Curso SQL - Apostila

79

Para alterar a estrutura de uma VIEW já existente, você pode utilizar o comando ALTER VIEW com o qual é possível realizar qualquer alteração dentro de uma VIEW, desde que não altere o seu nome. ALTER VIEW Nome_da_VIEW AS SELECT Campo1 as Alias1, Campo2 as Alias2,... FROM Tabela WHERE Campo3 = valor Para eliminar uma VIEW de uma base de dados, utilizamos o comando DROP VIEW: DROP VIEW Nome_da_VIEW 10.1 - WITH CHECK OPTION Se for necessário que todas as operações realizadas com os dados das tabelas por meio da VIEW obedeçam à cláusula WHERE devemos acrescentar a cláusula WITH CHECK OPTION na criação da VIEW: CREATE VIEW Nome_da_View AS SELECT Campo1 as Alias1, Campo2 as Alias2,... FROM Tabela WHERE Campo3 = Xvalor WITH CHECK OPTION 10.2 - WITH ENCRYPTION Quando uma VIEW é criada, algumas informações sobre ela são registradas nas tabelas do sistema:

• O nome da VIEW é registrado na tabela SYSOBJECTS; • Os comandos de criação da VIEW (código fonte), são

registrados na tabela do sistema SYSCOMMENTS. Qualquer usuário que tenha acesso às tabelas do sistema conseguirá ler o código de criação das suas próprias VIEWS: SELECT Text FROM SYSCOMMENTS WHERE Id = (SELECT Id FROM SYSOBJECTS WHERE Name = 'Nome_da_VIEW') Podemos também criptografar o código de criação das VIEWS, através da cláusula WITH ENCRYPTION: CREATE VIEW Nome_da_VIEW

Page 80: Curso SQL - Apostila

80

WITH ENCRYPTION AS SELECT Campo1 as Alias1, Campo2 as Alias2,... FROM Tabela WHERE Campo3 = valor 10.3 – VIEW de VIEW É possível ainda criar uma VIEW que filtre dados de outra VIEW. Mas a necessidade de criar VIEWS aninhadas deve sempre ser bem avaliada, já que esta prática pode causar problemas de performance. Primeira VIEW: CREATE VIEW Nome_da_VIEW_um AS SELECT Campo1 as Alias1, Campo2 as Alias2,... FROM Tabela VIEW aninhada: CREATE VIEW Nome_da_VIEW_dois AS SELECT Campo1 as Alias1 FROM Tabela WHERE Campo3 = valor Se for mesmo necessário trabalhar com VIEWS aninhadas, devemos atribuir nomes a elas que indiquem seu nível de aninhamento para facilitar a detecção de possíveis problemas de performance.

Page 81: Curso SQL - Apostila

81

11 – STORED PROCEDURES STORED PROCEDURE é um tipo de programa criado no servidor do banco de dados, dentro de uma base de dados específica, que tem como principal objetivo concentrar operações realizadas com os dados contidos no banco de dados. 11.1 – Regras para Criar STORED PROCEDURES • Uma STORED PROCEDURE pode receber parâmetros de entrada; • Os parâmetros de entrada de uma STORED PROCEDURE não podem ser do tipo TABLE; (Sql Server); • Uma STORED PROCEDURE usa três formas para retornar valores:

o Com o comando RETURN; o Com um SELECT como último comando

(retornando o conteúdo de uma tabela); o Com parâmetros OUTPUT (retornando vários

valores de uma só vez); • Dentro de uma STORED PROCEDURE podemos executar qualquer comando da linguagem Transact-SQL, exceto o próprio comando CREATE PROCEDURE; • Para executar uma STORED PROCEDURE, devemos utilizar o comando EXECUTE ou a procedure SP_EXECUTESQL. Sintaxe: CREATE PROC[EDURE] Nome_Da_Procedure[;Numero] [{@parâmetro datatype}] [VARYING][=default][OUTPUT] [,…n] [WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}] [FOR REPLICATION] [AS] Corpo da Procedure: Comandos Transact-SQL: Numero – É um numero inteiro usado para agrupar PROCEDURES de mesmo nome, assim elas podem ser eliminadas juntas com um único comando DROP PROCEDURE. Suponha que na base de dados existam as seguintes PROCEDURES:

• P_AlteraSal;1 • P_AlteraSal;2

Page 82: Curso SQL - Apostila

82

• P_AlteraSal;3 • P_AlteraSal;4

Se alterar salário tiver cálculos diferenciados, é interessante utilizar o mesmo nome com o Numero. Podemos utilizar uma PROCEDURE de cada vez, na situação adequada: EXEC P_AlteraSal;1 <parametros> EXEC P_AlteraSal;2 <parametros> EXEC P_AlteraSal;3 <parametros> EXEC P_AlteraSal;4 <parametros> Estas PROCEDURES não podem ser eliminadas individualmente, devem ser eliminadas todas de uma só vez. DROP PROCEDURE P_AlteraSal. VARYING – Tipo de dado que se aplica apenas a parâmetros de cursores. DEFAULT – É um valor definido como padrão para um parâmetro de uma PROCEDURE para que o gerenciado de banco de dados tenha um valor definido previamente para o referido parâmetro, no caso de nada ser informado durante a sua execução. OUTPUT – É um parâmetro que indica que a PROCEDURE retorna um valor sem utilizar o comando RETURN: CREATE PROCEDURE P_Conta @Num_1 int, @Num_2 int, @Resposta bigint OUTPUT AS SET @Resposta = @Num_1 * @Num_2 Esta PROCEDURE recebe três parâmetros, sendo @Num_1, @Num_2 e @Resposta OUTPUT. No exemplo acima, a PROCEDURE apenas atribui à variável @Resposta o resultado da multiplicação de @Num_1 por @Num_2, não retornando diretamente valor algum. Observando o comando para executar a PROCEDURE abaixo: DECLARE @Resultado bigint EXEC P_Conta 10,7,@Resultado OUTPUT

Page 83: Curso SQL - Apostila

83

PRINT @Resultado Neste caso temos um retorno direto da PROCEDURE. WITH RECOMPILE – Indica que o servidor de banco de dados não deve manter na memória o plano de execução desta PROCEDURE, gerando um novo plano todas as vezes que ela for executada. WITH ENCRYPTION – Faz com que o gerenciador do banco de dados criptografe o código da STORED PROCEDURE na tabela de sistema SYSCOMMENTS, de modo a que a procedure não seja publicada durante uma replicação. FOR REPLICATION – Especifica que a STORED PROCEDURE não pode ser executada no servidor SUBSCRIBER. Uma procedure criada com este parâmetro é usada como um filtro e executada apenas durante a replicação. Esta opção não pode ser utilizada em conjunto com WITH RECOMPILE.

Page 84: Curso SQL - Apostila

84

12 – Transação É uma unidade lógica de processamento que tem por objetivo preservar a integridade e a consistência dos dados de um sistema. Ela é parte essencial de um sistema, pois permite que o gerenciador de banco de dados restaure os dados para uma situação anterior a uma falha no processamento, caso isto ocorra. Supondo que em um Banco Comercial fosse necessário realizar uma retirada um determinado valor em dinheiro de uma conta corrente, creditando esse mesmo valor em uma conta de aplicação, imaginemos que as contas correntes do Banco sejam controladas pela tabela ContaCorrente, e as contas de aplicação sejam controladas pela tabela Aplicação. Sendo assim, a unidade de processamento que deve ser criada vai conter um débito de uma conta e um crédito em outra conta. Assim sendo, é altamente imprescindível que o débito e o crédito sejam realizados. Esta unidade de processamento precisa ser realizada em duas etapas e é necessário garantir que o gerenciador de banco de dados realizará a tarefa por inteiro ou que não fará nada, caso ocorra algum problema entre a realização do débito e do crédito, preservando as tabelas ContaCorrente e Aplicação. Este requerimento do tipo “ou faz tudo ou não faz nada” é chamado de atomicidade. 12.1 – Comandos de Transação Begin Transaction, Rollback Transaction e Commit Transaction No Sql Server, para realizar esta unidade de processamento com atomicidade, devemos abrir a transação, realizar as operações com dados e verificar se algum problema ocorreu. Se todas as operações com os dados forem realizadas com sucesso, devemos confirmar a operação. Caso algum problema tenha ocorrido, devemos garantir que nada seja feito:

• Criar a Unidade de Processamento; • Realizar o DÉBITO; • Checar a ocorrência de erro; • Se ocorreu erro:

o Desfazer qualquer operação que tenha sido feita até este ponto;

Page 85: Curso SQL - Apostila

85

o Interromper o processamento;

• Realizar o CRÉDITO; • Checar a ocorrência de erro • Se ocorreu erro:

o Desfazer qualquer operação que tenha sido feita até este ponto;

o Interromper o processamento; • Se não ocorrer nenhum problema:

o Confirmar a operação. Para realizar este processamento, são necessários três comandos:

• BEGIN TRANSACTION: Cria uma transação(unidade de processamento lógico); • ROLLBACK TRANSACTION: Encerra a transação e desfaz qualquer operação que tenha sido realizada com os dados; • COMMIT TRANSACTION: Encerra a transação e efetiva qualquer operação que tenha sido realizada com os dados.

Observando o seguinte esquema: BEGIN TRANSACTION Realizar o DÉBITO Checar a ocorrência de algum erro Se ocorreu erro : ROLLBACK TRANSACTION RETURN Realizar o CRÉDITO Checar a ocorrência de algum erro Se ocorreu erro : ROLLBACK TRANSACTION RETURN Se não ocorrer nenhum problema COMMIT TRANSACTION 12.2 - @@ERROR @@ERROR: é uma variável global (também chamada de função), “alimentada” pelo próprio Sql Server após a realização de qualquer comando da linguagem Transact-Sql. Dentro de @@ERROR haverá o valor igual a zero se, após a realização de um comando nenhum erro ocorrer, ou aparecerá um numero

Page 86: Curso SQL - Apostila

86

representando um determinado erro (existente na tabela SYSMESSAGES), caso tenha ocorrido algum problema no processamento do comando: BEGIN TRANSACTION Realizar o DÉBITO IF @@ERROR <> 0 BEGIN ROLLBACK TRANSACTION RETURN END Realizar o CRÉDITO IF @@ERROR <> 0 BEGIN ROLLBACK TRANSACTION RETURN END COMMIT TRANSACTION

Page 87: Curso SQL - Apostila

87

13 – Tópicos avançados de SQL – CURSOR. Cursores são variáveis utilizadas para navegar pelas linhas retornadas de um comando SQL. Algumas vezes é conveniente armazenar o resultado de uma consulta para uso posterior, ou então utilizarmos o SQL em conjunto com algumas linguagens que não conseguem tratar o retorno de SQL contendo mais de uma linha, como acontece com o Cobol. Nestes casos, armazenamos o resultado em uma variável do tipo CURSOR, através da qual podemos navegar através de ponteiros. Quando não for necessário o uso de cursores, estes devem ser evitados, pois o SQL é uma linguagem criada para tratamento de conjuntos, ou seja, a performance de um comando SQL utilizando cursores é menos eficaz do que a manipulação de dados em conjunto. Cursores podem ser utilizados para operações de consulta, alteração e exclusão, nunca para uma operação de inclusão. A seguir, veremos como criar, abrir, acessar, fechar e desalocar da memória uma variável do tipo cursor. 13.1 – Criando um CURSOR. A declaração de um CURSOR no padrão SQL – 92: DECLARE NomeCursor CURSOR FOR comando SELECT [FOR UPDATE OF campo1, campo2.../ ORDER BY campo1…]; Onde:

• No uso de UPDATE não é necessário que a coluna a ser atualizada apareça no SELECT;

• O uso de FOR UPDATE OF e ORDER BY é mutuamente exclusivo; • Se especificarmos as colunas após o UPDATE, somente as

especificadas poderão ser alteradas, se nenhuma coluna for especificada, todas as colunas da tabela poderão ser alteradas.

Existem outras clausulas que podem ser acrescidas no momento da criação de um cursor, válidas para o padrão Transact – SQL. São elas:

Page 88: Curso SQL - Apostila

88

DECLARE NomeCursor CURSOR [LOCAL/ GLOBAL/ FORWARD_ONLY/ SCROLL/ STATIC/ KEYSET/ DYNAMIC/ FAST_FORWARD/ READ ONLY/ SCROLL_LOCKS/ OPTIMISTIC/ TYPE_WARNING] FOR comando SELECT [FOR UPDATE OF campo1, campo2.../] Onde:

• LOCAL: Especifica que o cursor é de uso local; • GLOBAL: Disponibiliza o escopo do cursor para toda a conexão; • FORWARD_ONLY: Neste caso a navegação pelo cursor somente

poderá ser executada da primeira para a última linha; • SCROLL: Disponibiliza todas as operações de busca no cursor; • STATIC: Impossibilita a alteração dos dados contidos no cursor; • KEYSET: Cria um controle através de índice para o cursor; • DYNAMIC: Este tipo de cursor reflete todas as alterações nos valores

dos dados contidos no cursor; • FAST_FORWARD: Otimiza a performance da navegação. Não pode

ser usado em conjunto com a opção FOWARD_ONLY; • READ_ONLY: Impede a alteração ou exclusão de algum dado

presente no cursor; • SCROLL_LOCKS: garante que os dados recuperados possam ser

alterados posteriormente. Não pode ser utilizada em conjunto com FAST_FORWARD;

• OPTIMISTIC: Garante que as linhas que tiverem seu valor alterado dentro do cursor não possam ser novamente alteradas ou deletadas;

• TYPE_WARNING: envia mensagens de advertência sempre que for alterado o tipo de dado em um cursor.

13.2 – Abrindo um CURSOR. O comando SELECT de um cursor somente será executado na abertura do cursor. É nesse momento que o SQL monta a lista de ponteiros e a associa ao cursor. A sintaxe do comando é: OPEN NomeCursor; 13.3 – Acessando um CURSOR. Navegamos dentro de um cursor utilizando o comando FETCH: FETCH NomeCursor [INTO variável/variáveis receptora(s)];

Page 89: Curso SQL - Apostila

89

Ao utilizarmos o comando, o ponteiro do cursor avança através do resultado da consulta, uma linha por vez. Quando usamos a opção INTO, os dados resultantes serão armazenados na(s) variável(variáveis) especificada(s). Para resgatar todas as linhas de uma consulta é necessário que o comando FETCH seja executado dentro de uma estrutura de repetição. Podemos checar o STATUS do cursor durante o processamento. Para isto, o Transact – SQL possui duas variáveis: @@SQLSTATUS e @@ROWCOUNT. O status pode assumir os seguintes valores:

• 0 (zero) se o processamento for OK; • 1 (um) se o processamento ocasionou erro; • 2 (dois) se não existirem mais linhas a serem processadas no

cursor. Outros BDs possuem diferentes variáveis para armazenar o status, como o DB2, que possui a variável chamada SQLCA (SQL COMMUNICATION ÁREA), que é composta, dentre outras subvariáveis pela SQLERRM e o SQLCODE. Caso haja erro (SQLCODE < 0) a SQLERRM contem uma parte da descrição do erro:

• Valores menores que zero: indicam que ocorreu erro; • Valores entre zero e cem (inclusive): indicam que o comando foi

executado, porém com restrições; • Valor igual a 100 (cem): Indica que o fim da lista de dados foi

atingido; • Valor igual a zero: Processamento OK.

13.4 – Fechando um CURSOR. O fechamento de um cursor é simples e tem a mesma sintaxe para todos os gerenciadores: CLOSE NomeCursor; O fechamento de um cursor não implica no desalocamento do mesmo na área de memória, após fecharmos um cursor, podemos reabri-lo sem problemas. Quando desejarmos desalocar o cursor da memória, usamos o comando DEALLOCATE: DEALLOCATE CURSOR NomeCursor; Assim liberamos a área de memória utilizada pelo cursor.

Page 90: Curso SQL - Apostila

90

Exemplos : Primeiro exemplo: Declaração de variáveis que serão utilizadas no cursor: DECLARE @cod_pai int, @nome_pai char(50), @idade_pai tinyint Primeira linha da declaração do cursor: DECLARE COPIA_TABELA CURSOR FOR (copia_tabela é o nome do cursor) Seleção de campos em uma tabela pra preencher o cursor: SELECT Cod_Pai, Nome_Pai, Idade_pai FROM PAI2 Abrindo o cursor: OPEN COPIA_TABELA Lendo a primeira linha do cursor: FETCH NEXT FROM COPIA_TABELA Colocando os dados da primeira linha do cursor nas variáveis: INTO @cod_pai, @nome_pai, @idade_pai Verificando se não ocorreu erro na leitura: WHILE @@FETCH_STATUS = 0 BEGIN Inserindo na tabela pai os valores da linha do cursor: INSERT INTO PAI VALUES(@cod_pai, @nome_pai, @idade_pai)

Page 91: Curso SQL - Apostila

91

Lendo a próxima linha do cursor: FETCH NEXT FROM COPIA_TABELA INTO @cod_pai, @nome_pai, @idade_pai END Fechando o cursor: CLOSE COPIA_TABELA Desalocando o cursor da memória: DEALLOCATE COPIA_TABELA Segundo exemplo: DECLARE @codrt int, @codgrpprio int, @codsusep char(4), @codresolucao int, @indpriosusep int DECLARE tblParametros_Cursor CURSOR FOR SELECT codGrpPrio, codSusep, codResolucao, indPrioSusep FROM tblPrioridades OPEN tblParametros_Cursor FETCH NEXT FROM tblParametros_Cursor INTO @codgrpprio, @codsusep , @codresolucao, @indpriosusep SET @Codrt = 10 WHILE @@FETCH_STATUS = 0 BEGIN

Page 92: Curso SQL - Apostila

92

INSERT INTO tblPrioridadesRT (codRT, codGrpPrio, codSusep, codResolucao, indPrioSusep ) VALUES (@codrt, @codgrpprio, @codsusep, @codresolucao, @indpriosusep) FETCH NEXT FROM tblParametros_Cursor INTO @codgrpprio, @codsusep , @codresolucao, @indpriosusep SET @codrt = @codrt + 1 END CLOSE tblParametros_Cursor DEALLOCATE tblParametros_Cursor

Page 93: Curso SQL - Apostila

93

14 – Erros comuns do SQL e suas soluções. Neste capítulo apresentaremos os erros mais comuns com as mensagens e as opções de tratamento dos mesmos. Erros de sintaxe/operacionais:

• table or view does not exist – Esta mensagem pode aparecer se o nome da tabela ou VIEW estiver escrito errado ou se o usuário não tiver acesso permitido à tabela ou ao VIEW;

• invalid username/password; logon denied – Acontece quando o login e/ou a senha do usuário estão incorretas. Pode ocorrer também quando o servidor não está disponível;

• FROM keyword not found where expected – Esta mensagem de erro pode nos induzir a procurar o erro no lugar errado. Pode ser que a clausula FROM não esteja realmente especificada, então a solução é fácil, porém muitas vezes o FROM está lá, mas existem erros nas clausulas anteriores, como, por exemplo, esquecer de fechar um parêntese ou esquecer a virgula para separar campos em um SELECT. Como o SQL não entende a sintaxe do SELECT, acusa o erro como se a clausula FROM tivesse sido esquecida;

• group function is not allowed here – Isto ocorre quando utilizamos uma função de agregação, como COUNT ou SUM na clausula GROUP BY, que só pode ser usada com campos da tabela;

• invalid column name – Este erro ocorre quando nos referenciamos a uma coluna da tabela que não existe;

• missing left parenthesis – Ocorre quando não fechamos os parênteses corretamente;

• missing right parenthesis – Parênteses foram fechados sem serem abertos;

• missing comma – Ocorre quando esquecemos de colocar a virgula em uma lista de campos a serem recuperados em uma instrução SELECT ou em uma lista de valores a serem inseridos em um INSERT;

• column ambigously defined – Aparece quando o nome de uma coluna não está bem definido na consulta, por exemplo ao realizar uma consulta em duas tabelas, ambas possuem a coluna nome. Neste caso devemos nos referenciar à coluna nome da tabela1 como tabela1.nome e a coluna da tabela2 como tabela2.nome. Se utilizarmos apenas nome o SQL não saberá a qual campo a consulta se refere;

• command not properly ended – Quando o caractere delimitador de um comando SQL (ponto e virgula, barra) não está presente;

Page 94: Curso SQL - Apostila

94

• missing expression – Acontece quando terminamos uma lista de argumentos com virgula, como em um SELECT ou valores a serem inseridos em um INSERT;

• not enough arguments for function – Os argumentos passados para a função estão incompletos ou incorretos;

• not enough values – Ocorre quando ao realizar um INSERT a lista de campos é maior que a lista de valores a serem inseridos;

• integrity constraint (campo1) violated – parent key not found – Acontece quando tentamos inserir um valor em uma tabela1 que possui uma chave estrangeira da tabela2 e este valor é inválido, ou seja, não existe na tabela2;

• inserted value too large for column – Um ou mais valores inseridos é maior que a capacidade da coluna;

• insufficient privileges – O usuário não tem permissão para executar o comando;

Erros de lógica:

• Usar palavras reservadas para nomear tabelas ou campos, como DATE, por exemplo;

• Usar a clausula DISTINCT em mais de uma coluna; • DROPAR uma tabela sem querer: é comum ter mais de uma

tabela com o mesmo nome em um sistema, por isso devemos estar atentos ao remover a tabela indicando a qual database a tabela pertence;

• Gerar um produto cartesiano ao realizar um JOIN de uma tabela: ocorre quando realizamos um JOIN sem utilizar a clausula WHERE, neste caso teremos valores duplicados;

• Permitir a duplicação de linhas nas tabelas: ocorre quando não existe uma definição de chave primária ou chaves estrangeiras na elaboração do Banco de dados. Um bom planejamento evita este tipo de redundância

• Incluir valores numéricos entre plics; • Incluir alfanuméricos sem plics ou colchetes.

Page 95: Curso SQL - Apostila

95

15 – Exercícios propostos. 1 - Usando a tabela Customers, escreva uma QUERY que retorne todas as companhias (CompanyName). 2 - Usando a tabela Customers, escreva uma QUERY que retorne todas as companhias (CompanyName), endereço (Address) e país (Country). 3 - Usando a tabela Customers, escreva uma QUERY que retorne todas as companhias (CompanyName), endereço (Address), e país (Country), mostrando nesta ordem : PAIS, COMPANHIA e ENDEREÇO, com nome da coluna em português. 4 - Usando a QUERY do exercício nº 3, reescreva-a retornando apenas a primeira linha do resultado. 5 – Usando a tabela Customers, escreva uma QUERY que retorne todos os Países, sem repeti-los. 6 - Usando a tabela Employees, escreva uma QUERY que retorne o PrimeiroNome (FirstName) e o Sobrenome (LastName) dos empregados, onde o Primeiro nome inicie com a letra M. 7 – Usando a QUERY do exercício nº 6, reescreva-a incluindo também a Cidade (City) filtrando pelos empregados que moram em Londres (London). 8 – Usando a tabela Products, escreva uma QUERY que retorne o Código do Produto (ProductID),Nome do Produto (ProductName) e Preço Unitário (UnitPreço), onde o preço esteja entre 18,00 e 20,00, ordenado pelo nome do produto. 9 – Usando a tabela Suppliers, escreva uma QUERY que retorne os três primeiros caracteres dos Países (Country), sem repeti-los. 10 – Usando a tabela Order Details, escreva uma QUERY que mostre o número de Itens do Pedido (OrderID), do pedido número 10273. 11 – Usando a tabela Order Details, mostre o Pedido (OrderID), o Código do Produto (ProductID), o Preço (UnitPrice) e a Quantidade (Quantity), onde o número do pedido seja igual a 10273. 12 – Usando a QUERY do exercício 11, inclua a coluna Valor, que retornará o resultado da quantidade vezes o preço unitário dos produtos listados.

Page 96: Curso SQL - Apostila

96

13 – Usando a tabela Order Details, escreva uma QUERY que retorne o Valor Total do pedido (OrderID), cujo numero seja o 10273. 14 – Usando a tabela Products, escreva uma QUERY que retorne a Media dos preços (UnitPrice) dos produtos. 15 – Usando a tabela Customers e Employees, escreva uma QUERY que retorne os países onde constem Clientes e Empregados. 16 – Usando a tabela Customers, escreva uma QUERY que retorne Companhia, Cidade e Pais, onde os países sejam Alemanha e Brasil, ordenados por Pais, Cidade e Companhia. 17 – Usando a tabela Products, escreva uma QUERY que retorne Nome do Produto, Preço Unitário e Quantidade em estoque, onde os preços variem entre 10,00 e 20,00 e que estejam em estoque, ordenados pelo Preço Unitário e pelo Nome do Produto. 18 – Usando a tabela Products, escreva uma QUERY que retorne a quantidade de produtos que NÃO estejam em estoque. 19 – Usando a tabela Products, escreva uma QUERY que retorne o Maior e o Menor preço dos produtos. 20 – Usando a tabela Tab_Frutas (FRUTAS), escreva uma QUERY que retorne a Data da Validade, acrescida de 05 anos, para o produto MAMAO. 21 – Usando a tabela Orders (NORTHWIND), escreva uma QUERY que retorne o OrderID e ShipName, trocando todas as letras ‘a’ por ‘*’ na coluna ShipName, onde OrderID esteja entre 10248 e 10255. 22 – Usando a tabela Orders, escreva uma QUERY que retorne Shipname e Posição da letra ‘a’ na coluna Shipname onde OrderId seja menor que 10255. 23 – Usando a tabela Customers, escreva uma QUERY que retorne Código do Cliente, Companhia, Tamanho do nome da companhia e País, onde o país seja o Brasil e o Código do Cliente comece com a letra Q, ordenado pelo nome da companhia. 24 – Usando a tabela Order Details, escreva uma QUERY que retorne Código do Pedido, Preço Unitário e Quantidade de todos os pedidos.

Page 97: Curso SQL - Apostila

97

25 – Usando a tabela Order Details, escreva uma QUERY que retorne Código do Pedido, Preço Unitário, Quantidade e o Valor do Pedido de todos os pedidos. 26 - Usando a tabela Order Details, escreva uma QUERY que retorne Código do Pedido, Valor Total por Pedido de todos os pedidos agrupados. 27- Observe a QUERY abaixo: SELECT P.Nome_Pai as 'Nome do Pai', F.Nome_Filho as 'Nome do Filho', Sexo_Filho as 'Sexo do Filho' FROM Pai P, Filho F Rode-a no banco TESTE e observe o seu resultado. Houve linha repetida? Quantas linhas retornaram? 28 – Agora rode a QUERY abaixo. SELECT P.Nome_Pai as 'Nome do Pai', F.Nome_Filho as 'Nome do Filho', Sexo_Filho as 'Sexo do Filho' FROM Pai P, Filho F WHERE P.Cod_Pai = F.Cod_Pai Quantas linhas retornaram? Qual foi a diferença entre esta consulta e a anterior? 29 – Faça a mesma consulta, agora utilizando a cláusula INNER JOIN. 30 – Observe o retorno da QUERY: “SELECT * FROM Pai, Filho”. 31 – Utilizando a QUERY acima, altere-a pra não mostrar valores repetidos, sem utilizar INNER JOIN. 32 – Faça a mesma consulta, agora utilizando a cláusula INNER JOIN. 33 – Utilizando a QUERY do exercício 29, inclua a condição de listar somente os Filhos de Sexo Feminino. 34 – Rode as QUERIES abaixo e observe os resultados: SELECT P.Nome_Pai as 'Nome do Pai', F.Nome_Filho as 'Nome do Filho', Sexo_Filho as 'Sexo do Filho' FROM Pai P LEFT JOIN Filho F ON P.Cod_Pai = F.Cod_Pai

Page 98: Curso SQL - Apostila

98

SELECT P.Nome_Pai as 'Nome do Pai', F.Nome_Filho as 'Nome do Filho', Sexo_Filho as 'Sexo do Filho' FROM Pai P RIGHT JOIN Filho F ON P.Cod_Pai = F.Cod_Pai SELECT P.Nome_Pai as 'Nome do Pai', F.Nome_Filho as 'Nome do Filho', Sexo_Filho as 'Sexo do Filho' FROM Pai P CROSS JOIN Filho F Vamos discutir os resultados… 35 – Execute a QUERY: SELECT * FROM Funcionario. Analise o resultado, observando que há uma recursividade. 36 – Usando a tabela Funcionário, escreva uma QUERY que retorne Nome do Chefe, Nome do Funcionário, ordenado pelo Nome do Chefe. 37 – Usando as tabelas Pai, Filho e Notas, escreva um QUERY que retorne o Nome do Pai, Nome do Filho e as Notas do Filho. 38 – Usando as tabelas Pai e Filho, escreva uma QUERY com SUBQUERY que retorne Os Nomes dos Filhos, onde o Nome do Pai possua a sílaba “ão”, na terceira posição. 39 - Usando as tabelas Pai e Filho, escreva uma QUERY com SUBQUERY que retorne os Nomes dos Filhos, onde o Nome do Pai contenha o sobrenome “Sobrinho”. 40 – Usando as tabelas Filho e Notas, escreva uma QUERY com SUBQUERY que retorne os Nomes dos filhos que tenham notas maiores que 8. 41 – Usando as tabelas Filho e Notas, escreva uma QUERY com SUBQUERY que retorne os Nomes dos filhos que NÃO tenham notas maiores que 8. 42 – Execute a QUERY: INSERT INTO PAI VALUES ('5', 'seu nome', 'sua idade'). Descreva o resultado. 43 – Execute a QUERY: INSERT INTO PAI VALUES ('seu nome', 'sua idade'). Analise o resultado. 44 – Execute a QUERY: INSERT INTO PAI VALUES ('seu nome', sua idade). Analise o resultado.

Page 99: Curso SQL - Apostila

99

45 – Execute a QUERY: INSERT INTO PAI VALUES (seu nome, sua idade). Descreva o resultado. 46 – Execute a QUERY: INSERT INTO PAI VALUES ([seu nome],sua idade). Descreva o resultado. 47 – Execute a QUERY: INSERT INTO PAI VALUES ('seu nome'). Descreva o resultado. 48 – Execute a QUERY: INSERT INTO PAI (Idade_pai, Nome_pai) VALUES (sua idade, seu nome). Descreva o resultado. 49 – Execute a QUERY: INSERT INTO PAI (Idade_Pai, Nome_Pai) VALUES (sua idade,"seu nome"). Descreva o resultado. 50 – Execute a QUERY: INSERT INTO PAI (Idade_Pai, Nome_Pai) VALUES (sua idade, [seu nome]). Descreva o resultado. 51 – Execute a QUERY: INSERT INTO PAI (Idade_Pai, Nome_Pai) VALUES (sua idade, 'seu nome'). Analise o resultado. 52 – Faça livremente DUAS QUERIES INSERT, utilizando qualquer tabela. Utilize INSERT POSICIONAL E DECLARATIVO. 53 – Altere a tabela pai, colocando o nome Julio Cezar onde o Código do Pai for igual a 7. 54 – Altere a tabela pai colocando o sobrenome “Ribeiro” entre os dois nomes existentes, onde o código do pai for igual a 4, sem alterar o nome todo, incluindo apenas o Ribeiro. 55 – Altere a tabela pai colocando no final da cada nome a string: “** velho **”, onde a idade for maior que 36. 56 – Insira dois pais na tabela Pai e depois os exclua. 57 – Crie um Banco de Dados Chamado “Exercicio’seu nome’”, utilizando o QUERY ANALYZER. Copie a resposta do QUERY ANALYZER e analise-a. 58 – Crie uma Tabela no seu Banco Chamada “Pai’sua idade’”, com três colunas:

• Coluna Cod_Pai -> do tipo que suporte de –32768 a 32767, com autonumeração, não aceitando nulos e Constraint Chave Primária.

Page 100: Curso SQL - Apostila

100

• Coluna Nome_Pai -> do tipo de tamanho variável que receba até 8000 caracteres, limitada a 30 e que não aceite nulos.

• Coluna Idade_Pai -> do tipo que aceite apenas números de 0 a 255

e que aceite nulos. 59 – Popule a tabela com pelo menos 5 linhas, usando o comando INSERT. 60 – Adicione duas colunas ao mesmo tempo, utilizando o comando ALTER:

• Estado_Civil -> de conteúdo variável com 10 bytes permitindo nulos • Ativo -> de conteúdo fixo de 01 byte não permitindo nulos e valor

padrão 1. 61 – Altere todos os dados da tabela, incluindo valores nos campos criados na última questão. 62 – Altere a coluna Estado_Civil para um conteúdo variável unicode, de tamanho 15 e que não permita nulos. 63 – Utilizando o database SYSAMPLE:

• Crie uma VIEW que apresente o nome dos clientes, o salário de cada um (totalizado com o salário dos cônjuges – daqueles que tiverem cônjuge) e o valor do maior crédito concedido a cada um.

• Crie uma VIEW que apresente o nome de todos os produtos vendidos em cada pedido.

• Crie uma VIEW que apresente o nome dos funcionários e o total de bônus de cada um.

Obs: Estude a função ISNULL.

64 – No database AVBP, a tabela Versão possui registros sobre as versões de vários sistemas. Crie uma VIEW que mostre todos as colunas relativas a última versão de cada sistema.