Banco de Dados I - FTP da PUC

Banco de Dados I
Linguagem de Consulta (parte I)
DDL
DML
Linguagem de Consulta:SQL
contéudo
SQL: Structured Query Language
Originalmente SEQUEL Structured English Query Language
Projeta e Implementada pela IBM para System R.
Não procedural e baseada em álgebra relacional
Linguagem padronizada de consulta (ANSI e ISO)
• SQ-86 (SQL1), SQ-92 (SQL2) e SQ-3 (O.O.)
DDL: criação e modificação de esquemas e restrições
DML: inserção, remoção, atualização e consulta de
dados
Parada técnica
Ciclo de vida do Proj. de Banco de Dados
Análise
Projeto Conceitual
Projeto
Projeto Lógico Implementação
Projeto Físico
Parada técnica
Ciclo de vida do Proj. de Banco de Dados
Análise
Projeto Conceitual
(de onde viemos)
Projeto
Projeto Lógico
(estamos aqui)
Implementação
Projeto Físico
(para onde vamos)
Mundo
Parada técnica
Real
Projeto Conceitual
Modelo
de
Dados
Diag. Entidade Relacionamento
(de onde viemos)
Projeto Lógico
Modelo
Lógico
Diagrama Relacional
(estamos aqui)
Projeto Físico
SQL
(para onde vamos)
Exemplo - pedido
Diagrama Entidade-Relacionamento
N
Pedido
Pedido
N
N
faz
tira
1
1
Cliente
Cliente
Vendedor
Vendedor
possui
N
Produto
Produto
Exemplo Pedido
N
Diagrama ER
Pedido
Pedido
N
possui
N
Produto
Produto
N
faz
tira
1
Cliente
Cliente
1
Vendedor
Vendedor
Diagrama de Tabelas
Pedido
Pedido
Cliente
Cliente
Vendedor
Vendedor
Item_de_Pedido
Item_de_Pedido
(possui )
(possui )
Produto
Produto
Exemplo Pedido
PEDIDO
PEDIDO
Núm_pedido ⌫
Núm_pedido ⌫
Prazo_entrega
Prazo_entrega
Código_cliente Código_cliente Código_vendedor
Código_vendedor
ITEM_DO_PEDIDO
ITEM_DO_PEDIDO
Núm_pedido ⌫
Núm_pedido ⌫
Código_produto⌫
Código_produto⌫
Quantidade
Quantidade
CLIENTE
CLIENTE
Código_cliente ⌫
Código_cliente ⌫
Nome_cliente
Nome_cliente
Endereço
Endereço
Cidade
Cidade
CEP
CEP
UF
UF
CGC
CGC
IE
IE
VENDEDOR
VENDEDOR
Código_vendedor⌫
Código_vendedor⌫
Nome_vendedor
Nome_vendedor
Faixa_comissão
Faixa_comissão
Salário_fixo
Salário_fixo
PRODUTO
PRODUTO
Código_produto⌫
Código_produto⌫
Descrição
Descrição
Unidade
Unidade
Val_unit
Val_unit
SQL:DDL
exemplo - pedido
Linguagem de Definição de
Dados: ... create, alter, drop
Criação e Destruição de Tabelas
0 comando CREATE TABLE cria a tabela solicitada e obedece a
seguinte forma:
CREATE TABLE <tabela>
(<descrição das colunas>);
(<descrição das chaves>);
<tabela> - 0 nome da nova tabela a ser criada;
<descrição das colunas> - É uma lista de colunas
(campos) e seus respectivos tipos de dados. (smallint,
char, money, varchar, integer, decimal, float, real, date,
time, timestamp, logical).
<descrição das chaves> - É a lista de colunas que
são tratadas como chaves: primária ou estrangeira.
Criação e Destruição de Tabelas
Alguns tipos em Oracle:
char (n) : cadeia de caracter de tamanho fixo (pode-se usar character - máx n 255).
varchar ou varchar2(n): caracteres de tamanho variável. (pode-se usar character
varing - máx n 2000).
smallint: é um inteiro pequeno.
number (p,d): número com máx. de 38 caracteres, onde p é o total incluindo a casa
decimal e d é o número de casas decimais. Ex: numeric(3,1) 44,5 444,5 e 0,31
float (n): número em ponto flutuante com precisão definida pelo usuário em pelo menos
n dígitos.
date: tipo calendário contendos século, ano (4 dígitos), mês, dia,horas, minutos e
segundos. 12:00 quando somente data for informado, formato padrão: DD-MON-YY )
long: alfanumérico de tamanho variável. ( 2Giga), um por tabela. Não pode ser usado
em consulta
raw ou long raw: campo binário de tamanho variável. ( 2000 e 2Giga)
Criando tabelas
Create table CLIENTE
( código_cliente smallint not null,
Nome_cliente char(20),
Endereço char (30),
Cidade char(15),
Cep char(8),
uf char(2),
Cgc char(20) unique,
ie char(20),
Primary key(código_cliente) );
Create table PEDIDO
( num_pedido smallint not null primary key,
Prazo_entrega smallint not null,
Código_cliente smallint not null,
Código_vendedor smallint not null,
Foreign key (código_cliente)
References CLIENTE ( codigo_cliente),
Foreign key (código_vendedor)
References VENDEDOR );
Ex:tabelas ITEM_DO_PEDIDO
CREATE TABLE ITEM_DO_PEDIDO
(
num_pedido smallint not null,
codigo_produto smallint not null,
quantidade number(3),
FOREIGN KEY (num_pedido)
REFERENCES PEDIDO,
FOREIGN KEY (codigo_produto)
REFERENCES PRODUTO,
PRIMARY KEY ( num_pedido, codigo_produto ) );
Ex:tabelas Vendedor e Produto
CREATE TABLE VENDEDOR
( código_vendedor smallint not null,
nome_vendedor char(20),
salário_fixo number(6,2),
faixa_comissão char(1),
PRIMARY KEY (código_vendedor) );
CREATE TABLE PRODUTO
( código_produtosmallint not null ,
unidade char(3),
descrição_produtoChar (30),
val_unit number(10,2),
CONSTRAINT CLIC_PK PRIMARY KEY (código_produto));
Destruição de Tabelas
Forma: DROP TABLE <tabela>;
DROP TABLE PEDIDO;
SQL:DML
exemplo - pedido
Linguagem de Manipulação de
Dados (DML): ...
consulta: select
inserção: insert
remoção: delete
atualização: update
Representação de Dados-SQL
Tabela (relação), coluna(atributo) e linhas (tuplas)
Aluno
tabela
RA
Nome
Idade
952001
Ana
19
992655
Marcão
20
302566
Paulinha
22
...
...
...
Coluna 1
Coluna 2
Coluna 3
Linha 1
Consultando dados: estrutura
Select
From
Where
colunas
tabela
restrições
(projeção π)
(linhas seleção δ)
Aluno
RA
Nome
Idade
952001
Ana
19
992655
Marcão
20
302566
Paulinha
22
...
...
...
tabela
Coluna 1
Linha 1
Extraindo Dados de uma Tabela: Comando
SELECT (projeção - π )
A) Selecionando Colunas Especificas da Tabela:
• Forma:
Select <nome(s) da(s) coluna(s)>
From <tabela>;
• Problema :
-Listar todos os produtos com respectivas descrições, unidades e
valores unitários.
SQL:
•
Select descrição, unidade, valor_unitário
From produto;
Resultado:
Extraindo Dados de uma Tabela: Comando
SELECT (projeção - π )
Exerc 2:
-Listar todos
clientes, com seus nomes, endereços e CGC.
SQL:
SELECT nome_cliente, endereço, CGC
FROM Cliente;
•Resultado:
Nome do
Cliente
Ana
Flávio
Jorge
Lúcia
...
Endereço
CGC
Rua 17 n 19
Av. Pres. Vargas 10
Rua Caiapo 13
Rua Itabira 123 Loja 9
...
12113231/0001-34
225334786/9387-9
14512764/98349-9
28315213/9348-8
...
José
Quadra 3 bl. 3 sl 1003
2176357/1232-3
Extraindo Dados de uma Tabela: Comando
SELECT (projeção - π )
B) Selecionando todas as Colunas da Tabela
• Forma:
Select *
From <tabela> ;
• Problema:
- Listar todo o conteúdo de Vendedor
SQL:
Select
From
Código do
Vendedor
209
111
11
240
720
213
101
310
250
*
vendedor ;
Nome do
Vendedor
José
Carlos
João
Antônio
Felipe
Jonas
João
Josias
Maurício
Salário
Fixo
1.800,00
2.490,00
2.780,00
9.500,00
4.600,00
2.300,00
2.650,00
870,00
2.930,00
Faixa de
Comissão
C
A
C
C
A
A
C
B
B
Selecionando somente alguns Registros da
Tabela: ( seleção - δ)
• Forma:
Select <nomes da(s) coluna(s)>
From <tabela>
Where <restrições>;
Onde:
Where
<nome da coluna> <operador> <valor>
C.1) Operadores Relacionais:
Observações:
= , <> ou != , < , >
>= , <=
quando o atributo é do tipo caracter, o <valor> deve estar entre aspas (‘).
Ex: ‘Parafuso’. Em SQL existe diferença entre ‘Parafuso’ e ‘parafuso’.
Selecionando somente alguns Registros da
Tabela: ( seleção - δ)
• Problema:
- Listar o num_pedido, o código_produto e a quantidade dos itens do pedido
com a quantidade igual a 35.
SQL:
Select
From
Where
num_pedido, código_produto, quantidade
item_do_pedido
quantidade = 35;
Resultado:
Número do
Pedido
Código do
Produto
Quantidade
121
31
35
138
77
35
Selecionando somente alguns Registros da
Tabela: ( seleção - δ)
Problema:
- Quais os clientes que moram em Niterói?
SQL:
Select nome_cliente
From cliente
Where cidade = 'Niterói' ;
Resultado:
NOME CLIENTE
Ana
Susana
Selecionando somente alguns Registros da
Tabela:
C.2) Operadores Lógicos:
AND , OR ;
NOT.
Uso de ( ) para alterar a precedência : AND > OR
Problema :
Listar
os produtos que tenham unidade igual a ‘M’ e valor unitário igual a R$ 1,05
SQL:
Select descrição_produto
From produto
Where unidade = ‘M’ AND val_unit = 1.05* ;
•Resultado:
DESCRICAO
Papel
Em inglês val_unit = 1.05 , em português val_unit = 1,05
Selecionando somente alguns Registros da
Tabela:
Exerc 3:
Liste os clientes e seus respectivos endereços que moram em ‘São
Paulo‘OU estejam na faixa de CEP entre '30077000' e '30079000'.
SQL:
SELECT nome_cliente, endereco
FROM Cliente
WHERE (cep >= ‘30077000’ AND cep <= ‘30079000’) OR
cidade = ‘São Paulo’ ;
•Resultado:
NOME CLIENTE
Flávio
Jorge
Maurício
Rodolfo
Beth
Lívio
Renato
ENDERECO CLIENTE
Av.Pres.Vargas10
Rua Caiapo13
Av.Paulista1236 sl 2345
Largo da Lapa 27 sobrado
Av. Climério n.45
Av. Beira Mar n.1256
Rua Meireles n.1231 bl.2 sl.345
Selecionando somente alguns Registros da
Tabela:
Problema:
- Mostrar todos os pedidos que não tenham prazo de entrega igual a 15 dias
SQL:
• Resultado:
Select num_pedido
From pedido
Where NOT ( prazo_entrega = 15 );
NÚMERO DO PEDIDO
121
97
137
148
104
203
......
98
91
138
119
127
Selecionando Registros da Tabela:
C.3) Operadores Between e NOT Between
Where <nome da coluna>
BETWEEN <valor1> AND <valor2>
Where <nome da coluna> NOT BETWEEN <valor1> AND <valor2>
Pesquisa por uma determinada coluna que esteja dentro
da faixa de valores.
Não há necessidade dos operadores >=, <= e AND.
• Os VALOR1 e VALOR2 têm que ser do mesmo tipo de
dado da coluna.
Selecionando Registros da Tabela:
C.3) Operadores Between e NOT Between
Problema:
-Listar o código e a descrição dos produtos que tenham o valor unitário na faixa
de R$ 0,32 até R$ 2,00.
SQL:
Select
From
Where
código_ produto, descrição_produto
produto
val_unit BETWEEN 0.32 AND 2.00;
Resultado:
CÓDIGO DO
PRODUTO
25
31
78
53
87
77
DESCRIÇÃO
Queijo
Chocolate
Vinho
Linha
Cano
Papel
Selecionando Registros da Tabela:
C.4) Operadores LIKE e NOT LIKE
-Where <nome da coluna> LIKE <valor>;
-Where <nome da coluna> NOT LIKE <valor>;
LIKE e NOT LIKE só trabalham sobre colunas que sejam do tipo CHAR.
Eles têm praticamente o mesmo funcionamento que os operadores =, < > ,
Podem utilizar os símbolos ( % ) e ( _ ) que podem fazer o papel de "Curinga":
% - substitui uma palavra
_ - substitui um caractere
Ex ‘LAPIS % 'pode enxergar os seguintes registros:
• ‘LAPIS PRETO’,
• ‘LAPIS CERA',
• 'LAPIS BORRACHA’
‘BROCA N_’ pode enxergar os seguintes registros;
• 'BROCA N1,
• 'BROCA N9,
• ‘BROCA N3,
Selecionando Registros da Tabela:
C.4) Operadores LIKE e NOT LIKE
Exerc 4:
- Listar todos os produtos que tenham a sua unidade começando por ‘Q’.
SQL:
SELECT código_produto, descrição_produto
FROM Produto
WHERE unidade LIKE ‘Q_’ ;
Resultado:
CODIGO DO PRODUTO
25
DESCRIÇÃO
Queijo
Selecionando Registros da Tabela:
C.4) Operadores LIKE e NOT LIKE
Exerc 5:
- Listar os vendedores que não começam pôr ‘Jo’
SQL:
Select
From
Where
código_vendedor, nome_vendedor
vendedor
nome_vendedor NOT LIKE ‘Jo%’ ;
Resultado:
CÓDIGO VENDEDOR
NOME VENDEDOR
111
Carlos
240
Antônio
720
Felipe
250
Maurício
Selecionando Registros da Tabela:
C.5) Operadores IN e NOT IN
-Where
<nome da coluna> IN <valores>;
-Where <nome da coluna> NOT IN <valores> ;
Pesquisam registros que estão ou não contidos no conjunto de
<valores> fornecido.
Estes minimizam o uso dos operadores: =, <>, AND e OR.
Selecionando Registros da Tabela:
C.5) Operadores IN e NOT IN
• Problema:
- Listar os vendedores que são da faixa de comissão A e B.
SQL:
Select nome_vendedor
From vendedor
Where faixa comissão IN (‘A’, 'B');
• Resultado:
NOME
VENDEDOR
Carlos
Felipe
Jonas
Josias
Maurício
Selecionando Registros da Tabela:
C.6) Operadores IS NULL e IS NOT NULL
- Where<nome da coluna> IS NULL;
- Where<nome da coluna> IS NOT NULL;
• Problema:
- Mostrar os clientes que não tenham inscrição estadual.
SQL:
Select
*
From
cliente
Where IE IS NULL ;
Selecionando Registros da Tabela:
D) Ordenando os Dados Selecionados
Select <nome da(s) coluna(s)>
From <tabela>
Where <condição(es)>
Order by <nome da(s) coluna(s)> ASC / DESC
ou
Order by <número da coluna >
ASC (default) / DESC
• Problema:
- Mostrar em ordem alfabética a lista de vendedores e seus respectivos
salários fixos.
SQL:
Select
nome_vendedor, salário_fixo
From
vendedor
Order by nome_vendedor;
Selecionando Registros da Tabela:
D) Ordenando os Dados Selecionados
• Problema:
- Listar os nomes, cidades e estados de todos os clientes, ordenados por estado e
cidade de forma descendente.
SQL:
Select
nome_cliente, cidade, UF
From
cliente
Order by UF DESC, cidade DESC;
• Problema:
- Mostrar a descrição e o valor unitário de todos os produtos que tenham a
unidade 'M', em ordem de valor unitário ascendente.
SQL:
Select
From
Where
Order by
descrição, val_unit
produto
unidade = ‘M’
2 ASC;
Selecionando Registros da Tabela:
E) Realizando CÁLCULOS com Informações Selecionada
• Problema:
- Mostrar o novo salário fixo dos vendedores, de faixa de comissão 'C’,
calculado com base no reajuste de 75% acrescido de R$ 120,00 de
bonificação. Ordenar pelo nome do vendedor.
SQL:
Select
From
Where
Order by
nome_vendedor, novo salário = (salário_fixo * 1.75) + 120
vendedor
faixa comissão = 'C'
nome_vendedor ;
Em SQL pode-se criar um campo que não pertence a tabela ou
mesmo renomear o nome da coluna com “novo nome” após a coluna
no comando select
Selecionando Registros da Tabela:
F) Utilizando Funções sobre Conjuntos
• MAX, MIN
• SUM, AVG
F.1) Buscando Máximos e Mínimos (MAX, MIN)
• Problema:
- Mostrar o menor e o maior salário de vendedor.
Resultado:
SQL:
Select
From
MIN (salário_fixo), MAX (salário_fixo)
vendedor ;
MIN (salário fixo)
MAX (salário fixo)
870,00
9.500,00
F.2) Totalizando Colunas (SUM)
•Problema:
-
Mostrar a quantidade total pedida para o produto 'VINHO' de código '78'.
SQL:
Select SUM(quantidade),
From item_do_pedido
Where código_produto = ‘78’ ;
Resultado:
SUM (Quantidade)
183
Selecionando Registros da Tabela:
F) Utilizando Funções sobre Conjuntos
F.2) Totalizando Colunas (SUM)
• Problema:
- Qual a média dos salários fixos dos vendedores?
SQL:
Resultado:
SELECT AVG(salário_fixo)
FROM Vendedor;
AVG(salário_fixo)
3.324,44
F.4) Contando os Registros (COUNT)
• Problema:
- Quantos vendedores ganham acima de R$ 2.500,00 de salário fixo?
SQL:
Resultado:
Select COUNT(*),
From vendedor
Where salário_fixo >2500 ;
COUNT(*)
5
Selecionando Registros da Tabela:
F.5) Utilizando a Cláusula DISTINCT
Vários registros dentro de uma tabela podem conter os mesmos valores, com
exceção da chave primária.
Algumas consultas podem trazer informações erradas.
A cláusulaDISTINCT,foi criada para não permitir que certas redundâncias,
obviamente necessárias, causem problemas.
• Problema:
- Quais as unidades de produtos, diferentes, na tabela produto?
SQL:
Select DISTINCT unidade
From produto;
Resultado:
UNIDADE
K
BAR
L
M
SAC
G
Selecionando Registros da Tabela:
F.6) Agrupando Informações Selecionadas (GROUP BY)
A cláusula GROUP BY, organiza a se1eção de dados em
grupos determinados.
• Forma:
Select <nome da(s) coluna(s)>
From
<tabela>
Where
<condição (ões)>
Group by <nome da(s) coluna(s)>;
Order by <nome da(s) coluna(s)>;
Selecionando Registros da Tabela:
F.6) Agrupando Informações Selecionadas (GROUP BY)
• Problema:
- Listar o número de produtos que cada pedido contém.
SQL:
Select
num_pedido, total_produto = COUNT(*)
From
item_do_pedido
Group by num_pedido;
Resultado:
NUMERO
DO PEDIDO
TOTAL DE
PRODUTOS
91
1
97
1
98
1
101
3
....
....
143
2
148
5
189
1
203
1
• Inicialmente, os registros são ordenados de forma ascendente por número do
pedido.
• Em segundo passo, é aplicada a operação COUNT (*) para cada grupo de registros
que tenha o mesmo numero de pedido.
• Após a operação de contagem de cada grupo, o resultado da consulta utilizando a
clausula GROUP BY é apresentado.
Selecionando Registros da Tabela:
F.7) Agrupando de forma Condicional (HAVING)
• Problema:
- Listar os pedidos que têm mais do que 3 produtos.
SQL:
Select
num_pedido, total_produto = COUNT(*)
From
item_do_pedido
Group by num_pedido
Having COUNT(*) >3;
Resultado:
NÚMERO DO PEDIDO
TOTAL DE PRODUTOS
119
4
148
5