Banco de Dados para Redes aula 06 19.11

Prévia do material em texto

SQL - Structured Query Language - Cont. 
 
Prof. Erick Jeronimo 
INSERT INTO é um dos comandos do SQL que 
permite a introdução de dados nas tabelas. 
Sintaxe: 
INSERT INTO <nome_tabela> [(atributo1,atributo2, 
atributo3,...)] 
VALUES (<valores>) 
Os parêntesis rectos indicam que esses elementos são 
opcionais. Os dados de cada atributo (separados por 
vírgulas) são introduzidos através da palavra VALUES. 
 
Definições usando SQL 
Comandos DML 
Exemplos: 
INSERT INTO Departamento 
VALUES (50, 'Manutenção' , 'Lisboa') 
ou 
INSERT INTO Departamento (Cod_Depart, Nome_pep, 
Localização) 
VALUES (50, 'Manutenção' , 'Lisboa') 
 
Definições usando SQL 
Comandos DML (cont.) 
UPDATE - Alteração de valores em um ou mais 
atributos numa tabela e com critérios específicos 
 
Sintaxe: 
UPDATE <nome_tabela> 
SET <atributo> = <expressão>, 
... 
[WHERE <condição>] 
 
 
Definições usando SQL 
Comandos DML (cont.) 
SET define quais são os atributos que se pretende 
actualizar e os novos valores para esse atributo. 
WHERE é opcional e é utilizada quando se pretende 
condicionar os tuplos. 
Exemplo: 
UPDATE Empregado 
SET Ordenado = Ordenado + 50.000 
WHERE Num_Superior=7589 
 
Definições usando SQL 
Comandos DML (cont.) 
DELETE - Eliminação de valores em um ou mais 
tuplos numa tabela e com critérios específicos. 
 
Sintaxe: 
DELETE FROM <nome_tabela> 
[WHERE <condição>] 
Exemplo: 
DELETE FROM Empregado 
WHERE Num_Superior=7589 
 
Definições usando SQL 
Comandos DML (cont.) 
SELECT - selecciona conjunto de atributos de uma(s) 
tabela(s) dada(s) pelo comando FROM. 
Sintaxe: 
SELECT <atributo1, atributo2,...> 
FROM <nome_tabela1, nome_tabela2,...> 
[WHERE <condição>] 
Exemplo: 
SELECT Cod_aluno, Nome, Morada 
FROM Aluno 
Definições usando SQL 
Comandos DML (cont.) 
O comando SELECT permite incluir expressões 
aritméticas e modificar o nome dos atributos. Uma 
expressão pode ser uma combinação de valores, 
operadores e funções que produzem um valor. Os 
operadores aritméticos que podemos incluir são: 
Operadores Descrição 
+ Soma 
- Subtracção 
* Multiplicação 
/ Divisão 
 
Definições usando SQL 
Comandos DML (cont.) 
Exemplo: 
SELECT Cod_emp, Ordenado*12 
FROM Empregado; 
 
O comando ORDER BY é utilizado para ordenar 
tuplos. Neste exemplo, os tuplos são ordenados por 
código de departamento que, por defeito, é realizado 
de forma ascendente: 
SELECT Cod_Depar, Cargo 
FROM Empregado 
ORDER BY Cod_Depar; 
 
 
Definições usando SQL 
Comandos DML (cont.) 
A utilização da cláusula DISTINCT permite eliminar linhas repetidas 
SELECT DISTINCT Cod_Depar, Cargo 
FROM Empregado 
ORDER BY Cod_Depar; 
 
O comando WHERE corresponde ao operador de selecção da álgebra 
relacional. O comando WHERE é utilizado a seguir ao comando FROM e 
contém uma(s) condição(s) que os tuplos têm que satisfazer para que 
sejam visualizados. A palavra WHERE deverá possuir três elementos: 
O nome do atributo. 
O operador de comparação. 
O nome de um atributo, uma constante ou uma lista de valores. 
 
Definições usando SQL 
Comandos DML (cont.) 
Os operadores de comparação podem ser divididos em duas 
categorias: lógicos e SQL. 
Os operadores lógicos testam as seguintes condições: 
Operador Significado 
= Igual a 
> Maior que 
>= Maior ou igual que 
< Menor que 
<= Menor ou igual que 
<> Diferente 
Definições usando SQL 
Comandos DML (cont.) 
Operadores SQL, existem quatro, que operam sobre 
todos os tipos de dados: 
Operador Significado 
BETWEEN ..AND.. Entre dois valores 
IN(lista) Corresponde a qualquer valor da 
 lista 
LIKE Cadeia de caracteres que satisfaz 
 uma condição 
IS NULL É um valor nulo 
 
Definições usando SQL 
Comandos DML (cont.) 
Ainda sobre os operadores de comparação utilizados no comando 
WHERE, existem as respectivas expressões de negação: 
Operador Significado 
NOT BETWEEN ..AND..Não entre dois valores 
NOT IN(lista) Corresponde a nenhum valor da 
 lista 
NOT LIKE Cadeia de caracteres que não 
 satisfaz uma condição 
IS NOT NULL É um valor não nulo 
<> Diferente 
 
 
Definições usando SQL 
Comandos DML (cont.) 
Exemplos: 
SELECT Nome, Cargo, Cod_Depar 
FROM Empregado 
WHERE Cargo='Secretario'; 
 
SELECT Nome, N_Superior 
FROM Empregado 
WHERE N_Superior IN (7902,7566,7788); 
 
SELECT Nome 
FROM Empregado 
WHERE Nome LIKE 'S%'; 
 
Definições usando SQL 
Comandos DML (cont.) 
SELECT Nome, N_Superior 
FROM Empregado 
WHERE N_Superior IS NULL; 
 
SELECT Nome, Ordenado 
FROM Empregado 
WHERE Ordenado NOT BETWEEN 100000 AND 200000; 
Definições usando SQL 
Comandos DML (cont.) 
SELECT Nome, Ordenado,Cargo 
FROM Empregado 
WHERE Ordenado BETWEEN 100000 AND 200000 
AND Cargo='Secretário'; 
 
SELECT Nome, Ordenado, Cargo 
FROM Empregado 
WHERE Ordenado BETWEEN 100000 AND 200000 
OR Cargo='Secretário'; 
 
Definições usando SQL 
Comandos DML (cont.) 
SQL - DML: Exemplos 
O comando CASE 
Permite mudar o valor de um dado, por exemplo, 
poderiamo ter codificado o atributo sexo como 1 = 
masculino, 2 = feminino, 0 = indefinido , e então ao 
fazermos um select queremos expressar os valores por 
extenso ao invés de usar código. 
 
SELECT mat, nome, 
 CASE 
 WHEN sexo=1 THEN ‘Masculino’ 
 WHEN sexo=2 THEN ‘Feminino’ 
 WHEN sexo=0 THEN ‘Indefinido’ 
 END, endereco, salario 
FROM Empregado 
Operações de conjunto 
• As operações de conjunto union, intersect, e except operam nas 
relações e correspondem às operações da álgebra relacional: 
, respectivamente 
• Cada uma dessas operacoes elimina automaticamente 
duplicatas; para reter todas as duplicatas use ALL: union all, 
intersect all e except all. 
 
SQL - DML: Exemplos 
A construção EXISTS 
 
É usada para verificar se o resultado de uma 
consulta aninhada é vazia ou não. É sempre usado 
em conjunto com um query aninhada. 
 
 A construção exists retorna o valor true se o 
argumento da subquery é não vazio. 
 
exists r  r  Ø 
not exists r  r = Ø 
SQL - DML: Exemplos 
A construção EXISTS 
A consulta Q13 poderia ser: 
 
 
 
 
 
 
Podemos usar o NOT EXISTS(Q) 
 
SELECT e.nome 
FROM empregado e 
WHERE EXISTS (SELECT * 
 FROM dependente 
 WHERE e.matricula = matricula 
 and e.nome = nome and e.sexo = sexo) 
SQL - DML: Exemplos 
Q.15 Recupere os nomes dos empregados que não têm 
dependentes 
 
 
 
Podemos usar um conjunto de valores explícitos: 
Q16. Selecione a matricula de todos os empregados que 
trabalham nos projetos 10, 20 ou 30 
SELECT e.nome 
FROM empregado e 
WHERE NOT EXISTS (SELECT * 
 FROM dependente 
 WHERE e.matricula = matricula) 
SELECT DISTINCT matric 
FROM alocacao 
WHERE codigop in (10,20,30) 
SQL - DML: Exemplos 
DIVISÃO: 
Ex.: Mostre os empregados que trabalham em todos os projetos 
do empregado com mat = 800. 
 
 
 
 
 
 
 
 
 
 
 
Note que X – Y = Ø  X Y 
 
OBS.: No Oracle o operador diferença é minus 
 
SELECT mat 
FROM empregado e 
WHERE NOT EXISTS ( 
 ( SELECT codproj FROM alocacao WHERE mat = 800) 
 EXCEPT 
 ( SELECT codproj FROM alocacao a WHERE a.mat = 
e.mat) 
) 
SQL - DML: Exemplos 
Podemos verificar valores nulos através de 
IS NULL e IS NOT NULL: 
 
Q17. Selecione os nomes de todos os 
empregados que não têm supervisoresSELECT nome 
FROM empregado 
WHERE supervisor IS 
NULL 
SQL - DML: Exemplos 
Funções 
 
SQL fornece 5 funções embutidas: 
 
COUNT: retorna o número de tuplas ou valores 
especificados numa query 
SUM: retorna a soma os valores de uma coluna 
AVG: retorna a média dos valores de uma coluna 
MAX: retorna o maior valor de uma coluna 
MIN: identifica o menor valor de uma coluna 
 
OBS.:Estas funções só podem ser usadas numa cláusula SELECT ou 
numa cláusula HAVING (a ser vista depois) 
SQL - DML: Exemplos 
Q18. Encontre o total de salários, o maior salário, o menor 
salário e a média salarial da relação empregados 
 
 
Q19. Encontre o maior e menor salário do departamento de 
Produção 
SELECT SUM(salario), MAX(salario), 
MIN(salario), AVG(salario) 
FROM Empregado 
SELECT MAX(salario), MIN(salario) 
FROM Empregado e, Departamento d 
WHERE e.depto = d.coddep and 
 d.nome = ‘Produção’ 
SQL - DML: Exemplos 
Q.20 Obtenha o número de empregados da empresa 
 
 
 
Q.21 Obter o número de salários distintos do departamento de 
Contabilidade 
 
 
 
O que aconteceria se escrevêssemos COUNT(salario) ao invés de 
COUNT(DISTINCT salario))? 
SELECT COUNT(*) 
FROM Empregado 
SELECT COUNT(DISTINCT salario) 
FROM empregado e, departamento d 
WHERE (e.depto = d.coddep and d.nome = 
‘Contabilidade’) 
SQL - DML: Exemplos 
Q.22 Obter o nome dos empregados que 
tenham 2 ou mais dependentes 
 
SELECT e.nome 
FROM empregado e 
WHERE (SELECT COUNT(*) 
 FROM Dependente d 
 WHERE e.matricula = d.matricula) >= 2) 
SQL - DML: Exemplos 
Ex.: Uso da função max numa query dentro 
de um SELECT de outra query: 
SELECT mat, salario , (SELECT MAX(salario) 
FROM empregado) 
FROM empregado; 
SQL - DML: Exemplos 
LIKE: 
Permite comparações de substrings. Usa dois caracteres 
reservados ‘%’ (substitui um número arbitrário de caracteres) e 
‘_‘ (substitui um único caracter). 
 
Q.27 Obter os nomes de empregados cujos endereços estão em 
Natal, RN 
- Existem várias outras funções para se trabalhar com Strings: SUBSTRING(), 
UPPER(), LOWER(), ... 
SQL:1999 introduziu o construtor alternativo ao LIKE: 
SIMILAR TO (que permite o uso de expressões regulares como as usadas em 
UNIX) 
SELECT nome 
FROM empregado 
WHERE endereco LIKE ‘%Natal,RN%’ 
SQL - DML: Exemplos 
Q27. Queremos ver o efeito de dar aos empregados que 
trabalham no ProdutoX um aumento de 10% 
 
SELECT e.nome, 0.1*salario 
FROM empregado e, alocacao a, projeto 
p 
WHERE e.matricula = a.matricula and 
 a.codigop = p.codproj and 
 p.nome = ‘ProdutoX’ 
Junções 
No exemplo anterior a junção será feita por 
colunas de mesmo nome 
 
Cuidado que nome em empregado não é o 
mesmo que nome em departamento. 
Junções 
Exemplos: Outer Join 
preserva no resultado valores que não casam com 
Motivação: as vezes precisamos mostrar estes 
valores que não casam 
ex. Tabelas empregado e departamento onde o 
código do departamento em empregado é chave 
estrangeira, portanto, pode haver valores nulos. 
Se quisermos uma lista de todos os empregados 
com os nomes dos respectivos departamentos, 
usando uma junção natural eliminaria os 
empregados sem departamento (com valores 
null) 
Junções 
Exemplos: Left Outer Join 
 
C1 C2
10 15
20 25
C3 C4
10 BB
15 DD
T1 T2 Junção left outer de T1 com T2 
C1 C2 C3 C4
10 15 10 BB
20 25 Null Null
SELECT * 
FROM T1 LEFT OUTER JOIN T2 
ON T1.C1 = T2.C3 
Junções 
Exemplos: Right Outer Join 
 
C1 C2
10 15
20 25
C3 C4
10 BB
15 DD
 T1 T2 Junção right outer de T1 com T2 
C1 C2 C3 C4
10 15 10 BB
Null Null 15 DD
SELECT * 
FROM T1 RIGHT OUTER JOIN T2 
ON T1.C1 = T2.C3 
Junções 
Exemplos: Full Outer Join 
 
C1 C2
10 15
20 25
C3 C4
10 BB
15 DD
T1 T2 Junção full outer de T1 com T2 
C1 C2 C3 C4
10 15 10 BB
20 25 Null Null
Null Null 15 DD
SELECT * 
FROM T1 FULL OUTER JOIN T2 
ON T1.C1 = T2.C3