Sql structured query language
Download
1 / 65

SQL Structured Query Language - PowerPoint PPT Presentation


  • 67 Views
  • Uploaded on

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)

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' SQL Structured Query Language' - deiter


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Sql structured query language

SQLStructured 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
Esquema

  • Agrupar tabelas e outros elementos (constraints, views...) que pertencem a mesma aplicação de banco de dados

    CREATE SCHEMA <esquema> AUTHORIZATION <autorização> ;


Esquema1
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]+ ;


Esquema2
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
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
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
Tabelas

CREATE TABLE [esquema.]tabela (

coluna1 tipo_dado [DEFAULT expr]

[constraint_coluna],

...

colunaN tipo_dado [DEFAULT expr]

[constraint_coluna],

[constraint_tabela]

);


Tipos de dados
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
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
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)

);


Exemplo1
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


Exemplo2
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)

);


Exemplo3
Exemplo

PARTIDA

(#Codigo_Partida,Cidade,Estado,Nome_Juiz,Data)

Codigo_partida  INTEGER

cidade  Varchar2(10)

estado  Varchar2(10)

nome_juiz  Varchar2(20)

data  date


Exemplo4
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
Mais Constraints!

  • ON DELETE

    • SET NULL

    • CASCADE

    • SET DEFAULT

  • ON UPDATE

    • SET NULL

    • CASCADE

    • SET DEFAULT


Exemplo5
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


Exemplo6
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
Alteração de tabelas

  • Criar, alterar ou eliminar colunas

  • Renomear a tabela

  • Criar ou eliminar constraints

  • Habilitar ou desabilitar constraints


Altera o de tabelas1
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] ;


Exemplo7
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
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
Í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;


Exemplo8
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
Linguagem de manipulação de dados

  • Inserindo dados

    INSERT INTO [esquema.]tabela

    (coluna1, coluna2, ... colunaN)

    VALUES

    (valor1, valor2 ... valorN);


Inserindo dados
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);


Exemplo9

s S s

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’);


Exemplo10
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 dados1
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
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
Apagando dados

DELETE [FROM] tabela

[WHERE condição];

  • Exemplo: Apagar Enc97FC

    DELETE FROM equipe WHERE codigo_equipe = 3;


Apagando dados1
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
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
Selecionando dados

  • Forma básica:

    SELECT <lista de atributos>

    FROM <lista de tabelas>

    WHERE <condição>


Selecionando dados1
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
Álgebra Relacional

  • π(*) jogador

    SELECT DISTINCT * FROM jogador;

  • σ(posicao_jog = atacante) jogador

    SELECT * FROM jogador WHERE posicao_jog = ‘Atacante’;


Lgebra relacional1
Á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
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
Interseção

  • R1  πnome_jogador(σ(posicao_jog = Atacante)Jogador)

  • R2  πnome_jogador(σ(codigo_equipe = 2)Jogador)

  • Resultado  R1 ∩ R2

    SELECT DISTINCT nome_jogador FROM jogador WHERE posicao_jog = ‘Atacante’

    INTERSECT

    SELECT DISTINCT nome_jogador FROM jogador WHERE codigo_equipe = 2;


Subtra o
Subtração

  • R1  πnome_jogador(σ(posicao_jog = Atacante)Jogador)

  • R2  πnome_jogador(σ(codigo_equipe = 2)Jogador)

  • Resultado  R1 – R2

    SELECT DISTINCT nome_jogador FROM jogador WHERE posicao_jog = ‘Atacante’

    MINUS

    SELECT DISTINCT nome_jogador FROM jogador WHERE codigo_equipe = 2;


Produto cartesiano
Produto Cartesiano

  • R1  πnome_jogador(π(*) Jogador)

  • R2  πnome_equipe(π(*) Equipe)

  • Resultado  R1 × R2

    SELECT DISTINCT nome_jogador, nome_equipe

    FROM jogador, equipe;


Join equijoin
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
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
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
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
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 o1
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
Outras consultas

  • Cobrimos todos os operadores da álgebra relacional.

  • Veremos agora outros tipos de consultas comuns em SGBDs relacionais.


Consultas teis
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 teis1
Consultas úteis

  • DISTINCT

    SELECT DISTINCT posicao_jog FROM jogador;

  • LIKE

    SELECT nome_jogador FROM jogador WHERE nome_jogador LIKE ‘%Luis%’;


Consultas teis2
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 teis3
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 teis4
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 teis5
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 teis6
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 teis7
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
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 desafios1
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 desafios2
Exercícios (desafios)

  • Selecione os nomes das equipes que fizeram gols nos jogos apitados por Silvia

    NOME_EQUIPE

    --------------------

    Sao Paulo


Exerc cios desafios3
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 desafios4
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 desafios5
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 desafios6
Exercícios (desafios)

  • Quantos gols cada equipe já fez?

    NOME_EQUIPE SUM(NUMERO_GOLS)

    -------------------- ----------------

    Corinthians 2

    Santos 6

    Sao Paulo 7


Exerc cios desafios7
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 desafios8
Exercícios (desafios)

  • Quem são, para que time jogam, os jogadores que fizeram gols em mais de um jogo?

    NOME_JOGADOR NOME_EQUIPE

    -------------------- --------------------

    Fabio Simplicio Sao Paulo

    Luis Fabiano Sao Paulo


ad