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

SQL Structured Query Language PowerPoint PPT Presentation


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


Sql structured query language

SQLStructured Query Language

Juliano Brito da Justa Neves

PESCD – Programa de Estágio Supervisionado de Capacitação Docente


Sql structured query language

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


  • Login