Upload
others
View
15
Download
0
Embed Size (px)
Citation preview
1
Curso de Técnico de Informática de Gestão 1
Linguagem SQLLMD – Linguagem de Manipulação de Dados
Bráulio Alturas
Curso de Técnico de Informática de Gestão 2
SQL como DML (Linguagem de Manipulação de Dados)
SELECT
Cláusula SELECT
Cláusula FROM
Joins
Cláusula WHERE
Cláusulas GROUP BY, HAVING e funções de agregação
UNION
UPDATE
DELETE
INSERT
Optimização de Querys
Views
2
Curso de Técnico de Informática de Gestão 3
SQL como DML (Linguagem de Manipulação de Dados)
SELECT (instrução para interrogação da base de dados)Select <campos>From <tabelas>Where <condições>Funções de AgregaçãoAgrupamento (group by)Restrições sobre grupos (having)Ordenação (order by)
INSERT, DELETE, UPDATE (para actualização da base de dados)
Curso de Técnico de Informática de Gestão 4
Comando SELECT
Um comando SQL típico para selecção de linhas obedece àseguinte estrutura (em que a cláusula SELECT corresponde àprojecção, a cláusula FROM ao produto cartesiano e a cláusula WHERE à selecção):
SELECT campos a seleccionar
FROM tabelas onde constam os campos indicados em Select
WHERE expressão lógica que indica quais as linhas que pretendemos seleccionar
ORDER BY campo pelo qual a listagem virá ordenada;
SELECT Nome, Morada
FROM Cliente
WHERE Cod_Postal = 1300
ORDER BY Nome;
Lista o nome e morada de uma tabela de clientes, mas apenas os clientes cujo código postal seja 1300 (ordenado por nome)
3
Curso de Técnico de Informática de Gestão 5
NOTA: É importante notar que qualquer comando SELECT devolve uma tabela (um conjunto de colunas e linhas).
Sempre que, no contexto da sintaxe da linguagem SQL for referida uma tabela, ela deve ser interpretada no sentido mais lato: uma tabela original (definida no esquema relacional) ou o resultado de um comando SELECT.
Comando SELECT
Curso de Técnico de Informática de Gestão 6
Comando SELECT
Exemplo com SELECT - ( SELECT <colunas> FROM <tabelas> )
Tabela ClienteNum NIF Nome Telefone Morada Cidade Vendas906000000 12364 Paula 21998 Av. AAA Lisboa 20 000900010036 12356 Joana 21333 R. XPTO Coimbra 30 000900110039 26875 Ana 22555 R. XXX Aveiro 40 000900211253 12593 Pedro 23387 R. VVV Aveiro 20 000900311236 22684 José 24463 R. SSS Porto 50 000900413654 55681 Isabel 23959 R. BBB Porto 30 000900512364 26426 Leonor 21456 R. SSS Lisboa 15 000
1) Qual o nome, morada e telefone dos Clientes?(como seleccionava se a tabela estivesse impressa?)
4
Curso de Técnico de Informática de Gestão 7
Comando SELECT
Exemplo com SELECT:
1) SELECT Nome, Morada, Telefone FROM Cliente
Resultado Final:Nome Morada TelefonePaula Av. AAA 21998Joana R. XPTO 21333Ana R. XXX 22555Pedro R. VVV 23387José R. SSS 24463Isabel R. BBB 23959Leonor R. SSS 21456
Curso de Técnico de Informática de Gestão 8
Comando SELECT
2) Qual o nome, telefone e morada dos clientes de Lisboa?
Num NIF Nome Telefone Morada Cidade Vendas906000000 12364 Paula 21998 Av. AAA Lisboa 20 000900010036 12356 Joana 21333 R. XPTO Coimbra 30 000900110039 26875 Ana 22555 R. XXX Aveiro 40 000900211253 12593 Pedro 23387 R. VVV Aveiro 20 000900311236 22684 José 24463 R. SSS Porto 50 000900413654 55681 Isabel 23959 R. BBB Porto 30 000900512364 26426 Leonor 21456 R. SSS Lisboa 15 000
5
Curso de Técnico de Informática de Gestão 9
Comando SELECT
2) SELECT Nome, Telefone, Morada FROM ClienteWHERE Cidade = ´Lisboa’
Resultado Final:
Nome Telefone MoradaPaula 21998 Av. AAALeonor 21456 R. SSS
SELECT <colunas> FROM <tabelas> [ WHERE <condição> ]
Uma condição é uma expressão que produz sempre um resultado do tipo boleano, isto é, TRUE ou FALSE (verdadeiro ou falso);
Cada condição tem como finalidade filtrar os registos que se pretendem calcular, i.e., para cada linha da tabela a condição é avaliada e caso seja verdadeira o registo é seleccionado.
Curso de Técnico de Informática de Gestão 10
Comando SELECT
Construção de condições com operadores relacionais ou lógicos
Utilizam-se operadores relacionais e/ou lógicos para impor e combinar restrições de selecção;
o resultado é sempre o valor lógico TRUE ou FALSE.
Operadores Relacionais<Campo> vs <Valor> ( =, >, <, >=, <=, <> )
Operadores Lógicospara combinar restrições ( AND, OR, NOT )
Precedência dos Operadoresparêntesis, multiplicação e divisão, adição e subtracção, NOT, AND, OR.
6
Curso de Técnico de Informática de Gestão 11
Comando SELECT
Construção de condições com outros operadores (BETWEEN)
���� BETWEEN para verificar a pertença ou não a um intervalo de valoresExemplo: Qual o nome e número dos Clientes cujas Vendas estão compreendidas entre 20000 e 30000?
Solução sem operador Between:SELECT Num, NomeFROM ClienteWHERE Vendas >= 20000 AND Vendas <= 30000
Solução com operador BETWEEN:SELECT Num, NomeFROM ClienteWHERE Vendas BETWEEN 20000 AND 30000
Curso de Técnico de Informática de Gestão 12
Comando SELECT
Construção de condições com outros operadores (IN)
���� IN para verificar a pertença ou não a um conjunto de valoresExemplo: Qual o nome, morada e contacto dos clientes das cidades de Lisboa e Coimbra ?
Solução sem operador IN:SELECT Nome, Morada, TelefoneFROM ClienteWHERE Cidade = "Lisboa" OR Cidade = "Coimbra“
Solução com operador INSELECT Nome, Morada, TelefoneFROM ClienteWHERE Cidade IN ("Lisboa","Coimbra")
7
Curso de Técnico de Informática de Gestão 13
Comando SELECT
Construção de condições com outros operadores (IS)
���� IS permite fazer comparações com o valor NULL (vazio)Exemplo: Qual o nome, morada e contacto dos clientes que possuam morada definida?
SELECT Nome, Morada, TelefoneFROM ClienteWHERE Morada IS NOT NULL
Curso de Técnico de Informática de Gestão 14
Comando SELECT
Construção de condições com outros operadores
Exemplo com os três operadores: Qual o nome, morada e contacto dos clientes cujas Vendas estão compreendidas entre 20000 e 30000, que são da cidade de Lisboa e Porto e que possuem morada definida?
SELECT Nome, Morada, Telefone FROM ClienteWHERE (Vendas BETWEEN 20000 AND 30000)AND (Cidade IN ("Lisboa", "Porto"))AND (Morada IS NOT NULL)
8
Curso de Técnico de Informática de Gestão 15
Comando SELECT
Construção de condições com outros operadores (AS)
���� AS permite atribuir um novo nome a um campo ou a uma tabelaExemplo: Qual o nome dos clientes que possuam morada definida?
SELECT C.Nome AS “Nome do Cliente”FROM Cliente AS CWHERE C.Morada IS NOT NULL
Curso de Técnico de Informática de Gestão 16
Comando SELECT
Funções de Agregação (COUNT, MAX, MIN, SUM, AVG)
Para cálculos sobre o resultado de um SELECT:
COUNT - devolve o número de linhasMAX (coluna) - devolve o maior valor da colunaMIN (coluna) - devolve o menor valor da colunaSUM (coluna) - devolve a soma de todos os valores da colunaAVG (coluna) - devolve a média (AVeraGe) de todos os valores da coluna
As funções Min, Max, Count (…) e Count (*) podem ser utilizadas com qualquer tipo de dados (numéricos, alfanuméricos)As funções SUM e AVG apenas se aplicam a campos numéricos.
9
Curso de Técnico de Informática de Gestão 17
Comando SELECT
Funções de Agregação (COUNT, MAX, MIN, SUM, AVG)
Para SELECCIONAR e CALCULAR
SELECT <f_agregacao> FROM <tabelas>[WHERE <condicao>]
<f_agregacao>-função a aplicar ao resultado da selecção<condicao> - condição de selecção dos elementos sobre os quais será aplicada a função de cálculo
NOTA: quando se utilizam funções de agregação não se podem seleccionar campos dos registos (excepto com o GROUP BY)
Curso de Técnico de Informática de Gestão 18
Comando SELECT
Funções de Agregação (COUNT, MAX, MIN, SUM, AVG)
Exemplo 1: Qual o número de clientes da empresa?
SELECT Count (*) FROM Cliente
Exemplo 2: Qual a menor venda a um cliente de Lisboa?
SELECT Min (Vendas) FROM ClienteWHERE Cidade= “Lisboa”
Exemplo 3: Qual o número de cidades?
SELECT DISTINCT Count (Cidade) FROM Cliente
10
Curso de Técnico de Informática de Gestão 19
Comando SELECT
Funções de Agregação (COUNT, MAX, MIN, SUM, AVG)
Repare-se que:
COUNT (*)devolve o número de linhas que resulta de um SELECT
COUNT (Coluna)devolve nº de ocorrências numa coluna diferentes de NULL
COUNT (DISTINCT Coluna)devolve o nº de ocorrências (sem repetições) na coluna
Curso de Técnico de Informática de Gestão 20
Qualquer expressão sintacticamente válida pode ser argumento da cláusula SELECT. Por exemplo, os dois seguintes comandos são válidos:
SELECT Produto, Quantidade * Preço FROM Item; (devolve duas colunas em que a segunda corresponde ao produto das colunas quantidade e preço);
SELECT ‘teste’ FROM Item; (se a tabela item tiver 20 linhas, o comando devolve 20 vezes a palavra teste).
Podem ser atribuídos aliases (sinónimos) às colunas. Por exemplo o comando anterior poderia ser escrito da seguinte forma (permite dar um nome – Total - à segunda coluna devolvida):
SELECT Produto, Quantidade * Preço AS Total FROM Item;
Comando SELECT
11
Curso de Técnico de Informática de Gestão 21
Caso pretendamos visualizar todos os campos de uma tabela, como alternativa a enumera-los todos, pode-se usar a constante *:
SELECT * FROM Item;
Caso pretendamos eliminar duplicados na listagem obtida, utiliza-se a cláusula DISTINCT (elimina linhas duplicadas)
SELECT DISTINCT CodPostal FROM Aluno; (devolve os códigos postais existentes)
Caso se pretenda listar dois atributos com o mesmo nome (correspondentes a duas tabelas referidas na cláusula FROM) énecessário preceder o nome do campo pelo nome da tabela de onde ele é originário.
Comando SELECT
Curso de Técnico de Informática de Gestão 22
Caso pretendamos apenas visualizar algumas linhas de uma tabela:SELECT FIRST * FROM Item;
SELECT TOP 3 * FROM Item;
Caso pretendamos armazenar o resultado em variáveis (apenas quando o comando apenas devolve uma linha)SELECT Max(Quantidade) INTO Maximo FROM ITEM;
A cláusula INTO apenas se justifica quando o SQL é utilizado dentro de outra linguagem de programação (Java, C, Visual Basic, etc.) ou em Stored Procedures(ver mais adiante). Na cláusula INTO podem-se referir várias variáveis (o mesmo número das expressões da cláusula SELECT).
Comando SELECT
12
Curso de Técnico de Informática de Gestão 23
SELECT – Cláusula FROM
Na cláusula FROM indicam-se os nomes das tabelas envolvidas na interrogação, separadas por vírgulas. Quando existe mais que uma tabela o SQL executa automaticamente um produto cartesiano entreas tabelas. Por exemplo, o seguinte comando executa um produto cartesiano entre as tabelas Cliente e Localidade, devolvendo todos os campos (das duas tabelas):
SELECT * FROM Cliente, Localidade;
Caso queiramos obter um JOIN é necessário explicitar a forma como o pretendemos obter, por exemplo:SELECT * FROM Cliente INNER JOIN Localidade;
SELECT * FROM Cliente LEFT OUTER JOIN Localidade;
Curso de Técnico de Informática de Gestão 24
SELECT – Cláusula FROM
Á semelhança dos sinónimos dos atributos, é possível atribuir aliases às tabelas (os sinónimos nas tabelas são relevantes nas subquerys, analisadas mais adiante): Select * From Cliente AS Cliente_Empresa;
Existe uma tabela de sistema que apenas contém uma linha denominada DUMMY. Ela pode ser utilizada quando pretendemos listar uma expressão que não é obtida a partir de nenhuma tabela.
SELECT COS(1) FROM DUMMY;
Devolve o cosseno de 1
13
Curso de Técnico de Informática de Gestão 25
SELECT – JOINS
Tipos de JOINS
Key Join SELECT * FROM Cliente KEY JOIN Localidade;
Critério: chave estrangeira. Apenas funciona se existir uma (e apenas uma) chave estrangeira a ligar as duas tabelas.
Natural Join SELECT * FROM Cliente NATURAL JOIN Localidade;
Critério: atributos com o mesmo nome. Apenas funciona se existir pelo menos um atributo com o mesmo nome e tipo de dados compatíveis.
Join com ComparaçõesSELECT * FROM Cliente JOIN Localidade
ON Cliente.Cod_Postal = Localidade.Cod_Postal;
Critério: indicado explicitamente no comando através do ON. É o mais flexível.
Curso de Técnico de Informática de Gestão 26
INNER JOIN: Produto Cartesiano, em que apenas são seleccionados tuplos nos quais os valores dos atributos comuns são idênticos. Normalmente o atributo comum não é repetido.
OUTER LEFT JOIN: resultado do NATURAL JOIN união com os tuplos da relação à esquerda que não ”cruzaram” (não existe nenhum tuplona relação à direita que tenha o mesmo valor no atributo comum) com a relação à direita.
OUTER RIGHT JOIN: resultado NATURAL JOIN união com os tuplosda relação à direita que não ”cruzaram” com a relação à esquerda.
SELECT – JOINS
14
Curso de Técnico de Informática de Gestão 27
INNER, LEFT OUTER e RIGHT OUTER JOIN
Podem ser utilizados nos Key, Natural e ON Join. Quando nada é indicado é efectuado um INNER Join.
SELECT * FROM Cliente NATURAL INNER JOIN Localidade;
SELECT * FROM Cliente NATURAL LEFT OUTER JOIN Localidade;
SELECT * FROM Cliente KEY RIGHT OUTER JOIN Localidade;
SELECT * FROM Cliente LEFT OUTER JOIN Localidade
ON Cliente.Cod_Postal = Localidade.Cod_Postal;
SELECT – JOINS
Curso de Técnico de Informática de Gestão 28
Exemplo de INNER JOIN
SELECT – JOINS
A Expressão:
SELECT REPRESENTANTE.nome, ENTIDADE.[designação social]FROM ENTIDADE INNER JOIN REPRESENTANTE ON ENTIDADE.[codigo interno]=REPRESENTANTE.[codigo interno];
É equivalente a:
SELECT REPRESENTANTE.nome, ENTIDADE.[designação social]FROM ENTIDADE, REPRESENTANTEWHERE ENTIDADE.[codigo interno]=REPRESENTANTE.[codigointerno];
15
Curso de Técnico de Informática de Gestão 29
SELECT – Cláusula WHERE
Na cláusula WHERE pode constar qualquer expressão lógica. A expressão é avaliada linha a linha, isto é, para cada linha o SQL avalia o valor da expressão e, caso seja verdadeira, devolve a linha.
SELECT * FROM Cliente WHERE CodPostal > 1000
AND CodPostal < 2000;
SELECT * FROM Cliente WHERE (CodPostal > 1000
AND CodPostal < 2000) OR CodPostal = 3000;
SELECT * FROM CodPostal WHERE 1 = 1; (devolve todos os registos)
Curso de Técnico de Informática de Gestão 30
SELECT – Cláusula WHERE
Os principais operadores utilizados na cláusula WHERE são: =, <, >, >=, <=, <>, AND, OR, NOT, IN, LIKE, BETWEEN e ISNULL. O operador IN é verdadeiro quando um elemento faz parte de um conjunto. O operador permite a utilização de wildcards. O operador ISNULL permite lidar com valores NULL. Alguns exemplos:
SELECT * FROM CLIENTE WHERE CodPostal BETWEEN 1000,2000;
SELECT * FROM CLIENTE WHERE Nome LIKE ‘João%’ (todos os clientes começados por João)
SELECT * FROM Cliente WHERE Nacionalidade IN (‘Portugal’,
‘Brasil’); (todos os clientes portugueses ou brasileiros)
SELECT * FROM Cliente WHERE Nacionalidade NOT IN (‘Portugal’,
‘Brasil’); (todos os clientes excepto os portugueses e brasileiros)
SELECT * FROM Cliente WHERE Nacionalidade IS NOT NULL; (todos os clientes com nacionalidade conhecida)
16
Curso de Técnico de Informática de Gestão 31
SELECT – Cláusula WHERE
A cláusula WHERE pode ser utilizada para produzir joins. Os dois seguintes comandos produzem o mesmo resultado (o 1º é mais eficiente devido às optimizações do SGBD).
SELECT * FROM Cliente INNER JOIN Localidade
ON Cliente.Cod_Postal = Localidade.Cod_Postal;
SELECT * FROM Cliente, Localidade
WHERE Cliente.Cod_Postal = Localidade.Cod_Postal;
Os seguinte comando produz um INNER JOIN apesar de estar indicado um LEFT JOIN (primeiro é efectuado o LEFT JOIN, mas posteriormente o SGBD apenas considera as linhas em que existe igualdade entre as chaves).
SELECT * FROM Cliente NATURAL LEFT OUTER JOIN Localidade
WHERE Cliente.Cod_Postal = Localidade.Cod_Postal;
Curso de Técnico de Informática de Gestão 32
SELECT – Cláusula GROUP BY
Agrupamentos (GROUP BY)
Para Agrupar: SELECT <colunas>FROM <tabelas>WHERE <condicao>][GROUP BY <Campo1, Campo2,…>]
• Quando se pretende passar a ter agrupamentos, perde-se a identidade individual em favor da categorização.• Os elementos do SELECT não podem referir características de elementos individuais mas de grupo, que poderão ser: cálculos referentes aos grupos, ou propriedades identificadoras de cada grupo.• A condição de WHERE aplica-se a cada elemento do grupo• O agrupamento efectua-se com base nos atributos escritos na cláusula GROUP BY.• As funções de Agregação executam cálculos autónomos dentro de cada grupo especificado pelo GROUP BY.
17
Curso de Técnico de Informática de Gestão 33
Sempre que existe uma função de agregação na cláusula SELECT, todos os restantes atributos da cláusula têm que estar incluídos na cláusula GROUP BY. O comando que de seguida se apresenta retorna o maior bilhete de identidade existente:
SELECT MAX(Bi) FROM Cliente;
Caso pretendêssemos visualizar o nome desse cliente, não poderíamos simplesmente acrescentar o atributo nome à cláusula SELECT. Pela regra anteriormente referida, teríamos que considerar a cláusula GROUP BY:
SELECT MAX(Bi), NOME FROM Cliente GROUP BY Nome;
No entanto, o resultado do comando seria a listagem de todos os nomes com a indicação do BI associado a cada nome. Mais adiante (Subquerys IV) apresenta-se a resolução desta interrogação.
SELECT – Funções de Agregação
Curso de Técnico de Informática de Gestão 34
Para além das funções COUNT e MAX, existem outras, tais como SUM, AVG e MIN.
Apenas a função COUNT não necessita de argumento: o primeiro comando retorna o total de registos de clientes enquanto o segundo devolve o total de clientes com o código postal conhecido:
SELECT COUNT(*) FROM Cliente;
SELECT COUNT(CodPostal) FROM Cliente;
Note-se que o segundo comando é equivalente ao seguinte:
SELECT COUNT(*) FROM Cliente WHERE CodPostal IS NOT NULL;
SELECT – Funções de Agregação
18
Curso de Técnico de Informática de Gestão 35
SELECT – Cláusula GROUP BY
Uma coluna que não seja uma função de agregação só pode estar na cláusula SELECT se estiver na cláusula GROUP BY.
Exemplo:
SELECT Departamento.DesigDep, Min (Empregado.SalarioBase) AS MinimoFROM Empregado, DepartamentoWHERE (Empregado.CodDep)= Departamento.CodigoDepGROUP BY Departamento.DesigDep
Curso de Técnico de Informática de Gestão 36
SELECT – Cláusula GROUP BY
Considere-se o seguinte exemplo:
Departamento (CodDep, DesigDep)
Categoria (CodCat, DesigCategoria,...)
Empregado (CodEmp, Nome, CodCat, CodDep, SalárioBase)
Perguntas:1) Qual o número de funcionários por departamento?2) Qual o montante de salários por departamento?3) Qual a média salarial por categoria profissional?4) Qual o salário mínimo praticado em cada Departamento para cada Categoria?
As respostas implicam … AGRUPAR e CALCULAR … para isso iremos utilizar a cláusula GROUP BY
19
Curso de Técnico de Informática de Gestão 37
SELECT – Cláusula GROUP BY
1) Qual o número de funcionários por departamento?
SELECT Departamento.DesigDep, Count (*) FROM Empregado, DepartamentoWHERE Empregado.CodDep= Departamento.CodDepGROUP BY Departamento.DesDep
Uma alternativa (qual a diferença?)
SELECT CodDep, Count (*)FROM EmpregadoGROUP BY CodDep
Pretendia-se listagem: Departamento, Número de EmpregadosDepartamento: Atributo de Agrupamento (DesigDep)Número Empregados: Função Count
Curso de Técnico de Informática de Gestão 38
SELECT – Cláusula GROUP BY
2) Qual o montante de salários por Departamento?
Pretende-se: Departamento, Montante SalarialDepartamento: Atributo de Agrupamento (DesDep)Montante Salarial: Função SUM
SELECT Departamento.DesigDep, Sum(Empregado.SalarioBase) AS Total
FROM Empregado, DepartamentoWHERE Empregado.CodDep= Departamento.CodDepGROUP BY Departamento.DesigDep
Uma alternativa (qual a diferença?)
SELECT CodDep, Sum (SalarioBase) AS TotalFROM EmpregadoGROUP BY CodDep
20
Curso de Técnico de Informática de Gestão 39
SELECT – Cláusula GROUP BY
3) Qual a média salarial por Categoria Profissional?
Pretende-se: Categoria, Média SalarialCategoria: Atributo de Agrupamento (CodCat )Média Salarial: Função AVG
SELECT Categoria.CodCat, Avg (Empregado.SalarioBase) AS MediaSalarioFROM Categoria, EmpregadoWHERE Empregado.CodCat= Categoria.CodCatGROUP BY Categoria.CodCat
Curso de Técnico de Informática de Gestão 40
SELECT – Cláusula GROUP BY
4) Qual o salário mínimo praticado em cada Departamento para cada Categoria?
Pretende-se: Departamento, Categoria,Salário Min.Departamento e Categoria: Atributos de Agrupamento (DesigDepe CodCat)
Salário Mínimo: Função MIN
SELECT Categoria.CodCat, Min (Empregado.SalarioBase) AS SalarioMinimo, Departamento.DesigDep
FROM Empregado, Categoria, DepartamentoWHERE (Empregado.CodCat=Categoria.codcat) and(Empregado.CodDep=Departamento.CodDep)
GROUP BY Categoria.CodCat, Departamento.DesDep
21
Curso de Técnico de Informática de Gestão 41
SELECT – Cláusula GROUP BY
4) Qual o salário mínimo praticado em cada Departamento para cada Categoria?
outra solução:
Pretende-se: Departamento, Categoria, Salário Min.Departamento e Categoria: Atributos de Agrupamento (CodDep e CodCat)
Salário Mínimo: Função MIN
SELECT CodDep, CodCat, MIN (SalarioBase)FROM EmpregadoGROUP BY CodDep, CodCat
Curso de Técnico de Informática de Gestão 42
O seguinte comando lista, para cada código postal, o número de clientes que a ele estão associados (desde que exista mais do que um cliente):
SELECT CodPostal, COUNT(CodPostal) FROM Cliente GROUP BY
CodPostal HAVING COUNT(CodPostal) > 1;
1500Luis056
2100Paula001
1300Nuno011
2100Ana013
1500João001
CodPostalNomeNúmero
Cliente
1500Luis056
1500João001
CodPostalNomeNúmero
CodPostal = 1500 (Total:2)
2100Paula001
2100Ana013
CodPostalNomeNúmero
CodPostal = 2100 (Total:2)
1300Nuno011
CodPostalNomeNúmero
CodPostal = 1300 (Total:1)
22100
11300
21500
COUNT(CodPostal)CodPostal
Nova tabela temporária
22100
21500
COUNT(CodPostal)CodPostal
Resultado Final
SELECT – Cláusula GROUP BY
22
Curso de Técnico de Informática de Gestão 43
SELECT – Cláusula HAVING
Restrições sobre os Agrupamentos (HAVING)
Qual o salário mínimo praticado em cada Departamento cuja média salarial é superior a €20.000 ?
Pretende-se: Departamento, Salário MinDepartamento: Atributo de Agrupamento (CodDep)Salário Mínimo: Função MINRestrição: A média salarial do GRUPO > 20.000
SELECT Departamento.coddep, MIN (SalarioBase) AS MinimoFROM Empregado, DepartamentoWHERE Empregado.coddep= Departamento.coddepGROUP BY Departamento.coddepHAVING AVG (SalarioBase)> 20000
Curso de Técnico de Informática de Gestão 44
SELECT – Cláusula HAVING
Qual o número de trabalhadores por departamento cujo salário é superior a €10.000, onde a média salarial desses trabalhadores é superior ou igual a €20.000?
Eliminam-se os empregados com salário inferior ou igual a €10.000WHERE SalarioBase > 10000
Agrupam-se os funcionários seleccionados por departamentoGROUP BY CodDep
Calcula-se a Média (com AVG) desses funcionários por departamento e seleccionam-se os departamentos cuja média é >= €20.000
HAVING AVG (SalarioBase) >= 20000
Contam-se os empregados seleccionados para esse grupoSELECT COUNT (*), CodDep
23
Curso de Técnico de Informática de Gestão 45
SELECT – Cláusula HAVING
Pretende-se: Departamento, NºTrabalhadores- Departamento: Atributo de Agrupamento (cod_dep)-NºTrabalhadores: Função COUNT
Restrições:- Salário individual > €10.000-A média salarial do GRUPO >= €20.000
SELECT CodigoDep, COUNT(*)FROM EmpregadoWHERE Empregado.SalarioBase > 10000GROUP BY CodDepHAVING AVG(SalarioBase)>= 20000
Curso de Técnico de Informática de Gestão 46
WHERE ou HAVING?
•A cláusula HAVING tem como finalidade seleccionar grupos que obedeçam a determinadas características de grupo
•A cláusula HAVING deve sempre conter funções de agregação
•A cláusula WHERE tem como finalidade seleccionar registos
•A cláusula WHERE nunca contém funções de agregação
•A cláusula WHERE tem prioridade em relação àcláusula HAVING
SELECT – Cláusula HAVING
24
Curso de Técnico de Informática de Gestão 47
SELECT – Cláusula ORDER BY
Ordenação (ORDER BY)
Seleccionar todos os Empregados, ordenando o resultado pelo código de empregado.
Utiliza-se comando SELECT com cláusula ORDER BY:
SELECT *FROM EmpregadoORDER BY CodEmp
Curso de Técnico de Informática de Gestão 48
SELECT – Cláusula ORDER BY
SELECT [DISTINCT] coluna, … | *FROM tabela[WHERE condição][GROUP BY ...][HAVING ...][ORDER BY coluna [ASC|DESC],...]
• A ordenação baseia-se no código ASCII de cada caracter.• Os dígitos aparecem antes dos caracteres alfabéticos e as maiúsculas aparecem antes das minúsculas.• 0 < 1<… < 9 <… < A < B < Z <… < a < b <… < z• Por defeito a ordenação é Ascendente.
SELECT * FROM EMPREGADOORDER BY nome = ORDER BY nome ASC (de A a Z)
25
Curso de Técnico de Informática de Gestão 49
SELECT – Subquerys
Uma subquery é um comando SELECT dentro de um comando SELECT. Muitas interrogações apenas podem ser resolvidas através de subquerys. Um comando SELECT normalmente liga-se a outro através da cláusula WHERE.
Os operadores IN e EXISTS são normalmente utilizadas nas subquerys.
Operador IN devolve verdade quando um elemento pertence a um conjunto.
Operador EXISTS devolve verdade caso a subquery retorne pelo menos uma linha.
Curso de Técnico de Informática de Gestão 50
SELECT – Subquerys
Os dois comandos abaixo apresentados (equivalentes) devolvem os nomes que estão associados a pelo menos duas pessoas (dois BI’s) (sem a cláusula DISTINCT o comando retornaria os nomes duplicados; os sinónimos das tabelas são necessários para evitar ambiguidades na condição WHERE da subquery):
SELECT DISTINCT(Nome) FROM Cliente as Cliente1
WHERE NOME IN
(SELECT NOME FROM Cliente as Cliente2 WHERE
Cliente1.bi <> Cliente2.bi);
SELECT DISTINCT(Nome) FROM Cliente as Cliente1
WHERE EXISTS
(SELECT * FROM Cliente as Cliente2 WHERE
Cliente1.bi <> Cliente2.bi AND
Cliente1.Nome = Cliente2.Nome);
26
Curso de Técnico de Informática de Gestão 51
SELECT – Subquerys
Os operadores ALL (todos) e ANY (pelo menos um) também são frequentes nas subquerys. O comando para retornar o maior bilhete de identidade (e nome associado) existente é: SELECT Nome, BI FROM Cliente
WHERE BI >= ALL
(SELECT BI FROM Cliente);
Caso pretendêssemos um bilhete de identidade que não fosse o menor, o comando seria: SELECT Nome, BI FROM Cliente
WHERE BI > ANY
(SELECT BI FROM Cliente);
Curso de Técnico de Informática de Gestão 52
SELECT – Subquerys
As subquerys também podem ser colocadas nas cláusula SELECT e FROM.
O seguinte exemplo devolve, para cada cliente, o total de facturas associadas:SELECT Nome, (SELECT COUNT(*) FROM Factura
WHERE Factura.BI = Cliente.BI)
FROM Cliente;
O seguinte exemplo devolve, para cada cliente, o total de facturas associadas:SELECT DISTINCT Nome
FROM (Select * From Cliente) as Cliente;
27
Curso de Técnico de Informática de Gestão 53
Comando UNION
A união de comandos SELECT é efectuada através do operador UNION.
O seguinte comando devolve os nomes dos clientes e fornecedores:
SELECT Nome FROM Cliente
UNION
SELECT Nome FROM Fornecedor;
Caso não pretendamos eliminar nomes duplicados o comando será:
SELECT Nome FROM Cliente
UNION ALL
SELECT Nome FROM Fornecedor;
Curso de Técnico de Informática de Gestão 54
Comando UPDATE
Um comando UPDATE para alteração de linhas obedece à seguinte estrutura:
UPDATE tabela a alterar
SET coluna a alterar = expressão
WHERE expressão lógica que indica quais as linhas que
pretendemos alterar
O seguinte comando transforma os códigos postais 1200 em 1500:UPDATE Cliente SET CodPostal = 1500
WHERE CodPostal = 1200;
28
Curso de Técnico de Informática de Gestão 55
Comando DELETE
Um comando DELETE para anulação de linhas obedece à seguinte estrutura:
DELETE FROM tabela a anular
WHERE expressão lógica que indica quais as linhas que
pretendemos alterar
O seguinte comando apaga os códigos postais 1200DELETE FROM Cliente
WHERE CodPostal = 1200;
Curso de Técnico de Informática de Gestão 56
Comando INSERT
Através do comando INSERT podem-se inserir uma linha ou várias linhas em simultâneo. Para inserir uma linha um comando INSERT obedece à seguinte estrutura:
INSERT INTO tabela a inserir (colunas onde vão ser inseridos os valores)
VALUES (valores a inserir)
Para inserir um conjunto de linhas, um comando INSERT obedece àseguinte estrutura:
INSERT INTO tabela a inserir (colunas onde vão ser inseridos os valores)
SELECT valores a inserir
FROM ...
INSERT INTO Produto (cod_produto, tipo) VALUES (123456, ‘MP’);
INSERT INTO Produto (cod_produto, tipo)
SELECT cod_materia, ‘MP’ FROM Materia_Prima;
29
Curso de Técnico de Informática de Gestão 57
Optimizações de Querys
Na maioria dos SGBD’s a optimização de querys é feita automaticamente pelo planeador do SGBD. Ele, com base em estimativas de tempos e com base no histórico das transacções, decide qual a melhor estratégia a adoptar para executar uma query.
Exemplosselect PLAN('SELECT Titulo FROM Pub');
Scan pub sequentially
select PLAN('SELECT ISBN FROM Pub');
Scan pub sequentially
select PLAN('SELECT ISBN FROM Pub Order By ISBN');
Scan pub using index ndx_id
select PLAN('SELECT * FROM Pub Where Data = 1900 ‘);
Scan pub using index ndx_data
select PLAN('SELECT * FROM Pub Where Data = 1900 or Data = 2000’);
Scan pub sequentially
Índice apenas em ISBN e Data
Não vale a pena usar o índice
Curso de Técnico de Informática de Gestão 58
Optimizações de Querys
select PLAN('SELECT Nome, Localidade FROM Cliente KEY JOIN Localidade');
Scan Localidade sequentially
Scan Cliente using foreign key FK_Localidade
select PLAN('SELECT Nome, Localidade FROM Localidade KEY JOIN Cliente’);
Scan Localidade sequentially
Scan Cliente using foreign key FK_Localidade
select PLAN(
'SELECT Nome FROM Cliente as CL1 Where NOT EXISTS (
SELECT * From Cliente as CL2 Where CL2.Nome = CL1.Nome AND Cl2.Bi<>Cl1.BI)');
Scan Cliente AS CL1 sequentially
Scan Cliente AS CL2 sequentially
select PLAN(
'SELECT Nome FROM Cliente as CL1 Where Cl1.NOME NOT IN (
SELECT CL2.Nome From Cliente as CL2 Where Cl2.Bi<>Cl1.BI)');
Scan Cliente AS CL1 sequentially
Scan Cliente AS CL2 sequentially
Diferentes comandos produzem o mesmo plano
30
Curso de Técnico de Informática de Gestão 59
Views
As Views não são mais do que comandos SELECT armazenados. São por vezes denominadas tabelas temporárias. Note-se que o resultado de uma execução de uma view (os registos que ela devolve) depende dos registos armazenados no momento nas tabelas de suporte à view. As views podem ser utilizadas dentro de comandos SELECT.
CREATE VIEW Clientes_Lisboa (BI, Nome)
AS Select BI, Nome FROM Cliente KEY JOIN Localidade
Where Localidade = 'Lisboa'
WITH CHECK OPTION;
Select Nome From Clientes_Lisboa;
As views não podem conter a cláusula ORDER BY e apenas permitem a inserção, remoção e alteração de registos caso não contenham as cláusulas GROUP BY e UNION. A cláusula CHECK OPTION rejeita alterações e inserções na view que não obedeçam ao critério da cláusula SELECT que a define.
Curso de Técnico de Informática de Gestão 60
Prepare Statement
O comando Prepare Statement permite optimizar acessos à base de dados que são efectuados múltiplas vezes em uma transacção. Quando um comando SQL é enviado ao SGBD, este, para além de ter o interpretar (parsing), tem de gerar um plano para a sua realização. O comando Prepare Statement evita que essas duas tarefas sejam executadas mais do que uma vez.
A comando apenas se justifica quando o SQL é utilizado dentro de outra linguagem de programação (Java, C, Visual Basic, etc.) ou em Stored Procedures(ver mais adiante).
Exemplo (simplificado, mais adiante apresenta-se um exemplo completo em JAVA)
PREPARE calc_factura("Update Factura Set Valor = ? Where Num_Factura = ? ;");
calc_factura.setDouble(1, totalFactura);
calc_factura.setInt(2, lastFactura);
calc_factura.executeSQL()
Passagem de parâmetros
Execução
31
Curso de Técnico de Informática de Gestão 61
Stored Procedures
Os Stored Procedures (SP) são procedimentos SQL compilados e armazenados junto da base de dados. Trata-se da forma mais eficiente de executar comandos SQL. Para além dos comandos SQL é possível utilizar as habituais primitivas de controlo (If, While), os habituais operadores lógicos e variáveis. Trata-se no entanto de uma linguagem simples que não substitui as linguagens procedimentais(C, Java, etc.).
É possível passar parâmetros para um SP, assim como um SP retornar valores (nomeadamente o resultado de comandos SELECT). Os SP podem ser chamados dentro de um comando SELECT ou a partir de uma linguagem procedimental (através da primitiva CALL).
Curso de Técnico de Informática de Gestão 62
Stored Procedures - Exemploscreate procedure dba.newPubdelay()
begin
declare i integer;
declare lastISBN integer;
select max(ISBN) into lastISBN from Pub;
set i=1;
while i < 100000 loop
set i=i+1
end loop;
insert into pub(isbn) values(lastISBN+1);
commit work
end
Se após este SP for executado um outro idêntico sem o ciclo (simula uma pausa) ocorre uma situação de dead lock.
create procedure dba.getPubAno(in Par_Ano integer)
result(ISBN integer,Titulo long varchar)
begin
select ISBN,Titulo from Pub where DAta = Par_Ano
end
O comando CALL getPubAno(2000); retorna as publicações de 2000
32
Curso de Técnico de Informática de Gestão 63
Triggers
Os triggers são procedimentos armazenados junto da base de dados que são associados a eventos que ocorrem nas tabelas. Através dos triggers o motor de base de dados reage automaticamente quando esses eventos (alterações dos dados nas tabelas) ocorrem. A forma como reagem é definida pelo procedimento associado ao trigger (um trigger apenas pode estar associado a um evento de uma tabela). Os eventos aos quais podem se associar os triggers: Insert, Update, Delete
e Update Of (para cada um deles é necessário discriminar se o evento ocorre antes
ou depois da operação terminar).
Os eventos Insert, Update e Delete ocorrem ao nível do registo completo, enquanto que o evento Update Of ocorre ao nível de um campo da tabela (énecessário indicar qual o atributo no momento da definição do trigger).
Os triggers podem ainda ser row-level ou statement-level. Os triggers row-level
ocorrem após cada linha da tabela ser alterada (anulada ou inserida). Os triggersstatement-level ocorrem após uma operação sobre a tabela ser concluída (por exemplo, após um conjunto de registos ser inserido).
Curso de Técnico de Informática de Gestão 64
Triggers
O procedimento associado ao trigger obedece no essencial à mesma sintaxe que os SP.
Exemplo: trigger (row-level) que é activado sempre que se insere uma matéria prima na tabela de produtos (insere um registo na tabela de matérias primas):
create trigger "dba".MateriaPrima after insert on Produto
referencing new as new_produto
for each row
when(new_produto.Tipo='MP')
begin
insert into MateriaPrima(cod_produto)
values(new_produto.cod_produto)
end