Embed Size (px)
Citation preview
Banco de Dados I
Aula 16 - Prof. Bruno Moreno
04/11/2011
Plano de Aula
• O comando DELETE
• Cláusula CHECK
• Comparações utilizando NULL
• Funções agregadas
• Junções de tabelas em SQL
• Visões
15:37
Comando DELETE
• A ausência da cláusula WHERE exclui todos os dados de <relação>
15:37
DELETE FROM <relação> WHERE <alguma_coluna> = <algum_valor>
DELETE exemplo
15:37
DELETE FROM Persons WHERE LastName='Tjessem' AND FirstName='Jakob'
DELETE exemplo
15:37
DELETE FROM Persons WHERE LastName='Tjessem' AND FirstName='Jakob'
Excluir todos os dados DELETE FROM Persons
Dependendo do SGBD utiliza-se *
Regras de Integridade
• Chave primária
• Chave estrangeira
• Unicidade (unique)
• Obrigatoriedade (not null)
• Check
15:37
Regras de Integridade
• Chave primária
• Chave estrangeira
• Unicidade (unique)
• Obrigatoriedade (not null)
• Check
15:37
CLÁUSULA CHECK Banco de Dados I
15:37
CHECK
• Restrição baseada em tupla
• Se aplica a cada tupla individualmente
– É verificada sempre que uma tupla for inserida ou atualizada
• Exemplo (1):
– Salário do funcionário tem que ser maior do que 500 e menor do que 5.000
15:37
CREATE TABLE EMPREGADO ( NOME VARCHAR(30) NOT NULL, SALARIO NUMBER(10) NOT NULL CHECK (SALARIO > 500 AND SALARIO < 5000)
);
CHECK
• Exemplo (2):
– Certificar que a data de criação do departamento é posterior à data de nomeação de seu chefe
15:37
CREATE TABLE DEPARTAMENTO( DNUMERO INT PRIMARY KEY, NOME VARCHAR(15) NOT NULL, GERENTE CHAR(11) NOT NULL, DATA_CRIACAO DATE, DAT_INICIO_GERENCIA DATE, CONSTRAINT DEPGERFK FOREIGN KEY (GERENTE) REFERENCES EMPR (cpf) ON DELETE SET NULL, CHECK(DATA_CRIACAO < DAT_INICIO_GERENCIA)
);
Todas restrições
CREATE TABLE EMPREGADO (
MATRICULA INT,
CPF CHAR(11),
NOME VARCHAR(30) NOT NULL,
DNO INT DEFAULT 1 NOT NULL,
SALARIO decimal(10) NOT NULL CHECK (SALARIO > 500
AND SALARIO < 5000),
CONSTRAINT EMPPK PRIMARY KEY (MATRICULA),
CONSTRAINT EMPUK UNIQUE (CPF),
CONSTRAINT EMPDEPFK FOREIGN KEY (DNO) REFERENCES
DEPARTAMENTO (codigo)
); 15:37
COMPARAÇÕES COM NULL Banco de Dados I
15:37
Comparações com NULL
• Significados de NULL – Valor desconhecido
• Existe mas não é conhecido • Exemplo
– Não se conhece a data de aniversário do ALUNO
– Valor indisponível • Existe mas é propositadamente omitido • Exemplo
– ALUNO possui telefone mas não informa propositadamente
– Valor não aplicável • Indefinido para determinada tupla • Exemplo
– Atributo pos_graduacao é NULL quando determinado aluno não tem nenhuma pós
15:37
Comparações com NULL
• Em BD, NULL != NULL
– NULLs de diferentes atributos são considerados diferentes
– UNKNOWN
• TRUE
• FALSE
15:37
Comparações com NULL
• IS → =
• IS NOT → <>
• Comparação pela igualdade não é apropriada porque SQL considera um NULL diferente de outro NULL
15:37
SELECT NOME FROM EMPREGADO WHERE GER_CPF IS NULL
CONSULTAS ANINHADAS Banco de Dados I
15:37
Consultas Aninhadas
• Permite a formulação de consultas dentro da cláusula WHERE de consultas externas
• Consulta não aninhada (SELECT DISTINCT PNUMERO
FROM PROJETO, DEPARTAMENTO, FUNCIONÁRIO
WHERE DNUM = DNUMERO AND GERCPF = CPF AND
UNOME = ‘Silva’)
UNION
(SELECT DISTINCT PNUMERO
FROM PROJETO, TRABALHA_EM, FUNCIONÁRIO
WHERE PNUMERO = PNO AND ECPF = CPF AND
UNOME = ‘Silva’)
15:37
Consultas Aninhadas
• Permite a formulação de consultas dentro da cláusula WHERE de consultas externas
• Consulta aninhada (SELECT DISTINCT PNUMERO
FROM PROJETO
WHERE PNUMERO IN (SELECT PNUMERO
FROM PROJETO, DEPARTAMENTO, FUNCIONARIO
WHERE DNUM = DNUMERO AND GERCPF = CPF AND UNOME = ‘Silva’)
OR PNUMERO IN (SELECT PNO
FROM TRABALHA_EM, FUNCIONARIO
WHERE ECPF = CPF AND UNOME = ‘Silva’)
15:37
Consultas aninhadas
Operador de comparação
Operador IN
• Compara um valor v com um conjunto (ou multiconjunto) de valores V retornando TRUE se v for um dos elementos de V
• Pode-se utilizar IN para comparar mais do que um valor
SELECT DISTINCT ECPF FROM TRABALHA_EM WHERE (PNO, HORAS) IN ( SELECT PNO, HORAS FROM TRABALHA_EM WHERE CPF = ‘123456789’);
15:37
Compara a subtupla (PNO, HORAS) de cada tupla de
TRABALHA_EM com as tuplas selecionadas pela consulta
aninhada
Operador ALL
• É utilizado com outros operadores >, >=, <, <= e <>
• Exemplo – Selecione o nome de todos os funcionários cujos
salários sejam maiores que os salários de todos os funcionários do departamento 5
SELECT NOME FROM FUNCIONARIO WHERE SALARIO > ALL (SELECT SALARIO FROM EMPREGADO WHERE DNO = 5);
15:37
Consultas Aninhadas
• Pode-se definir vários níveis de consultas aninhadas
• Outros operadores utilizados
– EXISTS
– NOT EXISTS
– CONTAINS
– EXCEPT
15:37
JUNÇÕES EM SQL Banco de Dados I
15:37
Junções de Tabelas em SQL
SELECT PNOME, UNOME, ENDERECO
FROM EMPREGADO, DEPARTAMENTO
WHERE DNOME = ‘Pesquisa’ AND DNUMERO = DNO
• Uma junção pode ser feita com uma tabela “juntada” no FROM
SELECT PNOME, UNOME, ENDERECO
FROM (EMPREGADO JOIN DEPARTAMENTO ON DNO = DNUMERO)
WHERE DNOME = ‘Pesquisa’
15:37
Condição de Junção
Joined Table
Junções de Tabelas em SQL
• Cada uma das junções da álgebra relacional podem ser especificadas desta maneira
– NATURAL JOIN
• Atributo de equijunção
– OUTER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN
15:37
Junções de Tabelas em SQL
• NATURAL JOIN
SELECT PNOME, UNOME, ENDERECO
FROM (EMPREGADO NATURAL JOIN
(DEPARTAMENTO AS DEPT(DNOME, DNO, GERCPF))) WHERE DNOME = ‘Pesquisa’;
• INNER JOIN (ou simplesmente JOIN)
– Tipo default de junção
– Junção ocorre somente com tuplas que satisfazem a condição de junção
15:37
Junções de Tabelas em SQL
• OUTER JOIN
– Utilizada quando deseja-se que tuplas que não satisfazem a condição seja selecionada no resultado
– LEFT JOIN
SELECT E.NOME AS NOME_EMPREGADO, S.NOME AS NOME_SUPERVISOR
FROM (EMPREGADO AS E LEFT OUTER JOIN EMPREGADO AS S ON E.SUPERCPF = S.CPF);
15:37 OUTER: pode ser omitida!
FUNÇÕES AGREGADAS Banco de Dados I
15:37
Funções agregadas
• Existem diversas funções agregadas
– COUNT, SUM, MAX, MIN, AVG...
• Exemplo (1)
– Encontre a soma dos salários, o maior salário, o menor salário e a média salarial de todos os funcionários
SELECT SUM(SALARIO), MAX(SALARIO), MIN(SALARIO), AVG(SALARIO) FROM EMPREGADO
15:37
Funções agregadas
• Exemplo (2) – Encontre a soma dos salários de todos os
funcionários do departamento ‘Pesquisa’, bem como o maior salário, o menor salário e a média salarial desse departamento
SELECT SUM(SALARIO), MAX(SALARIO), MIN(SALARIO), AVG(SALARIO) FROM (EMPREGADO JOIN DEPARTAMENTO ON DNO=DNUMERO) WHERE DNOME = ‘Pesquisa’;
15:37
Funções agregadas
• Exemplo (3)
– Recupere o número total de funcionários
SELECT COUNT(*) FROM FUNCIONARIO;
• Exemplo (4)
– Recupere o número total de funcionários do departamento ‘Pesquisa’
SELECT COUNT (*) FROM FUNCIONARIO, DEPARTAMENTO WHERE DNO = DNUMERO AND DNOME = ‘Pesquisa’;
15:37
Funções agregadas
• Exemplo (5)
– Recupere os diferentes salários contidos no banco de dados.
SELECT COUNT(DISTINCT SALARIO) FROM FUNCIONARIO;
15:37
Agrupamento
• As vezes é necessário utilizar funções agregadas para agrupamento de atributos
• Por exemplo: Recupere, para cada departamento, o número do departamento, o número de empregados e a média de seus salários.
15:37
NUM_DEP QTDE_EMP AVG_SAL
1 10 10.000
2 15 7.000
Funções agregadas para os departamentos 1 e 2
Agrupamento
• Exemplo (1): Recupere, para cada departamento, o número do departamento, o número de empregados e a média de seus salários.
SELECT DNO, COUNT(*), AVG(SALARIO) FROM FUNCIONARIO GROUP BY DNO
15:37
Agrupamento
• Exemplo (1):
SELECT DNO, COUNT(*), AVG(SALARIO) FROM FUNCIONARIO GROUP BY DNO
15:37
PNOME UNOME CPF SEXO SALÁRIO (R$) CPF_GERENTE DNO
João Silva 1 M 30.000,00 2 5
Maria Souza 2 F 40.000,00 8 5
José Ribeiro 3 M 25.000,00 7 4
Manoel Cabral 4 M 43.000,00 8 4
Joana Alvares 5 F 38.000,00 2 5
Julia Assunção 6 F 25.000,00 2 5
Tiago Medeiros 7 M 25.000,00 4 4
Pedro Falcão 8 M 55.000,00 null 1
FUNCIONARIO
• Exemplo (1):
SELECT DNO, COUNT(*), AVG(SALARIO) FROM FUNCIONARIO GROUP BY DNO
PNOME UNOME CPF SEXO SALÁRIO (R$) CPF_GERENTE DNO
João Silva 1 M 30.000,00 2 5
Maria Souza 2 F 40.000,00 8 5
José Ribeiro 3 M 25.000,00 7 4
Manoel Cabral 4 M 43.000,00 8 4
Joana Alvares 5 F 38.000,00 2 5
Julia Assunção 6 F 25.000,00 2 5
Tiago Medeiros 7 M 25.000,00 4 4
Pedro Falcão 8 M 55.000,00 null 1
Agrupamento
15:37
DNO COUNT AVG
1 1 55.000
4 3 31.000
5 4 33.250
FUNCIONARIO
Agrupamento
• Exemplo (2): Recupere, para cada projeto, seu número, nome e número de funcionários que nele trabalham
SELECT PNUMERO, PJNOME, COUNT(*), FROM PROJETO, TRABALHA_EM WHERE PNUMERO = PNO GROUP BY PNUMERO, PJNOME;
15:37
Agrupamento: HAVING
• E para usar funções de agrupamento somente para os grupos que satisfazem determinadas condições?
• Exemplo (2*)
Recupere, para cada projeto com mais do que dois funcionários, seu número, nome e número de funcionários que nele trabalham
15:37
Agrupamento: HAVING
• Exemplo (2*)
Recupere, para cada projeto com mais do que dois funcionários, seu número, nome e número de funcionários que nele trabalham
SELECT PNUMERO, PJNOME, COUNT(*), FROM PROJETO, TRABALHA_EM WHERE PNUMERO = PNO GROUP BY PNUMERO, PJNOME
HAVING COUNT(*) > 2;
15:37
Freqüência!
Aula 16 - Prof. Bruno Moreno
04/11/2011
