SQL92 DDL( RIS, ACTUALIZAÇÕES E VISTAS) DML (QUERIES, SUBQUERIES,JUNÇÕES, E OPERAÇÕES SOBRE CONJUNTOS)

SQL92

DDL( RIS, ACTUALIZAÇÕES E VISTAS)

DML (QUERIES, SUBQUERIES,JUNÇÕES, E

OPERAÇÕES SOBRE

SQL

• SQL, é uma linguagem de programação que foi

desenvolvida para questionar bases de dados relacionais usando uma abordagem não procedimental.

• O termo não procedimental significa que é possível

extrair informação dizendo ao sistema que informação queremos, por oposição a dizer como extrair essa

SQL é mais do que apenas uma Linguagem de

Consulta

• Linguagem de Definição de Dados (DDL):

• Criar / destruir / alterar relações e vistas.

• Define restrições de integridade (RI’s). • Linguagem de actualização:

• Insere /apaga / modifica (update) tuplos.

• Interage de perto com as RI´s. • Controlo de Acessos:

• Pode conceder (grant) / retirar (revoke) o direito ao acesso e à manipulação de tabelas (relações / vistas).

SQL Server Management Studio Express

• Criar bases de dados.

• Correr scripts SQL – São programas que podem conter comandos

DDL ou DML (ou combinação de ambos)

• Optimizar a performance do sistema.

• Analisar planos de querys

SQL Server Management Studio 2008

Academia MSDNAA / EST: http://msdn61.e-academy.com/msdnaa_lo9249

1. Comando SQL

2. Validar Comando SQL

3. Executar Comando SQL

Criação de Relações

• Cria a relação Barcos. São mostrados os nomes e tipos

de três campos.

CREATE TABLE Barcos

(bid INTEGER, bnome CHAR(10), cor CHAR(10))



Não foram específicadas nenhumas restrições de

Integridade. (Vamos discutir isto mais à frente.)

CREATE TABLE Reservas

Destruição e Alteração de Relações

• Destrói a relação Barcos. A informação do esquema e

os tuplos são apagadas.

DROP TABLE Barcos



O esquema de barcos é alterado pela adição

de um novo campo; cada tuplo da instância

corrente é extendido com um valor

null

no

novo campo.

ALTER TABLE Barcos

Restrições de Integridade

• Uma RI descreve condições que devem ser satisfeitas por

todas as instâncias legais de uma relação.

• Inserts/deletes/updates que violem as RI’s são desactivados. • Podem ser utilizadas para assegurar a aplicação da semântica

(e.g., id como chave), ou prevenir inconsistencias (e.g., snome tem que ser uma string, age deve ser < 200)

• Tipos de RI’s: Restrições de domínio, restrições de chave primária, restrições de chave forasteira, restrições gerais.

• restrições de domínio: Os valores dos campos têm que ser do tipo correcto. É sempre reforçada.

Chaves Primárias e Candidatas

• Chave de uma relação: Conjunto mínimo de campos , duma instância legal, que garanta que não haja dois tuplos distintos com os mesmos valores nos campos chave.

• É Possivel ter muitas candidate keys (especificadas usando UNIQUE), uma das quais é escolhida para primary key.

• Os campos da chave primária não podem conter valores nulos.

CREATE TABLE Reservas

( mnome CHAR(10)

bid INTEGER,

data DATE,

PRIMARY KEY (mnome, bid, data) )

CREATE TABLE Reservas

( mnome CHAR(10) NOT NULL,

bid INTEGER,

data DATETIME,

PRIMARY KEY (bid, data),

Chaves Forasteiras

• Foreign key: Conjunto de campos de uma relação R que é usado para se ´referir´ aos tuplos de uma outra relação S.

• Os campos devem ser chave (idealmente, primaria) de S.

• Nos tuplos de R, os valores dos campos devem corresponder a valores em algum tuplo de S, ou então são nulos.

CREATE TABLE Barcos

( bid INTEGER,

bnome CHAR(10),

cor CHAR(10),

PRIMARY KEY (bid) );

CREATE TABLE Reservas

( mnome CHAR(10) NOT NULL,

bid INTEGER,

data DATETIME,

PRIMARY KEY (bid, data) ,

UNIQUE (mnome),

FOREIGN KEY (bid)

Restrições Gerais

• Util quando se

trata de RI´s mais gerais do que as chaves.

• As restrições

podem ter nome.

CREATE TABLE Marinheiros

( mid INTEGER,

mnome CHAR(10),

grau INTEGER,

idade REAL,

PRIMARY KEY (mid),

CHECK ( grau >= 1

AND grau <= 10 ));

Inserção de novos Registos

• Inserção de registo uníco:

INSERT INTO Marinheiros (mid, mnome, grau, idade)

VALUES (12, ‘Emanuel’, 5, 21.0)



Inserção multipla de registos:

INSERT INTO Marinheiros (mid, mnome, grau, idade)

SELECT S.mid, S.nome, null, S.idade

FROM Alunos S

WHERE S.idade >= 18

Apagar Registos

• Podemos apagar todos os tuplos que satisfaçam a

condição da cláusula WHERE :

DELETE

FROM Marinheiros

WHERE grau IS NULL



O exemplo apaga todos os marinheiros sem

grau; a cláusula

pode conter consultas

complexas etc., em geral.



O que deve ser feito quando um apagamento causa

Modificação de Registos

• Comando UPDATE é usado

para modificar oa campos dos tuplos existentes

• A cláusula WHERE é aplicada

antes e determina os campos a serem modificados.

• A cláusula SET determina os

novos valores

• Se o campo a ser modificado

também for usado para determinar o novo valor, o

valor da cláusula WHERE é o

antigo.

UPDATE Marinheiros SET grau=grau-1

WHERE idade < 15

mid mnome grau idade

22 dustin 7 45.0 31 lubber 8 55.5 62 rusty 8 25.0 58 rusty 10 35.0 UPDATE Marinheiros SET grau=grau-1 WHERE grau >= 8

mid mnome grau idade

22 dustin 7 45.0

31 lubber 7 55.5

62 rusty 7 25.0

Reforço da Integridade Referencial

• Considere Barcos e Reservas; bid em Reservas é uma chave

forasteira que referencia Barcos.

• O que deve ser feito se um tuplo de Reservas for inserido com

um numero de barco (id) não existente? (REJEITA-SE!!)

• O que deve ser feito se um tuplo de barcos for apagado?

• Apagar também todos os tuplos de Reservas que se lhe refiram.

• Desactivar o apagamento do tuplo de Barcos que é referido.

• Atribua um default bid aos tuplos de Reservas que se lhe refiram.

• Atribua null aos tuplos de Reservas que se lhe refiram.

• São feitas as mesmas escolhas no caso de se fazer UPDATE

Integridade Referencial no SQL/92

• O SQL/92 suporta todas as 4

opções nos deletes e updates.

• Por omissão, NO ACTION

(delete/update é rejeitado)

• CASCADE (apagam também os tuplos que se referem ao tuplo apagado)

• SET NULL / SET DEFAULT (Atribui à chave forasteira o valor do tuplo referenciado)

CREATE TABLE Reservas

( mnome CHAR(10) NOT NULL,

bid INTEGER DEFAULT 1000,

data DATETIME,

PRIMARY KEY (bid, data) ,

UNIQUE (mname),

FOREIGN KEY (bid)

REFERENCES Barcos ON DELETE CASCADE

Vistas

• Uma vista é apenas uma relação, mas guarda-se uma

definição, em vez de um conjunto de tuplos.

CREATE VIEW MarinheirosActivos (nome, idade, data) AS SELECT S.mnome, S.idade, R.data

FROM Marinheiros S, Reservas R

WHERE S.mnome=R.mnome AND S.grau>6



As vistas podem ser eliminadas utilizando o

comando

 Como gerir DROP TABLE se houver uma vista na tabela?

 O comando DROP TABLE tem opções para o utilizador especificar

Vistas Actualizáveis

• O SQL/92 só permite actualizações a vistas baseadas em

tabelas simples.

CREATE VIEW MarinheirosNovos (mid, idade, grau) AS SELECT S.mid, S.idade, S.grau

FROM Marinheiros S

WHERE S.idade < 18



Cada tuplo da vista gerado por exactamente um

tuplo da tabela de base; logo cada comando

update/delete command na vista pode ser fácilmente

Sumário da DDL do SQL

• O DDL suporta a criação de relações, vistas e índices. As

tabelas podem ser alteradas (através da adição e eliminação e das RI’s).

• As Vistas podem ser consultadas como as relações, mas

Sumário (continuação)

• O SQL/92 suporta vários tipos de restrições de

integridade.

• Restrições de domínio, especificação de chaves primárias e candidatas, chaves forasteiras e restrições gerais.

• Em particular, as restrições de chave forastira interagem de perto com os comandos insert / delete / modify, e os

SQL92

– Data Manipulation Language

Marinheiros

mid mnome grau idade

10 Joao 7 33.0 11 Jose 5 25.0 15 Filipe 7 31.0 16 Antonio 3 31.0 19 Dusrte 7 26.5 20 Francisco 9 55.0 Barcos

bid Bnome cor

45 Vivaro Azul 89 Boat Amarelo 54 Caravela Preto 12 Atlantico Azul 45 Açores Vermelho 63 Barcaça Verde Reservas

bid mid data

45 10 11-12-2001 54 15 11-12-2001 12 10 5-11-2001 45 16 18-10-2001 54 15 14-11-2001 63 20 26-12-2001

O bloco básico da query

• A instrução SELECT extrai a

informação da base de dados.

• Na sua forma mais simples deve incluir:

• Uma cláusula SELECT, que lista todas as colunas a

visualizar

• Uma cláusula FROM, que especifica a tabela

• Exemplo:

• Para listar todos os numeros de marinheiros, nomes e idade da tabela Marinheiros

SELECT mid, mnome, idade FROM Marinheiros

mid mnome idade

10 Joao 33.0 11 Jose 25.0 15 Filipe 31.0 16 Antonio 27.0 19 Dusrte 26.5 20 Francisco 55.0

Impedir a selecção de linhas duplicadas

• Se fizermos

SELECT grau

FROM Marinheiros;

Aparecem todos os graus dos marinheiros duplicados. Para eliminar as repetições temos que fazer:

SELECT DISTINCT grau

A Cláusula ORDER BY

• Normalmente a sequência dos registos produzidos no

resultado duma consulta não é definida. A cláusula ORDER BY é utilizada para ordenar os registos. Tem que ser a

ultima cláusula da instrução SELECT

• Exemplo:

• Para saber os nomes, graus e idade dos marinheiros ordenados pelo nome faz-se:

SELECT MNOME, GRAU, IDADE FROM MARINHEIROS

ORDER BY MNOME;

• Nota:

• A ordenação de dados por omissão é ascendente. Para inverter a sequência digita-se DESC a seguir ao nome da coluna.

A cláusula WHERE

• A cláusula WHERE contém a condição que os registos têm que satisfazer para que sejam visualizados. Tem que

aparecer depois da cláusula FROM.

• Pode comparar valores das colunas, valores literais, expressões aritméticas ou fuções.

• Podem usar-se operadores aritméticos

• Operadores SQL (BETWEEN..AND.., IN(lista), LIKE, IS NULL)

• Exemplos:

SELECT MNOME, IDADE, GRAU FROM MARINHEIROS

WHERE GRAU BETWEEN 5 AND 8; (WHERE GRAU IN(3, 5);)

(WHERE MNAME LIKE ’F%’;) (WHERE GRAU IS NULL)

• Expressões de negação

• NOT BETWEEN; NOT IN; NOT LIKE; IS NOT NULL

• Consulta de dados com condições múltiplas

• AND; OR

• Exemplo:

SELECT MID, MNOME, GRAU, IDADE FROM MARINHEIROS

WHERE GRAU>7

AND IDADE=31

Funções de Grupo

• Estas funções operam sobre conjuntos de registos e são

utilizadas para extrair informação resumida sobre grupos de registos. Por defeito todos os registos de uma tabela são tratados como um grupo. A cláusula GROUP BY da instrução SELECT é utilizada para dividir os registos de uma tabela em grupos mais pequenos

• AVG: Valor médio (ignora nulos no Oracle)

• MAX: Valor máximo

• MIN: Valor Mínimo

• SUM: Soma dos valores (ignora nulos no Oracle)

• VARIANCE: variância (ignora nulos no Oracle)

• COUNT: Numero de vezes que toma um valor não nulo, se usarmos o * obriga a contar todos os registos, inclusive os repetidos e os

nulos

• Exemplos:

SELECT IDADE, AVG(GRAU) MÉDIA

FROM MARINHEIROS

GROUP BY IDADE;

SELECT AVG(GRAU) MÉDIA

FROM MARINHEIROS

• Cláusula HAVING

• Utiliza-se quando se pretende restringir a informação contida nos grupos

• Exemplo: para mostrar a idade média de todos os graus com mais de 3 pessoas faz-se

SELECT GRAU, AVG(IDADE)

FROM MARINHEIRO

GROUP BY GRAU

HAVING COUNT(*)>3

• Exemplo: para mostrar apenas as idades cujo grau mínimo é igual a 5 :

SELECT IDADE, MIN(GRAU)

FROM MARINHEIROS

GROUP BY IDADE

• Pode-se ainda utilizar a cláusula WHERE juntamente com as funções de grupo. A ordem das cláusulas tem que ser a seguinte:

SELECT colunas FROM tabela(s)

WHERE condições de registo GROUP BY coluna(s)

HAVING condições de grupo de registos ORDER BY coluna(s)

Junções

• Equi-junção: os valores da coluna são iguais em

ambas as tabelas

• Não equi-junção: junção entre tabelas quer não

tenham colunas correspondentes

• Equi-junção

• A condição de junção é especificada na cláusula WHERE

SELECT colunas FROM tabelas

WHERE a condição de junção é...

• Exemplo:

SELECT MARINHEIROS.MNOME, BARCOS.BNOME, RESERVAS.DATA

FROM MARINHEIROS, RESERVAS, BARCOS

WHERE MARINHEIROS.MID=RESERVAS.MID

AND RESERVAS.BID=BARCOS.BID

Junções

• Exemplo de uma não equi-junção

• Relação entre as tabelas MARINHEIROS e GRELHA_SALARIAL, uma vez que nenhuma coluna de MARINHEIROS corresponde directamente a uma coluna de GRELHA_SALARIAL. A relação vai ser obtida

utilizando um operador diferente do sinal igual(=).

• Suponhamos que para determinar o nível de remuneração de um marinheiro (salário) o seu grau tem que estar entre qualquer par de limites superior e inferior

SELECT M.MNOME, M.GRAU, G.SALARIO

FROM MARINHEIROS M, GRELHA_SALARIAL G

WHERE M.GRAU BETWEEN G.GRAUMIN AND G.GRAUMAX ORDER BY G.SALARIO;

GRELHA_SALARIAL

SALÁRIO GRAUMIN GRAUMAX

10000 1 2

20000 3 5

30000 6 7

• Junção de uma tabela com ela própria

• Se quisermos fazer uma consulta que nos apresente todos os MARINHEIROS que têm maior grau que o seu :

SELECT M1.NOME, M1.GRAU, M2.NOME, M2.GRAU FROM EMP MARINHEIROS M1, MARINHEIROS M2

WHERE M1.CHEFE=M2.MID AND M1.GRAU<M2.GRAU

mid mnome grau Chefe idade

10 Joao 7 15 33.0 11 Jose 5 16 25.0 15 Filipe 7 20 31.0 16 Antonio 3 19 27.0 19 Dusrte 7 20 26.5 20 Francisco 9 55.0

Operadores sobre conjuntos

• Os operadores de conjuntos (UNION,

INTERSECTION, MINUS) são designados por

junções verticais pq a junção de 2 tabelas se faz de acordo com colunas e não de acordo com linhas.

SELECT IDADE FROM MARINHEIROS WHERE GRAU=9 UNION SELECT IDADE FROM MARINHEIROS WHERE GRAU=30

Resultados da 1ª consulta mais os da 2ª consulta SELECT IDADE FROM MARINHEIROS WHERE GRAU=9 INTERSECTION SELECT IDADE FROM EMP WHERE GRAU=3

Resultados que coincidem em ambas as consultas SELECT IDADE FROM MARINHEIROS WHERE GRAU=9 MINUS SELECT IDADE FROM MARINHEIROS WHERE GRAU=3

Resultados da 1ª consulta que não resultem da segunda

Subconsultas

• Uma subconsulta é uma instrução SELECT que está

encadeada noutra instrução SELECT que produz resultados intermédios. A utilização de subconsultas permite a um utilizador construir comandos potentes a partir de comandos simples.

SELECT coluna1, coluna2,...

FROM tabela

WHERE coluna=

(SELECT coluna

FROM tabela

Exemplo

• Para encontrar o MARINHEIRO que tenha o grau mais baixo da empresa (cujo valor é à partida desconhecido), são necessários 2 passos:

1. Encontrar o grau mais baixo:

SELECT MIN(GRAU)

FROM MARINHEIROS;

2. Encontrar o marinheiro que tem o grau mais baixo:

SELECT MNOME, GRAU

FROM MARINHEIROS

WHERE GRAU=(resultado da query anterior)

Podemos combinar os dois comandos numa subconsulta encadeada:

SELECT MNOME, GRAU

FROM MARINHEIROS

WHERE GRAU=( SELECT MIN(GRAU)

Operadores ANY, ALL e EXISTS

• Estes operadores podem ser utilizados em subconsultas

que produzam mais do que um registo na cláusula WHERE ou HAVING em conjunção com os operadores lógicos.

• O operador ANY compara um valor com cada valor produzido por uma subconsulta.

• Exemplo:

• Para visualizar os marinheiros que têm um grau maior do que o menor grau dos marinheiros com 31 anos:

SELECT MNOME, GRAU FROM MARINHEIROS

WHERE GRAU>ANY(SELECT DISTINCT GRAU

FROM MARINHEIROS

WHERE idade=31)

ORDER BY GRAU DESC;

• O grau mais baixo dos marinheiros com 31 anos é 3, do António. A

consulta principal produziu como resultado todos os marinheiros que têm um grau maior do que 3. (‘>ANY’ significa aqui maior ou igual ao minimo, ‘=ANY’ seria equivalente a escrevermos IN)

• O operador ALL compara um valor com qualquer dos valores produzidos pela subconsulta:

• Exemplo

• Para encontrar todos os marinheiros que têm um grau maior do que qualquer dos marinheiros com 31 anos.

SELECT MNOME, GRAU FROM MARINHEIROS

WHERE GRAU>ALL(SELECT DISTINCT GRAU

FROM MARINHEIROS

WHERE idade=31)

ORDER BY GRAU DESC;

O grau mais alto dos marinheiros com 31 anos é 7, do Filipe. Por isso a consulta produz como resultado todos os marinheiros cujo grau é maior do que 7.

• O operador EXISTS pode também ser utilizado para

verificar se existe determinado valor. O resultado é