jogador - DC

Exercícios SQL
Marilde Santos
Sumário
• Revisão SQL
– Conjunto de slides disponibilizado por Juliano
Neves.
• Restrições, Datas e mais SQL
• Exercícios Laboratório de Banco de Dados
– Controle acadêmico
– Controle bancário
• Dicas
SQL
Structured Query Language
Juliano Brito da Justa Neves
PESCD – Programa de Estágio Supervisionado
de Capacitação Docente
2003
SQL
• Linguagem padrão para SGBDs relacionais
– Motivo pelo grande sucesso dos SGBDs relacionais
• Linguagem de Definição de Dados (DDL)
• Linguagem de Manipulação de Dados (DML)
Esquema
• Agrupar tabelas e outros elementos (constraints,
views...) que pertencem a mesma aplicação de
banco de dados
CREATE SCHEMA <esquema> AUTHORIZATION
<autorização> ;
Esquema
• Esquemas no Oracle
– Criar várias tabelas e views e dar permissões
em uma única transação.
CREATE SCHEMA AUTHORIZATION
<esquema> [create_table,
create_view, grant]+ ;
Esquema
• Esquemas no Oracle
– O CREATE SCHEMA não cria realmente um
esquema. O esquema é automaticamente criado
quando um usuário é criado.
• Vamos utilizar um dos usuários previamente
criados.
Oracle
• Conexão com o SGBD Oracle
–
–
–
–
Putty
SSH: falcon.comp.ufscar.br
Login: Pessoal de cada um
No prompt do linux:
• sqlplus
• Login: compX  X número de 1 a 20
• Senha: igual ao login
Estudo de Caso
EQUIPE
(#Codigo_Equipe,Nome_Equipe,Cidade,Estado)
JOGADOR
(#Codigo_Jogador,Nome_Jogador,Posição_Jog,#Codigo_Equipe)
PARTIDA
(#Codigo_Partida,Cidade,Estado,Nome_Juiz,Data)
JOGA
(#Codigo_Jogador,#Codigo_Partida,Numero_Gols)
Tabelas
CREATE TABLE [esquema.]tabela (
coluna1 tipo_dado [DEFAULT expr]
[constraint_coluna],
...
colunaN tipo_dado [DEFAULT expr]
[constraint_coluna],
[constraint_tabela]
);
Tipos de Dados
•
•
•
•
•
•
•
•
•
•
•
•
Integer, Float, Real...
Char (n)
Varchar2 (n)
Clob
Long
Blob
Raw e Long Raw
Number (p,e)
Date
Timestamp
Interval Year (p) to month
Interval Day (dp) to second (sp)
Constraints
• Grupo 1
– NOT NULL
– Unique
• Grupo 2
– Check
– Primary key
– Foreign key
CONSTRAINT nome tipo expr
constraint cod Primary Key
constraint fcod Foreign Key references
tabela(coluna)
constraint chk Check (uf in (‘SP’,
‘MG’))
Exemplo
EQUIPE
(#Codigo_Equipe,Nome_Equipe,Cidade,Estado)
CREATE TABLE equipe (
codigo_equipe INTEGER constraint
equipe_pk Primary Key,
nome_equipe Varchar2(20) NOT NULL,
cidade Varchar2(10),
estado Varchar2(10)
);
Exemplo
JOGADOR
(#Codigo_Jogador,Nome_Jogador,Posição_Jog,#Codigo_Equipe)
Codigo_jogador  INTEGER
Nome_jogador  Varchar2(20)
Posicao_jog  Varchar2(15)
Codigo_Time  INTEGER
Exemplo
JOGADOR
(#Codigo_Jogador,Nome_Jogador,Posição_Jog,Codigo_equipe)
CREATE TABLE jogador (
codigo_jogador INTEGER constraint jogador_pk Primary
Key,
nome_jogador Varchar2(20) NOT NULL,
posicao_jog Varchar2(15),
codigo_equipe INTEGER,
constraint jogador_fk Foreign Key (codigo_equipe)
references equipe(codigo_equipe)
);
Exemplo
PARTIDA
(#Codigo_Partida,Cidade,Estado,Nome_Juiz,Data)
Codigo_partida  INTEGER
cidade  Varchar2(10)
estado  Varchar2(10)
nome_juiz  Varchar2(20)
data  date
Exemplo
PARTIDA
(#Codigo_Partida,Cidade,Estado,Nome_Juiz,Data)
CREATE TABLE partida (
codigo_partida INTEGER constraint
partida_pk Primary Key,
cidade Varchar2(10) NOT NULL,
estado Varchar2(10),
nome_juiz Varchar2(20) NOT NULL,
data Date
);
Mais Constraints!
• ON DELETE
– SET NULL
– CASCADE
– SET DEFAULT
• ON UPDATE
– SET NULL
– CASCADE
– SET DEFAULT
Exemplo
JOGA
(#Codigo_Jogador,#Codigo_Partida,Numero
_Gols)
codigo_jogador  Integer
Se apagar jogador, apaga.
codigo_partida  Integer
Se apagar partida, apaga.
numero_gols  Integer
Exemplo
JOGA
(#Codigo_Jogador,#Codigo_Partida,Numero_Gols)
CREATE TABLE joga (
codigo_jogador integer,
constraint joga1_fk foreign key (codigo_jogador)
references jogador(codigo_jogador)
on delete cascade,
codigo_partida integer,
constraint joga2_fk foreign key (codigo_partida)
references partida(codigo_partida)
on delete cascade,
numero_gols integer
);
Alteração de tabelas
•
•
•
•
Criar, alterar ou eliminar colunas
Renomear a tabela
Criar ou eliminar constraints
Habilitar ou desabilitar constraints
Alteração de Tabelas
ALTER TABLE [esquema.]tabela
[add coluna tipo_dado [DEFAULT expr]
[constraint_coluna] ]
[modify coluna tipo_dado [DEFAULT expr]
[constraint_coluna] ]
[add constraint_coluna/constraint_tabela]
[drop constraint_coluna/constraint_tabela
[cascade]]
[enable constraint_coluna/constraint_tabela]
[disable constraint_coluna/constraint_tabela] ;
Exemplo
• Alterar a tabela joga para que o valor default de
Numero_gols seja 0;
ALTER TABLE joga MODIFY numero_gols
INTEGER DEFAULT ‘0’;
• Adicionar uma Primary Key em joga
ALTER TABLE joga ADD constraint
joga_pk Primary Key
(codigo_jogador, codigo_partida);
Apagando Tabelas
DROP TABLE [esquema.]tabela
[CASDADE CONSTRAINTS];
• CASCADE CONSTRAINTS elimina todas
as restrições presentes em outras tabelas que
façam referência à tabela que está sendo
eliminada.
Índices
• Criar
CREATE [UNIQUE] INDEX índice ON
tabela (coluna [ASC | DESC]);
• UNIQUE  Índice não aceita valores repetidos.
• É criado um índice UNIQUE sempre que uma
Primary Key é criada.
• Apagando o índice
DROP INDEX índice;
Exemplo
select index_name from
user_indexes;
CREATE UNIQUE INDEX my_index ON
partida (nome_juiz);
select index_name from
user_indexes;
Linguagem de manipulação de dados
• Inserindo dados
INSERT INTO [esquema.]tabela
(coluna1, coluna2, ... colunaN)
VALUES
(valor1, valor2 ... valorN);
Inserindo dados
• Se for inserir na mesma ordem da definição
da tabela:
INSERT INTO [esquema.]tabela
VALUES (valor1, ... valorN);
• Inserção em determinados campos
INSERT INTO [esquema.]tabela
(colunaX, colunaY) VALUES
(valorX, valorY);
Exemplo
• Criar o time Saravá Saci Soccer
INSERT INTO equipe
(codigo_equipe,nome_equipe,cidade,
estado) VALUES (1,’SSS’,’São
Carlos’, ‘São Paulo’);
Opção
INSERT INTO equipe VALUES (1,
‘SSS’, ‘São Carlos’, ‘São Paulo’);
Exemplo
• Inserir time “Tiradentes”, de Brasília, DF
INSERT INTO equipe VALUES
(2,’Tiradentes’,’Brasilia’,’DF’);
• Inserir time “Enc97FC”
INSERT INTO equipe (codigo_equipe,
nome_equipe) VALUES (3,’Enc97FC’);
Inserindo dados
• Cuidados com as restrições (constraints)!
INSERT INTO JOGADOR VALUES
(1,’Juliano’,’goleiro’,4);
INSERT INTO equipe
(codigo_equipe, nome_equipe)
VALUES (4, ‘Selecao’);
INSERT INTO JOGADOR VALUES
(1,’Juliano’,’goleiro’,4);
Atualizando dados
UPDATE tabela SET coluna =
valor [, coluna = valor...]
[WHERE condição];
• Exemplo: Juliano mudou para atacante!
UPDATE jogador SET posicao_jog
= ‘atacante’ where
codigo_jogador = 1;
Apagando dados
DELETE [FROM] tabela
[WHERE condição];
• Exemplo: Apagar Enc97FC
DELETE FROM equipe WHERE
codigo_equipe = 3;
Apagando dados
• Cuidados com as restrições (constraints)!
DELETE FROM equipe WHERE
codigo_equipe = 4; FALHA!
DELETE jogador;
DELETE FROM equipe WHERE
codigo_equipe = 4; OK!
Consultando dados: SELECT
• Até o final da aula!
• Álgebra relacional
• Endereço do script
http://www.dc.ufscar.br/~juliano
• Execução do script
sqlplus> @ sql.txt
Selecionando dados
• Forma básica:
SELECT <lista de atributos>
FROM
<lista de tabelas>
WHERE <condição>
Selecionando dados
SELECT [DISTINCT] {*, colunas
[AS alias], expressões, funções..}
FROM {tabelas [AS alias]}
[WHERE condição]
[GROUP BY colunas]
[HAVING condição]
[ORDER BY colunas [ASC | DESC]];
Álgebra Relacional
• π(*) jogador
SELECT DISTINCT * FROM jogador;
• σ(posicao_jog = atacante) jogador
SELECT * FROM jogador WHERE
posicao_jog = ‘Atacante’;
Álgebra Relacional
• πnome_jogador(σ(posicao_jog = Atacante)Jogador)
SELECT DISTINCT nome_jogador FROM
jogador where posicao_jog =
‘Atacante’;
• ρNome(πnome_jogador(σ(posicao_jog = Atacante)Jogador))
SELECT DISTINCT nome_jogador AS
Nome FROM jogador where
posicao_jog = ‘Atacante’;
União
• R1  πnome_jogador(σ(posicao_jog = Atacante)Jogador)
• R2  πnome_jogador(σ(codigo_equipe = 2)Jogador)
• Resultado  R1 U R2
SELECT DISTINCT nome_jogador FROM
jogador WHERE posicao_jog =
‘Atacante’
UNION
SELECT DISTINCT nome_jogador FROM
jogador WHERE codigo_equipe = 2;
Interseção
• R1  πnome_jogador(σ(posicao_jog = Atacante)Jogador)
• R2  πnome_jogador(σ(codigo_equipe = 2)Jogador)
• Resultado  R1 ∩ R2
SELECT DISTINCT
jogador WHERE
‘Atacante’
INTERSECT
SELECT DISTINCT
jogador WHERE
nome_jogador FROM
posicao_jog =
nome_jogador FROM
codigo_equipe = 2;
Subtração
• R1  πnome_jogador(σ(posicao_jog = Atacante)Jogador)
• R2  πnome_jogador(σ(codigo_equipe = 2)Jogador)
• Resultado  R1 – R2
SELECT DISTINCT
jogador WHERE
‘Atacante’
MINUS
SELECT DISTINCT
jogador WHERE
nome_jogador FROM
posicao_jog =
nome_jogador FROM
codigo_equipe = 2;
Produto Cartesiano
• R1  πnome_jogador(π(*) Jogador)
• R2  πnome_equipe(π(*) Equipe)
• Resultado  R1 × R2
SELECT DISTINCT nome_jogador,
nome_equipe
FROM jogador, equipe;
Join (Equijoin)
• R1  π(*) Jogador
• R2  π(*) Equipe
• Resultado  R1 |x|equipe.codigo_equipe =
jogador.codigo_equipe R2
SELECT *
FROM jogador, equipe
WHERE equipe.codigo_equipe =
jogador.codigo_equipe;
Theta Join
• R1  π(*) Jogador
• R2  π(*) Equipe
• Resultado  R1 |x|equipe.codigo_equipe >
jogador.codigo_equipe R2
SELECT *
FROM jogador, equipe
WHERE equipe.codigo_equipe >
jogador.codigo_jogador;
Natural Join
• R1  πnome_jogador, codigo_equipe(π(*) Jogador)
• R2  πnome_equipe, codigo_equipe(π(*) Equipe)
• Resultado  πnome_jogador,nome_equipe( R1 *codigo_equipe
R2)
SELECT DISTINCT nome_jogador,
nome_equipe, codigo_equipe
FROM jogador, equipe
WHERE equipe.codigo_equipe =
jogador.codigo_equipe;
Outer Join ]x|
• R1  πnome_jogador, codigo_equipe(π(*) Jogador)
• R2  πnome_equipe, codigo_equipe(π(*) Equipe)
• Resultado  πnome_jogador,nome_equipe( R1
]x|equipe.codigo_equipe = jogador.codigo_equipe R2)
SELECT nome_jogador, nome_equipe
FROM jogador, equipe
WHERE equipe.codigo_equipe (+) =
jogador.codigo_jogador;
Divisão
• Todos os jogadores que fizeram gol em
partidas onde Fabio Simplicio fez gol
Fabio  σnome_jogador = ‘Fabio Simplicio’(Jogador)
F_Par  πcodigo_partida(Joga |x|joga.codigo_jogador =
Fabio.codigo_jogador Fabio)
Jog_Par  πcodigo_jogador,codigo_partida
(Joga)

Result  Jog_Par F_Par
Divisão
SELECT * FROM jogador WHERE
nome_jogador = ‘Fabio Simplicio’;
SELECT DISTINCT codigo_partida FROM
joga WHERE codigo_jogador = 51;
SELECT DISTINCT codigo_jogador,
codigo_partida FROM joga;
SELECT DISTINCT codigo_jogador FROM
joga WHERE (codigo_partida = 1 OR
codigo_partida = 5) AND
(codigo_jogador != 51);
Outras consultas
• Cobrimos todos os operadores da álgebra
relacional.
• Veremos agora outros tipos de consultas
comuns em SGBDs relacionais.
Consultas úteis
• Várias condições
SELECT nome_jogador FROM Jogador
WHERE posicao_jog = ‘Goleiro’ AND
codigo_equipe != 2;
SELECT nome_jogador FROM Jogador
WHERE posicao_jog = ‘Goleiro’ OR
posicao_jog = ‘Atacante’;
Consultas úteis
• DISTINCT
SELECT DISTINCT posicao_jog
FROM jogador;
• LIKE
SELECT nome_jogador FROM
jogador WHERE nome_jogador
LIKE ‘%Luis%’;
Consultas úteis
• Operadores Matemáticos
SELECT codigo_partida,
numero_gols * 10 FROM joga;
• BETWEEN
SELECT codigo_jogador,
nome_jogador FROM jogador
WHERE codigo_jogador BETWEEN
10 AND 20;
Consultas úteis
• IN
SELECT nome_jogador FROM jogador
WHERE codigo_jogador IN (SELECT
codigo_jogador FROM joga WHERE
numero_gols = 2);
• ALL
SELECT codigo_jogador FROM joga
WHERE numero_gols > ALL (SELECT
numero_gols FROM joga WHERE
codigo_partida = 2);
Consultas úteis
• EXISTS
SELECT nome_jogador FROM jogador
WHERE EXISTS (SELECT * FROM joga
WHERE jogador.codigo_jogador =
joga.codigo_jogador);
• NOT
SELECT nome_jogador FROM jogador
WHERE NOT EXISTS (SELECT * FROM
joga WHERE jogador.codigo_jogador
= joga.codigo_jogador);
Consultas úteis
• SUM, MAX, MIN, AVG
SELECT SUM(numero_gols),
MAX(numero_gols),
MIN(numero_gols),
AVG(numero_gols) FROM joga;
• COUNT
SELECT COUNT(*)FROM jogador
WHERE codigo_equipe = 1;
Consultas úteis
• GROUP BY
SELECT COUNT(*), codigo_equipe FROM
jogador GROUP BY codigo_equipe;
• HAVING
SELECT COUNT(*), codigo_equipe FROM
jogador GROUP BY codigo_equipe
HAVING COUNT(*) > 30;
Consultas úteis
• IS [NOT] NULL
SELECT * FROM partida WHERE
nome_juiz IS NULL;
SELECT * FROM partida WHERE
nome_juiz IS NOT NULL;
Exercícios (desafios)
• Selecione os nomes das equipes que fizeram
gols nos jogos apitados por Silvia
πnome_equipe(σ(nome_juiz = ‘Silvia’)(
(((Partida |x|partida.codigo_partida joga.codigo_partidaJoga)
|x|codigo_jogador = jogador.codigo_jogadorJogador)
|x|codigo_equipe = equipe.codigo_equipeEquipe)
)
Exercícios (desafios)
• Selecione os nomes das equipes que fizeram gols nos jogos apitados
por Silvia
SELECT DISTINCT nome_equipe
FROM equipe e, jogador j, partida p, joga
WHERE p.nome_juiz ='Silvia'
AND p.codigo_partida = joga.codigo_partida
AND joga.codigo_jogador = j.codigo_jogador
AND j.codigo_equipe = e.codigo_equipe;
Exercícios (desafios)
• Selecione os nomes das equipes que fizeram
gols nos jogos apitados por Silvia
NOME_EQUIPE
-------------------Sao Paulo
Exercícios (desafios)
• Daqueles jogadores que marcaram gols, selecione o nome daqueles
que não marcaram gols em Santos
SELECT DISTINCT nome_jogador
FROM jogador j, joga
WHERE joga.codigo_jogador = j.codigo_jogador
AND j.codigo_jogador NOT IN
(SELECT codigo_jogador
FROM joga, partida p
WHERE p.cidade = ‘Santos’
AND joga.codigo_partida = p.codigo_partida);
Exercícios (desafios)
•
Daqueles jogadores que marcaram gols, selecione o nome daqueles que não
marcaram gols em Santos
NOME_JOGADOR
-------------------Anderson
Carlos Alberto
Diego
Fabio Simplicio
Leandro Amaral
Nenem
William
7 linhas selecionadas.
Exercícios (desafios)
• Quantos gols cada equipe já fez?
SELECT nome_equipe,
SUM(numero_gols)
FROM equipe e, joga, jogador j
WHERE e.codigo_equipe =
j.codigo_equipe
AND j.codigo_jogador =
joga.codigo_jogador
GROUP BY nome_equipe;
Exercícios (desafios)
• Quantos gols cada equipe já fez?
NOME_EQUIPE
SUM(NUMERO_GOLS)
-------------------- --------------Corinthians
2
Santos
6
Sao Paulo
Exercícios (desafios)
• Quem são, para que time jogam, os jogadores que
fizeram gols em mais de um jogo?
SELECT nome_jogador, nome_equipe
FROM jogador j, equipe e
WHERE j.codigo_equipe =
e.codigo_equipe
AND (SELECT COUNT(*) FROM joga
WHERE j.codigo_jogador =
joga.codigo_jogador) > 1;
Exercícios (desafios)
• Quem são, para que time jogam, os
jogadores que fizeram gols em mais de um
jogo?
NOME_JOGADOR
-------------------Fabio Simplicio
Luis Fabiano
NOME_EQUIPE
-------------------Sao Paulo
Sao Paulo
Constraints
Uma restrição representa um mecanismo capaz de
implementar controles que garantam a consitência dos
dados (integridade de dados e referencial).
– Pode ser definido em nível de coluna ou em
nível de tabela
– Alternativa: utilização de triggers que
apresentam desempenho menor mas oferecem
toda a flexibilidade de uma linguagem de
programação (PL/SQL).
Tabela de constraints
null
Informa se o campo em questão pode receber valores
nulos. Caso não possa deve ser precedido pela palavra not.
unique
Indica que os valores na coluna não podem ser repeidos.
Cria um índice automaticamente.
check
Determina uma regra de validação (especifica que valores
a coluna pode assumir).
Primary key
Indica a chave primária da relação. Cria um índice
automaticamente.
Foreign key
Identifica uma chave estrangeira da tabela. Implementada
pela cláusula references.
Check
• Pode-se criar regra de validação (constraint
check)
Create table aluno
(….
uf char(2) default ‘RJ’ constraint alunos_uf_ch
check (uf in (‘SP’, ‘MG’, ‘RJ’, ‘ES’))
);
Foreign Key
• Ao definir chaves estrangeiras (foreign key)
– A cláusula references informa que a tabela corrente
estará relacionada com a mencionada na forma de 1
(mencionada) para N (corrente).
– O campo referenciado deve ser chave primária ou
possuir restrição unique e ser do mesmo tipo do campo
corrente.
– Note a impossibilidade da criação de um
relacionamento N:M.
– O relacionamento 1:1 deve ser resolvido por
programação (Triggers).
Alter table
• Para alterar a estrutura de uma tabela:
–
–
–
–
Criar, alterar ou eliminar colunas
Renomear a tablea
Criar ou eliminar constraints
Habilitar ou desabilitar constraints
Sintaxe Alter table
Alter table [schema.]tabela
[add (coluna tipo_dado [default expressão]
[constraint_coluna, …)]
[modify (coluna tipo_dado [default expressão]
[constraint_coluna , …)]
[rename to novo_nome_tabela]
[add constraint_coluna/constraint_tabela]
[drop constraint_coluna/constraint_tabela [cascade]]
[enable constraint_coluna/constraint_tabela]
[disable constraint_coluna/constraint_tabela];
Insert into
• O bd pode ser alimentado via aplicativos
produzidos por ferramentas front-end, tais como:
Oracle Forms, MS-Visual Basic ou Borland
Delphi.
• Via SQL:
Insert into [schema.]tabela Coluna1, coluna2, colunaN
Values (valor1, valor2, valorN);
• Para os dados serem definitivamente gravados
logo após alguns comandos insert emita o
comando commit;
Select
Select [distinct] {*, colunas [alias],
expressões, funções,…}
From {tabelas [alias],}
[Where condição]
[group by colunas]
[having condição]
[order by colunas [asc|desc]];
Funções de Datas
• Add_months(data,n)  adiciona n meses à data
• Months_between(data1, data2)  número de meses entre
2 datas
• Next_day(data, ‘dia_semana’)  uma data, após a data
especificada, em que o dia da semana seja igual ao
dia_semana.
• Last_day(data)  data do último dia do mês em que se
encontra a variável data.
• Sysdate  a data corrente do servidor de BD
• Current_date  data corrente da sessão.
• Extract  permite extrair um campo de uma data (ano,
mês, hora,etc)
Funções de conversão de dados
• To_char(número ou data, ‘formato’)
– Converte um número ou data em uma cadeia de
caracteres do tipo varchar2 com o formato
definido pela variável formato
• To_date(char, [‘formato’])
– Converte uma cadeia de caracteres,
especificando uma data, no tipo date, de acordo
com a variável formato. (default: DD-MM-AA)
Funções de conversão de dados
Select to_char(sysdate, ‘dd/mm/yy hh24:mi’)
“Hoje”, to_char(sysdate+28/24, ‘dd/mmyy
hh24:mi’) “Amanhã + 4 horas”
Hoje
Amanhã + 4 horas
25/03/04 16:23 26/03/04 20:23
Funções de conversão de dados
Select * from instrutor where admissao >
to_date(’31-1-1998’, ‘dd/mm/yyyy’);
Dicionário de Dados
Consiste em um conjunto de tabelas e views que proporcionam um acesso
apenas de leitura a todos os usuários de um banco de dados.
• Prefixo user_
– Contém tosdos os objetos dos quais o usuário é
proprietário. Ex.: user_objects, user_tables,
user_constraints, user_tab_columns.
• Prefixo all_
– Objetos públicos. Ex.: all_objects, all_tables,
all_constraints, all_tab_columns.
Dicionário de Dados
• Dictionary (dict)
– Mostra todas as tabelas e visões do dicionário
de dados
• Tab, cat
– Exibem as tabelas do usuário corrente.
• Col
– Exibe as colunas das tabelas do usuário
corrente
Dicas
• Pode-se criar em editor externo arquivos para
criação, alimentação e consulta em tabelas
(scripts)
– Salvar em arquivos .sql
– Para ativar o arquivo gerado, utilize o comando start ou
@.
– Também é possível abrir o arquivo utilizando o
SQLPlus Worksheet usando o comando Arquivo e
Abrir.
• Todo comando SQL deve ser finalizado por ponto
e vírgula (;)
Dicas
• É recomendável criar primeiro as tabelas sem
constraints e depois incluí-los via alter table.
• Para comprovar a criação de tabelas:
select * from tab;
select * from cat;
• Para verificar como é a tabela (describe):
desc <nome da tabela>
• Para listar todas as constraints criadas pelo usuário
corrente:
select constraint_name from user_constraints;
• Para excluir tabelas:
drop table [schema.]tabela [cascade constraints]
Dicas
O oracle possui uma tabela, de uso comum,
denominada DUAL, que contém uma única coluna
dummy, e uma única linha, com o valor “X”. Esta
tabela é usada quando se deseja retornar um valor
em uma única ocorrência, como o de uma
constante ou expressão que nãos eja derivada de
uma tabela de dados do usuário.
select sysdate Hoje, next_day(sysdate, ‘domingo’)
Domingo from dual;
Hoje
26/03/04
Domingo
28/03/04
Controle Acadêmico
Esquema Relacional
• Aluno(RA, cpf, nro_rg, est_rg, Prenome,
Sobrenome, telefone, endereço, UF,
data_matricula)
• Histórico(cod_turma, RA, nota_final)
• Turma(cod_turma, sala, cod_instrutor, cód_curso)
• Instrutor(código instrutor, cpf, nro_rg, est_rg,
Prenome, Sobrenome, fone, admissão)
• Curso(cód_curso, nome, carga_horaria, preco)
• PreRequisito(cod_curso, pré_requisito)
Exemplo
Aluno(RA, cpf, nro_rg, est_rg, Prenome, sobrenome,
telefone, endereço, UF, data_matricula)
create table aluno
(RA number(3) constraint instr_pk primary key,
cpf varchar2(11) constraint instr_cpf_nul not null,
nro_rg varchar2(10) constraint inst_Nrg_nul not null,
est_rg varchar2(2) constraint inst_Estrg_nul not null,
prenome varchar2(15) constraint inst_pnome_nul not null,
sobrenome varchar2(15) constraint inst_snome_nul not nul,
fone varchar2(10),
endereco varchar2(40),
UF varchar2(2),
data_matricula date default sysdate);
Exercício
• Fazer scripts com:
– Criação de todas as tabelas
– Alimentação da base de dados
• Escrever, em português, consultas úteis a
este sistema
– Resolver tais consultas usando SQL (de
preferência fazer scripts)
Controle Bancário
Pré
CPF
Sobre
nome
rua
end
nro
CEP
cliente
titular
M
nro
saldo
poupança
(T,D)
N
possui
aniversário
conta
N
Limite
crédito
corrente
pertence
1
cod
nome
agência
N
em
1
cod
cidade
estado
nome
Pré
Sobre
rua
nro
CodTrans
CPF
nome
end
valor
CEP
1
cliente
efetua
data
N
movimento
correntista
hora
descrição
titular
poupança
(T,D)
N
possui
M
nro
saldo
tipo
aniversário
conta
N
Limite
crédito
corrente
pertence
1
cod
nome
agência
N
em
1
cod
cidade
estado
nome
Tarefas
• Efetuar o mapeamento para um esquema
relacional
• Criar tabelas no Oracle
• Alimentar o banco de dados
• Listar um conjunto de consultas
interessantes para esse sistema
• Executar tais consultas no banco de dados
Tarefas
• Efetuar as seguintes consultas nas relações:
– Listar as contas corrente conjuntas e seus
respectivos correntistas onde o titular da conta
tem o sobrenome “Silva”
– Listar a movimentação (descrição, tipo, valor,
data) da(s) conta(s) corrente cujo titular é
“André Silva” no período entre 01/03/2004 e
26/03/2004.