SQL DML, Integridade e Subconsultas - Banco de Dados I

Prévia do material em texto

SQL - DML
 Comandos DML da linguagem SQL
– INSERT
– UPDATE
– DELETE
SQL – Inserir registos
 INSERT INTO <nome da tabela> [(coluna1, coluna2,...)]
VALUES (valor1, valor2, ...);
 Exemplos:
Insert into album
Values (964,’Broken Bells’,523,’./media/2007 – Broken 
Bells/Cover.jpg’)
 Pode inserir-se um registo de cada vez ou um conjunto deles 
(quando se indica os valores explicitamente no comando)
 Pode omitir-se a lista de nomes de colunas desde que se 
respeite a ordem das colunas definida na tabela e não se omita 
nenhum valor
 Pode inserir-se apenas alguns campos de um registo
SQL – Inserir registos
 INSERT INTO <nome da tabela> [(coluna1, coluna2,...)]
SELECT (lista)
FROM <tabela(s)>
WHERE <condição>;
 Exemplo:
Insert into album (titulo,artista)
Select (nomealbum,idartista)
From albuns_emprestados
Where nome_artista=‘Broken Bells’;
 O número de itens na lista do SELECT tem de ser igual ao 
número de colunas referidas no comando INSERT 
SQL – Actualizar registos
 UPDATE <nome da tabela> [pseudónimo]
SET coluna = (valor, expressão,..),
coluna = (valor, expressão,..), ....
WHERE <condição>;
 Exemplo:
Update faixas, artista
Set faixas.bitrate=320
Where faixas.artista=artista.artista_id and artista.nome=‘Broken Bells’
 Se a cláusula WHERE for omitida todos os registos da tabela 
são actualizados.
 Os valores a actualizar podem ser o resultado de subconsultas
ou expressões.
SQL – Apagar registos
 DELETE FROM <nome de tabela>
WHERE <condição>;
 Exemplo:
 Delete From faixas
 Where genero=25
 Se a cláusula WHERE for omitida todos os registos da tabela 
são apagados.
SQL – Preservação de integridade
 Restrições de integridade são definidas
– na criação de tabelas;
– na alteração de tabelas.
 Restrições de integridade são verificadas na execução de
– INSERT;
– UPDATE;
– DELETE.
SQL – Violação de integridade
CREATE TABLE ALUNO
(A_NUM NUMBER(4) PRIMARY KEY,
NOME CHAR(60) NOT NULL,
END CHAR(120),
TEL NUMBER(8),
BI NUMBER(4) UNIQUE);
CREATE TABLE DISCIPLINA
(NOME CHAR(60) UNIQUE,
D_CODIG CHAR(6) PRIMARY KEY,
ANO NUMBER(1) CHECK ((ANO >= 1) AND (ANO <= 5)),
SEM NUMBER(1) CHECK (SEM IN (1,2)));
CREATE TABLE A_D
(A_NUM NUMBER(4),
D_CODIG CHAR(6),
EPOCA CHAR(6),
NOTA NUMBER(2) CHECK ((NOTA >= 0) AND (NOTA <= 20)),
CONSTRAINT A_D_CE1 FOREIGN KEY (A_NUM) REFERENCES ALUNO (A_NUM),
CONSTRAINT A_D_CE2 FOREIGN KEY (D_CODIG) REFERENCES DISCIPLINA
SQL – Violação de integridade
INSERT INTO A_D
VALUES ( 1004, ‘ELEC’, ‘Julho’, 12);
INSERT INTO ALUNO
VALUES (1002, ‘A. Cavaco’, ‘R. de Baixo, 
23’, 987654)
INSERT INTO A_D
VALUES ( 1002, ‘ALGB’, ‘Set’);
INSERT INTO DISCIPLINA
VALUES ( ‘Física’, ‘FISC’, 0, 1);
Erro: viola a integridade referencial pois 
um valor numa de chave externa -
1004 - não tem correspondência na 
tabela de referência – Aluno -
Erro: viola restrição de chave única na 
coluna BI
Correcto, apesar de NOTA ficar a Null
Erro: viola teste de integridade na coluna 
Ano
SQL – Violação de integridade
UPDATE ALUNO
SET NOME = ‘D. Mendes’,
A_NUM = 1006
WHERE A_NUM = 1008;
UPDATE A_D
SET D_CODG = ‘ELEC’,
NOTA = 14
WHERE A_NUM=1008;
DELETE FROM ALUNO
WHERE A_NUM = 1008;
Erro: viola a integridade referencial pois altera 
dado de referência correspondente à chave 
externa A_NUM da tabela A_D
Correcto, pois a chave externa D_CODG é 
alterada mas mantem um valor que existe 
na tabela Disciplina
Erro: viola a integridade referencial pois há 
dados dependentes do registo a apagar na 
tabela A_D
SQL – Violação de integridade
 Regras para a manipulação de dados na tabela de referência e 
as acções a desencadear sobre os dados dependentes:
– RESTRICT 
 Não é permitido actualizar ou apagar dados de referência.
– SET TO NULL 
 Se os dados de referência são apagados todos os dados dependentes 
ficam a Null.
– SET TO DEFAULT
 Idem, com a excepção de que os dados dependentes ficam com um 
valor por defeito.
– CASCADE 
 Quando os dados de referência são alterados todos os dados 
dependentes são actualizados.
SQL – Violação de integridade
 Exemplo
CREATE TABLE A_D (
A_NUM NUMBER(4),
D_CODIG CHAR(6),
EPOCA CHAR(6),
NOTA NUMBER(2) CHECK ((NOTA >= 0) AND (NOTA <= 20)),
CONSTRAINT A_D_CP PRIMARY KEY (A_NUM, D_CODIG, EPOCA), CONSTRAINT 
A_D_CE1 FOREIGN KEY (A_NUM) REFERENCES ALUNO (A_NUM) ON DELETE 
CASCADE,
CONSTRAINT A_D_CE2 FOREIGN KEY (D_CODIG) REFERENCES DISCIPLINA 
(D_CODIG) ON DELETE CASCADE;
SQL – Subconsultas
LMD do SQL
Comandos de Manipulação de dados do SQL 
Subconsultas -Subquery 
Operando escalar
ANY, IN, SOME, ALL
Vectores
Exists
Comparações usando Subconsultas
Subconsultas correlacionadas
SubConsultas na cláusula FROM
Joins como alternativa
SQL – Subconsultas
 Subconsultas – SubQuery
 Uma das pesquisas mais comuns é encontrar, por exemplo, 
dados de empregado que possui maior salário.
 Para obter essa informação é preciso fazer duas coisas: 
– primeiro, determinar qual é o maior salário da empresa
– seleccionar o empregado com o salário igual a esse valor. 

 Este problema e toda a sua classe de problemas (encontrar a 
informação em dois ou mais passos) resolve-se com uma 
técnica chamada subconsulta (Comando SELECT dentro de 
comando SELECT)
 .
SQL – Subconsultas
 Sintaxe de Subconsultas 
 SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
 Cláusula/Consulta externa (outer query (or outer
statement)) 
 -> SELECT * FROM t1 WHERE column1
 Consulta interna / Sub Consulta (subquery) 
 -> SELECT column1 FROM t2
SQL – Subconsultas
 Subconsultas – Exemplo 
 Utilização da sintaxe de acordo com o SQL standard (suportado MySQL) 
 DELETE 
 FROM t1
 WHERE s11 > ANY
 (SELECT COUNT(*) /* no hint */ 
 FROM t2
 WHERE NOT EXISTS
 (SELECT * 
 FROM t3
 WHERE ROW(5*t2.s1,77)=
 (SELECT 50,11*s1 
 FROM t4 UNION SELECT 50,77 FROM
 (SELECT * FROM t5) AS t5)));
SQL – Subconsultas
 RESTRIÇÕES na utilização de subconsultas 
– (VER Section F.3, “Restrictions on Subqueries”)
 Uma subconsulta pode conter qualquer cláusula típica do 
comando 
 - SELECT: 
 - DISTINCT, 
 - GROUP BY, 
 - ORDER BY, 
 - LIMIT, 
 - UNION constructs,
 - JOINS
SQL – Subconsultas
 RESTRIÇÃO 1:
– consulta externa deve ser um dos comandos: 
 - SELECT, 
 - INSERT, 
 - UPDATE, 
 - DELETE, 
 - SET, ou 
 - DO.
SQL – Subconsultas
 RESTRIÇÃO 2
– Não se deve modificar uma tabela que está a ser alvo de uma 
subconsulta. Esta restrição aplica-se a comandos como:
 - DELETE, 
 - INSERT, 
 - REPLACE, 
 - UPDATE, 
 - LOAD DATA INFILE
SQL – Subconsultas
 VANTAGENS das subconsultas
 Permite a utilização de comandos estruturados de forma que é 
possível isolar cada parte da comando
 Funciona como uma alternativa à realização de operações que 
requerem junções e uniões complexas
 Sintaxe mais amigável que a de junções e uniões
SQL – Subconsultas
 RESULTADOS das subconsultas
 operando escalar (um valor único) – Scalar subqueriy
 operando vectorial
– uma linha– row subquer
– uma coluna – column subquery
 operando matricial 
– tabela - uma ou mais linhas para uma ou mais coluna – Table
subquery
SQL – Subconsultas
Subconsulta como OPERANDO escalar – Exemplo 
 
CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL); 
INSERT INTO t1 VALUES(100, 'abcde'); 
 
Tabela t1 
S1 S2 
100 abcde 
 
Commando: 
SELECT (SELECT s2 FROM t1) 
 
Resultado: 
S2 
abcde 
 
SQL – Subconsultas
 Utilização de Subconsultasem comparações
– Forma mais utilizada de subqueries
 non_subquery_operand comparison_operator (subquery)
 operando_não_subquery operador_de_comparação (subquery)
 Operadores de comparação: 
 = 
 > 
 < 
 >=
 <= 
 <>
SQL – Subconsultas
 Utilização de SubConsultas em comparações - EXEMPLOS
'a' = (SELECT column1 FROM t1)
 Originalmente esta era a forma permitida de utilização de 
subconsultas. Actualmente os SGBD permitem outras utilizações.
 Utilização de SubConsultas em comparações – EXEMPLOS
 Encontrar valores numa tabela t1 que sejam iguais ao valor máximo 
da tabela t2 (impossível de conseguir com Joins)

 SELECT column1 FROM t1
 WHERE column1 = (SELECT MAX(column2) FROM t2);
SQL – Subconsultas
 Utilização de SubConsultas em comparações – EXEMPLOS
 Encontrar todos os registos numa tabela t1 que tenham um valor que 
ocorra 2 vezes numa dada coluna (Subconsulta correlacionada 
(selfjoin)
 SELECT * FROM t1 AS t
 WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);
SQL – Subconsultas
 UTILIZAÇÃO de Subqueryies – subconsultas simples e 
subconsultas correlacionada
 –> subconsultas simples 
 Uma subconsulta simples é uma consulta dentro de outra consulta em 
que os resultados da consulta interna vão influenciar os da externa. 
 Numa subconsulta simples o SELECT interno é executado primeiro e 
o(s) seu(s) valor(es) é/são passado(s) para o SELECT externo para 
efeitos de teste. 
 O SELECT interno pode representar por si só uma consulta bastante 
complicada, fazendo uso das cláusulas WHERE, GROUP BY e HAVING
 –> subconsultas correlacionada
 A consulta externa influencia a consulta interna
SQL – Subconsultas
 Exemplo:
– Quais os empregados com salário superior à média dos salários do seu 
departamento. 
SELECT nemp, nome, sal, ndep
FROM emp e
WHERE sal > (SELECT avg(sal)
FROM emp
WHERE ndep = e.ndep)
ORDER BY ndep, sal DESC;
– A subconsulta é correlacionada porque o SELECT interno usa a informação 
e.ndep proveniente do SELECT externo para saber sobre que pessoas 
calcular a média (as do mesmo departamento).
– (Obs. Tente fazer o mesmo sem subconsulta correlacionada)
Resultado 
NEMP NOME SAL NDEP 
1839 Jorge Sampaio 890000 10 
1788 Maria Dias 565000 20 
1566 Augusto Reis 450975 20 
1902 Catarina Silva 435000 20 
1698 Duarte Guedes 380850 30 
1654 Ana Rodrigues 221250 30 
1521 Nelson Neves 212250 30 
 
SQL – Subconsultas
 Exemplo:
– Listar cada Instituição, com referência ao curso com melhor nota do último 
colocado e qual foi essa nota:

SELECT a.Instituicao, tblCursos.Curso, NotaUltimoColocado
FROM tblConcursos, tblEscolas, tblCursos, tblInstituicoes a, tblCursoConcurso
WHERE tblConcursos.CodigoLinha = tblCursoConcurso.CodLinha
AND tblCursoConcurso.CodCurso = tblCursos.CodigoCurso
AND tblCursos.Escola = tblEscolas.CodEscola
AND tblEscolas.Instituicao = a.CodInstituicao
AND NotaUltimoColocado = ( SELECT max( NotaUltimoColocado ) 
FROM tblConcursos, tblEscolas, tblCursos, tblInstituicoes b, tblCursoConcurso
WHERE tblConcursos.CodigoLinha = tblCursoConcurso.CodLinha
AND tblCursoConcurso.CodCurso = tblCursos.CodigoCurso
AND tblCursos.Escola = tblEscolas.CodEscola
AND tblEscolas.Instituicao = b.CodInstituicao
AND a.CodInstituicao = b.CodInstituicao
GROUP BY b.Instituicao );
SQL – Subconsultas
 Operador EXISTS
– Para além dos operadores lógicos e operadores SQL, é frequente aparecer 
nas subconsultas correlacionadas o operador EXISTS.
– O operador EXISTS devolve verdade se a subconsulta produzir uma ou 
mais linhas e devolve falso caso contrário. Obviamente, se a consulta não 
fosse correlacionada, o SELECT interno seria executado apenas uma vez, e 
assim o seu valor seria o mesmo para todos os registos do SELECT 
externo.
 Tradicionalmente, uma subconsulta com EXISTS inicia-se com 
SELECT *, mas qualquer outra projecção para além de * seria 
irrelevante, pois o MySQL ignora a lista numa subconsulta com 
EXISTS.
SQL – Subconsultas
 Exemplo:
 Quais os departamentos nos quais existem empregados?
SELECT *
FROM dep d
WHERE EXISTS (SELECT *
FROM emp
WHERE ndep = d.ndep);
Resultado 
NDEP NOME LOCAL 
10 Contabilidade Condeixa 
20 Investigação Mealhada 
30 Vendas Coimbra 
 
SQL – Subconsultas
 O operador NOT continua a poder ser aplicado.
 Exemplo:
 Quais os departamentos nos quais não existem quaisquer 
empregados?
SELECT *
FROM dep d
WHERE NOT EXISTS (SELECT *
FROM emp
WHERE ndep = d.ndep);