BANCO DE DADOS II
Professor: Marcus Sampaio
Monitor: Eduardo Santiago Moura
Keyword, clause e statement
2



Keyword: faz referência a um elemento SQL
individual. Ex: SELECT, FROM.
Clause: é uma parte de um statement SQL. Ex:
SELECT employee_id, last_name, ...
Statement: é uma combinação de duas ou mais
clauses. Ex: SELECT * FROM employees
SQL Statements
3
SELECT
Recupera dados da base
INSERT
UPDATE
DELETE
MERGE
Cria novas linhas, modifica linhas existentes e remove linhas de
tabelas da base de dados, respectivamente. Conhecidas como
data manipulation language (DML)
CREATE
ALTER
DROP
RENAME
TRUNCATE
Cria, modifica e remove estruturas das tabelas. Conhecidas como
data definition language (DDL)
COMMIT
ROLLBACK
SAVEPOINT
Manipula as modificações feitas pelas DML statements
GRANT
REVOKE
Concede ou remove direito de acesso a base de dados.
Conhecidas como data control language (DCL)
Select Clause
4
SELECT
É uma lista de uma ou mais
colunas
*
Seleciona todas as colunas
DISTINCT
Elimina duplicatas
column | expression
Seleciona o nome da coluna ou
expressão
alias
Renomeia as colunas selecionadas
FROM table
Tabela contendo as colunas
selecionadas
Expressões Aritméticas
5

* / + - : podem ser usados em qualquer cláusula
de um statement exceto na cláusula FROM, com a
respectiva precedência.
Usando ALIAS
6

Renomeia uma coluna, muito útil para cálculos.
Where Clause
7

A cláusula WHERE pode comparar valores em
colunas, valores literais, expressões aritméticas ou
funções.
Comparison Conditions
8
=
Igual
>
Maior
>=
Maior igual
<
Menor
<=
Menor igual
<>
Diferente
BETWEEN ... AND ...
Entre dois valores (inclusive)
IN (lista)
Comparação com qualquer valor da lista
LIKE
Comparação com um padrão de caracter
IS NULL
Verifica se o valor é null
Comparison Conditions
9
Comparison Conditions
10


% : denota zero ou
mais caracteres
_ : denota um
caracter
Expressões Lógicas
11

AND, OR e NOT
Order By Clause
12

Ordena as linhas de maneira:
 ASC:
ascedentes (ordem default)
 DESC: ordem descendente
 Podem ser ordenados colunas que não estão no SELECT
Single-row functions
13
Single-row functions
14


Number functions:
DUAL: é uma tabela dummy que pode ser usada para ver
resultados de funções ou cálculos.
Single-row functions
15

Date functions:
MONTHS_BETWEEN
Número de meses entre duas
datas
ADD_MONTHS
Adiciona meses a data
NEXT_DAY
Próximo dia da data
especificada
LAST_DAY
Último dia da data
especificada
ROUND
Arredonda a data
TRUNC
Trunca a data
Single-row functions
16


Date functions:
SYSDATE: é uma função que retorna a data e hora
corrente
Single-row functions
17

Conversion functions: a conversão de tipos de dados
pode ser feita de duas formas:
 Conversão
Implícita (feita automaticamente pelo Oracle)
 Conversão Explícita

Implícita
De
Para
VARCHAR2 ou CHAR
NUMBER
VARCHAR2 ou CHAR
DATE
NUMBER
VARCHAR2
DATE
VARCHAR2
Single-row functions
18

Explícita
 TO_CHAR
com datas
Single-row functions
19


Date Format Model
YYYY
Ano completo em números
YEAR
Nome do ano falado
MM
Valor do mês com 2 dígitos
MONTH
Nome do mês completo
MON
Abreviação do mês com 3 letras
DY
Abreviação do dia da semana com 3 letras
DAY
Nome do dia da semana completo
DD
Dia do mês numérico
Existem inúmeros outros formatos, favor consultar
material
Single-row functions
20

Explícita
 TO_CHAR
com números
Single-row functions
21

Number Format Model
Elemento
Descrição
Exemplo
Resultado
9
Qtd de 9s indica o tamanho do número exibido
999999
1234
0
Exibe 0s iniciais
099999
001234
$
Exibe o $ inicial
$999999
$1234
L
Exibe o símbolo monetário local
L999999
FF1234
.
Coloca ponto na posição desejada
999999.99
1234.00
,
Coloca vírgula na posição desejada
999,999
1,234
MI
Menos na direita
9999999MI
1234-
PR
Coloca parentizador
999999PR
<1234>
EEEE
Notação científica
99.999EEEE
1.234E+03
V
Multiplica por 10 n vezes (n = num 9s depois do V)
9999V99
123400
B
Exibe valores zero em branco, não 0
B9999.99
1234.00
Single-row functions
22

Explícita
 TO_NUMBER
 TO_DATE
Single-row functions
23

General functions:
NVL (expr1, expr2)
Converte um valor null para um valor
atual
NVL2 (expr1, expr2, expr3)
If expr1 é null, NVL2 retorna
expressão expr2. If expr1 é null,
retorna expr3.
NULLIF (expr1, expr2)
Compara as duas expressões e
retorna null if forem iguais ou expr1 se
diferentes
COALESCE (expr1, ..., exprn)
Retorna a primeira expressão not-null
da lista
Single-row functions
24

General functions:
Single-row functions
25

Conditional functions:
Single-row functions
26

Conditional functions:
Cartesian Products
27

É formado quando:
A
condição de join é omitida
 A condição de join é inválida
 Todas as linhas das tabelas “casam”
Join
28




Simple Join:
Usualmente, na condição de join são usadas as
colunas de chave primária e chave estrangeira
A condição de join é especificada na cláusula
Where
Regra geral: para fazer join de n tabelas são
necessárias no mínimo n-1 condições de join
Join
29

Regra geral:
Equijoin
30

Equijoin: quando a primary key de uma tabela é
exatamente igual a foreign key da outra. Também
chamado de simple join e inner join
Equijoin
31

Equijoin:
Table Aliases
32

Vantagens:
 Simplifica
as consultas
 Aumenta performace (muitas vezes os nomes da tabelas são
grandes e usando alias, o SQL fica menor usando menos
memória)
Nonequijoin
33

Nonequijoin:
 Quando
não há colunas comuns nas tabelas envolvidas em um
relacionamento
 Consiste em um relacionamente obtido com um operador que
não é o de igualdade (=), como, por exemplo, o BETWEEN
Nonequijoin
34

Nonequijoin:
Outerjoin
35

Outerjoin:
 Quando
deseja-se retornar linhas que não válidas na
condição do join
 Declarado pelo operador (+), colocado no lado do join
deficiente de informação

Para retornar inclusive linhas faltantes da tabela1

Para retornar inclusive linhas faltantes da tabela2
Outerjoin
36

Outerjoin:
 Restrições:


O operador só pode aparecer em 1 lado da condição do join
Na condição envolvendo o outerjoin não podem ser usado os
operadores IN ou OR.
Outerjoin
37
Selfjoin
38

Selfjoin:

O nome do gerente de cada empregado?
Selfjoin
39

Selfjoin:

O nome do gerente de cada empregado?
Group Functions
40
AVG ( [DISTINCT | ALL] n )
Média do valor de n, ignorando valores nulos
COUNT ( {* | [DISTINCT | ALL] expr} )
Número de linhas, onde expr avalia alguma
coisa diferente de null ( contando todas as
linhas usando *, incluindo duplicatas e linhas
com valores nulos)
MAX ( [DISTINCT | ALL] expr )
Valor máximo da expr, ignorando valores nulos
MIN ( [DISTINCT | ALL] expr )
Valor mínimo da expr, ignorando valores nulos
STDDEV ( [DISTINCT | ALL] x )
Desvio padrão de x, ignorando valores nulos
SUM ( [DISTINCT | ALL] n )
Soma dos valores de n, ignorando valores nulos
VARIANCE ( [DISTINCT | ALL] n )
Variância de n, ignoranodo valores nulos

_ = default
Group Functions
41

Avg e Sum para tipos numéricos

Max e Min para qualquer tipo de dados
Group Functions
42


Count (*) retorna o nº de linhas da tabela
Count (expr) retorna o nº de linhas com valores
não-nulos da expr
Group Functions
43

Group Functions ignoram valores nulos nas colunas,
para forçar sua inclusão pode-se utilizar NVL
Group by Clause
44



Objetivo: Dividir linhas da tabela em grupos menores
Sintaxe:
Todas as colunas do SELECT que não estiverem em
Group Functions, deve ser declaradas no Group by
Group by Clause
45

Order by: por default as linhas tem ordenação
ascendente, mas podem ser modificados através da
cláusula Order by
Group by Clause
46

Illegal Queries: ao utilizar uma mistura de campos
simples (colunas) e funções de agregação é
obrigatório incluir os campos simples na cláusula do
group by
Group by Clause
47

Gropu by de múltiplas colunas
Having by Clause
48



Objetivo: Restringir grupos
Sintaxe:
Apenas os grupos que satisfazerem a condição são
retornados
Subquery
49



A subquery (inner query) é executada primeiro que a
query principal
O resultado da subquery pode ser usado na query
principal (outer query)
Sintaxe:
Subquery
50

Problema:
 Consulta
Principal: Quais empregados tem o salário maior
que o de Abel?

Consulta Interna: Qual é o salário de Abel?
Types of Subqueries
51


Single-row: consulta interna que retorna uma única
linha do SELECT interno
Multiple-row: consulta interna que retorna mais de
uma linha do SELECT interno
Single-row Subqueries
52

Operadores de comparação:
=
Igual
>
Maior
>=
Maior igual
<
Menor
<=
Menor igual
<>
Diferente
Single-row Subqueries
53
Having Clause with Subqueries
54


O servidor Oracle executa as subqueries primeiro
O servidor Oracle retorna o resultado para a
cláusula HAVING da consulta principal
Multiple-row Subqueries
55

Operadores de comparação:
IN
Igual a qualquer menbro da lista
ANY
Compara o valor com cada valor
retornado da subquery
ALL
Compara o valor todos os valores
retornados da subquery
Multiple-row Subqueries
56
Data Manipulation Language (DML)
57

Um statement DML é executado quando:
 Adiciona-se
uma linhas em uma tabela
 Modifica-se linhas existentes de uma tabela
 Remove-se linhas existentes de uma tabela

Uma transação consiste de um coleção de statements
DML que formam uma “unidade lógica de trabalho”
Insert Statement
58


Sintaxe:
Com essa sintaxe um único valor pode ser inserido
por vez
Insert Statement
59



Método implícito: omissão de colunas
Método explícito: ao omitir as colunas, deve-se inserir
NULL para colunas indesejadas
Copiando valores:
Update Statement
60


Sintaxe:
Com essa sintaxe uma ou mais linhas podem ser
inseridas por vez
Update Statement
61
Delete Statement
62

Sintaxe:

Exemplos:
Create Table Statement
63

Sintaxe:

Exemplo:
Data Types
64
Datetime Data Types
65


TIMESTAMP: é uma extensão do tipo Date
Armazena o ano, mês, dia, mais a hora, minuto e
segundo
Alter Table Statement
66

Usado:
 Adicionar
uma nova coluna
 Modificar uma coluna existente
 Definir o valor default de uma coluna
 Drop de uma coluna
Alter Table Statement
67
Drop Statement
68

Atenção:
 Todos
os dados e estruturas da tabela são deletados
 Qualquer transação pendente é “commitdada”
 Todos os índices são deletados

Comentários (tabela ou colunas):
Constraints
69
NOT NULL
Especifica que a coluna não pode conter valores nulos
UNIQUE
Especifica que a coluna ou um conjunto de colunas
contenham valores únicos para todas as linhas da tabela
PRIMARY KEY
Identifica únicamente cada linha da tabela
FOREIGN KEY
Estabelece uma chave de relacionamento entre colunas da
referente tabela e da tabela referenciada
CHECK
Especifica que a condição deve ser verdadeira

Definindo constraints:
Not Null
70

Garante que valores nulos não são permitidos
para a especificada coluna
Unique
71

Garante que cada valor em uma coluna ou
conjunto de colunas seja único
Primary Key e Check
72
Foreign Key
73
Foreign Key
74