jogador - DC

SQL
Structured Query Language
Juliano Brito da Justa Neves
PESCD – Programa de Estágio
Supervisionado de Capacitação Docente
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,cida
de,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
7
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