Aula 08 - DML and DDL

Prévia do material em texto

Banco de Dados I 
Prof. Me. Carlos Beluzo
Prof. Dr. Tiago Carvalho
Tecnologia em Análise e Desenvolvimento de Sistemas
SQL: INTRODUÇÃO
SQL - Introdução
• Linguagem desenvolvida e implementada pelo 
laboratório de pesquisa da IBM em San Jose –
década de 70
• Inicialmente chamada  SEQUEL (Structured English 
QUEry Language)
• Criada como interface entre usuários e o primeiro 
SGBDR (Sistema de Gerenciamento de Banco de 
Dados Relacionais) – SYSTEM R
SQL - Introdução
• É uma linguagem para manipulação e recuperação 
de dados
• Linguagens de Query (LQ) em BD: 
• Fundamentação formal Subsidiam otimização 
• LQ <> linguagens de programação: 
• não pensadas para cálculos complexos. 
• suportam acessos simples e eficientes a 
extensos conjuntos de dados
SQL - Introdução
• Padrão industrial que atinge grande parte do mercado 
de SGBDs
• Atrativos:
• Pequena quantidade de comandos para 
realizar uma grande quantidade de operações
• simplicidade 
• grande poder de consulta
• padrão facilita migração
Padrões SQL
• SQL 2008
• SQL 2006
• SQL 2003
• SQL99 (SQL3)
• SQL92 (SQL2)
• SQL86 (SQL1)
• Leitura Adicional: História e Implementações do 
SQL (https://goo.gl/cRBMcT)
https://goo.gl/cRBMcT
Padrões SQL
• SQL 2008
• SQL 2006
• SQL 2003
• SQL99 (SQL3)
• SQL92 (SQL2)
• SQL86 (SQL1)
• Leitura Adicional: História e Implementações do 
SQL (https://goo.gl/cRBMcT)
https://goo.gl/cRBMcT
DDL E DML
DDL e DML
• Dois conjuntos principais de comandos:
• DDL – Data Defini t ion Language : 
especificação do esquema da base de dados
• DML – Data Manipulation Language : 
inserção, remoção, alteração e consultas na 
instância da base de dados
DDL e DML - Exemplos
• DDL: CREATE, DROP, e ALTER
• TABLE, DATABASE, DOMAIN, EXCEPTION, 
GENERATOR, INDEX, PROCEDURE, ROLE, 
SHADOW, TRIGGER, E VIEW
• DML: SELECT (FROM), DELETE (FROM), INSERT 
(INTO), UPDATE
• Existem variações entre fabricantes, tanto com 
relação ao conjunto de funcionalidades quanto a 
como estas funcionalidades operam.
DDL e DML - Exemplos
• DDL: CREATE, DROP, e ALTER
• TABLE, DATABASE, DOMAIN, EXCEPTION, 
GENERATOR, INDEX, PROCEDURE, ROLE, 
SHADOW, TRIGGER, E VIEW
• DML: SELECT (FROM), DELETE (FROM), INSERT 
(INTO), UPDATE
• Existem variações entre fabricantes, tanto com 
relação ao conjunto de funcionalidades quanto a 
como estas funcionalidades operam.
DDL e DML - Exemplos
• Alguns comandos da DDL:
• CREATE TABLE
• ALTER TABLE 
• DROP TABLE
• CREATE VIEW
• ALTER VIEW
• DROP VIEW
• …
POSTGRESQL
PostgreSQL
PostgreSQL
PostgreSQL Nome do servidor
PostgreSQL Endereço do servidor
Porta
Usuário
Senha
PostgreSQL
COMANDOS DDL
CREATE
• CREATE DATABASE - cria uma nova base de dados
 
CREATE TABLE tabela (
 atrib1 tipo [<restrições da coluna 
1>],
 atrib2 tipo [<restrições da coluna 
2>], 
 ....
 atribn tipo [<restrições da coluna 
n>], 
 
 <restrições da tabela>
); 
•
CREATE
CREATE
Consulta
Execução
Resultado
CREATE
CREATE
• CREATE TABLE - criar uma tabela, definir colunas e 
restrições
 
CREATE TABLE tabela (
  atrib1 tipo [<restrições da coluna 1>],
  atrib2 tipo [<restrições da coluna 2>], 
  ....
  atribn tipo [<restrições da coluna n>], 
  
  <restrições da tabela>
);   
•
Tipos de Dados
• INTEGER | SMALLINT
• NUMERIC | DECIMAL[(precision, scale)]
• precision - número total de dígitos
• scale - número de dígitos depois do ponto
• DOUBLE PRECISION | REAL
• CHAR(n) -  tamanho fixo - n caracteres
• CHARACTER VARYING(n) (VARCHAR) - tamanho variável 
• máximo de n caracteres
• BYTEA (BLOB) – Binary Large Object
• DATE | TIME | TIMESTAMP
Restrições
• De coluna:
• NOT NULL 
• DEFAULT valor
• CHECK(condição)
Restrições
• De tabela:
• PRIMARY KEY ( <atributos chave primária> )
• UNIQUE ( <atributos chave candidata> )
• FOREIGN KEY ( <atributos chave estrangeira> 
REFERENCES tabelaRef [(<chave primária>)]
[<ações>]
• Ações:
• ON DELETE | ON UPDATE
• CASCADE | SET NULL  | SET DEFAULT
• CHECK(condição)
CREATE - Exemplo
CREATE - Exemplo
CREATE - Exemplo
Exercício
• Crie as tabelas para o seguinte esquema de dados:
ALTER
• ALTER TABLE – incluir/alterar/remover definições de 
colunas e restrições
 ALTER TABLE tabela <ação>;
• Ação:
• ADD novoAtrib  tipo  [<restrições de coluna>] 
• ADD [CONSTRAINT nome] <restrição de 
tabela> 
• DROP atributo [CASCADE | RESTRICT]
• DROP CONSTRAINT nome
• ALTER atributo DROP DEFAULT;
• ALTER atributo SET DEFAULT <valor>; 
• RENAME TO novoNome
ALTER
• ADD novoAtrib tipo [<restrições de coluna>]
• DROP atributo [CASCADE | RESTRICT]
• CASCADE – todas as visões e restrições 
(constraints) que referenciam o atributo são 
removidas automaticamente
• RESTRICT – atributo só é removido se não 
houver nenhuma visão ou restrição que o 
referencie
ALTER - Exemplo
ALTER - Exemplo
DROP
• DROP TABLE - exclui uma tabela da base de dados
DROP TABLE tabela [CASCADE | 
RESTRICT];
• CASCADE: todas as visões e restrições que 
referenciam a tabela são removidas 
automaticamente
• RESTRICT: a tabela é removida somente se não 
for referenciada em nenhuma restrição ou visão
Exercício
• Agora, modifique as tabelas para:
• acrescentar o atributo Cidade de Origem para a 
tabela Aluno
• remover o atributo Numero da tabela Turma 
• como fica a tabela matrícula?
• acrescentar uma restrição para que a nota de um 
aluno seja sempre maior ou igual a 0 e menor ou 
igual a 10
• remover uma restrição da tabela Disciplina
COMANDOS DML
INSERT
• INSERT - insere registros em uma tabela
 
INSERT INTO TABLE_NAME (column1, 
column2, column3,…columnN)
VALUES (value1, value2,...valueN)
INSERT - Exemplo
INSERT - Exemplo
INSERT - Exemplo
DELETE
• DELETE - remove um registro de uma tabela
 
DELETE FROM <tabela> WHERE 
<condição>
DELETE - Exemplo
UPDATE
• UPDATE - modifica valores de um registro em uma 
tabela
 
UPDATE <tabela>

SET <campo1>=<valor1> [,..., 
<campon>=<valorn>] WHERE 
<condição> 
UPDATE - Exemplo
Exercício
• Popule o seu banco de dados com pelo menos:
• 15 alunos
• 10 professores
• 3 disciplinas
• 2 turmas por disciplina
• 3 alunos matriculas por turma
• SELECT -recupera os dados de uma ou mais tabelas, 
baseado num conjunto de restrições compostas de 
funções, operandos, comandos, sub-comandos e 
cláusulas não obrigatórias.  
SELECT * | <campo
1
>[,..., <campo
n
>] 
FROM <tabela1>[,..., <tabelan>] 
WHERE <condição/junção> 
SELECT
SELECT - Exemplo
SELECT - Exemplo
SELECT - Exemplo
Operadores AND, OR, NOT
• A clausula WHERE pode ser combinada com os 
operadores AND, OR e NOT
• Os operadores AND e OR podem ser utilizados para 
filtrar registros baseado em uma ou mais condições
• O operador AND recupera um registro se todas as 
condições combinadas pelos ANDs são verdadeiras
• O operador OR recupera um registro se, pelo menos, 
uma das condições combinadas pelos ORs é 
verdadeira
• O operador NOT recupera um registro se as 
condições não são verdadeiras
Operadores AND, OR, NOT
Operadores AND, OR, NOT
Operadores AND, OR, NOT
Operadores AND, OR, NOT
Operador LIKE
• Em SQL, o operador LIKE permite comprar um 
determinado campo (atributo) com uma expressão 
regular
• SELECT … FROM <tabela> WHERE <condição/
junção> LIKE <expressão_regular> 
• Elementos da expressão regular:
• % → qualquer cadeia com 0 a n caracteres
• _ → exatamente um caractere (qualquer)
• \ → caractere de escape
Operador LIKE
Operador LIKE
Operador AS
• O operador AS serve para renomear colunas ou 
tabelas, de acordo com um “apelido” para a consulta
• SELECT <column> AS c1 FROM <tabela> AS tab
• Em consultas envolvendo mais de uma tabela, a 
criação de apelidos é de grande utilidade de forma a 
indicar a qual tabela uma determinada coluna está 
associada
• SELECT tab.<column> AS c1, tab2.<column> as 
c2 FROM <tabela1> AS tab, <tabela2> AS tab2 
Operador AS
Operador ORDER BY
• O operador ORDER BY é utilizadopara ordenar o 
resultado da consulta
• A ordenação pode ser realizada de forma crescente 
ou decrescente
SELECT <coluna> FROM <tabela> WHERE 
<condição> ORDER BY <coluna> ASC | DESC
Cláusulas DISTINCT e ALL
• DISTINCT: a cláusula DISTINCT em SQL é utilizada 
quando se deseja retornar apenas tuplas de valores 
distintos
• DISTINCT não retorna linhas da tabela que 
contenham valores repetidos
• ALL: a cláusula ALL é uma cláusula implícita se não 
especificada que retorna todas as tuplas, mesmo se 
repetidas
Cláusulas DISTINCT e ALL
Cláusulas DISTINCT e ALL
Cláusulas DISTINCT e ALL
Cláusula LIMIT
• LIMIT: a cláusula LIMIT é utilizada para limitar o 
número de tuplas (linhas da tabela) a serem 
retornadas
• Ela pode ser utilizada em conjunto com a cláusula 
OFFSET quando a intensão da consulta for retornar 
registros do meio da tabela
SELECT <nome_coluna> FROM <tabela> WHERE 
<condição> LIMIT <numero de tuplas> OFFSET 
<posicao-inicial>
Cláusula GROUP BY
• GROUP BY: a cláusula GROUP BY é utilizada, 
geralmente, em conjunto com as funções de 
agregação (COUNT, AVG, SUM, MIN, MAX) para 
agrupar os resultados de uma ou mais colunas
SELECT <nome_coluna> FROM <tabela> WHERE 
<condição> GROUP BY <nome_coluna>
Funções COUNT, AVG e SUM
• COUNT: retorna o número de tuplas (linhas da tabela) 
que satisfazem um critério específico
SELECT COUNT(<nome_coluna>) FROM <tabela> 
WHERE <condição>
• AVG: retorna o valor numerico médio da coluna
SELECT AVG(<nome_coluna>) FROM <tabela> 
WHERE <condição>
• SUM: retorna a soma total dos valores numéricos da 
coluna
SELECT SUM(<nome_coluna>) FROM <tabela> 
WHERE <condição>
Funções MIN e MAX
• MIN: retorna o menor valor numérico de uma coluna 
específica
SELECT MIN(<nome_coluna>) FROM <tabela> 
• MAX: retorna o maior valor numérico de uma coluna 
específica
SELECT MAX(<nome_coluna>) FROM <tabela> 
WHERE <condição>
Exercício
• Modele os exercícios 1 e 2 da Lista 01 - Parte 01, 
construindo e povoando todo o banco de dados. Você 
deve criar desde o MER até o database do banco de 
dados, passando pelas etapas de modelagem lógico-
relacional, normalização de tabelas e criação de todo 
o banco utilizando SQL.
• Crie, pelo menos, 5 consultas diferentes para cada 
um dos bancos. Você deve descrever o que suas 
consultas devem retornar e utilizar, pelo menos uma 
vez, todos os recursos aprendidos até o momento
Exercício
Exercício
• Modele o banco de dados descrito na imagem a 
seguir, inserindo o seguinte número de registros:
• 10 clientes
• 5 funcionários
• 3 escritórios
• 20 produtos
• 5 linhas de produtos
• 2 formas de pagamento
• 30 pedidos