SQL Programação em SQL
SQL como Linguagem de Manipulação de Dados A SQL possui um conjunto de instruções que se podem classificar em dois grupos: • Interrogação da Base de Dados (SELECT) • Actualização da Base de Dados (INSERT, DELETE e UPDATE)
SQL como Linguagem de Manipulação de Dados Interrogação Modelo de uma questão SQL,
na sua forma mais simples:
SELECT <colunas> FROM <tabelas> [WHERE <condição>] Em que:
<colunas> especifica a lista de atributos cujos valores interessa conhecer <tabelas> especifica quais as tabelas envolvidas no processamento <condição> traduz a expressão lógica que define a condição a verificar
SQL como Linguagem de Manipulação de Dados Interrogação
Ex: Considerando o universo dos clientes de um banco, assim como as contas e empréstimos desses clientes nas várias agências do banco, representamos as seguintes relações:
Clientes (cod_cliente, cliente, profissao, localidade) Agencias (cod_agencia, agencia, localidade) Contas (num_conta, tipo_conta, cod_cliente, cod_agencia, saldo) Emprestimos (num_emprestimo, cod_cliente, cod_agencia, valor)
SQL como Linguagem de Manipulação de Dados Interrogação
Utilizando o esquema relacional anterior: 1 – Quais os clientes (cod_cliente, cliente) deste banco? SELECT cod_cliente, cliente FROM Clientes
SQL como Linguagem de Manipulação de Dados Interrogação
2 – Quais os clientes que residem em Braga? SELECT * FROM Clientes WHERE localidade = ‘Braga’ Nota: O asterisco (*) na cláusula SELECT indica que se pretendem todos os atributos no resultado
SQL como Linguagem de Manipulação de Dados Interrogação
3 – Quais os clientes (cod_cliente) com contas na agencia cod_agencia=‘123’? SELECT DISTINCT cod_cliente FROM Contas WHERE cod_agencia = ‘123’ Nota: Dado que na agência podem existir clientes com várias contas, a forma de evitar a repetição do mesmo cod_cliente no resultado final é a utilização da cláusula DISTINCT.
SQL como Linguagem de Manipulação de Dados Interrogação
4 – Quais os clientes que residem na mesma localidade das agências onde possuem contas? SELECT Clientes. * FROM Clientes, Agencias WHERE Clientes.localidade = Agencias.localidade
Nota: Trata-se de uma questão que envolve a junção das tabelas Clientes e Agências sobre o atributo localidade
SQL como Linguagem de Manipulação de Dados Interrogação
5 – Quais os clientes com empréstimos de valor superior a 50000 Euros? SELECT Clientes. * FROM Clientes, Emprestimos WHERE Clientes.cod_cliente = Emprestimos.cod_cliente AND Emprestimos.valor > 50000 ou
SQL como Linguagem de Manipulação de Dados Interrogação
Clientes com empréstimos de valor superior a 50000 Euros?
SELECT C. * FROM Clientes C, Emprestimos E WHERE C.cod_cliente = E.cod_cliente AND E.valor > 50000 Nota: Versão simplificada que recorre a sinónimos (aliases) nas duas tabelas
SQL como Linguagem de Manipulação de Dados Interrogação
6 – Quais os nomes dos clientes com a mesma profissão que o cliente com o cod_cliente = ‘1234’? SELECT C1.cliente FROM Clientes C1, Clientes C2 WHERE C1.profissao = C2.profissao AND C2.cod_cliente = ‘1234’ Nota: Exemplo que necessita da utilização de sinónimos para distinguir entre diferentes instâncias da mesma tabela
SQL como Linguagem de Manipulação de Dados Interrogação
7 – Listar as contas (num_conta, saldo) da agência cujo cod_agencia = ‘123’ por ordem decrescente do seu valor de saldo. SELECT num_conta, saldo FROM Contas WHERE cod_agencia = ‘123’ ORDER BY saldo DESC Nota: ORDER BY permite ordenar o resultado por um, ou mais, dos seus atributos. Por predefinição, a ordenação é ascendente (ASC), a menos que seja indicado o contrário.
SQL como Linguagem de Manipulação de Dados Interrogação
8 – Quantas contas existem em todas as agências do banco? SELECT COUNT (*) FROM Contas Nota: Exemplo da utilização de uma função de agregação. Existem outras funções de agregação para o cálculo do máximo, do mínimo, da média e do somatório (respectivamente, MAX, MIN, AVG e SUM).
SQL como Linguagem de Manipulação de Dados Interrogação
9 – Quantos clientes possuem contas na agência cujo cod_agencia = ‘123’? SELECT COUNT (DISTINCT cod_cliente) FROM Contas WHERE cod_agencia = ‘123’ Nota: Desta forma, apesar de vários clientes poderem ter mais do que uma conta nesta filial, apenas são contabilizados uma vez.
SQL como Linguagem de Manipulação de Dados Interrogação
10 – Listar o número de contas existentes em cada agência. SELECT cod_agencia, COUNT (*) FROM Contas GROUP BY cod_agencia Nota: GROUP BY permite que as funções de agregação (COUNT) actuem sobre agrupamentos dentro da tabela. Sem a cláusula GROUP BY, a função de agregação COUNT actuaria sobre toda a tabela.
SQL como Linguagem de Manipulação de Dados Interrogação
11 – Para cada agência (cod_agencia) com menos de 1000 contas, listar os valores máximo e mínimo dos saldos dessas contas, assim como o saldo médio.
SELECT cod_agencia, MAX(saldo), MIN(saldo), AVG(saldo) FROM Contas GROUP BY cod_agencia HAVING COUNT (*) < 1000
Nota: A cláusula HAVING está associada à cláusula GROUP BY, sendo como um filtro sobre esta última.
SQL como Linguagem de Manipulação de Dados Interrogação
12 – Quais os clientes cuja profissão é desconhecida? SELECT * FROM Clientes WHERE profissao IS NULL
Nota: A forma de testar se o valor de uma coluna é nulo é através do predicado IS NULL ( ou IS NOT NULL).
SQL como Linguagem de Manipulação de Dados Interrogação
13 – Quais os clientes (cod_cliente e cliente) da agência cod_agencia = ‘123’? SELECT C1.cod_cliente, C1.cliente FROM Contas Co, Clientes C1 WHERE Co.cod_agencia = ‘123’ AND Co.cod_cliente = C1.cod_cliente UNION SELECT C1.cod_cliente, C1.cliente FROM Emprestimos E, Clientes C1 WHERE E.cod_agencia = ‘123’ AND E.cod_cliente = C1.cod_cliente
Nota: UNION corresponde à operação de união da álgebra relacional e, tal como esta, elimina quaisquer duplicados. A UNION pode ser sempre substituída pela utilização de um OR.
SQL como Linguagem de Manipulação de Dados Interrogação
SELECT C1.cod_cliente, C1.cliente FROM Emprestimos E, Contas Co, Clientes C1 WHERE (Co.cod_agencia = ‘123’ AND Co.cod_cliente = C1.cod_cliente) OR (E.cod_agencia = ‘123’ AND E.cod_cliente = C1.cod_cliente)
Nota: UNION pode ser sempre substituída pela utilização de um OR.
SQL como Linguagem de Manipulação de Dados Interrogação 14 – Quais os clientes (cod_cliente e cliente) que são, simultaneamente, depositantes na agência cujo cod_agencia = ‘123’?
SELECT C1.cod_cliente, C1.cliente FROM Contas Co, Clientes C1 WHERE Co.cod_agencia = ‘123’ AND Co.cod_cliente = C1.cod_cliente INTERSECT SELECT C1.cod_cliente, C1.cliente FROM Emprestimos E, Clientes C1 WHERE E.cod_agencia = ‘123’ AND E.cod_cliente = C1.cod_cliente
Nota: INTERSECT, corresponde à operação de intersecção da álgebra relacional. A INTERSECT pode ser sempre substituída pela utilização de um AND entre duas condições.
SQL como Linguagem de Manipulação de Dados Interrogação
15 – Quais os clientes (cod_cliente e cliente) da agência com cod_agencia = ‘123’ que apenas são depositantes? SELECT C1.cod_cliente, C1.cliente FROM Contas Co, Clientes C1 WHERE Co.cod_agencia = ‘123’ AND Co.cod_cliente = C1.cod_cliente EXCEPT SELECT C1.cod_cliente, C1.cliente FROM Emprestimos E, Clientes C1 WHERE E.cod_agencia = ‘123’ AND E.cod_cliente = C1.cod_cliente
Nota: EXCEPT, corresponde à operação de diferença da álgebra relacional.
SQL como Linguagem de Manipulação de Dados Interrogação
16 – Quais os clientes (cod_cliente e cliente) com, pelo menos, um empréstimo no banco? SELECT C.cod_cliente, C.cliente FROM Clientes C WHERE EXISTS (SELECT * FROM Emprestimo E WHERE C.cod_agencia = E.cod_cliente)
Nota: O predicado EXISTS permite verificar se o resultado de uma subquestão é, ou não, o conjunto vazio.
SQL como Linguagem de Manipulação de Dados Interrogação
17 – Quais as agências (cod_agencia, agencia) com depositantes residentes em Lisboa? SELECT A.cod_agencia, A.agencia FROM Agencias A, Contas C WHERE C. cod_cliente IN (SELECT cod_cliente FROM Clientes WHERE localidade = ‘Lisboa’) AND C.cod_agencia = A.cod_agencia
Nota: O predicado IN permite verificar se um elemento está contido num conjunto.
SQL como Linguagem de Manipulação de Dados Interrogação
18 – Quais os clientes cujo saldo total das suas contas é superior ao valor de qualquer empréstimo contraído neste banco?
SELECT C1.* FROM Clientes C1 WHERE (SELECT SUM (Co.saldo) FROM Contas Co WHERE Co.cod_cliente = C1.cod_cliente) > ALL (SELECT valor FROM Emprestimos)
Nota: O predicado ALL, correspondente ao quantificador universal, permite verificar se uma condição é satisfeita para todos os elementos de um conjunto. Da mesma forma existem os quantificadores ANY ou SOME que permitem verificar de uma condição é satisfeita por, pelo menos, um dos elementos de um conjunto.
SQL como Linguagem de Manipulação de Dados Interrogação
SELECT C1.* FROM Clientes C1 WHERE (SELECT SUM (Co.saldo) FROM Contas Co WHERE Co.cod_cliente = C1.cod_cliente) > (SELECT MAX(valor) FROM Emprestimos)
Nota: Outra forma de resolver a questão, anterior, poderia ser recorrendo à função de agregação MAX, para calcular o valor do maior empréstimo contraído no banco.
SQL como Linguagem de Manipulação de Dados Interrogação
19 – Para cada cliente (cod_cliente) apresentar o seu saldo total.
SELECT cod_cliente, SUM (saldo) FROM Contas GROUP BY cod_cliente
SQL como Linguagem de Manipulação de Dados Interrogação
INNER JOIN Em muitos casos, é preciso apresentar um resultado que não está apenas numa tabela. Utiliza-se o INNER JOIN para fazer esse relacionamento das tabelas.
SQL como Linguagem de Manipulação de Dados Interrogação
Exemplo: Uma escola com várias turmas. Os alunos estão inscritos na secretaria. O que se deseja saber é a turma a que um aluno pertence. Possuímos aqui pelo menos duas tabelas, Aluno e Turma, onde o relacionamento é um-paramuitos, (um aluno só pode estar matriculado numa turma, porém uma turma possui vários alunos.
SQL como Linguagem de Manipulação de Dados Interrogação
SELECT Aluno.nome, Turma.turma Selecciona o campo nome da tabela aluno e campo turma da tabela Turma
FROM Aluno A origem é a tabela Aluno. Isso significa que a tabela Aluno é o lado UM de um relacionamento. Poder-se-ia colocar a tabela Turma como origem.
INNER JOIN Turma A tabela Aluno será ligada à tabela Turma.
ON Aluno.cod_turma = Turma.cod_turma Faz-se aqui o relacionamento das tabelas.
ORDER BY Aluno.Nome; A pesquisa vai ser exibida na ordem alfabética dos nomes dos alunos.
SQL como Linguagem de Manipulação de Dados Interrogação
Com o INNER JOIN, existem ainda o LEFT JOIN e o RIGHT JOIN, que funcionam de forma semelhante. O INNER JOIN, faz o relacionamento entre duas tabelas e depois exibe o que está nas duas tabelas do relacionamento indicado.
SQL como Linguagem de Manipulação de Dados Interrogação
O LEFT JOIN faz a mesma coisa, porém retorna os dados de acordo com a tabela da esquerda (a primeira tabela que foi declarada), ainda que na relação com a tabela da direita não haja o valor do campo (de relação). O RIGHT JOIN, faz o mesmo que o LEFT JOIN, porém retorna os dados de acordo com a tabela da direita (a segunda tabela que foi declarada), ainda que na relação com a tabela da esquerda não haja o valor do campo (de relação).