Sql structured query language
This presentation is the property of its rightful owner.
Sponsored Links
1 / 65

SQL Structured Query Language PowerPoint PPT Presentation


  • 48 Views
  • Uploaded on
  • Presentation posted in: General

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)

Download Presentation

SQL Structured Query Language

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


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

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


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


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 nome_jogador FROM jogador WHERE posicao_jog = ‘Atacante’

    INTERSECT

    SELECT DISTINCT nome_jogador FROM jogador WHERE codigo_equipe = 2;


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

  • 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 NOME_EQUIPE

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

    Fabio Simplicio Sao Paulo

    Luis Fabiano Sao Paulo


  • Login