Baixe Oracle Apostila SQL e outras Notas de estudo em PDF para Sistemas de Informação, somente na Docsity!
I nstituto M unicipal de E nsino S uperior de A ssis
Prof. Alex Poletto - [email protected]
SQL
1. Objetivos do curso
Esse curso tem por objetivo dar condições ao aluno de se lançar na linha de
DBA’s e na área de Desenvolvimento de Aplicações. Esse curso abrange a criação de
estruturas de bancos de dados e o armazenamento, recuperação e manipulação de dados em
um banco de dados relacional.
2. Histórico
A SQL se tornou o padrão para linguagens de banco de dados relacionais. Existem
diversas versões de SQL. A versão original foi desenvolvida pela IBM no Laboratório de
Pesquisa de San José. Essa linguagem, originalmente chamada de Sequel, foi implementada
no início dos anos 70. Desde então, a linguagem Sequel foi evoluindo e passou a ser chamada
de SQL, isto é, Structured Query Language (Linguagem de Consulta Estruturada). A grande
vantagem de se usar a SQL, é que inúmeros produtos dão suporte atualmente ao uso da SQL.
Nas décadas de 80 e 90, o American National Standards Institute (ANSI), a
Internacional Standards Organization (ISO) e a IBM, publicaram os padrões para a SQL.
3. Conceitos Básicos
Sistemas de banco de dados comerciais precisam de uma linguagem de consulta
mais fácil para atender as necessidades dos usuários. Neste tópico estudaremos a linguagem
comercial mais utilizada no mercado, para tal fim, a SQL. A SQL usa uma combinação de
construtores em álgebra e cálculo relacional.
A SQL possui também muitos outros recursos, além da consulta ao banco de
dados, tais como meios para definição da estrutura de dados, para modificação de dados e
para a especificação de restrições de segurança.
A linguagem SQL possui diversas partes, definidas a seguir:
- Linguagem de definição de dados – DDL: definição, exclusão e alteração de esquemas,
criação de índices.
- Linguagem interativa de manipulação de dados – DML: inserção, exclusão, modificação e
consulta de tuplas (linhas) no banco de dados.
- Incorporação DML: comandos DML’s embutidos em linguagens de programação de uso
geral, como PL/I, Visual Basic, Delphi, Pascal, C, etc.
I nstituto M unicipal de E nsino S uperior de A ssis
Prof. Alex Poletto - [email protected]
- Definição de Visões: comandos DDL’s para definição de visões.
- Autorização: comandos DCL’s para especificação de direitos de acesso a relações e
visões.
- Integridade: comandos DDL’s para a especificação de regras de integridade onde os dados
que serão armazenados no banco de dados devem satisfazer.
- Controle de transações: comandos para especificação de iniciação e finalização de
transações
4. Conceitos e Definições do SQL da ORACLE
Convenções Tipográficas dentro de códigos
Letras maiúsculas Comandos, funções SQL> SELECT codigo FROM curso; Letras minúsculas, itálico Variáveis de sintaxe SQL> CREATE ROLE papel ; Inicial maiúscula Gatilhos de forms Trigger name: When-Validate-Item
4.1. SQL e SQLPlus*
Para tal objetivo, utilizaremos o produto SQL*Plus, pois é nesse software que são
formatadas, elaboradas e executadas as SQL’s. Quando você informa uma instrução SQL, ela
é armazenada em uma parte da memória chamada buffer de SQL e permanece lá até que você
informe uma nova instrução. O SQL*Plus é uma ferramenta, um ambiente Oracle que
reconhece e submete instruções SQL ao Oracle Server para execução e contém sua própria
linguagem de comando.
Recursos do SQL
- Podem ser utilizados por uma grande faixa de usuários, incluindo aqueles com pouca ou
nenhuma experiência em programação.
- É uma linguagem não procedural.
- Reduz o período de tempo necessário para a criação e manutenção de sistemas.
- É uma linguagem similar ao inglês.
Recursos do SQL*Plus
- Aceita entrada ad hoc das instruções.
I nstituto M unicipal de E nsino S uperior de A ssis
Prof. Alex Poletto - [email protected]
ED[IT] nome arquivo [.ext] Chama o editor para editar o conteúdo de um arquivo. SPO[OL] nome arquivo [.ext] Imprime um arquivo na impressora do sistema. EXIT Sai do código SQL*Plus.
4.4. Estabelecendo Login no SQLPlus*
Para estabelecer Login, inicia-se o SQL*Plus através do iniciar, programas,
Oracle. O usuário deverá entrar com seu nome, senha e nome do banco de acesso.
5. Definições de Instruções SQL.
Segue-se abaixo a lista de instruções que servirão como estrutura do curso. O
curso terá seu caminho traçado de acordo com a coluna Parte da tabela a seguir:
Instrução Descrição SELECT recuperação de dados Recupera dados do banco de dados. INSERT UPDATE DML DELETE
Insere linhas. Altera linhas. Deleta linhas. CREATE ALTER DROP DDL RENAME TRUNCATE
Cria estruturas (tabelas). Altera estruturas (tabelas). Elimina estruturas (tabelas). Renomeia estruturas (tabelas). Trunca estruturas (tabelas). VIEW SEQUENCE
Criando Visões Criando Seqüência COMMIT SAVEPOINT Controle de transação ROLLBACK
Grava em disco. Desfaz transação, antes do COMMIT. Marcar pontos de gravação. GRANT Fornece privilégios.
I nstituto M unicipal de E nsino S uperior de A ssis
Prof. Alex Poletto - [email protected]
REVOKE DCL Remove privilégios. FORMATANDO RELATÓRIOS Formatação de relatórios.
6. Expressões, Operadores e Tipos de Dados
6.1 Expressões Aritméticas
Operador Descrição
+ Adicionar
* Multiplicar
/ Dividir
Precedência * / + -
6.2. Operadores de Comparação
Operador Descrição
= Igual a
> Maior do que
>= Maior do que ou igual a
< Menor do que
<= Menor do que ou igual a <> Diferente de
6.3. Operadores de Comparação mais Avançados
Operador Descrição BETWEEN ... AND... Entre dois valores (inclusive) IN(list) Lista de valores LIKE Um padrão de caractere IS NULL É um valor nulo.
6.4. Operadores Lógicos
Operador Significado AND TRUE se todas as condições forem atendidas. OR TRUE se pelo menos uma das condições forem atendidas. NOT TRUE se a condição seguinte for FALSE.
6.5. Tipo de dados
Tipo de dado Descrição NUMBER( p,s ) Valor numérico que possui um número máximo de dígitos p , o número dígitos à direita do ponto decimal s. VARCHAR( s ) Valor de caracteres com comprimento variável do tamanho
I nstituto M unicipal de E nsino S uperior de A ssis
Prof. Alex Poletto - [email protected]
1. Trabalhando com Recuperação de Dados - Estruturas Básicas
1.1. Instrução SELECT Básica – Recuperação de Dados
SELECT [DISTINCT] {*, coluna [ apelido ], ... } FROM tabela ;
- Na instrução SELECT você identifica quais campos (colunas) você necessita obter.
- FROM : identifica de qual tabela você quer obter as informações.
- DISTINCT : suprime os itens duplicados.
- : seleciona todos os campos (colunas) da tabela.
- coluna : seleciona a coluna escolhida.
- apelido : fornece cabeçalhos diferentes aos campos selecionados.
- tabela : nome da tabela desejada.
Outros definições necessárias
- Valor nulo : não está disponível, não é atribuído, é desconhecido ou não é
aplicável, não o mesmo que um zero ou um espaço em branco. Em expressões aritméticas o resultado será nulo.
- Apelidos de coluna : defini-se com a palavra-chave AS entre o nome da coluna e o
apelido. Se o apelido tiver letras maiúsculas e minúsculas, e espaços ou caracteres especiais, torna se necessário o uso de aspas duplas.
- Operador Concatenação : concatena colunas ou strings de caractere a outras colunas. É
representado por duas barras verticais (| |).
Exemplos de instruções
SQL> SELECT * FROM professor; Lista todos os campos (colunas) da tabela professor.
SQL> SELECT codigo, nome FROM professor; Lista apenas o codigo e o nome da tabela professor.
SQL> SELECT DISTINCT cargo FROM professor; Lista apenas o codigo do cargo da tabela professor, sem repetição (DISTINCT)
SQL> SELECT nome, salario+200 FROM professor; Lista o nome e o salário com mais 200 reais da tabela de professor.
SQL> SELECT nome, salario, salario*15/100 AS aumento FROM professor; Lista o nome, o salário e um aumento de 15%, tendo como cabeçalho aumento.
I nstituto M unicipal de E nsino S uperior de A ssis
Prof. Alex Poletto - [email protected]
SQL> SELECT nome AS “Nome”, salario AS “Aumento Salarial” FROM professor; Lista o nome e o salário tendo como cabeçalho Nome e Aumento Salarial.
SQL> SELECT nome | | ‘ faz aniversário em ‘ | | nascimento AS “Lista Aniversariantes” FROM professor; Lista o nome e a data de nascimento, com strings para frases, tendo como cabeçalho Lista
Aniversariantes.
1.2. Comando DESC
DESC[RIBE] tabela ;
- Utiliza-se o comando DESC[RIBE] para exibir a estrutura (nome do campo e tipo de
dados) de uma tabela.
- tabela : nome da tabela desejada.
1.3. Usando a Cláusula ORDER BY
SELECT [DISTINCT] {*, coluna [ apelido ], ... } FROM tabela ORDER BY coluna ;
- Utiliza-se a cláusula ORDER BY para classificar as linhas. Temos duas opções, a ASC e a
DESC.
- A opção ASC que defini ordem crescente e é a ordem default.
- A opção DESC que defini ordem decrescente.
- coluna : seleciona a coluna a ser ordenada.
Exemplos de instruções
SQL> SELECT codigo, nome, salario 2 FROM professor 3 ORDER BY nome DESC; SQL> SELECT codigo, nome, salario*13 anual 2 FROM professor 3 ORDER BY anual; SQL> SELECT codigo, nome 2 FROM professor 3 ORDER BY cargo, nome;
1.4. Usando a Cláusula GROUP BY
SELECT [DISTINCT] {*, coluna [ apelido ], ... }
I nstituto M unicipal de E nsino S uperior de A ssis
Prof. Alex Poletto - [email protected]
SQL> SELECT codigo, nome, salario 2 FROM professor 3 WHERE nome LIKE ‘F%’; SQL> SELECT codigo, nome, salario 2 FROM professor 3 WHERE nome LIKE ‘_A%’; SQL> SELECT codigo, nome, salario 2 FROM professor 3 WHERE nascimento IS NULL; SQL> SELECT codigo, nome, salario 2 FROM professor 3 WHERE nascimento IS NULL; SQL> SELECT codigo, nome, salario 2 FROM professor 3 WHERE salario>=1300 AND nome=’FABIO’; SQL> SELECT codigo, nome, salario 2 FROM professor 3 WHERE salario NOT IN (1000, 1500, 2000);
1.6. Usando a Cláusula HAVING
SELECT coluna, grupo_função FROM tabela [GROUP BY grupo_por_expressão ] [HAVING grupo_condição ];
- Utiliza-se a cláusula HAVING para especificar quais grupos serão exibidos. Portanto,
restrinja ainda mais os grupos com base nas informações agregadas.
- grupo_condição : restringe os grupos de linhas retornados aos grupos para os quais a
condição especificada seja true.
- Execução : 1º as linhas são agrupadas. 2º a função de grupo é aplicada ao grupo.
3º os grupos que correspondem aos critérios na cláusula HAVING são exibidos.
Exemplos somente da cláusula
HAVING MAX(salario)>1250;
Exemplos de instruções
SQL> SELECT depto, MAX(salario) 2 FROM professor 3 GROUP BY depto 4 HAVING MAX(salario)>1500; SQL> SELECT depto, SUM(salario) 2 FROM professor
I nstituto M unicipal de E nsino S uperior de A ssis
Prof. Alex Poletto - [email protected]
3 WHERE depto BETWEEN 3 AND 6 4 GROUP BY depto 5 HAVING SUM(salario)> 6 ORDER BY SUM(salario);
1.7. Obtendo Dados de Várias Tabelas
Algumas vezes é necessário utilizar dados a partir de uma ou mais tabelas. Para
isso é preciso vincular as tabelas para ter acesso aos dados das duas tabelas. Uma condição de
junção é criada a partir da cláusula WHERE. Usa se a notação tabela.coluna , já que existem
campos (colunas) iguais em tabelas diferentes.
SELECT tabela1.coluna, tabela2.coluna FROM tabela1, tabela WHERE tabela1.coluna1 = tabela2.coluna2;
Exemplos de junções
SQL> SELECT professor.codigo, professor.nome, cargo.descricao 2 FROM professor, cargo 3 WHERE professor.cargo = cargo.codigo; SQL> SELECT p.codigo, p.nome, c.descricao 2 FROM professor p, cargo c 3 WHERE p.cargo = c.codigo;
Utilizando Funções de Grupo
- Essas funções operam em conjuntos de linhas para fornecer um resultado por grupo.
- As funções de grupo trabalham com as cláusulas GROUP BY e HAVING.
- A cláusula GROUP BY é utilizada para agrupar dados.
- A cláusula HAVING é utilizada para incluir ou excluir linhas agrupadas.
1.7.1. Funções de Grupo
Função Descrição AVG([DISTINCT|ALL] n ) Valor médio de^ n , ignorando valores nulos. COUNT({|[DISTINCT|ALL]* expr }) Fornece o número de linhas. A expr para algo diferente de nulo, ou * para todas as linhas (duplicados e nulos também) MAX([DISTINCT|ALL] expr ) Valor máximo de expr , ignorando valores nulos_._ MIN([DISTINCT|ALL] expr ) Valor mínimo de expr , ignorando valores nulos_._ STDDEV([DISTINCT|ALL] n ) Desvio padrão de n , ignorando valores nulos. SUM([DISTINCT|ALL] n ) Valores somados de^ n , ignorando valores nulos VARIANCE([DISTINCT|ALL] n ) Variação de n , ignorando valores nulos.
I nstituto M unicipal de E nsino S uperior de A ssis
Prof. Alex Poletto - [email protected]
1.9. Utilizando Variáveis de Substituição
- Utiliza-se variáveis de substituição do SQL*Plus para armazenar valores
temporariamente.
- Usa se o & “E comercial” para solicitar um valor ao usuário. Valores caracter e data usa
se aspas simples.
- Usa se o && “E comercial duplo” se desejar reutilizar o valor da variável sem precisar
solicitar sempre ao usuário.
- Usa se o comando DEFINE para criar uma variável de usuário de tipo de dados CHAR.
- Comando UNDEFINE para limpa-la.
- Usa se o comando ACCEPT para ler a entrada do usuário e armazenar em uma variável.
Exemplos de instruções
SQL> SELECT depto, nome, salario, cargo 2 FROM professor 3 WHERE cargo = &numcar; Enter value for numcar: SQL> SELECT depto, nome, salario+salario/ 2 FROM professor 3 WHERE nascimento = ‘&nascprof’; Enter value for nascprof: SQL> ACCEPT numdepto PROMPT ‘Digite o número do Departamento:’ SQL> SELECT * 2 FROM professor 3 WHERE depto = UPPER(‘&numdepto’) 2 / Digite o número do Departamento: SQL> DEFINE numdepto = 4 SQL> SELECT * 2 FROM professores 3 WHERE depto = &numdepto;
2. Trabalhando com DML – Linguagem de Manipulação de Dados
2.1. Instrução INSERT
INSERT INTO tabela [( coluna [, coluna ...])] VALUES ( valor [, valor ...]);
- Com a instrução INSERT você adiciona novas linhas em uma tabela.
- tabela : é o nome da tabela.
I nstituto M unicipal de E nsino S uperior de A ssis
Prof. Alex Poletto - [email protected]
- coluna : é o nome da coluna a ser preenchida.
- valor : é o valor correspondente para a coluna.
- INSERT com VALUES adiciona somente uma linha por vez a uma tabela.
Exemplos de instruções
SQL> INSERT INTO professor (codigo, nome, nascimento, salario, cargo, depto) 2 VALUES (15, ‘FULANO DE TAL’,’10-JAN-69’,1500,1,2); 1 row created. SQL> INSERT INTO cargo (codigo, descricao) 2 VALUES (10, ‘LIVRE DOCENTE’); 1 row created. SQL> INSERT INTO depto (codigo, descricao) 2 VALUES (&numdepto, ‘&nomedepto’); Enter value for numdepto: Enter value for nomedepto: 1 row created.
2.2. Instrução UPDATE
UPDATE tabela SET coluna = valor [, coluna = valor , ...] [WHERE condição ];
- Com a instrução UPDATE você modifica linhas em uma tabela.
- tabela : é o nome da tabela.
- coluna : é o nome da coluna a ser preenchida.
- valor : é o valor correspondente para a coluna.
- condição : identifica as linhas a serem atualizadas e é composta de nomes de
colunas, expressões, constantes, subconsultas e operadores de comparação.
Exemplos de instruções
SQL> UPDATE professor 2 SET depto = 3 3 WHERE codigo=2; 1 row update. SQL> UPDATE professor 2 SET salario = salario*1. n row update.
I nstituto M unicipal de E nsino S uperior de A ssis
Prof. Alex Poletto - [email protected]
Exemplos de instruções
SQL> CREATE TABLE depto 2 (codigo NUMBER(2), 3 (descricao VARCHAR2(30)); Table created. SQL> DESCRIBE depto SQL> CREATE TABLE cargo 2 AS 3 (SELECT codigo, nome, salario, nascimento 4 FROM professor 5 WHERE cargo=2); Table created. SQL> DESCRIBE depto SQL> CREATE TABLE teste 2 AS 3 ( SELECT * FROM professor 4 WHERE 1=2); Table created. Obs: colocando uma condição inútil será copiado somente a estrutura. SQL> DESCRIBE teste
3.2. Instrução ALTER TABLE
ALTER TABLE tabela ADD ( tipo de dados da coluna [DEFAULT expr ] [, tipo de dados da coluna ]...); ALTER TABLE tabela MODIFY ( tipo de dados da coluna [DEFAULT expr ] [, tipo de dados da coluna ]...);
- Com a instrução ALTER TABLE você adiciona, modifica e eliminar colunas em uma
tabela.
- Com a cláusula ADD você adiciona colunas.
- Com a cláusula MODIFY você modifica colunas.
- Com a cláusula DROP COLUMN você elimina colunas.
- Para alterar a estrutura de uma tabela o usuário deve ter o privilégio ALTER TABLE.
- tabela : é o nome da tabela.
- coluna : é o nome da nova coluna.
- tipo de dados : é o tipo de dados e o comprimento da nova coluna.
- DEFAULT expr : especifica um valor default para a nova coluna.
Exemplos de instruções
I nstituto M unicipal de E nsino S uperior de A ssis
Prof. Alex Poletto - [email protected]
SQL> ALTER TABLE depto 2 ADD (localidade VARCHAR2(25)); Table altered. SQL> DESCRIBE depto SQL> ALTER TABLE depto 2 MODIFY (localidade VARCHAR2(35)); Table altered. SQL> DESCRIBE depto SQL> ALTER TABLE depto 2 DROP COLUMN localidade; Table altered. SQL> DESCRIBE depto
3.3. Instrução DROP TABLE
DROP TABLE tabela ;
- Com a instrução DROP TABLE você elimina uma tabela por completo, tanto os dados
quanto sua estrutura.
- Todas as transações pendentes sofrerão COMMIT (PARTE IV).
- Todos os índices serão eliminados.
- Não existe a possibilidade de ROLLBACK, de recuperação da tabela (PARTE IV).
- Para eliminar uma tabela o usuário deve ter o privilégio DROP TABLE.
- tabela : é o nome da tabela.
Exemplos de instruções
SQL> DROP TABLE depto10; Table dropped.
3.4. Instrução RENAME
RENAME old_name TO new_name;
- Com a instrução RENAME você renomeia uma tabela.
- Não existe a possibilidade de ROLLBACK, de recuperação da tabela.
- Para eliminar uma tabela você deve ser o proprietário da tabela.
- old_name : é o nome antigo da tabela.
- new_name : é o novo nome da tabela.
I nstituto M unicipal de E nsino S uperior de A ssis
Prof. Alex Poletto - [email protected]
- As restrições devem ser satisfeitas sempre que ocorrer a inserção, atualização ou deleção
de linhas em uma tabela. Restrições
Restrição Descrição NOT NULL Especifica que esta coluna não pode conter um valor nulo. UNIQUE Especifica uma coluna ou combinação de colunas cujos valores devem ser excluídos para todas as linhas na tabela. PRIMARY KEY Identifica exclusivamente cada linha da tabela. FOREIGN KEY Estabelece e impõem um relacionamento de chave estrangeira entre a coluna e a coluna da tabela referenciada. CHECK Especifica uma condição que deve ser verdadeira.
Exemplo de instruções
SQL> CREATE TABLE depto 2 (codigo NUMBER(2), 3 descricao VARCHAR2(30), 3 CONSTRAINT depto_descricao_pk UNIQUE (descricao) 4 CONSTRAINT depto_codigo_ck CHECK(codigo BETWEEN 10 AND 99)); SQL> CREATE TABLE professor 2 (codigo NUMBER(4), 3 nome VARCHAR2(30), 4 ... 5 cargo NUMBER(2) NOT NULL, 6 CONSTRAINT professor_codigo_pk PRIMARY KEY (codigo), 7 CONSTRAINT professor_cargo_fk FOREIGN KEY (cargo) 8 REFERENCES depto(codigo));
Remove a restrição do cargo (chave estrangeira) da tabela PROFESSOR.
SQL> ALTER TABLE professor 2 DROP CONSTRAINT professor_cargo_fk;
Remove a restrição de chave primária da tabela DEPTO e elimina a restrição de chave
estrangeira associada na coluna PROFESSOR.DEPTO.
SQL> ALTER TABLE depto 2 DROP PRIMARY KEY CASCADE;
Desativar restrições de integridade dependentes, usando a cláusula DISABLE e CASCADE
SQL> ALTER TABLE professor 2 DISABLE CONSTRAINT professor_depto_fk CASCADE;
Ative restrições de integridade dependentes, usando a cláusula ENABLE
I nstituto M unicipal de E nsino S uperior de A ssis
Prof. Alex Poletto - [email protected]
SQL> ALTER TABLE professor 2 ENABLE CONSTRAINT professor_depto_fk;
Consulte a tabela USER_CONSTRAINTS para ver todos os nomes e definições de restrições
SQL> SELECT constraint_name, constraint_type, search_condition 2 FROM user_constraints 3 WHERE table_name = ‘PROFESSOR’; Instrução para visualizar todos os nomes e definições de restrições. Visualize as colunas associadas aos nomes de restrições na view USER_CONS_COLUMNS
SQL> SELECT constraint_name, column_name 2 FROM user_cons_columns 4 WHERE table_name = ‘PROFESSOR’; Instrução para visualizar as colunas associadas aos nomes de restrições.
3.7. Criando Índice
- A instrução CREATE INDEX cria índices em uma ou mais colunas.
- Os índices são utilizados para acelerar a recuperação de linhas, para reduzir a E/S do disco
usando um método rápido de acesso a caminhos para localizar os dados rapidamente.
CREATE INDEX indice ON tabela (coluna[, coluna]...);
Quando criar um índice
- Quando uma coluna for usada freqüentemente na cláusula WHERE ou em uma condição
de junção.
- Quando a coluna contiver uma ampla faixa de valores.
- Quando a coluna contiver um grande número de valores nulos.
- Quando duas ou mais colunas forem usadas juntas com freqüência em uma cláusula
WHERE ou em uma condição de junção.
- Quando a tabela for grande e se espera que a maioria das consultas recupere menos que 2
a 4% das linhas.
Quando não criar um índice
- Quando a tabela for pequena.
- Quando as colunas não forem utilizadas com freqüência como uma condição na consulta.
- Quando se espera que a maioria das consultas recupere mais que 2 a 4% das linhas.
- Quando a tabela for atualizada com freqüência.
