SQL

 

Introdução

 

A história do SQL começa em 1970 com a publicação por E. F. Codd, no ACM Journal, de um artigo intitulado “A Relational Model of Data for Large Shared Data Banks”.

O modelo proposto por Codd é hoje considerado a base de trabalho para qualquer Sistema de Gestão de Base de Dados Relacional (SGBDR).

A primeira implementação da linguagem SEQUEL foi realizada pela IBM e tinha por objectivo a implementação do modelo de Codd. A evolução desta linguagem veio a dar origem ao SQL.

A primeira implementação comercial de SQL foi realizada pela Relational Software, Inc., hoje conhecida por Oracle Corporation.

Nos dias de hoje, a linguagem SQL é considerada um standard dos Sistemas de Gestão de Base de Dados Relacionais (SGBDR); por isso, todos os fabricantes a integram nos seu produtos.

A linguagem SQL pertence à 4ª Geração das Linguagens de Programação, da qual é a única sobrevivente. Não é, no entanto, uma evolução das linguagens de 3ª Geração (Pascal, BASIC, C, COBOL, FORTRAN), já que estas têm características bem diferentes:

  • Existência de Variáveis, vectores, ...;

  • Existência de instruções condicionais (if, switch, case, ...);

  • Existência de ciclos (for, while, do...while, repeat...until);

  • Possibilidade de escrita de funções e procedimentos;

 

Nenhuma destas características existe no SQL, havendo maior ligação entre a 3ª e 5ª gerações de linguagens de programação do que com a 4ª.

A linguagem SQL destina-se, por isso e pela sua simplicidade, não só a informáticos, como também a gestores, utilizadores, administradores de bases de dados, etc..

No entanto, a sua principal diferença em relação às linguagens de 3ª geração é a ausência nestas de um objectivo pré-definido, coisa que no SQL está bem determinado: proporcionar o interface entre o SGBDR e o utilizador, através da manipulação de dados.

A linguagem SQL implementa os conceitos definidos no modelo relacional, reduzindo assim as incompatibilidades entre os sistemas e evitando a opção por arquitecturas proprietárias que implicam maiores custos de desenvolvimento e maior esforço financeiro e humano por parte dos intervenientes.

Com a linguagem SQL é possível:

  • Criar, alterar e remover todas as componentes de uma base de dados, como tabelas, índices, views, etc.;

  • Inserir, alterar e apagar dados;

  • Interrogar a base de dados;

  • Controlar o acesso dos utilizadores à base de dados, e às operações a que cada um deles tem acesso;

  • Obter a garantia da consistência e integridade dos dados;

  A linguagem SQL é composta por vários conjuntos de comandos:

  • DDL (Data Definition Language): comandos para definir ou modificar a composição das tabelas, apagar tabelas, criar índices, definir “views”, especificar direitos de acesso a tabelas e views;

  • DML

o   (interactive Data Manipulation Language): inclui uma linguagem de consulta baseada em álgebra relacional e em cálculo relacional sobre registos; inclui também comandos para inserir, apagar e modificar registos na base de dados;

o   (embedded Data Manipulation Language): projectada para ser usada a partir de linguagens de programação de uso geral, da 3ª geração;

 

As novas versões de SQL incluem capacidades de verificação de integridade dos dados, bem como comandos para especificação do princípio e fim de transacções; algumas implementações permitem o impedimento explícito de acesso aos dados, para controle de acesso concorrencial.

 

Comandos

 

A interrogação de qualquer base de dados relacional faz-se utilizando o comando SELECT. A sintaxe do comado é a seguinte:

 

SELECT Campo1 , Campo2 , ... , CampoN , *

FROM Tabela1 , ... , TabelaK

[WHERE  condição]

[GROUP BY … ]

[HAVING … ]

[ORDER BY … ]

 

  • As cláusulas entre [ ] são opcionais; no entanto, sempre que aparecerem terá de ser pela ordem indicada;

  • Se a cláusula WHERE for omitida, a condição será considerada verdadeira;

  • Se indicarmos * no SELECT em vez de campos, serão seleccionados todos os campos das tabelas envolvidas;

  • O resultado de uma consulta de SQL com o comando SELECT é sempre uma tabela;

  • Podemos repetir campos no SELECT; as repetições terão nomes atribuídos pelo SGBDR;

  • A ordem dos campos de saída é a que consta no SELECT, e pode ser diferente daquela usada na criação da tabela;

  • Podemos fazer selecção de expressões ou constantes:

           Ex.:  nomes e idades daqui a cinco anos

SELECT nome, idade +5

FROM pessoas

  • Na prática, o SGBDR pode converter o comando SQL para um mais eficiente;

  • Por defeito, por uma questão  de morosidade no processamento, nunca são retirados registos duplicados num comando SELECT; se pretendermos eliminar os duplicados usamos SELECT DISTINCT Campo1, ........;

  • SELECT ALL Campo1, ........   é equivalente a SELECT Campo1, ........;

Cláusula WHERE

 

A cláusula WHERE admite os seguintes operadores:

Relacionais

=

igual a

> 

maior que

>=

maior ou igual a

< 

menor que

<=

menor ou igual a

<> (mais vulgar) ou !=

diferente

 

 

Lógicos

cond1 AND cond2

conjunção

cond1 OR cond2

disjunção

NOT condição

negação

 

 

Especiais

campoN [NOT]BETWEEN  valor1 AND valor2

verifica intervalos de valores

campoN [NOT]IN (valor1, ..., valorN)

verifica conjuntos de valores

campoN IS [NOT] NULL

trata valores NULL

campoN LIKE '.......'

compara strings

tabela1 [NOT] CONTAINS tabela2

compara tabelas

[NOT] EXISTS tabela

verifica tabelas vazias

 

Nota: um campo tem valor NULL quando não está preenchido;

 

Ex.1: nomes dos clientes moradores em cidades onde existem vendedores

        SELECT DISTINCT nome

        FROM clientes

        WHERE cidade IN (SELECT DISTINCT cidade  FROM vendedores)

 

Ex.2: nomes dos clientes com o mesmo número e a mesma cidade de algum

          vendedor

         SELECT DISTINCT nome

         FROM clientes

         WHERE <cidade, n_cliente> IN (SELECT DISTINCT cidade, n_vend FROM vendedores)

 

Ex.3: nomes dos clientes que alugaram todos os vídeos de comédia

         SELECT nome

         FROM clientes

         WHERE (SELECT n_video                                 (vídeos alugados pelo cliente)

                                   FROM alugueres

                                   WHERE alugueres.n_cliente=cliente.n_cliente) 

 

CONTAINS

                                  

(SELECT numero                               (vídeos de comédia)

FROM filmes

WHERE genero="comedia")

 

Em relação ao operador LIKE há a notar:

  • A sintaxe é:     WHERE campoN [NOT] LIKE 'padrão de pesquisa' ESCAPE 'caracter de escape'

  • Para o Access, * quer dizer 0 ou mais caracteres e ? quer dizer 1 caracter; no entanto, noutros SGBDRs o sinal % corresponde ao * e o sinal _ corresponde ao ?;

  • Se quisermos procurar os próprios caracteres * ou ?, teremos de os preceder de um caracter que anule o contexto especial dos mesmos (caracter de "escape") e indicar o caracter de escape:

           Ex.: procura nomes de clientes cuja cidade tem o caracter ?

                   SELECT nome

                   FROM clientes

                   WHERE cidade LIKE '*=?*' ESCAPE '='

  • Existem outras variações aplicada ao padrão de pesquisa, como por exemplo '[abc]*': qualquer string começada pelo caracter a, b ou c;

Precedência dos operadores:

 

Ordem decrescente

 ↓

(   )

parêntesis

* , /

multiplicação, divisão

+ , -

soma, subtracção

NOT

negação lógica

AND

conjunção lógica

OR

disjunção lógica

 

 

Cláusula AS

 

Esta cláusula renomeia um campo de saída;

 

Ex.: SELECT idade AS idade_agora, idade +5 AS idade_depois

        FROM pessoas

 

 

Cláusula ORDER BY

 

  • Para efeitos de ordenação, os caracteres são vistos pelos seus códigos ASCII, isto é:

 

0 < 1 < ... < 8 < 9 <  ... < A < B < ... < Y < Z < .. < a < b < ... < y < z

 

  • A sua sintaxe é:

 

ORDER BY campo1[ASC|DESC], campo2[ASC|DESC],...

 

  • A ordem assumida é ascendente (ASC); no entanto, podemos explicitar essa ordem escrevendo ASC, que não terá nenhum efeito prático;

  • A ordenação é feita prioritariamente pelo campoN, e para valores iguais deste é feita pelo campoN+1;

  • Podemos indicar o campo de ordenação pela sua posição no comando SELECT:

 

Seleccionar na tabela Comissão o valor a receber, o montante do imposto (17%) e o valor líquido, para os indivíduos cujo Id é 14 ou 25, apresentando a ordenação por Id e Imposto:

SELECT Id, Valor AS líquido, Valor*0,17 AS Imposto, Valor+Valor*0,17 AS Bruto

FROM comissão

WHERE Id IN (14,25)

ORDER BY Id, Valor*0,17

 

Este comando é pouco eficiente, na medida em que, se quisermos alterar o valor da expressão do imposto, teremos que o fazer em vários locais; a expressão mais eficiente seria:

 

SELECT Id, Valor AS líquido, Valor*0,17 AS Imposto, Valor+Valor*0,17 AS Bruto

FROM comissão

WHERE Id IN (14,25)

ORDER BY Id, 3         (posição do campo no comando SELECT)

 

 

Junção de Tabelas (JOIN)

 

  • Chave estrangeira: é o campo, ou conjunto de campos, de uma tabela que permite ligar os dados desta tabela a uma outra onde este mesmo conjunto de campos existe como chave primária.

  • Produto cartesiano de tabelas (ou Cross Join): junção de duas ou mais tabelas através da sua menção na cláusula FROM:

Ex.: Se tivermos as duas tabelas seguintes:

 

Pessoa

 

Postal

Id

Nome

Idade

Salário

Telefone

Cod

 

Código

Local

1

Carlos

24

170000

219473659

1000

 

1000

Lisboa

2

José

23

150000

227379573

4000

 

3000

Coimbra

 

 

 

 

 

 

 

4000

Porto

 

e fizermos o seguinte comando de SQL:

 

SELECT *

FROM Pessoa, Postal

 

obteremos o seguinte resultado:

 

Id

Nome

Idade

Salário

Telefone

Cod

Código

Local

1

Carlos

24

170000

219473659

1000

1000

Lisboa

1

Carlos

24

170000

219473659

1000

3000

Coimbra

1

Carlos

24

170000

219473659

1000

4000

Porto

2

José

23

150000

227379573

4000

1000

Lisboa

2

José

23

150000

227379573

4000

3000

Coimbra

2

José

23

150000

227379573

4000

4000

Porto

 

  • O produto cartesiano entre tabelas associa a cada linha da primeira tabela o conjunto das linhas da segunda tabela;

 

No entanto, apenas nos interessa saber as linhas nas quais os campos comuns às duas tabelas contêm valores iguais; neste exemplo, Cod e código. Assim teremos de fazer a junção através das chaves estrangeiras:

 

SELECT *

FROM Pessoa, Postal

WHERE Cod=código

 

o que nos dará o resultado pretendido:

 

Id

Nome

Idade

Salário

Telefone

Cod

Código

Local

1

Carlos

24

170000

219473659

1000

1000

Lisboa

2

José

23

150000

227379573

4000

4000

Porto

 

Neste caso obtemos um Equi-Join: todos os campos das tabelas são apresentados e a ligação entre elas é feita através de uma igualdade, dando assim origem a duas colunas de conteúdos rigorosamente iguais.

 

Normalmente, após um Equi-Join queremos eliminar uma das colunas iguais; obtemos então um Natural Join:

 

SELECT Pessoa.*, Local

FROM Pessoa, Postal

WHERE Cod=código

 

Tanto o Equi-Join como o Natural Join fazem parte de um tipo de ligação mais geral denominada INNER JOIN.

 

 

INNER JOIN

 

O Inner-Join é o tipo de junção de duas ou mais tabelas, em que a ligação é feita através da Chave Primária de uma e da Chave Estrangeira da outra, apresentando apenas os registos em que exista essa ligação entre as tabelas.

 

Ex.:

 

SELECT Nome, Cod, Local

 

SELECT Nome, código, Local

FROM Pessoa, Postal

ou

FROM Pessoa, Postal

WHERE Cod=código

 

WHERE Cod=código

 

O nome de uma tabela pode ser reduzido ou alterado num SELECT, através de um alias que deve ser colocado à frente do nome da tabela, e que passará a ser uma outra forma de identificar a tabela:

 

Ex.:        SELECT P.Id, Nome, Mensagem, Valor

              FROM Pessoa P, Comissão C, Mensagem M

              WHERE (P.Id = C.Id) AND (C.Id_msg = M.Id_msg)

              ORDER BY Nome

 

A junção de N tabelas num único comando SELECT obriga à colocação de, pelo menos, N-1 condições de junção.

 

Finalmente, o Inner-Join pode, em alguns sistemas (Access, SQL Server e outros), ser escrito num formato que especifica a sua natureza; assim:

 

SELECT Nome, Local

é

SELECT Nome, Local

FROM Pessoa, Postal

equivalente

FROM Pessoa INNER JOIN  Postal

WHERE Cod=código

a

ON Pessoa.Cod=Postal.código

 

 

 

OUTER JOIN

 

O Outer-Join é um tipo mais alargado de junção, que permite juntar as tabelas e obter não apenas os registos onde existe igualdade dos campos comuns, mas todos os registos de uma das tabelas,  ainda que não exista correspondente na outra tabela;

 

Ex. (implementação em Access ou SQL Server):

 

SELECT Nome, Cod, código, Local

FROM Postal LEFT JOIN Pessoa

ON Postal.código=Pessoa.Cod

 

o resultado será

Nome

Cod

Código

Local

Carlos

1000

1000

Lisboa

 

 

3000

Coimbra

 

 

4000

Porto

 

 

1000

Lisboa

 

 

3000

Coimbra

José

4000

4000

Porto

 

O Outer-Join pode ser feito à esquerda ou à direita, isto é, mostra-se respectivamente todo o conteúdo da tabela da esquerda e respectiva ligação à tabela da direita (LEFT JOIN), ou todo o conteúdo da tabela da direita e respectiva ligação à tabela da esquerda (RIGHT JOIN). Em qualquer dos casos, para as linhas nas quais os campos de ligação não contêm valores correspondentes, os campos provenientes da tabela só de ligação (Pessoa, no exemplo anterior) serão preenchidos com NULL.

 

 

 

UNION

 

Este operador permite juntar o conteúdo de múltiplos comandos SELECT; para isso, as tabelas argumento têm que ter o mesmo número de campos e os campos homólogos têm que ser do mesmo tipo.

O nome dos campos de saída será o nome dos campos da primeira tabela/consulta argumento.

Se pretendermos ordenar o resultado podemos colocar uma única cláusula ORDER BY no último SELECT, e esta ordenação terá efeito em todo o resultado final.

Por defeito, o UNION elimina sempre linhas duplicadas. Se pretendermos conservá-las, usamos UNION ALL.

 

INTERSECT

 

Este operador permite juntar o resultado de dois comandos SELECT, apresentando apenas as linhas que resultam de ambos os comandos.

 

 

MINUS

 

Este operador devolve os registos que resultam do primeiro SELECT e que não aparecem no segundo.

 

 

Funções de Agregação

 

Estas funções, também designadas por Funções Estatísticas, têm por objectivo obter informação sobre conjuntos de linhas especificados na cláusula WHERE ou sobre grupos de linhas indicados na cláusula GROUP BY. Não podem, no entanto, ser utilizadas na cláusula WHERE.

 

Função

Descrição

COUNT

Devolve o número de linhas

MAX

Devolve o Maior valor do campo

MIN

Devolve o Menor valor do campo

SUM

Devolve a Soma de todos os valores do campo

AVG

Devolve a Média (AVeraGe) de todos os valores do campo

 

 

Função COUNT

 

Esta função pode ser usada de três formas distintas:

 

Count (*)

Devolve o número de linhas que resulta de um SELECT

Count (campo)

Devolve o número de ocorrências no campo diferentes de NULL

Count (DISTINCT campo)

Devolve o número de ocorrências (sem repetição) no campo

 

 

A aplicação de uma ou mais funções de agregação devolve sempre uma única linha de resultado;

 

Ex.:   Quantos clientes não têm telefone ?

 

SELECT count(*) AS Total

FROM clientes

WHERE telefone IS NULL

 

Total

4

 

 

Funções MIN e MAX

 

Estas funções permitem obter o menor e o maior valor de um determinado campo.

 

Ex.: Qual é o vendedor com a maior idade de admissão ?

 

SELECT nome, MAX (Idade_admissão) AS Idade

FROM vendedores

 

 

Nome

Idade

Carla Santos

28

Agostinho Paiva

28

 

 

 

Função SUM

 

Esta função devolve a soma de um determinado campo.

 

Ex.: Qual o valor total das comissões a pagar ?

 

SELECT SUM (Valor) AS Comissões

FROM comissão

 

 

Comissões

42480

 

Função AVG (AVeraGe)

 

A função Avg devolve a média dos valores de um determinado campo.

 

Ex.: Qual é a média de idades de admissão dos vendedores ?

 

SELECT AVG (Idade_admissao) AS Idade_Média

FROM vendedores

 

 

Idade_Média

23,7

 

 

As funções MIN, MAX, Count (campo) e Count(*) podem ser utilizadas com qualquer tipo de dados, enquanto as funções SUM e AVG apenas podem ser aplicadas a campos numéricos.

Se for encontrado o valor NULL no campo ao qual é aplicada a função de agregação, este é ignorado pela função.

Para além das funções de agregação, existem funções que actuam sobre cada um dos elementos individualmente, e que são particulares de cada SGBDR.

 

 

 

Cláusula GROUP BY

 

 

As funções de agregação anteriores actuam sobre a totalidade da tabela resultante do SELECT; no entanto, pode ser interessante realizar funções sobre conjuntos de registos da tabela: nesse caso usaremos a cláusula GROUP BY para formar esses conjuntos.

Se um comando SELECT contiver a cláusula GROUP BY, então todos os campos seleccionados (no SELECT) têm de estar presentes na cláusula GROUP BY.

 

Ex.: Considerando a tabela

 

Comissao

Id

Valor

 

mostrar, para cada Id, o valor total das comissões;

 

SELECT Id, SUM (Valor) AS Total

FROM Comissao

GROUP BY Id

 

Id

Total

....

.......

 

 

Utilizando a cláusula GROUP BY, o uso das funções de agregação já não resulta em apenas uma linha, mas antes uma linha por grupo.

 

Ex.: Mostrar, para cada nome, o valor total das comissões

 

SELECT nome, SUM (Valor) AS Total

FROM Pessoa, Comissao

WHERE Pessoa.Id = Comissao.Id

GROUP BY nome

 

 

nome

Total

 

 

Ex.: Mostrar o valor total (salário + comissões) a receber por cada pessoa

 

SELECT nome, SUM (Valor) + Salario  AS Total

FROM Pessoa, Comissao

WHERE Pessoa.Id = Comissao.Id

GROUP BY nome, salario

 

 

 

nome

Total

 

 

Ex.: Seleccionar a maior comissão de cada Id

 

SELECT Id, MAX (Valor) AS Maior

FROM Comissao

GROUP BY Id

 

 

Id

Maior

 

 

A função de agregação Count (*), se utilizada em conjunto com a cláusula GROUP BY, devolve o número de ocorrências (linhas) dentro de cada grupo. 

Se utilizar-mos a cláusula GROUP BY e não utilizarmos nenhuma função de agregação, então o SELECT corresponde a um SELECT DISTINCT.

 

 

Cláusula HAVING

 

 

Esta cláusula serve para fazer restrições ao nível dos grupos que são processados, isto é, apresentar apenas os grupos que apresentem uma determinada característica.

 

Ex. Mostrar o valor total de cada comissão, agrupada por Id, apenas para os Id com comissões totais superiores as 1000:

 

SELECT Id, SUM (valor) AS Total

FROM Comissão

GROUP BY Id

HAVING SUM (valor) >1000

 

Id

Total

Ex. Seleccionar o Nome de todas as Pessoas que tenham alguma Mensagem com Id superior a 35:

 

SELECT Nome

FROM Pessoa, Comissão

WHERE Pessoa.Id = Comissão.Id

GROUP BY Nome

HAVING MAX (Id_msg) >35

 

 

Nome

 

Note-se que as colunas referenciadas nas cláusulas GROUP BY ou HAVING não têm obrigatoriamente que aparecer no SELECT.

 

A cláusula WHERE destina-se a restringir os registos a considerar na selecção, ao passo que a cláusula HAVING serve para restringir os grupos que foram formados depois de aplicada a restrição da cláusula WHERE; assim, se no mesmo comando SELECT aparecerem as cláusulas WHERE, GROUP BY e HAVING, é aplicada a restrição do WHERE; em seguida, os registos que a satisfazem são agrupados pela cláusula GROUP BY; é então aplicada a cláusula HAVING a cada grupo; os grupos que a satisfazem são usados pelo SELECT para gerar a tabela final.

 

Sub-Consultas

 

Uma sub-consulta consiste num comando SELECT como argumento de um outro.

 

Ex. Qual o nome da pessoa com o menor salário;

precisamos de :  Qual o valor do menor salário

 

SELECT MIN (Salario) AS Menor

FROM Pessoa

 

Menor

74000

 

                          Qual o Nome da Pessoa à qual esse salário corresponde

 

SELECT Nome

FROM Pessoa

WHERE Salário = 74000

 

Nome

António Dias

 

Juntando ambos os comandos:

 

SELECT Nome

FROM Pessoa

WHERE Salário =(SELECT MIN (Salário) AS Menor

FROM Pessoa )

 

 

Nome

António Dias

Considerando as tabelas seguintes:

 

DVD

num

titulo

Id_genero

zona

duracao

data_aquisicao

custo

faces

Id_realizador

Id_produtora

 

Realizadores

Id

nome

nacionalidade

premios

data_nasc

 

  

Produtoras

Id

nome

país

ano_fundacao

 

 

Generos

Id

tipo

descricao

 

Exercícios

 

Implementa comandos em SQL para realizar as consultas que produzam os resultados seguintes:

 

1. todos os DVDs da zona 2;

2. todos os DVDs excepto os da zona 2;

3. dos DVDs de face dupla, todos os que têm duração superior a 180 min;

4. todos os DVDs de acção;

5. todos os DVDs da zona 2, de comédia, realizados por Woody Allen;

6. todos os DVDs de aventura realizados por realizadores nascidos antes de 1960;

7. o número de DVDs, zona 2, da DreamWorks;

8. o total do valor dos DVDs de cada uma das zonas;

9. o nome de todos os realizadores noruegueses de DVDs de aventura;

10.o nome do realizador e o nome da produtora de todos os DVDs adquiridos até Dez de 2000;

11. o título e realizador do DVD mais caro;

12. o título e realizador do DVD mais barato;

13. o número de DVDs de cada um dos géneros existentes;

14. o número de DVDs de cada uma das produtoras;

15. o número de DVDs que cada realizador realizou para cada produtora;

16. o custo médio dos DVDs da zona 4;

17.a produtora que tem mais filmes realizados pelo realizador que realizou menos filmes de comédia;

18. a lista de realizadores começando no mais novo acabando no mais velho;

19.o número de DVDs por nacionalidade de realizador;

20. todos os títulos de DVDs de cada um dos realizadores;

21. o valor total gasto em DVDs de cada uma das produtoras;