Linguagem SQL: Conceitos e Comandos

Prévia do material em texto

Linguagem SQL
Prof. Paulo Sena
Linguagem SQL
 O Modelo Relacional prevê, desde sua 
concepção, a existência de uma 
linguagem baseada em caracteres que 
suporte a definição do esquema físico 
(tabelas, restrições, etc.), e sua 
manipulação (inserção, consulta, 
atualização e remoção)
Linguagem SQL
 A Linguagem SEQUEL ou SQL (Structured Query 
Language) é padrão para SGBDs Relacionais.
 Embora seja capaz de prover acesso facilitado aos 
dados, a linguagem SQL possui certas limitações, 
como a impossibilidade de manipular uma tabela 
linha-a-linha, exigindo sua extensão, neste caso, 
através da definição de cursores e cláusulas.
Conjuntos de Comandos da 
Linguagem SQL
 A Linguagem SQL pode ser dividida em 
5 conjuntos de comandos:
Conjuntos de Comandos da 
Linguagem SQL
– Linguagem de consulta de dados (DQL –
Data Query Language): comando SELECT
– Linguagem de manipulação de dados 
(DML - Data Manipulation Language): 
comandos para inserções (INSERT), 
atualizações (UPDATE) e exclusões 
(DELETE)
Conjuntos de Comandos da 
Linguagem SQL
– Linguagem de definição de dados (DDL -
Data Definition Language): comandos para 
criação e manutenção de objetos do banco 
de dados: CREATE, ALTER, DROP
– Linguagem para controle de transações: 
BEGIN, COMMIT, ROLLBACK
– Linguagem para controle de acesso a 
dados: GRANT e REVOKE
Apresentação do Estudo de Caso
 O Estudo de caso aqui utilizado visa 
modelar a realidade de uma livraria
 São apresentados os esquemas 
conceitual, lógico e físico
 Todas as tabelas estão “populadas” e 
sobre estas devem ser executados os 
exercícios
Linguagem de Definição de 
Dados (DDL)
Linguagem de definição de dados 
(DDL)
 A linguagem de definição de dados 
permite a criação, manutenção e 
eliminação de objetos do banco de 
dados:
– tabelas
– visões
– índices
– seqüências
– sinônimos
SQL - DDL
 Criação de um BD
– SQL padrão não oferece tal comando
• BDs são criados via ferramentas do SGBD
– alguns SGBDs (SQL Server, DB2, MySQL) 
oferecem este comando
• create database nome_BD
• drop database nome_BD
SQL - DDL
 Comandos para definição de esquemas
– create table
• define a estrutura da tabela, suas restrições de 
integridade e cria uma tabela vazia
– alter table
• modifica a definição de uma tabela 
– drop table
• remove uma tabela com todas as suas tuplas
Convenções de Nomes
 Devem começar com uma letra
 Pode ter de 1 a 30 caracteres
 Pode conter somente A-Z, a-z, 0-9, _, $ 
e #
 Os nomes devem ser únicos por 
usuário
 Não podem ser utilizadas palavras 
reservadas (salvo se entre aspas)
Tipos de Dados Básicos
 CHAR(tamanho): seqüência de 
caracteres de tamanho fixo
 VARCHAR(tamanho): seqüência de 
caracteres de tamanho variável
 NUMBER(total, decimais): valores 
numéricos
 DATE: data e hora
Tipos de Dados para Caracteres
 CHAR(tamanho [BYTE | CHAR]): até 2000 bytes
– Alocação de bytes é o padrão, mas podem ser alocados 
CHARs, em formato Unicode
 NCHAR(tamanho): até 2000 bytes em alocação 
Unicode (2 ou 3 bytes por caracter)
 VARCHAR2 ou VARCHAR(tamanho [BYTE | 
CHAR]): até 4000 bytes
 NVARCHAR2: até 4000 bytes
 CLOB: até 232 – 1 bytes (4 GB)
 NCLOB: até 232 – 1 bytes (4 GB) em alocação 
Unicode
 LONG: até 231 – 1 bytes (2 GB), mantido para 
compatibilidade
Tipos de Dados para Data e Tempo
 DATE: ano, mês, dia, hora, minuto, segundo, 
armazenados em 7 bytes
 INTERVAL DAY (precisão) TO SECOND (precisão): 
intervalos entre duas datas em dias, horas, minutos e 
segundos
 INTERVAL (precisão) YEAR TO MONTH: intervalo 
entre duas datas em anos e meses
 TIMESTAMP (precisão): valores de instantes de 
tempo com precisão de até 9 casas decimais nos 
segundos
 TIMESTAMP (precisão) WITH TIME ZONE: inclui a 
informação do fuso horário
 TIMESTAMP (precisão) WITH LOCAL TIME ZONE: 
recupera a informação ajustada ao fuso horário local
Tipos de Dados Binários
 BLOB: até 232 – 1 bytes (4 GB) de 
dados binários armazenados no banco 
de dados
 BFILE: até 232 – 1 bytes (4 GB) de 
dados binários armazenados em 
arquivos externos
 RAW (tamanho): armazena até 2000 
bytes, mantido para compatibilidade
 LONG RAW: até 231 – 1 bytes (2 GB) , 
mantido para compatibilidade
Comando CREATE TABLE
 Permite a criação de uma tabela:
– CREATE TABLE nome_da_tabela (
nome_da_coluna tipo_de_dado [NULL|NOT 
NULL] restrições_de_coluna, ...
restrições_de_tabela);
 Exemplo:
– CREATE TABLE autores (
cod_autor NUMBER (4) NOT NULL,
nome VARCHAR2 (100) NOT NULL,
descricao VARCHAR2 (1024)
);
Restrições de integridade
 Integridade de Entidade
 Integridade de Domínio
 Integridade Referencial
 Integridade definida pelo usuário
Restrições de Integridade
Integridade de Entidade
 Toda a tabela deve possuir, 
obrigatoriamente, uma coluna (ou uma 
composição de colunas) cujo valor é único 
para todas as instâncias  CHAVE 
PRIMÁRIA (PK)
 Eventualmente, outras colunas além da 
chave primária podem ser únicas  CHAVE 
ALTERNATIVA (AK)
Restrições de Integridade
Integridade de Domínio
 Cada coluna da tabela tem seus valores 
restringidos por
– tipos de dados: definem o domínio de uma coluna
– cod_cliente NUMBER ( 6 )
– username VARCHAR2 ( 20 )
 Restrições a valores nulos: definem se as 
colunas serão obrigatórias ou opcionais
– email VARCHAR2 ( 40 ) NOT NULL
– ddd NUMBER ( 3 )
Restrições de Integridade
Integridade de Domínio
– valores padrão: define um valor padrão a 
ser associado a uma coluna se não for 
fornecido valor (ou for explicitamente 
fornecido um valor NULL) para esta 
durante a inserção
importado CHAR ( 1 ) DEFAULT 'N' NOT NULL
– A definição de valores padrões é particularmente 
importante para colunas NOT NULL
Restrições de Integridade
Integridade de Domínio
– restrições de validação: restringem os 
valores que podem ser atribuídos a uma 
coluna
CONSTRAINT CHK_PROD_IMPORTADO 
CHECK (importado in ('S','N'))
– tipos definidos pelo usuário: tipo de dado 
construído a partir de tipos pré-definidos, 
contendo restrição a valores nulos, regras 
de validação e um valor padrão
• Não disponível em todos os SGBDs
Restrições de Integridade
Integridade Referencial
 As restrições de integridade referencial 
garantem a consistência dos relacionamentos 
entre as tabelas
Restrições de Integridade
Integridade Referencial
 No exemplo, a definição da restrição de 
integridade referencial seria criada como 
segue
CONSTRAINT FK_EST_CID FOREIGN KEY (uf) 
REFERENCES estados (uf)
 Esta restrição estabelece uma relação entre 
o atributo uf da tabela CIDADES e a chave 
primária da tabela ESTADOS
Restrições de Integridade
Integridade Referencial
 O SGBD deve prover mecanismos para 
assegurar que a restrição de integridade seja 
respeitada, ou seja, uma linha violando a 
restrição de integridade referencial não 
poderá existir;
 Estes mecanismos prevêem a execução de 
uma ação em resposta a uma atualização, a 
fim de manter a integridade referencial.
Restrições de Integridade
Integridade Referencial
 Existem diferentes ações possíveis para 
evitar que uma operação de atualização viole 
as restrições de integridade referencial
– RESTRICT: impede a execução da operação
Inserir uma Sala situada em um Prédio não 
existente
– CASCADE: propaga a operação para respeitar 
as restrições de integridade referencial
Remover uma Disciplina do Currículo, 
removendo todos os Pré-requisitos associados a 
esta
Restrições de Integridade
Integridade Referencial
– SET NULL: assegura as restrições de 
integridade referencial associando um valor 
NULL à chave estrangeira que poderia violá-las
Remover um Professor, fazendo com que a 
coluna matricula_professor em 
DISCIPLINAS_TURMAS fosse atualizada para 
NULL em todas as disciplinas que atua
 NULL não é considerado um valor, portanto, não 
fere as restrições de integridade referencial
– SET DEFAULT: semelhante à ação deSET 
NULL, mas associa um valor padrão à chave 
estrangeira
Restrições de Integridade
Integridade Definida pelo Usuário
 O Projetista do Banco de Dados pode definir 
restrições de integridade complexas, 
utilizando, por exemplo, o conceito de
triggers
 Triggers são porções de código ativadas 
após certos eventos (inserção, atualização 
ou exclusão de linhas da tabela)
Restrições de Integridade de Tabelas
– CONSTRAINT nome_da_restrição 
PRIMARY KEY (colunas)
– CONSTRAINT nome_da_restrição 
FOREIGN KEY (colunas) REFERENCES 
nome_da_tabela_pai [ON DELETE 
CASCADE]
– CONSTRAINT nome_da_restrição 
UNIQUE (colunas)
– CONSTRAINT nome_da_restrição 
CHECK (expressao)
Restrições de Integridade de Colunas
– CONSTRAINT nome_da_restrição NOT NULL
– CONSTRAINT nome_da_restrição PRIMARY 
KEY
– CONSTRAINT nome_da_restrição 
REFERENCES nome_da_tabela_pai [ON 
DELETE CASCADE]
– CONSTRAINT nome_da_restrição UNIQUE
– CONSTRAINT nome_da_restrição CHECK 
(expressao)
Exemplo
create table usuarios (
cod_usuario number ( 6 ),
nome varchar2 ( 100 )
constraint nn_usu_nome not null,
cpf char ( 11 )
constraint nn_usu_cpf not null,
email varchar2 ( 40 )
constraint nn_usu_email not null,
username varchar2 ( 20 )
constraint nn_usu_username not null,
password varchar2 ( 20 )
constraint nn_usu_password_not null,
constraint pk_usuarios primary key 
(cod_usuario),
constraint ak_usu_cpf unique (cpf),
constraint ak_usu_username unique (username));
Exemplo
create table produtos (
cod_produto number ( 5 ) not null,
titulo varchar2 ( 200 ) not null,
ano_lancamento date not null,
importado char ( 1 ) not null,
preco number ( 10, 2 ) not null,
prazo_entrega number ( 3 ) not null,
constraint pk_produtos
primary key (cod_produto),
constraint chk_prod_importado
check (importado in (‘S',‘N'))
);
Exemplo
create table administradores (
cod_administrador number ( 6 ),
nivel_privilegio number ( 1 ) not null,
constraint pk_administradores primary 
key (cod_administrador),
constraint fk_usu_adm foreign key 
(cod_administrador) references usuarios 
(cod_usuario)
);
Definindo Valores Padrão
 Pode ser definido um valor padrão para uma 
coluna (literais, expressões ou funções)
 Se não for definido um valor, será utilizado o 
padrão
– create table pedidos (
num_pedido number ( 7 ),
cod_cliente number ( 6 ) not null,
cod_endereco number ( 2 ) not null,
data_emissao date not null default 
sysdate,
constraint pk_pedidos primary key 
(num_pedido));
Utilizando Valores Padrão
– insert into pedidos (num_pedido, 
cod_cliente, cod_endereco)
values (17645, 540, 290);
 Foi omitida a coluna data_emissão, 
logo, será utilizado o valor padrão
– insert into pedidos (num_pedido, 
cod_cliente, cod_endereco, 
data_emissao)
values (17645, 540, 290, NULL);
 Foi explicitamente definido NULL
Comando ALTER TABLE
 Permite a alteração de uma tabela
ALTER TABLE nome_da_tabela
[ADD definição de coluna,]
[MODIFY definição de coluna,]
[DROP COLUMN nome,]
[RENAME COLUMN antigo TO novo,]
[ADD definição de constraint,]
[MODIFY CONSTRAINT definição de 
constraint,]
[DROP CONSTRAINT nome,]
[RENAME CONSTRAINT antigo TO novo,]
[ENABLE | DISABLE constraint,]
[RENAME TO novo_nome];
Exemplo
 alter table pedidos
add valor_total number(10,2);
 alter table enderecos
modify cod_endereco number(3);
 alter table autores
drop column descricao;
 alter table clientes
rename column cod_usuario to 
cod_cliente;
 alter table produtos
add constraint chk_prod_prazo
check(prazo between 3 and 30);
Exemplo
 alter table produtos
add constraint chk_prod_prazo
check(prazo between 3 and 
30);
 alter table produtos
drop constraint 
chk_prod_importado;
Exemplo
 alter table enderecos
modify cod_endereco number(3);
 alter table produtos
add constraint chk_prod_prazo
check(prazo between 3 and 30);
 alter table usuarios
disable constraint pk_usuarios;
 alter table usuarios
enable constraint pk_usuarios;
 alter table pedidos_produtos
rename to itens_pedidos;
Comando DROP TABLE
 Permite a eliminação de uma tabela:
– DROP TABLE nome_da_tabela
[CASCADE CONSTRAINTS];
– drop table tipos_telefones
cascade constraints;
Comando TRUNCATE TABLE
 Permite a eliminação de todas as linhas 
de uma tabela, de forma rápida e 
liberando o espaço alocado, sem 
possibilitar a execução de rollback:
– TRUNCATE TABLE nome_da_tabela;
Linguagem de Modelagem de 
Dados (DML)
SQL – DML
 Define operações de manipulação de 
dados
– (INSERT)
– (UPDATE)
– (DELETE)
 Instruções declarativas
– manipulação de conjuntos
– especifica-se o que fazer e não como fazer
SQL – DML
 Inserção de dados
INSERT INTO nome_tabela
[(lista_atributos)]
VALUES (lista_valores_atributos)
[, (lista_valores_atributos)]
 Exemplos
INSERT INTO Ambulatorios VALUES (1, 1, 30)
INSERT INTO Medicos
(codm, nome, idade, especialidade, CPF,
cidade)
VALUES (4, ‘Carlos’, 28,’ortopedia’, 
11000110000, ‘Joinville’);
MySQL
SQL – DML
 Alteração de dados
UPDATE nome_tabela
SET nome_atributo_1 = Valor
[{, nome_atributo_n = Valor}]
[WHERE condição] 
 Exemplos
UPDATE Medicos
SET cidade = ‘Florianopolis’
UPDATE Ambulatorios
SET capacidade = capacidade+5,andar=3
WHERE nroa = 2
SQL – DML
 Exclusão de dados
DELETE FROM nome_tabela
[WHERE condição] 
 Exemplos
DELETE FROM Ambulatorios
DELETE FROM Medicos
WHERE especialidade = ‘cardiologia’
or cidade ‘Florianopolis’
Linguagem de Controle de Dados 
(DCL)
Privilégios de usuários
 CONCEDER e REVOGAR privilégios em vários objetos de 
banco de dados
 Conceder:
 Podem ser concedidos aos usuários vários privilégios para as 
tabelas. Essas permissões podem ser qualquer combinação de 
SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER 
ou ALL.
 Sintaxe:
GRANT privileges ON object TO user;
Privilégios de usuários
Privilege Description
SELECT Habilidade de usar SELECT na tabela.
INSERT Habilidade de usar INSERT statements on the table.
UPDATE Habilidade de usar UPDATE statements on the table.
DELETE Habilidade de usar DELETE statements on the table.
REFERENCES
Habilidade de criar constraints que referenciem a 
tabela.
ALTER
Habilidade de usar ALTER TABLE para mudar a 
definição da tabela
ALL
Garante todas as permissões acima: SELECT, INSERT, 
UPDATE, DELETE, e REFERENCES.
*Privilégios tanto para conceder quanto para revogar.
Privilégios de usuários
 Exemplos:
GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO smithj;
GRANT ALL ON employees TO smithj;
GRANT SELECT ON employees TO public;
Privilégios de usuários
 Revogar privilégios 
 Depois de conceder privilégios, você pode precisar 
revogar alguns ou todos esses privilégios. Para fazer 
isso, você pode executar um comando revoke. Você 
pode revogar qualquer combinação de SELECT, 
INSERT, UPDATE, DELETE, REFERENCES, ALTER 
ou ALL.
REVOKE privileges ON object FROM user;
Privilégios de usuários
 Exemplos:
REVOKE DELETE ON employees FROM anderson;
REVOKE ALL ON employees FROM anderson;
REVOKE SELECT ON employees FROM public;
Linguagem de Transação de 
Dados (DTL)
Transações SQL
 Nem todo SGBD permite trabalhar queries como 
transações.
 O que é?
– É uma unidade lógica de processamento que tem por 
objetivo preservar a integridade e a consistência dos dados. 
Esse processamento pode ser executado todo ou não 
garantindo a atomicidade das informações.
– Seguem as propriedades ACID: Atomicidade, Consistência, 
Isolamento e Durabilidade.
Transações SQL
 A sintaxe básica de uma transação é:
Begin Transaction
--Corpo de comando
Commit ou Rollback
 Onde:
Begin Transaction: Tag inicial para o inicio de uma transação.
--Corpo de comando: Conjunto de comando a serem 
executados dentro de uma transação.
Commit ou Rollback: Comandos que finalizam a transação 
onde o ‘commit’ confirma o conjunto de comandos e o ‘rollback’ 
desfaz todo o processo executado pelo corpo de comandoscaso tenha ocorrindo algum evento contrario ao desejado.
Transações SQL
 Exemplo
 BEGIN TRANSACTION
UPDATE FROM TbContas
SET NuSaldo= 10.000
WHERE NuSaldo 18
Select CPF, nome 
From Pacientes
Select CPF, nome 
From Pacientes
Where idade > 18
Comando SELECT
 Facilidades para projeção de informações
– Não há eliminação de duplicatas no Select
• tabela ≡ coleção 
– retorno de valores calculados
• uso de operadores aritméticos (+,-,*,/)
– invocação de funções de agregação
• COUNT (contador de ocorrências [de um atributo])
• MAX / MIN (valores máximo / mínimo de um atributo)
• SUM (somador de valores de um atributo)
• AVG (média de valores de um atributo)
Comando SELECT
 Eliminação de duplicatas
select [distinct] lista_atributos
...
 Exemplo
– buscar as especialidades dos médicos
select distinct especialidade
from Médicos
Comando SELECT
 Retorno de valores calculados - Exemplos
– quantos grupos de 5 leitos podem ser 
formados em cada ambulatório?
select nroa, capacidade/5 as grupos5
from Ambulatórios
– qual o salário líquido dos funcionários (desc. 
10%)?
select CPF, salário – (salário * 0.1) as 
líquido from Funcionários
Comando SELECT
 Função COUNT - Exemplos
– informar o total de médicos ortopedistas
select count(*) as TotalOrtopedistas
from Médicos
where especialidade = ´ortopedia´
– total de médicos que atendem em ambulatórios
select count(nroa) as Total
from Médicos
não conta nulos
Comando SELECT
 Função SUM - Exemplo
– informar a capacidade total dos ambulatórios 
do primeiro andar
select sum(capacidade) as TotalAndar1
from Ambulatórios
where andar = 1
Comando SELECT
 Função AVG - Exemplo
– informar a média de idade dos pacientes de 
Florianópolis
select avg(idade) as MediaPacFpolis
from Pacientes
where cidade = ´Florianópolis´
Comando SELECT
 Funções MAX / MIN - Exemplo
– informar o menor e o maior salário pagos aos 
Funcionários do departamento pessoal com 
mais de 50 anos
select min(salário) as mínimo,
max(salário) as máximo
from Funcionários
where depto = ´Pessoal´
and idade > 50
Comando SELECT
 Funções de Agregação com distinct
– valores duplicados não são computados
– exemplos
select count(distinct especialidade)
from Médicos
select avg(distinct salário)
from Funcionários
Comando SELECT
 Observação sobre as funções de agregação
– não podem ser combinadas a outros atributos da 
tabela no resultado da consulta
select andar, COUNT (andar)
from Ambulatórios
Cláusula WHERE
 Facilidades para seleção de dados
– busca por padrões
• cláusula [NOT] LIKE
– teste de existência de valores nulos
• cláusula IS [NOT] NULL
– busca por intervalos de valores
• cláusula [NOT] BETWEEN valor1 AND valor2
– teste de pertinência elemento-conjunto, listas
• cláusula [NOT] IN
Cláusula WHERE
 Busca por padrões
where atributo like ´padrão´
% : casa com qualquer cadeia de caracteres
´_´ : casa com um único caractere
[a-f] : casa com qualquer caractere entre 
´a´ e ´f´ (SQL-Server)
 Exemplos
– buscar CPF e nome dos médicos com inicial M
select CPF, nome
from Médicos
where nome like ´M%´
Cláusula WHERE
 Exemplos
– buscar nomes de pacientes cujo CPF termina com 
20000 ou 30000
select nome
from Pacientes
where CPF like '%20000‘
or CPF like '%30000‘
 Observações
– em alguns dialetos SQL, ´*´ é usado invés de ´%´
– não é possível testar padrões em atributos datetime
(SQL-Server)
Cláusula WHERE
 Teste de valores nulos - Exemplo
– buscar o CPF e o nome dos médicos que 
não dão atendimento em ambulatórios
select CPF, nome
from Médicos
where nroa is null
Cláusula WHERE
 Busca por intervalos de valores - Exemplo
– buscar os dados das consultas marcadas para o 
período da tarde
select *
from Consultas
where hora between ´14:00´ and ´18:00´
Cláusula WHERE
 Teste de pertinência elemento-conjunto -
Exemplo
– buscar os dados das médicos ortopedistas, 
traumatologistas e cardiologistas de Florianópolis
select *
from Médicos
where cidade = ´Florianópolis´
and especialidade in (´cardiologia´,
´traumatologia´,
´cardiologia´)
Ordenação de Resultados
 Cláusula ORDER BY
– Default é ASC
select lista_atributos
from lista_tabelas
[where condição]
[order by nome_atributo 1 [desc] {[, 
nome_atributo n [desc]]} ]
 Exemplos
select * select salário, nome
from Pacientes from Funcionários 
order by nome order by salário desc, nome 
Ordenação de Resultados
 É possível determinar a quantidade 
de valores ordenados a retornar
select ...
limit valor1 [,valor2]
 Exemplos
select * select salário, nome
from Pacientes from Funcionários 
order by nome order by salário desc, 
limit 5 nome
limit 5,10
retorna as 5 primeiras tuplas 
retorna tuplas 6 a 15 
Definição de Grupos
 Cláusula GROUP BY
select lista_atributos
from lista_tabelas
[where condição]
[group by lista_atributos_agrupamento 
[having condição_para_agrupamento] ]
 GROUP BY
– define grupos para combinações de valores dos atributos definidos
em lista_atributos_agrupamento
– apenas atributos definidos em lista_atributos_agrupamento
podem aparecer no resultado da consulta
– geralmente o resultado da consulta possui uma função de
agregação
Definição de Grupos
 Exemplo
select especialidade, count(*)
from Médicos
group by especialidade
especialidade “grupos”
ortopedia codm nome idade RG cidade nroa
1 João 40 1000010000 Fpolis 1
4 Carlos 28 1100011000 Joinville
pediatria codm nome idade RG cidade nroa
3 Pedro 51 1100010000 Fpolis 2
neurologia codm nome idade RG cidade nroa
5 Márcia 33 1100011100 Biguaçu 3
traumatologia codm nome idade RG cidade nroa
2 Maria 42 1000011000 Blumenau 2
6 Joana 37 1111110000 Fpolis 3
7 Mauro 53 1111000011 Blumenau 2
especialidade Count
ortopedia 2
pediatira 1
neurologia 1
traumatologia 3
Definição de Grupos
 Cláusula HAVING
– define condições para que grupos sejam 
formados
• condições só podem ser definidas sobre atributos 
do agrupamento ou serem funções de agregação
– existe somente associada à cláusula GROUP 
BY
 Exemplos
select especialidade, count(*)
from Médicos
group by especialidade
having count(*) > 1
Atualização com Consulta
 Comandos de atualização podem incluir 
comandos de consulta
– necessário toda vez que a atualizaçãodeve 
testar relacionamentos entre tabelas
 Exemplo 1
delete from Consultas
where hora > ’17:00:00’
and codm in (select codm
from Médicos
where nome = ‘Maria’)
Atualização com Consulta
 Exemplo 2
update Médicos
set nroa = NULL
where not exists 
(select * from Médicos m
where m.codm Médicos.codm 
and m.nroa = Médicos.nroa)
 Exemplo3
update Ambulatórios
set capacidade = capacidade + 
(select capacidade 
from Ambulatórios where nroa = 4)
where nroa = 2
Atualização com Consulta
 Exemplo 4 (supondo MedNovos(código, nome, 
especialidade))
insert into MedNovos
select codm, nome, especialidade
from Médicos
where idade ‘12:00’
and Pacientes.codp = Consultas.codp
Select m2.nome
From Médicos m1, Médicos m2
Where m1.nome = ‘João’
and m1.especialidade = m2.especialidade
Joins
- Combina colunas de uma ou mais tabelas
- Existem diferentes tipos de junções em SQL
- Veremos aqui:
- I N N E R
- N AT U R A L
- OUTER (RIGHT, L E F T OR F U L L )
- Alguns SGBDs podem suportar outros tipos de junções não
apresentadas aqui
Com os seguintes dados
Cliente
id nome
1532 Asdrúbal
1755 Doriana
1780 Quincas
93 DinoTech
97 Proj
cliente_empresa
id cnpj
93 58.443.828/0001-02
97 44.876.234/7789-10
cliente_particular
id cpf
1532 448.754.253-44
1755 567.387.387-44
1780 576.456.123-55
corrida
cliiid placa datapedido
1755 DAE6534 2003-02-15
97 JDM8776 2003-02-18
taxi
placa marca modelo anofab
DAE6534 Ford Fiesta 1999
DKL4598 Wolkswagen Gol 2001
DKL7878 Ford Fiesta 2001
JDM8776 Wolkswagen Santana 2002
JJM3692 Chevrolet Corsa 1999
INNER JOIN
- Ou apenas JOIN: seleciona as tuplas de acordo com a 
condição em ON.
- Exemplo: recuperar clientes que fizeram corridas de taxi
SELECT *
FROM cliente INNER JOIN corrida ON cliid=id;
- Equivalente a
SELECT *
FROM cliente, corrida 
WHERE cliid=id;
id nome cliid placa datapedido
1755 Doriana 1755 DAE6534 2003-02-15
97 Proj 97 JDM8776 2003-02-18
NATURAL JOIN
- Seleciona as tuplas que tem valor equivalente para as 
colunas/atributos de mesmo nome (mesmo que na álgebra)
- Exemplo: recupera todos os taxis que fizeram corridas
SELECT *
FROM taxi NATURAL JOIN corrida;
a junção acontece em
placa
- Similar a:
SELECT *
FROM taxi as t INNER JOIN corrida as c ON 
c.placa=t.placa;
- Neste caso, placa aparece duas vezes no esquema resultante (c.placa e t.placa)
SELECT 
FROM TableA 
NATURAL JOIN
TableB
placa marca modelo anofab cliid datapedido
DAE6534 Ford Fiesta 1999 1755 2003-02-15
JDM8776 Volkswagen Santana 2002 97 2003-02-18
L E F T OUTER JOIN
- Ou apenas LEFT JOIN: seleciona todas as tuplas da tabela à esquerda + tuplas da 
direita, desde que satisfaça a condição em ON.
- Isso permite que o lado direito tenha dados de valor NULL
- Exemplo: recupera todos os clientes e possíveis corridas que ele tenha feito
- Podemos ver que a consulta recuperar clientes que fizeram ou não corridas.
SELECT *
FROM cliente LEFT JOIN corrida ON cliid=id;
id nome cliid placa datapedido
1755 Doriana 1755 DAE6534 2003-02-15
97 Proj 97 JDM8776 2003-02-18
1532 Asdrúbal
93 DinoTech
1780 Quincas
L E F T OUTER JOIN
- Podemos adicionar cláusula WHERE para recuperar apenas dados da esquerda que 
não têm correspondência com a direita.
- Isto é, apenas os dados em que o lado direito é NULL
- Exemplo: recupera todos os clientes que não fizeram corridas
SELECT *
FROM cliente LEFT JOIN corrida ON cliid=id 
WHERE cliid IS NULL;
id nome cliid placa datapedido
1532 Asdrúbal
93 DinoTech
1780 Quincas
- Simétrico ao L E F T O U T E R J O I N
R I G H T O U T E R J O I N
F U L L OUTER JOIN
- Seleciona todas as tuplas que satisfazem a condição em ON + todas as tuplas das 
esquerda que não tiveram correspondências + todas as tuplas da direita que não 
tiveram correspondência
- Exemplo: recupera todos os clientes particulares, todas as corridas e associa 
corridas a clientes
SELECT *
FROM cliente_particular FULL OUTER JOIN corrida ON cliid=id;
- Podemos notar: o primeiro cliente_particular fez corrida; os demais (ids 1532 e
1780) nunca fizeram corrida; e ainda, existe uma corrida feita por algum cliente
(cliid 97) que não é particular (neste contexto, é uma empresa).
id cpf cliid placa datapedido
1755 567.387.387-44 1755 DAE6534 2003-02-15
97 JDM8776 2003-02-18
1532 448.754.253-44
1780 576.456.123-55
F U L L OUTER JOIN
 Similar ao LEFT e RIGHT JOIN podemos selecionar apenas as tuplas 
que não tem correspondência com o outro lado.
 Desta forma, permitiremos recuperar as tuplas que tem valores nulos ou 
na esquerda ou na direita.
 Exemplo: recupera corridas não feitas por clientes particulares e clientes 
particulares que não fizeram corridas
SELECT *
FROM cliente_particular FULL OUTER JOIN corrida ON cliid=id 
WHERE id IS NULL OR cliid IS NULL;
id cpf cliid placa datapedido
97 JDM8776 2003-02-18
1532 448.754.253-44
1780 576.456.123-55
Subconsultas ou Consultas Aninhadas
 Forma alternativa de especificar consultas 
envolvendo relacionamentos entre tabelas
 Otimização
– filtragens prévias de dados na subconsulta
• apenas tuplas/atributos de interesse são combinados com 
dados da(s) tabela(s) da consulta externa
 Cláusulas de subconsulta
– nome_atributo [NOT] IN (consulta_SQL)
– nome_atributo [ | >= | | !=] ANY
(consulta_SQL)
– nome_atributo [ | >= | | !=] ALL
(consulta_SQL)
Subconsultas com IN 
 Testam a relação de pertinência ou não-
pertinência elemento-conjunto
select lista_atributos
from tabela1 [...]
where atributo_ou_expressão [NOT] IN
(consulta_SQL)
Exemplos
SQL
Select nome
From Médicos
Where codm in
(select codm
from Consultas
where data = ‘06/11/13’)
Select CPF 
From Funcionários
Where CPF not in
(select CPF
from Pacientes)
Select CPF
From Médicos
Where CPF in
(select CPF
from Pacientes)
Subconsultas com ANY 
 Permitem outras comparações do tipo 
elemento-conjunto
– testa se um valor é >, |>=|| !=] 
ANY
(consulta_SQL)
Exemplos
SQL
Select nome
From Médicos
Where codm = any (ou in)
(select codm
from Consultas
where data = ‘06/11/13’)
Select nome
From Funcionários
Where idade |>=|| !=] 
ALL(consulta_SQL)
 Não tem mapeamento para a álgebra
relacional
– não é equivalente a divisão
• na divisão existe apenas comparação de igualdade
• dividendo deve ter mais atributos que o divisor
• não filtra automaticamente atributos do dividendo
Exemplos
Select nome
From Funcionários
Where salário > all
(Select salário
From Funcionários
Where departamento = ‘contábil’)
Select nome
From Funcionários
Where CPF all (or not in)
(Select CPF
From Pacientes)
Comparações Elemento-Elemento 
 Casos em que a subconsulta retorna 
apenas um elemento como resultado
– cardinalidade da subconsulta = 1
– não é utilizada nenhuma cláusula de 
subconsulta neste caso
select lista_atributos
from tabela1 [, ...]
where atributo_ou_expressão [=||>=|| 
!=] (consulta_SQL com um único 
elemento)
Exemplos
Select nome
From Funcionários
Where salário> 
(Select salário
From Funcionários
Where CPF = 22000200002)
select nome, CPF
from Médicos
where CPF 10000100001
and especialidade = 
(select especialidade
from Médicos
where CPF = 10000100001)
Subconsultas com EXISTS 
 Quantificador existencial do cálculo 
relacional
– testa se um predicado é V ou F na subconsulta
– para cada tupla da consulta externa a ser 
analisada, a subconsulta é executada
select lista_atributos
from tabela1 [, ...]
where [NOT] EXISTS (consulta_SQL)
Exemplos
SQL
Select nome
From Médicos m
Where exists
(Select *
From Consultas
Where data = ‘06/11/13’
and codm = m.codm)
Select f.nome
From Funcionários f
Where f.depto = ‘pessoal’
and not exists
(Select *
From Pacientes
Where CPF = f.CPF)
Exemplo
SQL
Select p.nome
From Pacientes p
Where not exists
(Select *
From Médicos m
Where not exists
(Select *
From Consultas c
Where c.codm = m.codm
and c.codp = p.codp))
Subconsulta na Cláusula FROM 
• Gera uma tabela derivada a partir de uma 
ou mais tabelas, para uso na consulta 
externa
– otimização: filtra linhas e colunas de uma 
tabela que são desejadas pela consulta 
externa
select lista_atributos
from (consulta_SQL) as 
nome_tabela_derivada
Exemplos
SQL
select Medicos.*, C.hora
from Medicos join
(select codm, hora 
from Consultas 
where data = '06/11/13') 
as C
on Médicos.codm = C.codm
select Amb.*
from (select nroa, andar from ambulatorios) as Amb
join 
(select nroa from Medicos 
where cidade = ‘Fpolis') 
as MFlo
on Amb.nroa = MFlo.nroa
Criando uma Tabela a Partir de uma 
Consulta
 SQL permite a criação de uma tabela a partir 
do resultado de uma consulta:
– CREATE TABLE nome_da_tabela (colunas) 
AS
select ...
– CREATE TABLE teste (cod_cliente, 
nome_cliente) AS
select u.cod_usuario, u.nome
from usuarios u
where u.cod_cliente in (
select c.cod_cliente
from clientes c);
Views
- é uma tabela virtual baseada em um conjunto de resultados de uma 
consulta SQL
- é uma tabela derivada de outras tabelas
- podemos usar visões para especificar tabelas que iremos usar com 
frequência
- Sintaxe:
CREATE VIEW view_name AS SELECT column1, 
column2, ... FROM table_name
WHERE condition (...);
Exemplo
- Para corridas realizadas, os nomes e id dos clientes, dados do carro e da
corrida.
CREATE VIEW cliTaxi AS SELECT *
FROM taxi NATURAL JOIN corrida JOIN cliente ON cliid=id;
- Como recuperar os dados dessa visão?
SELECT * FROM cliTaxi;
placa marca modelo anofab cliid datapedido id nome
DAE6534 Ford Fiesta 1999 1755 2003-02-15 1755 Doriana
JDM8776 Wolkswagen Santana 2002 97 2003-02-18 97 Proj
- Nomes dos clientes que fizeram corridas:
SELECT nome FROM clitaxi;
- modelos de carros que fizeram corridas: SELECT modelo FROM clitaxi;
- Data de corrida mais recente
SELECT max(datapedido) FROM clitaxi;
- CNPj de clientes que fizeram corrida:
SELECT cnpj FROM clitaxi AS t INNER JOIN cliente_empresa as c ON 
c.id=t.cliid;
Exemplo - Possíveis consultas
Código Armazenado no Banco de 
Dados
 O Modelo Relacional não previa, 
originalmente, a possibilidade de armazenar 
trechos de código no banco de dados. No 
entanto, foi adaptado para permitir a 
definição de
– Stored Procedures: trechos de código escritos em 
linguagem SQL, armazenados no BD, e que 
podem ser ativados a partir de aplicações-cliente, 
comandos SQL, outras stored procedures, etc.
– Triggers: trechos de código armazenados no BD 
ativados automaticamente após determinados 
eventos
Código Armazenado no Banco de 
Dados
 No Oracle, os trechos de código 
armazenado (triggers, stored 
procedures e stored functions) são 
criados utilizando-se a linguagem PL-
SQL
 PL-SQL é uma linguagem de 
programação de código procedural
Exemplo de Trigger
create trigger t_itens_pedidos after insert or update or 
delete on pedidos_produtos for each row
begin
if inserting or updating then
update pedidos
set valor_total = valor_total + :new.valor * 
:new.quantidade
where num_pedido = :new.num_pedido;
endif;
if deleting or updating then
update pedidos
set valor_total = valor_total - :old.valor * 
:old.quantidade
where num_pedido = :old.num_pedido;
endif;
end;
/
Exemplo de Stored Procedure
create procedure reajusta_precos (percentual in 
number) as
begin
update produtos
set preco = preco * (1 + percentual/100);
end
/
Comando CREATE INDEX
 Para criar índices deve ser utilizado o 
comando CREATE INDEX
– CREATE [UNIQUE] INDEX nome ON 
tabela(colunas);
– create index idx_usu_nome on 
usuarios (nome);
 O comando DROP INDEX pode ser 
utilizado para eliminar um índice
Quando Criar Índices?
 A coluna é usada freqüentemente nas 
cláusulas WHERE ou em joins
 A coluna contém inúmeros valores 
distintos
 A coluna contém muitos NULL
 A tabela tem muitas linhas e as 
consultas normalmente retornam 
poucas linhas
Quando não Criar Índices?
 A tabela é pequena
 As colunas não aparecem em 
condições ou joins
 As consultas retornam muitas linhas
 A tabela é atualizada muito 
freqüentemente
Utilizando Sinônimos
 Sinônimos são nomes alternativos pelos 
quais podem ser conhecidos os objetos do 
banco de dados (tabelas, visões, sequences, 
stored procedures, etc.)
 Sinônimos podem ser públicos, acessíveis 
por todos os usuários, ou privados
– CREATE [PUBLIC] SYNONYM nome FOR 
objeto;
– create public synonym usuarios for 
cursosql.usuarios;
 O comando DROP SYNONYM pode ser 
utilizado para eliminar um sinônimo
Exercícios (MySQL)
1. Crie um BD com nome Clinica
2. Crie as seguintes tabelas neste BD, considerando que os atributos 
sublinhados são chaves primárias e os em itálico são chaves 
estrangeiras:
– Ambulatorios: nroa (int), andar (numeric(3)) (não nulo), 
capacidade (smallint)
– Medicos: codm (int), nome (varchar(40)) (não nulo), idade 
(smallint) (não nulo), especialidade (char(20)), CPF (numeric(11)) 
(único), cidade (varchar(30)), nroa (int) 
– Pacientes: codp (int), nome (varchar(40)) (não nulo), idade 
(smallint) (não nulo), cidade (char(30)), CPF (numeric(11)) (único), 
doenca (varchar(40)) (não nulo)
– Funcionarios: codf (int), nome (varchar(40)) (não nulo), idade 
(smallint), CPF (numeric(11)) (único), cidade (varchar(30)), salario 
(numeric(10)), cargo (varchar(20))
– Consultas: codm (int), codp (int), data (date), hora (time)
3. Crie a coluna nroa (int) na tabela Funcionarios
4. Crie os seguintes índices:
– Medicos: CPF (único)
– Pacientes: doenca
5. Remover o índice doenca em Pacientes 
6. Remover as colunas cargo e nroa da tabela de Funcionarios
Exercícios (MySQL)
Popular as tabelas:
Ambulatorios
nroa andar capacidade
1 1 30
2 1 50
3 2 40
4 2 25
5 2 55
Medicos
codm nome idade especialidade CPF cidade nroa
1 Joao 40 ortopedia 10000100000 Florianopolis 1
2 Maria 42 traumatologia 10000110000 Blumenau 2
3 Pedro 51 pediatria 11000100000 São José 2
4 Carlos 28 ortopedia 11000110000 Joinville
5 Marcia 33 neurologia 11000111000 Biguacu 3
Funcionarios
codf nome idade cidade salario CPF
1 Rita 32 Sao Jose 1200 20000100000
2 Maria 55 Palhoca 1220 30000110000
3 Caio 45 Florianopolis 1100 41000100000
4 Carlos 44 Florianopolis 1200 51000110000
5 Paula 33 Florianopolis 2500 61000111000
Pacientes
codp nome idade cidade CPF doenca
1 Ana 20 Florianopolis 20000200000 gripe
2 Paulo 24 Palhoca 20000220000 fratura
3 Lucia 30 Biguacu 22000200000 tendinite
4 Carlos 28 Joinville 11000110000 sarampo
Consultas
codm codp data hora
1 1 2006/06/12 14:00
1 4 2006/06/13 10:00
2 1 2006/06/13 9:00
2 2 2006/06/13 11:00
2 3 2006/06/14 14:00
2 4 2006/06/14 17:00
3 1 2006/06/19 18:00
3 3 2006/06/12 10:00
3 4 2006/06/19 13:00
4 4 2006/06/20 13:00
4 4 2006/06/22 19:30
Exercícios (MySQL)
1) O paciente Paulo mudou-se para Ilhota
2) A consulta do médico 1 com o paciente 4 passou 
para às 12:00 horas do dia 4 de Julho de 2006
3) A paciente Ana fez aniversário e sua doença agora é 
cancer
4) A consulta do médico Pedro (codf= 3) com o paciente 
Carlos (codf = 4) passou para uma hora e meia depois
5) O funcionário Carlos (codf = 4) deixou a clínica
6) As consultas marcadas após as 19 horas foram 
canceladas
7) Os pacientes com câncer ou idade inferior a 10 anos 
deixaram a clínica
8) Os médicos que residem em Biguacu e Palhoca 
deixaram a clínica
Realizar as seguintes atualizações no BD:
Exercícios
Realizar as seguintes consultas no BD:
1) Buscar o nome e o CPF dos médicos com menos de 40 anos ou com especialidade
diferente de traumatologia
2) Buscar todos os dados das consultas marcadas no período da tarde após o dia
19/06/2006
3) Buscar o nome e a idade dos pacientes que não residem em Florianópolis
4) Buscar a hora das consultas marcadas antes do dia 14/06/2006 e depois do dia
20/06/2006
5) Buscar o nome e a idade (em meses) dos pacientes
6) Em quais cidades residem os funcionários?
7) Qual o menor e o maior salário dos funcionários da Florianópolis?
10) Qual o horário da última consulta marcada para o dia 13/06/2006?
11) Qual a média de idade dos médicos e o total de ambulatórios atendidos por eles?
12) Buscar o código, o nome e o salário líquido dos funcionários. O salário líquido é
obtido pela diferença entre o salário cadastrado menos 20% deste mesmo salário
13) Buscar o nome dos funcionários que terminam com a letra “a”
14) Buscar o nome e CPF dos funcionários que não possuam a seqüência “00000” em
seus CPFs
15) Buscar o nome e a especialidade dos médicos cuja segunda e a última letra de
seus nomes seja a letra “o”
16) Buscar os códigos e nomes dos pacientes com mais de 25 anos que estão com
tendinite, fratura, gripe e sarampo
Exercícios
Defina cada uma das seguintes buscas através de um produto, de uma junção (e de uma 
junção natural, quando possível). Quando necessário, utilizar junção externa:
1) nome e CPF dos médicos que também são pacientes do hospital
2) pares (código, nome) de funcionários e de médicos que residem na mesma cidade
3) código e nome dos pacientes com consulta marcada para horários após às 14 horas 
4) número e andar dos ambulatórios utilizados por médicos ortopedistas
5) nome e CPF dos pacientes que têm consultas marcadas entre os dias 14 e 16 de junho 
de 2006
6) nome e idade dos médicos que têm consulta com a paciente Ana
7) código e nome dos médicos que atendem no mesmo ambulatório do médico Pedro e 
que possuem consultas marcadas para dia 14/06/2006
8) nome, CPF e idade dos pacientes que têm consultas marcadas com ortopedistas para 
dias anteriores ao dia 16
9) nome e salário dos funcionários que moram na mesma cidade do funcionário Carlos e 
possuem salário superior ao dele
10) dados de todos os ambulatórios e, para aqueles ambulatórios onde médicos dão 
atendimento, exibir também os seus códigos e nomes
11) CPF e nome de todos os médicos e, para aqueles médicos com consultas marcadas, 
exibir os CPFs e nomes dos seus pacientes e as datas das consultas
Exercícios
Resolva o que se pede utilizando subconsultas IN:
1) nome e CPF dos médicos que também são pacientes do hospital
2) código e nome dos pacientes com consulta marcada para horários após às 14 horas
3) nome e idade dos médicos que têm consulta com a paciente Ana
4) número e andar dos ambulatórios onde nenhum médico dá atendimento
5) nome, CPF e idade dos pacientes que têm consultas marcadas sempre para dias 
anteriores ao dia 16
Resolva o que se pede utilizando subconsultas ANY e/ou ALL:
1) números e andares de todos os ambulatórios, exceto o de menor capacidade
2) nome e idade dos médicos que têm consulta com a paciente Ana
3) nome e a idade do médico mais jovem (sem usar função MIN!)
4) nome e CPF dos pacientes com consultas marcadas para horários anteriores a todos 
os horários de consultas marcadas para o dia 12 de Novembro de 2006
5) nome e CPF dos médicos que não atendem em ambulatórios com capacidade 
superior à capacidade dos ambulatórios do segundo andar
Resolva o que se pede utilizando subconsultas EXISTS:
1) nome e CPF dos médicos que também são pacientes do hospital
2) nome e idade dos médicos que têm consulta com a paciente Ana
3) número do ambulatório com a maior capacidade (sem usar função MAX!)
4) nome e CPF dos médicos que têm consultas marcadas com todos os pacientes
5) nome e CPF dos médicos ortopedistas que têm consultas marcadas com todos os 
pacientes de Florianópolis
CREATE TABLE tipos_telefones (
cod_tipo_telefone NUMBER ( 2 ) NOT NULL,
descricao VARCHAR2 ( 20 ) NOT NULL,
CONSTRAINT PK_TIPOS_TELEFONES PRIMARY KEY (cod_tipo_telefone)
) TABLESPACE TSP_CURSOSQL
/
CREATE TABLE administradores (
cod_administrador NUMBER ( 6 ) NOT NULL,
nivel_privilegio NUMBER ( 1 ) NOT NULL,
CONSTRAINT PK_ADMINISTRADORES PRIMARY KEY (cod_administrador)
) TABLESPACE TSP_CURSOSQL
/
CREATE TABLE clientes_enderecos (
cod_cliente NUMBER ( 6 ) NOT NULL,
cod_endereco NUMBER ( 2 ) NOT NULL,
data_cadastro DATE NOT NULL,
CONSTRAINT PK_CLIENTES_ENDERECOS PRIMARY KEY (cod_cliente, cod_endereco)
) TABLESPACE TSP_CURSOSQL
/
CREATE TABLE produtos (
cod_produto NUMBER ( 5 ) NOT NULL,
titulo VARCHAR2 ( 200 ) NOT NULL,
ano_lancamento DATE NOT NULL,
importado CHAR ( 1 ) NOT NULL,
preco NUMBER ( 10, 2 ) NOT NULL,
prazo_entrega NUMBER ( 3 ) NOT NULL,
CONSTRAINT PK_PRODUTOS PRIMARY KEY (cod_produto),
CONSTRAINT CHK_PROD_IMPORTADO CHECK (importado in ('S','N'))
) TABLESPACE TSP_CURSOSQL
/
CREATE TABLE enderecos (
cod_endereco NUMBER ( 2 ) NOT NULL,
rua VARCHAR2 ( 30 ) NOT NULL,
numero NUMBER ( 5 ) NOT NULL,
complemento VARCHAR2 ( 20 ),
cod_cidade NUMBER ( 4 ) NOT NULL,
cep CHAR ( 8 ) NOT NULL,
CONSTRAINT PK_ENDERECOS PRIMARY KEY (cod_endereco)
) TABLESPACE TSP_CURSOSQL
/
CREATE TABLE telefones (
cod_cliente NUMBER ( 6 ) NOT NULL,
cod_telefone NUMBER ( 2 ) NOT NULL,
cod_tipo_telefone NUMBER ( 2 ) NOT NULL,
ddd NUMBER ( 3 ),
numero VARCHAR2 ( 10 ) NOT NULL,
CONSTRAINT PK_TELEFONES PRIMARY KEY (cod_cliente, cod_telefone)
) TABLESPACE TSP_CURSOSQL
/
CREATE TABLE clientes (
cod_cliente NUMBER ( 6 ) NOT NULL,
data_nascimento DATE,
data_cadastro DATE NOT NULL,
CONSTRAINT PK_CLIENTES PRIMARY KEY (cod_cliente)
) TABLESPACE TSP_CURSOSQL
/
CREATE TABLE estados (
uf CHAR ( 2 ) NOT NULL,
nome VARCHAR2 ( 20 ) NOT NULL,
regiao CHAR ( 2 ) NOT NULL,
CONSTRAINT PK_ESTADOS PRIMARY KEY (uf)
) TABLESPACE TSP_CURSOSQL
/
CREATE TABLE usuarios (
cod_usuario NUMBER ( 6 ) NOT NULL,
nome VARCHAR2 ( 100 ) NOT NULL,
cpf CHAR ( 11 ) NOT NULL,
email VARCHAR2 ( 40 ) NOT NULL,
username VARCHAR2 ( 20 ) NOT NULL,
password VARCHAR2 ( 20 ) NOT NULL,
CONSTRAINT PK_USUARIOS PRIMARY KEY (cod_usuario),
CONSTRAINT AK_USU_CPF UNIQUE (cpf),
CONSTRAINT AK_USU_USERNAME UNIQUE (username)
) TABLESPACE TSP_CURSOSQL
/
CREATE TABLE cidades (
cod_cidade NUMBER ( 4 ) NOT NULL,
nome VARCHAR2 ( 40 ) NOT NULL,
uf CHAR ( 2 ) NOT NULL,
CONSTRAINT PK_CIDADES PRIMARY KEY (cod_cidade)
) TABLESPACE TSP_CURSOSQL
/
CREATE TABLE pedidos_produtos (
num_pedido NUMBER ( 7 ) NOT NULL,
cod_produto NUMBER ( 5 ) NOT NULL,
quantidade NUMBER ( 3 ) NOT NULL,
valor_unitario NUMBER ( 10, 2 ) NOT NULL,
CONSTRAINT PK_PEDIDOS_PRODUTOS PRIMARY KEY (num_pedido, cod_produto)
) TABLESPACE TSP_CURSOSQL
/
CREATE TABLE autores (
cod_autor NUMBER ( 4 ) NOT NULL,
nome VARCHAR2 ( 100 ) NOT NULL,
descricao VARCHAR2 ( 1024 ),
CONSTRAINT PK_AUTORES PRIMARY KEY (cod_autor)
) TABLESPACE TSP_CURSOSQL
/
CREATE TABLE pedidos (
num_pedido NUMBER ( 7 ) NOT NULL,
cod_cliente NUMBER ( 6 ) NOT NULL,
cod_endereco NUMBER ( 2 ) NOT NULL,
data_emissao DATE NOT NULL,
CONSTRAINT PK_PEDIDOS PRIMARY KEY (num_pedido)
) TABLESPACE TSP_CURSOSQL
/
CREATE TABLE autores_produtos (
cod_autor NUMBER ( 4 ) NOT NULL,
cod_produto NUMBER ( 5 ) NOT NULL,
CONSTRAINT PK_AUTORES_PRODUTOS PRIMARY KEY (cod_autor, cod_produto)
) TABLESPACE TSP_CURSOSQL
/
ALTER TABLE administradores ADD ( CONSTRAINT FK_USU_ADM FOREIGN KEY 
(cod_administrador) REFERENCES usuarios (cod_usuario))
/
ALTER TABLE clientes_enderecosADD ( CONSTRAINT FK_CLI_CLIEND FOREIGN KEY 
(cod_cliente) REFERENCES clientes (cod_cliente))
/
ALTER TABLE clientes_enderecos ADD ( CONSTRAINT FK_END_CLIEND FOREIGN KEY (cod_endereco)
REFERENCES enderecos (cod_endereco))
/
ALTER TABLE enderecos ADD ( CONSTRAINT FK_CID_END FOREIGN KEY (cod_cidade)
REFERENCES cidades (cod_cidade))
/
ALTER TABLE telefones ADD ( CONSTRAINT FK_CLI_TEL FOREIGN KEY (cod_cliente)
REFERENCES clientes (cod_cliente))
/
ALTER TABLE telefones ADD ( CONSTRAINT FK_TIPTEL_TEL FOREIGN KEY (cod_tipo_telefone)
REFERENCES tipos_telefones (cod_tipo_telefone))
/
ALTER TABLE clientes ADD ( CONSTRAINT FK_USU_CLI FOREIGN KEY (cod_cliente)
REFERENCES usuarios (cod_usuario))
/
ALTER TABLE cidades ADD ( CONSTRAINT FK_EST_CID FOREIGN KEY (uf)
REFERENCES estados (uf))
/
ALTER TABLE pedidos_produtos ADD ( CONSTRAINT FK_PED_PEDPROD FOREIGN KEY (num_pedido)
REFERENCES pedidos (num_pedido))
/
ALTER TABLE pedidos_produtos ADD ( CONSTRAINT FK_PROD_PEDPROD FOREIGN KEY (cod_produto)
REFERENCES produtos (cod_produto))
/
ALTER TABLE pedidos ADD ( CONSTRAINT FK_CLIEND_PED FOREIGN KEY (cod_cliente, cod_endereco)
REFERENCES clientes_enderecos (cod_cliente, cod_endereco))
/
ALTER TABLE autores_produtos ADD ( CONSTRAINT FK_AUT_AUTPROD FOREIGN KEY (cod_autor)
REFERENCES autores (cod_autor))
/
ALTER TABLE autores_produtos ADD ( CONSTRAINT FK_PRD_AUTPROD FOREIGN KEY (cod_produto)
REFERENCES produtos (cod_produto))
/