Linguagem SQL

07 – Linguagem SQL
SI1 - 07
1
Etapas do processo
Levantamento dos requisitos
Requisitos
Estudo do modelo Entidade Associação
Concepção do Modelo Entidade Associação
Estudo do modelo Relacional
Modelo Entidade Associação
Concepção do Modelo Relacional
Modelo Relacional
Estudo da Normalização
Normalização do Modelo Relacional
Modelo Relacional Normalizado
Estudo da LDD -SQL
Construção do modelo físico para um SGBD
Estudo da álgebra relacional
Estudo da LMD -SQL
Desenho físico e implementação
SI1 - 07 de Dados
Utilização da Bases
2
O que é / como usar o SQL ?

SQL (Structured Query Language) é uma linguagem normalizada,
utilizada nos SGBD para realizar acções de:






Interrogação
Manipulação de Dados
Definição de Dados
Controlo de Transações
Autorizações e Segurança
Na maioria dos SGBD Relacionais, o SQL pode ser utilizado:


Interactivamente
Embutido em Linguagens de Programação

existem diversas extensões em que os comandos SQL podem ser
embutidos em linguagem de programação tradicionais (C, C++, etc).
SGBD – Sistemas de Gestão de Bases de Dados
SI1 - 07
3
Componentes da linguagem SQL

LDD - Linguagem de Definição de Dados


do inglês DDL - “Data Definition Language”
Permite:


construir o Esquema da base de dados
LMD - Linguagem de Manipulação de Dados


do inglês DML - “Data Manipulation Language”
Permite:




aceder à informação armazenada na base de dados
inserir nova informação na base de dados
eliminar informação na base de dados
alterar informação na base de dados
SI1 - 07
4
Características da linguagem SQL

Unifica a LDD e a LMD numa única linguagem

Inclui aspectos relacionados com a administração da base de
dados

É um standard da ISO e da ANSI

É uma Linguagem não-Procedimental


Existe uma clara abstracção perante a estrutura física dos dados



Especifica-se O QUÊ e não COMO
não é necessário especificar caminhos de acesso
não é necessário elaborar algoritmos de pesquisa física
As operações efectuam-se sobre:


conjuntos de Dados (Tabelas)
não é necessário (nem possível) manipular os Dados Linha-a-Linha.
SI1 - 07
5
Perspectiva Histórica


1970: “Codd” define o Modelo Relacional
1974: IBM desenvolve o SYSTEM/R com a linguagem SEQUEL


Lançamento de SGBDs comerciais




(Structured English Query Language) cujo nome depois evoluiu para SQL
1979: Primeiro SGDB comercial (ORACLE)
1981: SGBD INGRES
1983: IBM anuncia o DB2
Normas SQL




1986, 1987: Norma SQL-87 (ANSI X3.135-1986 e ISO 9075:1987)
1989: Norma SQL-89 (ANSI X3.135.1-1989)
1992: Norma SQL-92 ou SQL2 (ISO/IEC 9075:1992)
1999: Norma SQL:1999 ou SQL3


2003: Norma SQL:2003


Added regular expression matching, recursive queries, triggers, support for procedural
and control-of-flow statements, non-scalar types, and some object-oriented features.
Introduced XML-related features, window functions, standardized sequences, and columns with
auto-generated values
2006: Norma SQL:2006

ISO/IEC 9075-14:2006 defines ways in which SQL can be used in conjunction with XML. It
defines ways of importing and storing XML data in an SQL database, manipulating it within the
database and publishing both XML and conventional SQL-data in XML form. In addition, it
provides facilities that permit applications to integrate into their SQL code the use of XQuery, to
concurrently access ordinary SQL-data and XML documents.
SI1 - 07
6
SQL - Alguns comandos principais

Definição de Dados


Manipulação de Dados


SELECT. Usado para interrogar a BD.
Controlo de Transações


INSERT, UPDATE, DELETE. Usados para inserir novos registos em
tabelas, alterar ou eliminar registos já existentes.
Interrogação


CREATE, ALTER, DROP. Usados para criar, alterar ou eliminar
qualquer estrutura de dados (tabelas, vistas e índices).
COMMIT, ROLLBACK. Usados para controlar explicitamente as
transacções.
Autorizações e Segurança

GRANT, REVOKE. Usados para atribuir ou retirar direitos de acesso.
SI1 - 07
7
Operações Relacionais

Operadores Unários



Operadores Binários







Restrição
Projecção
União
Intersecção
Diferença
Produto Cartesiano
Junção
Divisão agregação
Operação Relacional

consiste na aplicação de um Operador Relacional a um conjunto de
uma ou mais relações e tem como resultado uma nova relação.
SI1 - 07
8
Definição de dados
criar, alterar, apagar tabelas
DDL – Linguagem de Definição de Dados
SI1 - 07
9
Definição do Esquema da Base de Dados

Comandos SQL pertencentes à Linguagem de Definição de Dados
(LDD):



CREATE, para criar uma tabela
ALTER,
para alterar uma tabela existente
DROP,
para eliminar uma tabela a existente

Uma tabela pode ser criada em qualquer momento de uma sessão SQL
A estrutura de uma tabela pode ser alterada em qualquer momento de
uma sessão SQL
Toda a informação eliminada não pode ser recuperada

Uma tabela não tem qualquer dimensão predeterminada


SI1 - 07
10
Tipos de Dados

O Standard SQL2:







BIT( n ), BIT VARYING( n )
CHARACTER( n ), CHARACTER VARYING( n )
DATE, TIME, TIMESTAMP
DECIMAL, NUMERIC
FLOAT, REAL, DOUBLE PRECISION
INTEGER, SMALLINT
No caso do SQL Server:






Booleano: BIT
Binários: BINARY[(n)], VARBINARY[(n)]
Caracter: CHAR[(n)], VARCHAR[(n)]
Data e Hora: DATETIME, SMALLDATETIME (ao milissegundo, ao minuto)
Numérico aproximado: FLOAT[(n)], REAL (n bits of mantissa (1-53), default 53,
real = float(24)
Numérico exacto:



Decimal: DECIMAL[(p[,s])], NUMERIC[(p[,s])] (numeric = decimal)
Inteiro: BIGINT, INT, SMALLINT, TINYINT (respectivamente 8, 4, 2 e1 bytes)
Monetário: MONEY, SMALLMONEY (respectivamente 8 e 4 bytes)*
* Ambos com precisão de 1/10000 da unidade
SI1 - 07
11
Tipos de Dados

No caso do MySQL:






Binários: BIT[(n)]
Caracter: CHAR[(n)], VARCHAR[(n)], ENUM, TEXT
Data e Hora: DATE, DATETIME, TIME, YEAR, TIMESTAMP
Numérico exacto: DECIMAL[(p[,s])], NUMERIC [(p[,s])],
Numérico aproximado: FLOAT[(n, d)], DOUBLE[(m, d)]
Inteiro: BIGINT, INT, MEDIUMINT, SMALLINT, TINYINT
MEDIUMINT - 3 bytes
SI1 - 07
12
Tipos de Dados (cont.)

Algumas características de tipos de dados (no caso de SQL Server):

Para os tipos de dados VAR...(n) apenas é alocado o espaço necessário
para guardar o dado, até ao máximo definido (n), que pode ter o valor de
max (231-1 bytes/caracteres ).

Para o correspondente tipo sem o prefixo VAR, é alocado todo o espaço
definido (n), independentemente da dimensão efectiva do dado.


Pelo facto de ocupar um espaço fixo, o acesso ao tipo VAR... pode ser mais rápido
do que ao correspondente tipo sem o prefixo VAR
É de utilizar o tipo VAR quando se prevê, nessa coluna, existência de valores
nulos ou grandes variações na dimensão dos dados

BINARY(n), n grupos de 2 dígitos hexadecimais, para guardar dados binários

DATETIME, data e hora (8bytes), com resolução ao milissegundo (3.33 ms) *

SMALLDATETIME, data e hora (4bytes), com resolução ao minuto *
* desde 1/1/1900
SI1 - 07
13
Tipos de Dados (cont. 1)

Algumas características de tipos de dados (no caso de SQL Server):


DECIMAL e NUMERIC são idênticos e existem ambos por razões de
compatibilidade.
DECIMAL[(p[,s])] - o parâmetro “p” (precision) indica o número máximo total
de dígitos; o parâmetro “s” (scale) indica o número máximo de dígitos à
direita do ponto decimal.



No SQL Server precision pode ir de 1 a 38, com valor por omissão de 18. Scale
por ir até 0 a precision, sendo o valor por omissão de 0.
Espaço ocupado: varia em função de precision/p, sendo 5 se p  [1 – 9], 9 se p 
[10-19], 13 se p  [20-28] e 17 se p  [29-38].
FLOAT[(n)] – n indica o número de bits para guardar a mantissa.


No SQL server, o n pode variar entre 1 e 53, sendo 53 o seu valor por omissão,
mas o seu valor é tido como 24 se for inferior ou igual a 24, e 53 se for superior a
24. O espaço ocupado é de 4 bytes para n = 24 e 8 bytes para n = 53, tendo
respectivamente uma precisão de 7 e 15 dígitos.
O tipo REAL, corresponde a FLOAT(24)
SI1 - 07
14
Criação de uma tabela

Comando CREATE
CREATE TABLE nome_tabela (
nome_coluna tipo [restrição_coluna], ...
[restrição_tabela, …]
[AS SELECT comando]
)

Exemplo de criar uma tabela com algumas colunas
CREATE TABLE ITEMFACTURA (
numFactura int,
numLinha int,
produto varchar(30),
quantidade int NOT NULL DEFAULT ‘1’
)
NOT NULL, indica que a coluna não pode receber o valor NULL
DEFAULT ‘1’, indica que o valor por omissão é 1
SI1 - 07
15
Criação de uma tabela - restrições

restrição_coluna (aplica-se apenas a uma coluna)

restrição_tabela (aplica-se a uma ou mais colunas)

Definição de uma restrição:
CONSTRAINT nome_restrição
[PRIMARY KEY | UNIQUE] ( coluna, … ) |
[FOREIGN KEY ( coluna, … ) {REFERENCES tabela [( coluna, … )]
[ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL} ] |
[ON UPDATE {NO ACTION | CASCADE | SET DEFAULT | SET NULL} ] } |
[CHECK ( condição )]

ON DELETE/UPDATE: NOACTION, CASCADE, SET DEFAULT, SET NULL
Remoção de um tuplo que tenha tuplos a referencia-lo, por intermédio de uma chave estrangeiras,
resulta em:
ON DELETE NOACTION→ a acção de DELETE não é realizada
ON DELETE CASCADE→ na remoção/alteração desses tuplos
ON DELETE SET DEFAULT→ na colocação das referências com o valor por omissão
ON DELETE SET NULL → na colocar dessas referências a NULL
SI1 - 07
16
Criação de uma tabela – exemplos

Definição de uma tabela com uma chave primária, uma chave
candidata (alternativa) e restrição de valor NULL por CHECK:
CREATE TABLE ALUNO (
numAluno int CONSTRAINT pk_ALUNO PRIMARY KEY,
numBI char(8) NOT NULL CONSTRAINT ak1_ALUNO UNIQUE,
nome varchar(100),
CONSTRAINT ck1_ALUNO CHECK ( nome IS NOT NULL )
)

Definição de uma tabela com chave primária composta:
CREATE TABLE ITEMFACTURA (
numFactura int,
numLinha int,
produto varchar(50) not null,
quantidade int not null,
CONSTRAINT pk_ITEMFACTURA PRIMARY KEY ( numFactura,
numLinha )
)
SI1 - 07
17
Criação de uma tabela – exemplos

Definição de uma tabela com chave estrangeira composta:
CREATE TABLE ENTREGA (
numEntrega int,
dataEntrega datetime NOT NULL,
numFactura int NOT NULL,
numLinha int NOT NULL,
CONSTRAINT pk_ENTREGA PRIMARY KEY ( numEntrega ),
CONSTRAINT fk1_ENTREGA_ITEMFACTURA
FOREIGN KEY ( numFactura, numLinha )
REFERENCES ITEMFACTURA ( numFactura, numLinha )
ON DELETE CASCADE
)
*
Considere-se que cada item de factura pode ser entregue separadamente.
Não será o caso normal.

ON DELETE CASCADE indica que,


a remoção de uma linha da tabela FACTURA ,
implica a remoção das linhas da tabela ENTREGA que lhe estiverem associadas
* Quando a FK referencia uma PK não é necessário declarar os atributos da PK (no SQL Server)
SI1 - 07
18
Criação de uma tabela – exemplos

Definição de uma tabela com uma regra de verificação (CHECK):
CREATE TABLE ENCOMENDA (
numEnc int CONSTRAINT pk_ENCOMENDA PRIMARY KEY,
dataEnc datetime NOT NULL,
codCliente int NOT NULL CONSTRAINT fk1_ENCOMENDA_CLIENTE
FOREIGN KEY REFERENCES CLIENTE (codCliente),
dataEntrega datetime,
CONSTRAINT ck1_ENCOMENDA CHECK ( dataEntrega > dataEnc )
)

Definição de uma tabela com valores seleccionados de outra tabela:
CREATE TABLE EMPREGADO_AUX (
(codigoEmpregado, nomeEmpregado, codigoCategoria)
AS SELECT codEmp, nome, codCat FROM EMPREGADO
)
1.
2.
Cria a tabela Empregado_aux com as colunas codigoEmpregado, nomeEmpregado e
codigoCategoria, que irão ter domínios idênticos respectivamente às colunas codEmp,
nome e codCat da tabela Empregado.
A tabela Empregado_aux é preenchida com o resultado do select, ou seja com todos os
empregados.
No SQL Server: select codDep, nomeDep into departamento_aux from departamento where ...;
SI1 - 07
19
Alterações a uma tabela

Comando ALTER
ALTER TABLE nome_tabela
[ ADD novas colunas | novas restrições_coluna ]
[ MODIFY definição das colunas ]
[ DROP coluna | restrição_coluna ]

Algumas alterações possíveis:





acrescentar colunas, eventualmente acompanhadas de restrições
acrescentar restrições de integridade à tabela
modificar o tipo de determinada coluna
remover uma restrição da tabela
É uma boa política criar as tabelas e depois adicionar as chaves estrangeiras
com ALTER, pois fica independente da ordem pela qual as tabelas são
criadas. Exemplo:
Alter table INSCRICAO add constraint fk_inscricao_aluno
foreign key (numAluno) references ALUNO
SI1 - 07
20
Alterações a uma tabela (cont. 1)

Algumas limitações às alterações:


Não se pode modificar uma coluna com valores NULL para NOT NULL.
Só se pode adicionar uma coluna NOT NULL a uma tabela que não contenha
nenhuma linha.



solução: adicionar a coluna como NULL, preenche-la completamente e depois
mudar para NOT NULL
Pode-se decrementar o tamanho de uma coluna e o tipo de dados, caso
essa coluna tenha valor NULL em todas as linhas.
Exemplos de alterações:
ALTER TABLE EMPREGADO
ADD comissao int NOT NULL;
ALTER TABLE DEPARTAMENTO
MODIFY nome varchar( 50 ) NOT NULL;
SI1 - 07
21
Alterações a uma tabela (cont. 2)

Exemplos de alterações:
Eliminar uma coluna:
ALTER TABLE EMPREGADO
DROP comissao

Eliminar uma restrição de integridade:
ALTER TABLE ENCOMENDA
DROP CONSTRAINT ck1_ENCOMENDA

Acrescentar uma restrição de integridade (chave estrangeira):
ALTER TABLE ENCOMENDA
ADD CONSTRAINT fk1_ENCOMENDA_cliente
FOREIGN KEY codCliente
REFERENCES CLIENTE ( codCliente )

SI1 - 07
22
Remoção de uma tabela

Comando DROP
DROP TABLE nome_tabela

Exemplo de remoção da tabela EMPREGADO
DROP TABLE EMPREGADO

Algumas características da acção de remoção de uma tabela:



a remoção de uma tabela causa a perda de todos os dados nela existentes,
assim como de todos os índices associado
Uma tabela, assim como uma qualquer entidade, só pode ser removida caso
não haja nenhuma dependência sobre ela
uma tabela só pode ser removida por quem tenha permissões para tal
SI1 - 07
23
Colunas auto-incrementáveis ( Identity )

Identity
 permite declarar uma coluna com valores autoincrementáveis,
 pode-se definir o valor inicial (valor da esquerda) e o passo
do incremento (valor da direita)
CREATE TABLE new_employees (
id int IDENTITY(1,1),
firstname varchar (20),
lastname varchar(30)
);
INSERT new_employees (firstname, lastname)
VALUES ('Karin', 'Josephs');
-- ficará com id = 1
INSERT new_employees (firstname, lastname)
VALUES ('Pirkko', 'Koskitalo');
-- ficará com id = 2
(verificar se é standard)
Extra / Não standard
SI1 - 07
24
Constraint Unique (Chaves candidatas)

Constraint Unique




Permite declarar que um conjunto de colunas tem de ter sempre
valores únicos. Tal permite declarar chaves alternativas.
Cria um índex com valores únicos, o que permite acelerar as pesquisas
que utilizem as colunas existentes no índex.
Pode-se aplicar UNIQUE na definição de uma coluna, ou aplicar como
uma restrição (constraint) de uma tabela.
Exemplo:
create table hotel (
designacao varchar(30),
regiao varchar(20) not null,
codHotel int IDENTITY(1,1),
preco decimal(8,2) not null,
data_inauguracao smalldatetime not null,
constraint pk_hotel primary key (designacao, regiao),
constraint ak_hotel unique (codHotel)
);
SI1 - 07
25
UniqueIdentifier

UniqueIdentifier – tipo identificador único

É um tipo de identificador global e único: 16-byte GUID com a forma
textual de: ‘xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx’
CREATE TABLE MyUniqueTable (
UniqueColumn UNIQUEIDENTIFIER DEFAULT NEWID(),
Characters VARCHAR(10)
)
GO
INSERT INTO MyUniqueTable(Characters) VALUES ('abc')
INSERT INTO MyUniqueTable VALUES (NEWID(), 'def')
GO
Extra / Não standard
SI1 - 07
26
Colunas computadas (computed columns)

Colunas computadas



São colunas virtuais, que não são fisicamente existentes na tabela, mas
que existem como resultado da aplicação e uma expressão aplicada às
restantes colunas da mesma tabela
Uma coluna computada pode ficar fisicamente registada caso se assinale
como PERSISTED.
Exemplo:
CREATE TABLE FACTURA (
numFactura int,
numLinha int,
produto varchar(50) not null,
quantidade int not null,
custoUnitario decimal(18,2) not null,
custo AS custoUnitario * quantidade,
CONSTRAINT pk_FACTURA PRIMARY KEY ( numFactura, numLinha )
)
SI1 - 07
27
Manipulação de dados
inserir, alterar, remover linhas
SI1 - 07
28
Manipulação de Dados

Comandos SQL pertencentes à Linguagem de Manipulação de Dados
(LMD):

INSERT - insere novas linhas numa tabela
INSERT INTO <nome da tabela> [(coluna1, coluna2, ...)]
VALUES (valor1, valor2, ...)
[comando SELECT]

UPDATE - actualiza linhas de uma tabela
UPDATE <nome da tabela>
SET coluna=valor | expressão, coluna=valor | expressão, ...
[WHERE <condição>]

DELETE - remove linhas de uma tabela
DELETE FROM <nome da tabela>
[WHERE <condição>]
SI1 - 07
29
Inserção de linhas numa tabela


Inserção directa de dados
Pretende-se registar a existência do departamento de Marketing,
localizado em Lisboa e com código 4
INSERT INTO DEPARTAMENTO ( codDep, nome, localizacao )
VALUES( 4, 'Marketing', 'Lisboa’ )

Características do comando INSERT (aplicado deste modo):




apenas se pode inserir uma linha de cada vez *
pode-se omitir a lista de nomes de colunas, desde que se respeite a ordem
das colunas definidas na tabela e não se omita nenhum valor
pode-se inserir uma linha apenas com algumas das colunas da tabela,
ficando os campos omitidos com valores nulos
É boa política indicar os nomes das colunas, pois fica-se com independência
face a alterações na definição da tabela
Multirow inserts, since SQL-92 (DB2, MySQL, H2, PostgreSQL)
INSERT INTO table (column1, [column2, ... ])
VALUES (value1a, [value1b, ...]) [, (value2a, [value2b, ...]) ] *
Exemplo:
INSERT INTO phone_book VALUES ('John Doe', '555-1212'), ('Peter Doe', '555-2323');
SI1 - 07
30
Insert MultiRow no SQL Server
Uma forma de se conseguir um insert multi-linha, quando tal não existe, é fazer
a junção dos dados através do comando UNION
insert into aeronave
(codAeronave, fabricanteAeronave, modeloAeronave, autonomia)
select 'AIRBUS-A580', 'AIRBUS', 'A580', '5000' union all
select 'AIRBUS-A480', 'AIRBUS', 'A480', '4000' union all
select 'AIRBUS-A380', 'AIRBUS', 'A380', '3000' union all
select 'BOIENG-747' , 'BOIENG', '747' , '5500';
SI1 - 07
31
Inserção de linhas numa tabela (cont.)

Inserção com dados provenientes de uma tabela

Pretende-se copiar para a tabela Empregado_Lisboa os empregados da
tabela Empregado com localização de ‘Lisboa’:
INSERT INTO EMPREGADO_LISBOA( codEmp, nome )
SELECT E.codEmp, E.nome
FROM EMPREGADO AS E
WHERE E.localizacao = ‘Lisboa’ *

Características do comando INSERT (aplicado deste modo):

o número de colunas na lista da cláusula SELECT tem que ser igual ao
número de colunas referidas no comando INSERT e os domínios de colunas
correspondentes têm que ser compatíveis
* O comando SELECT será descrito mais à frente
SI1 - 07
32
Actualização de linhas numa tabela

Pretende-se registar o facto do empregado com código 123 ter mudado
de departamento e de chefe. O seu novo departamento tem código 444
e o seu novo chefe tem código 654
UPDATE EMPREGADO
SET codDep = 444, codEmpChefe = 654
WHERE codEmp = 123

Características do comando UPDATE:



A acção de update será aplicada às linhas da tabela EMPREGADO que
verifiquem a cláusula WHERE
se a cláusula WHERE for omitida, todas as linhas da tabela são actualizadas
os valores a actualizar podem ser o resultado de expressões ou
interrogações (SELECT) à Base de Dados
SI1 - 07
33
Remoção de linhas numa tabela

Pretende-se remover os empregados do departamento 222
DELETE FROM EMPREGADO
WHERE codDep = 222

Características do comando DELETE:


se a cláusula WHERE for omitida, todas as linhas da tabela são removidas
Pretende-se remover toda a informação relativa ao histórico dos
empregados
DELETE FROM HISTORICO_EMPREGADO
SI1 - 07
34
Interrogação de dados
Comando Select
LMD - Linguagem de Manipulação de Dados
Select …
from …
where …
SI1 - 07
35
Interrogação à Base de Dados

Começando pela interrogação da Base de Dados, a sintaxe geral de
uma questão SQL é a seguinte:
SELECT [DISTINCT] <colunas> | *
FROM <tabelas>
[WHERE <condição>]

Onde





<colunas> especifica a lista de atributos cujos valores interessa conhecer
<tabelas> especifica quais as tabelas envolvidas no processamento da
interrogação
<condição> traduz a expressão lógica que define a condição a verificar
DISTINCT é aplicado a todas as colunas especificadas na cláusula SELECT
e elimina as repetições existentes
O símbolo * é utilizado quando se pretendem seleccionar todos os atributos
da tabela especificada na cláusula FROM
SI1 - 07
36
Projecção
EMPREGADO
codEmp
nomeEmp
1
António Abreu
2
Bernardo Bento
3
Carlos Castro
4
Manuel Matos

dataAdmissao
01-Jan-99
01-Jan-99
01-Jan-99
7-Feb-90
codCat
1
1
3
3
codDept
1
2
3
2
codEmpChefe
1
1
1
2
Qual o código e nome de cada empregado ?

 codEmp, nomeEmp (EMPREGADO)
SELECT codEmp, nomeEmp
FROM EMPREGADO
resultado
codEmp
1
2
3
4
nomeEmp
António Abreu
Bernardo Bento
Carlos Castro
Manuel Matos
SI1 - 07
37
Projecção (select distinct)
EMPREGADO
codEmp
nomeEmp
1
António Abreu
2
Bernardo Bento
3
Carlos Castro
4
Manuel Matos

dataAdmissao
01-Jan-99
01-Jan-99
01-Jan-99
7-Feb-90
codCat
1
1
3
3
codDept
1
2
3
2
Quais as datas de admissão e código de categoria dos
empregados?

 dataAdmissao, codCat (EMPREGADO)
resultado
dataAdmissao
01-Jan-99
01-Jan-99
01-Jan-99
7-Feb-90
SELECT dataAdmissao, codCat
FROM EMPREGADO

codEmpChefe
1
1
1
2
codCat
1
1
3
3
DISTINCT - garante que não existem tuplos duplicados
resultado
SELECT DISTINCT dataAdmissao, codCat
FROM EMPREGADO
SI1 - 07
dataAdmissao
01-Jan-99
01-Jan-99
7-Feb-90
codCat
1
3
3
38
Selecção ou Restrição

codCat
1
2
3
designacao
CategoriaA
CategoriaB
CategoriaC
salarioBase
300
250
160
Quais as categorias cujo salário base é superior a 250 ?

 salarioBase > 250 ( CATEGORIA )
SELECT *
FROM CATEGORIA
WHERE salarioBase > 250

CATEGORIA
Operadores:
=, <, <=, >, >=, <>
OR, AND, NOT
codCat
1
designacao
CategoriaA
salarioBase
300
Pretende-se, não só informação das categorias designadas por
‘CategoriaA’, mas também das que têm salário base inferior a 200

 designacao = ‘CategoriaA’  salarioBase < 200 ( CATEGORIA )
codCat
1
3
designacao
CategoriaA
CategoriaC
salarioBase
300
160
SELECT *
FROM CATEGORIA
WHERE designacao = ‘CategoriaA’ OR salarioBase < 200
SI1 - 07
39
Composição de operações de Restrição e Projecção
EMPREGADO
codEmp
nomeEmp
1
António Abreu
2
Bernardo Bento
3
Carlos Castro
4
Manuel Matos

dataAdmissao
01-Jan-99
01-Jan-99
01-Jan-99
7-Feb-90
codCat
1
1
3
3
codDept
1
2
3
2
codEmpChefe
1
1
1
2
Qual o nome e data de admissão dos empregados da categoria com
código 3, admitidos antes do dia 1 de Janeiro de 1998 ?

 nomeEmp, dataAdmissao (  codCat = 3  dataAdmissao < ‘01/01/1998’ ( EMPREGADO ) )
SELECT DISTINCT nomeEmp, dataAdmissao
FROM EMPREGADO
WHERE codCat = 3 AND dataAdmissao < ‘01/01/1998’

nomeEmp
Manuel Matos
dataAdmissao
7-Feb-90
Quais os nomes dos empregados que são chefes deles próprios?

 nomeEmp (  codEmp = codEmpChefe ( EMPREGADO ) )
SELECT DISTINCT nomeEmp
FROM EMPREGADO
WHERE codEmp = codEmpChefe
SI1 - 07
nomeEmp
António Abreu
40
União

Considere-se o seguinte Esquema Relacional



CLIENTE( nome, morada, telefone )
FORNECEDOR( nome, morada, telefone )
Qual o conjunto que tem todos os clientes e todos os fornecedores ?

CLIENTE  FORNECEDOR
SELECT *
FROM CLIENTE
UNION
SELECT *
FROM FORNECEDOR


UNION - garante que não existem tuplos duplicados
UNION ALL - inclui todos os tuplos com eventuais duplicados
SI1 - 07
41
União (cont.)

A fim de fazer os convites para a festa de Natal em Lisboa, pretende-se
a lista com todos os nome e telefones de todos os empregados e
somente dos fornecedores de Lisboa, existentes nas tabelas
EMPREGADO e FORNECEDOR
EMPREGADO
codEmp
1
2
3
4
SELECT nome, telefone
FROM EMPREGADO
UNION ALL
SELECT nomeForn, telefone
FROM FORNECEDOR
WHERE localidade = ‘Lisboa’
nome
António Abreu
Bernardo Bento
Carlos Castro
Manuel Matos
Fornecedor
nomeForn
Continentix
Jumbix
Feira Novix
Pingo Docix
nome
telefone
Continentix
Jumbix
António Abreu
Bernardo Bento
Carlos Castro
Manuel Matos
222111333
222222111
212555666
212777888
212555444
212333111
SI1 - 07
telefone
222111333
222222111
222333444
222555111
telefone
212555666
212777888
212555444
212333111
localidade
Lisboa
Lisboa
Porto
Porto
42
Intersecção e Diferença

Quais os clientes que são também fornecedores ?
CLIENTE  FORNECEDOR
SELECT *
FROM CLIENTE
INTERSECT
SELECT *
FROM FORNECEDOR


Quais os clientes que não são fornecedores ?
CLIENTE - FORNECEDOR
SELECT *
FROM CLIENTE
EXCEPT (ou MINUS em versões anteriores à SQL.92)
SELECT *
FROM FORNECEDOR

SI1 - 07
43
União, Intersecção e Diferença

Estas operações só podem ser aplicadas se os seus operandos
(Relações) forem “Compatíveis em União”

Duas Relações R e S são Compatíveis em União se:


tiverem o mesmo Grau (número de Atributos)
a cada Atributo em R corresponder outro em S com Domínio para o qual
exista uma conversão implícita. A conversão típica é,



cadeias de caracteres convertidas na que tiver maior dimensão
tipos numéricos convertidos no que tiver maior precisão
Exemplo em que as Relação não são Compatíveis em União

CLIENTE  ( nome ( FORNECEDOR ) )
SELECT * FROM CLIENTE
UNION
SELECT nome FROM FORNECEDOR
SI1 - 07
44
Produto Cartesiano
FORNECE
codFornecedor
1
2
2
3

MATERIAL
numeroMaterial
001
002
codMaterial
001
001
002
002
Pretende-se uma lista onde cada código de fornecedor e cada código de
material, apareça combinado com cada um dos materiais

FORNECE  MATERIAL
SELECT *
FROM FORNECE, MATERIAL

nome
Parafuso
Roda Dentada
Ou escrevendo de forma explicita
codFornecedor
1
1
2
2
2
2
3
3
codMaterial
001
001
001
001
002
002
002
002
numeroMaterial
001
002
001
002
001
002
001
002
nome
Parafuso
Roda Dentada
Parafuso
Roda Dentada
Parafuso
Roda Dentada
Parafuso
Roda Dentada
SELECT *
FROM FORNECE CROSS JOIN MATERIAL
SI1 - 07
45
Produto Cartesiano e Restrição


O Produto Cartesiano, por si só, tem uma utilidade limitada pois
os registos são associados sem qualquer critério
Pode ter mais interesse saber por exemplo,


Qual o material fornecido por cada fornecedor ?
 codMaterial = numeroMaterial (FORNECE  MATERIAL)
SELECT *
FROM FORNECE, MATERIAL
WHERE codMaterial = numeroMaterial

O interesse prático da aplicação da operação de Restrição sobre
a Relação resultante de um Produto Cartesiano,

levou à definição da operação de Junção (Join)
SI1 - 07
46
Junção

Qual o material fornecido por cada fornecedor ?

FORNECE
codMaterial = numeroMaterial
MATERIAL
SELECT *
FROM FORNECE INNER JOIN MATERIAL
ON ( codMaterial = numeroMaterial )
codFornecedor
1
2
2
3

codMaterial
001
001
002
002
numeroMaterial
001
001
002
002
nome
Parafuso
Parafuso
Roda Dentada
Roda Dentada
A sintaxe geral de uma Junção é a seguinte:
… FROM <tabela1> [NATURAL] [<tipoJunção>] JOIN <tabela2>
[ON <condição>] ...


<tipoJunção> ::= INNER | <junçãoExterna> [OUTER]
<junçãoExterna> ::= LEFT | RIGHT | FULL
Inner join implicito -> SELECT * FROM FORNECE, MATERIAL WHERE codMaterial = numeroMaterial
Inner join explicito -> SELECT * FROM FORNECE INNER JOIN MATERIAL ON ( codMaterial = numeroMaterial )
SI1 - 07
47
Ambiguidade na identificação dos Atributos

Pode existir ambiguidade nas operações quando,


A ambiguidade é eliminada com a qualificação do Atributo,


colocando o nome da Relação como prefixo do nome do Atributo
Considere-se o seguinte Esquema Relacional



dois (ou mais) Atributos têm o mesmo nome em Relações diferentes
FORNECE( codFornecedor, codMaterial )
MATERIAL( codMaterial , nome )
Qual o código e nome dos materiais fornecidos por cada fornecedor?

 3, 4, 1 ( FORNECE
2=1
MATERIAL )
SELECT FORNECE.codMaterial, nome, codFornecedor
FROM FORNECE, MATERIAL
WHERE FORNECE.codMaterial = MATERIAL.codMaterial
SI1 - 07
48
Ambiguidade na identificação dos Atributos (cont.)

Pode também existir ambiguidade nas operações quando,


a mesma Relação é referida mais do que uma vez numa operação
a Relação na cláusula FROM é o resultado de um SELECT

A ambiguidade é eliminada atribuindo um pseudónimo à Relação

Considere-se o seguinte Esquema Relacional


EMPREGADO( numBI, primNome, ultNome, numBIChefe )
Para cada empregado, pretende-se saber o seu primeiro e último nome
e também o primeiro e último nome do seu chefe.

 2, 3, 6, 7 ( EMPREGADO
4=1
EMPREGADO )
SELECT EMP.primNome, EMP.ultNome, CHEFE.primNome, CHEFE.ultNome
FROM EMPREGADO AS EMP, EMPREGADO AS CHEFE
WHERE EMP.numBIChefe = CHEFE.numBI
SI1 - 07
49
Junção, Projecção e Restrição
FORNECE
codFornecedor
1
2
2
3

MATERIAL
numeroMaterial
001
002
codMaterial
001
001
002
002
nome
Parafuso
Roda Dentada
Qual o nome dos materiais fornecidos pelo fornecedor de código 1 ?

 nome (  codFornecedor = ‘1’ (FORNECE
codMaterial = numeroMaterial
SELECT M.nome AS nomeDoMaterial
FROM FORNECE AS F INNER JOIN MATERIAL AS M
ON ( F.codMaterial = M.numeroMaterial )
MATERIAL ))
nomeDoMaterial
Parafuso
WHERE F.codFornecedor = ‘1’


Notar que também às colunas se podem atribuir pseudónimos
Por vezes os pseudónimos são usados para melhorar a legibilidade
SI1 - 07
50
Select geradores de tabelas temporárias

Um select gera uma relação, que pode ser utilizada como uma tabela
normal
SELECT F.nomeForn
FROM ( SELECT *
FROM FORNECEDOR
WHERE localidade = ‘Porto’ ) AS F
Fornecedor
nomeForn
Continentix
Jumbix
Feira Novix
Pingo Docix
telefone
222111333
222222111
222333444
222555111
localidade
Lisboa
Lisboa
Porto
Porto
F
nomeForn
Feira Novix
Pingo Docix
nomeForn
Feira Novix
Pingo Docix
SI1 - 07
telefone
222333444
222555111
localidade
Porto
Porto
51
Junção, Projecção e Restrição (cont.)

Outra solução para a questão do slide 24 agora poderia ser:

 nome ((  codFornecedor = 1 (FORNECE))
codMaterial = numeroMaterial
SELECT M.nome
FROM ( SELECT *
FROM FORNECE
WHERE codFornecedor = 1 ) AS F
INNER JOIN
MATERIAL AS M
ON ( F.codMaterial = M.numeroMaterial )

MATERIAL) )
FORNECE
codFornecedor
1
2
2
3
codMaterial
001
001
002
002
MATERIAL
numeroMaterial
001
002
nome
Parafuso
Roda Dentada
Nesta solução a operação de Restrição é efectuada antes da Junção


o conjunto de tuplos a considerar na Junção é menor do que na solução
anterior
contudo os SGBD já fazem estas optimizações de forma automática
SI1 - 07
52
Não Equi-Junção

A condição de Junção pode não impor igualdade entre duas colunas
NOTA
numAluno
111
222
111
333
222

disciplina
Economia
Inglês
Álgebra
Estatistica
Biologa
ESCALA
notaMax
9
13
15
17
20
nota
15
12
17
13
16
notaMin
0
10
14
16
18
classif
Reprovado
Suficiente
Bom
Muito Bom
Excelente
Qual a classificação (classif) do aluno 222 nas diversas disciplinas ?

 1, 2, 6 (  1 = ‘222’ ( NOTA
3 >= 2  3 <= 1
ESCALA ) )
SELECT numAluno, disciplina, classif
FROM NOTA AS N INNER JOIN ESCALA AS E
numAluno disciplina
classif
222
Inglês
Suficiente
222
Biologa
Muito Bom
ON ( N.nota BETWEEN E.notaMin AND E.notaMax )
WHERE N.numAluno = ‘222’

O operador BETWEEN é apenas uma forma simplificada de escrever

( nota >= notaMin AND nota <= notaMax )
SI1 - 07
53
Junções Múltiplas

Considere-se o seguinte Esquema Relacional




Para cada categoria, pretende-se saber qual o nome dos empregados,
salário base e respectivo nome do departamento


CATEGORIA( codCat, nomeCat, salarioBase )
DEPARTAMENTO( codDep, nomeDep, localizacao )
EMPREGADO( cod_Emp, nomeEmp, codCat, codDep, codEmpChefe )
 2, 5, 3, 10 ((CATEGORIA
1=3
EMPREGADO)
7=1
DEPARTAMENTO)
Uma junção é um operador binário, pelo que um junção entre três
relações tem de ser resolvida com duas junções.
SELECT C.nomeCat, E.nomeEmp, C.salarioBase, D.nomeDep
FROM
( CATEGORIA AS C
INNER JOIN EMPREGADO AS E ON ( C.codCat = E.codCat ) )
INNER JOIN DEPARTAMENTO AS D ON ( E.codDep = D.codDep )
SI1 - 07
54
Junções Múltiplas (cont.)

Outro modo de escrever a mesma interrogação é:
SELECT nomeCat, nomeEmp, C.salarioBase, nomeDep
FROM CATEGORIA AS C, EMPREGADO AS E, DEPARTAMENTO AS D
WHERE C.codCat = E.codCat AND E.codDep = D.codDep

Esse comando está a indicar para se fazer o produto cartesiano entre os
dados das três tabelas e depois aplicar-se uma selecção ao resultado

No entanto um interpretador de SQL poderá construir o mesmo plano de
execução para a interrogação escrita de um ou de outro modo
É uma má prática utilizar produtos
cartesianos quando se pretendem
efectuar joins. Dificulta a leitura das
querys e potencia bugs devido a
selecções mal feitas.
SI1 - 07
55
Natural join



Uma Junção Natural (Natural Join) compara todas as colunas, que
têm o mesmo nome, nas duas tabelas
A tabela resultante apenas contém uma coluna por cada par de
colunas comparadas
Expressão relacional de uma junção natural:


CATEGORIA
EMPREGADO
Comando SQL com um natural join:
SELECT *
FROM EMPREGADO NATURAL JOIN DEPARTAMENTO

Esquema de Relação resultante

???( cod_Emp, nomeEmp, codCat, codDep, codEmpChefe, nomeDep,
localizacao )
CATEGORIA( codCat, nomeCat, salarioBase )
DEPARTAMENTO( codDep, nomeDep, localizacao )
EMPREGADO( cod_Emp, nomeEmp, codCat, codDep, codEmpChefe )
SI1 - 07
56
Junção Externa (Outer Join) – full
CATEGORIA( codCat, nomeCat, salarioBase )
DEPARTAMENTO( codDep, nomeDep, localizacao )
EMPREGADO( cod_Emp, nomeEmp, codCat, codDep, codEmpChefe )

Quais os empregados e categorias existentes e para cada empregado
quais as categorias superiores à sua ?

EMPREGADO
3<1
CATEGORIA
SELECT *
FROM EMPREGADO EMP FULL OUTER JOIN CATEGORIA CAT
ON ( EMP.codCat < CAT.codCat )
De notar a omissão de AS
SI1 - 07
57
Junção Externa – left e right

Se apenas estiverem disponíveis as operação de Junção Externa à
Esquerda e à Direita, a interrogação anterior pode ser escrita,

(EMPREGADO
3<1CATEGORIA)
 (EMPREGADO
3<1CATEGORIA)
SELECT *
FROM EMPREGADO EMP LEFT OUTER JOIN CATEGORIA CAT
ON ( EMP.codCat < CAT.codCat )
UNION
SELECT *
FROM EMPREGADO EMP RIGHT OUTER JOIN CATEGORIA CAT
ON ( EMP.codCat < CAT.codCat )
SI1 - 07
58
Junção Externa – exemplo
CATEGORIA( codCat, nomeCat, salarioBase )
DEPARTAMENTO( codDep, nomeDep, localizacao )
EMPREGADO( cod_Emp, nomeEmp, codCat, codDep, codEmpChefe )

Quais os nomes de todos departamentos existentes e para cada um
qual o código e nome dos empregados que lá trabalham ? Notar que
devem ser apresentados todos os departamentos, mesmo os que não
têm empregados.

 2, 4, 5 ( DEPARTAMENTO
1=4
EMPREGADO ) )
SELECT DEP.nomeDep, codEmp, EMP.nomeEmp
FROM DEPARTAMENTO DEP LEFT OUTER JOIN EMPREGADO EMP
ON ( DEP.codDep = EMP.codDep )
SI1 - 07
59
Funções de agregação
SI1 - 07
60
Funções de Agregação

Podem ser usadas na cláusula SELECT



MAX( <expressão> )


valor máximo da expressão, para as linhas seleccionadas. O resultado não
inclui tuplos duplicados.
MIN( <expressão> )


ou HAVING (que será posteriormente apresentada)
aplicam-se sobre um grupo de linhas, devolvendo um resultado
valor mínimo da expressão, para as linhas seleccionadas. O resultado não
inclui tuplos duplicados.
<expressão>

pode incluir operadores aritméticos: +, - ,*, / e os ()
SI1 - 07
61
Funções de Agregação (cont.)

COUNT( * )


COUNT( [DISTINCT] <coluna> )


somatório dos valores [diferentes] da expressão
AVG( [DISTINCT] <expressão> )


número de linhas excluindo as que, para a coluna indicada, têm valor NULL.
Caso se use DISTINCT não se consideram valores duplicados
SUM( [DISTINCT] <expressão> )


número total de linhas
média (SUM / COUNT) dos valores [diferentes] da expressão
SUM e AVG apenas se aplicam a valores numéricos
SI1 - 07
62
Funções de Agregação (cont. 1)
CATEGORIA( codCat, nomeCat, salarioBase )
DEPARTAMENTO( codDep, nomeDep, localizacao )
EMPREGADO( cod_Emp, nomeEmp, codCat, codDep, codEmpChefe )

Qual o nome do empregado que aparecerá em primeiro lugar numa
ordenação lexicográfica crescente ?

 MIN nome ( EMPREGADO )
SELECT MIN( nomeEmp )
FROM EMPREGADO

Qual o total de nomes diferentes dos empregados em ‘Lisboa’ ?

 COUNT 5 ( (  localizacao = ‘Lisboa’ ( DEPARTAMENTO ) )
EMPREGADO )
SELECT COUNT( DISTINCT E.nomeEmp )
FROM DEPARTAMENTO AS D INNER JOIN EMPREGADO AS E
ON ( D.codDep = E.codDep )
WHERE localizacao = ‘Lisboa’
SI1 - 07
63
Funções de Agregação (cont. 2)

Qual o valor total dos salários base a pagar aos empregados do
departamento 1 e qual o valor de IRS (calculado a 22%)?

 SUM salarioBase, SUM (salarioBase)*0.22 (
(  codDep = 1 ( EMPREGADO ) )
3=1 CATEGORIA
)
SELECT SUM( C.salarioBase ), SUM( C.salarioBase) * 0.22 as IRS
FROM EMPREGADO as E INNER JOIN CATEGORIA as C
on ( E.codCat = C.codCat )
WHERE codDep = 1
CATEGORIA( codCat, nomeCat, salarioBase )
DEPARTAMENTO( codDep, nomeDep, localizacao )
EMPREGADO( cod_Emp, nomeEmp, codCat, codDep, codEmpChefe )
SI1 - 07
64
Sub-Interrogação (subquery)
CATEGORIA( codCat, nome, salarioBase )
DEPARTAMENTO( codDep, nome, localizacao )
EMPREGADO( cod_Emp, nome, codCat, codDep, codEmpChefe )

Qual o nome dos empregados que trabalham no mesmo departamento
que o(s) empregado(s) com nome ‘Rui Costa' ?
SELECT DISTINCT E2.nome
FROM EMPREGADO AS E1 INNER JOIN EMPREGADO AS E2
ON ( E1.codDep = E2.codDep )
WHERE E1.nome = Rui Costa’ AND E1.codEmp <> E2.codEmp

Outra solução consiste em considerar duas sub-interrogações:


Qual o código do departamento do(s) empregado(s) ‘Rui Costa' ?
Qual o nome dos empregados do departamento, com o código obtido na
interrogação anterior, mas que não são esse ‘Rui Costa’ ?
SI1 - 07
65
Sub-Interrogação (cont.)

Qual o código do departamento do(s) empregado(s) ‘Rui Costa' ?
SELECT DISTINCT E2.codDep
FROM EMPREGADO AS E2
WHERE E2.nome = ‘Rui Costa‘

Qual o nome dos empregados do(s) departamento(s) com os códigos
obtidos na interrogação anterior, mas que não são ‘Rui Costa’ ?

assumindo que existem cinco empregados com o nome ‘Rui Costa’ e que
que três deles estão no departamento 4, um está no 7 e outro no 11
SELECT DISTINCT E1.nome
FROM EMPREGADO AS E1
WHERE E1.nome <> ‘Rui Costa' AND
E1.codDep IN ( 4, 7, 11 ) *
* Operador IN (v1, v2, v3), equivale a “valor =v1 OR valor = v2 OR valor = v3”
SI1 - 07
66
Sub-Interrogação (cont. 1)

Resposta à questão inicial:


Qual o nome dos empregados que trabalham no mesmo departamento que
o(s) empregado(s) com nome 'Rui Costa' ?
Obtida através da composição das duas interrogações numa única
interrogação:
SELECT DISTINCT E1.nome
FROM EMPREGADO AS E1
WHERE E1.nome <> 'Rui Costa' AND
E1.codDep IN
( SELECT DISTINCT E2.codDep
FROM EMPREGADO AS E2
WHERE E2.nome = 'Rui Costa’ )

Sub-interrogação (interrogação interior): “(SELECT … E2.codDep ...”
SI1 - 07
67
Sub-Interrogação Não-Correlacionada

Numa Sub-Interrogação Não-Correlacionada a interrogação interior não
necessita dos valores da interrogação exterior

Quais os códigos e nomes das categorias com menor salário base ?
SELECT C.codCat, C.nome
FROM CATEGORIA AS C
WHERE C.salarioBase =
( SELECT MIN( D.salarioBase )
FROM CATEGORIA AS D )

A interrogação interior (SELECT MIN(… ) não depende da exterior


a interrogação interior é executada em primeiro lugar e apenas uma vez
a relação devolvida na interrogação interior permite resolver a exterior
SI1 - 07
68
Sub-Interrogação Correlacionada

Numa Sub-Interrogação Correlacionada a interrogação interior necessita
de valores da interrogação exterior

Quais as categorias cujo salário base é inferior a metade do valor médio
dos salários efectivos dos empregados dessas categorias ?
CATEGORIA( codCat, nome, salarioBase )
EMPREGADO( codEmp, nome, salarioEfectivo, codCat, codDep )
SELECT C.*
FROM CATEGORIA AS C
WHERE C.salarioBase <
( ( SELECT AVG( E.salarioEfectivo )
FROM EMPREGADO AS E
WHERE E.codCat = C.codCat ) / 2 )

A interrogação interior (SELECT AVG(… ) depende da exterior


a informação da interrogação exterior é passada à interior *
para cada linha da interrogação exterior é executada a interior
* Devido ao WHERE E.codCat = C.codCat, que compara com um valor da interrogação externa
SI1 - 07
69
Cláusula WHERE

Foi anteriormente apresentada a sintaxe geral de uma questão SQL
SELECT [DISTINCT] <colunas> | *
[FROM <tabelas>]
[WHERE <condição>]

<condição>


Cada “Predicado”,



consiste numa colecção de “Predicados”
pode envolver qualquer composição de sub-expressões do mesmo tipo,
combinada com os operadores lógicos AND, OR, NOT e parêntesis
Cada “Predicado”,

quando avaliado produz um valor lógico verdadeiro ou falso, para o tuplo
considerado
SI1 - 07
70
Predicados

Os Predicados podem ser utilizados num contexto estático, sendo
avaliados com base em valores constantes.


Alguns Predicados também ser avaliados com base em valores
dinâmicos, a retirar da base de dados



... WHERE E1.codDep IN ( 4, 7, 11 ) ...
… WHERE D.codDep IN ( SELECT E2.codDep FROM EMPREGADO AS E2)
neste caso é utilizada um “Sub-Interrogação” (Subquery)
As “Sub-Interrogações” podem-se usar em Predicados:




Comparação, BETWEEN
IN
ALL, ANY
EXISTS
SI1 - 07
71
Predicados (cont.)

LIKE: devolve true se a cadeia de caracteres tiver um determinado
padrão


comparação (=, <>, >, <, >=, <=) e BETWEEN



WHERE salarioBase > ANY ( SELECT … )
EXISTS (se existe pelo menos uma linha *)


WHERE codCat IN ( 1, 2 )
ANY (em pelo menos um valor *), ALL (em todos os valores *)


WHERE nomeEmp = ‘Manuel Silva’
WHERE codEmp BETWEEN 1 AND 5
IN


WHERE nomeEmp LIKE '%n%‘ (nomes com ‘n’, % = 0 ou mais caracteres)
WHERE EXISTS ( SELECT … )
Teste de valor nulo

WHERE comissao IS NULL
* da subquery
SI1 - 07
72
LIKE

Devolve true se a cadeia de caracteres tiver um determinado padrão

<expressão> [NOT] LIKE <padrão>


expressão: deve ser do tipo cadeia de caracteres (string)
padrão: pode incluir meta-caracteres, que não sendo precedidos do caracter
de excepção, têm o seguinte significado,





‘%’ qualquer sequência de zero ou mais caracteres
‘_’ um único qualquer caracter
[G-J] ou [GHIJ] qualquer caracter do intervalo (G a J, neste exemplo)
[^G-J] ou [^GHIJ] qualquer caracter não pertencente ao intervalo mencionado
Nomes de empregados que começam por “Ana”
SELECT * FROM EMPREGADO
WHERE nome LIKE ‘Ana%’



Nomes que comecem por ‘A’, ‘B’ ou ‘C’: LIKE ‘[ABC]%’ ou LIKE ‘[A-C]%’
Nomes que não contenham ‘Silva’:
NOT LIKE ‘%Silva%’
Nomes que a segunda letra não seja um ‘a’: LIKE '_[^a]%'
SI1 - 07
73
LIKE – carácter de excepção

<expressão> [NOT] LIKE <padrão> [ESCAPE <caracterExcepção>]


caracterExcepção: carácter que anula o tratamento especial dos caracteres
de controlo (já descritos)
Quais as categorias cujo primeiro caracter do nome não pertence ao
alfabeto (a A, … z Z) e que inclui um underscore (_) ?
SELECT * FROM CATEGORIA
WHERE nome LIKE ‘[^A-Za-z]%+_%’ ESCAPE ‘+’

Nomes que comecem por ‘+_’: LIKE '++_%' ESCAPE '+';
O carácter ‘_’ é precedido do carácter de escape ‘+’.
Pode-se definir um qualquer carácter de escape
SI1 - 07
74
IN

Verifica se um valor está contido numa coluna de uma tabela, ou numa
lista de valores

<construtor de linha> [NOT] IN
( {sub-interrogação | lista de expressões escalares} )

Quais as categorias dos empregados com maior salário efectivo ?
CATEGORIA( codCat, nome, salarioBase )
SELECT C.*
EMPREGADO( codEmp, nome, salarioEfectivo, codCat, codDep )
FROM CATEGORIA AS C
WHERE C.codCat IN
( SELECT E.codCat
FROM EMPREGADO AS E
WHERE E.salarioEfectivo =
( SELECT MAX( F.salarioEfectivo )
FROM EMPREGADO AS F ) )
SI1 - 07
75
IN (cont.)

Quais as categorias dos empregados com maior salário efectivo ?


sem utilizar o Predicado IN, temos outra solução,
CATEGORIA 1=4 (EMPREGADO 3=1( MAX salarioEfectivo (EMPREGADO)))
SELECT DISTINCT C.*
FROM CATEGORIA AS C INNER JOIN
(
EMPREGADO AS E INNER JOIN
( SELECT MAX( salarioEfectivo )
FROM EMPREGADO ) AS E1( salarioEfectivoMax )
ON ( E.salarioEfectivo = E1.salarioEfectivoMax )
)
ON ( C.codCat = E.codCat )
E1( salarioEfectivoMax ) → declaração do nome da tabela e da coluna
SI1 - 07
76
ANY, ALL – any exemplo

Verifica se em todas (ALL) ou algumas (ANY) linhas, da subquery, o
atributo obedece a uma expressão envolvendo operadores relacionais

<construtor de linha> <comparação> {ALL | ANY} (sub-interrogação*)

Qual o nome dos empregados cujo salário efectivo é superior ao de
alguns empregados da mesma categoria ?
SELECT E.nome
FROM EMPREGADO AS E
WHERE E.salarioEfectivo > ANY
( SELECT E1.salarioEfectivo
FROM EMPREGADO E1
WHERE E.codCat = E1.codCat )

substituindo ANY por ALL teríamos como resultado uma Relação vazia
* A sub-interrogação deve devolver uma só coluna de valores
SI1 - 07
77
ALL – exemplo

Qual o nome dos empregados cujo salário efectivo é superior ao salário
efectivo de todos os empregados dos departamentos localizados em
‘Lisboa’ ?
SELECT E.nome
FROM EMPREGADO AS E
WHERE E.salarioEfectivo > ALL
( SELECT E1.salarioEfectivo
FROM EMPREGADO AS E1 INNER JOIN DEPARTAMENTO AS D
ON ( E1.codDep = D.codDep )
WHERE D.localizacao = ’Lisboa' )

Sem utilizar o Predicado ALL, temos outra solução,

“… E.salarioEfectivo > ( SELECT MAX( E1.salarioEfectivo ) …”
DEPARTAMENTO( codDep, nomeDep, localização )
EMPREGADO( codEmp, nome, salarioEfectivo, codCat, codDep )
SI1 - 07
78
EXISTS, NOT EXISTS

Verifica a existência de, pelo menos, uma linha numa tabela

[NOT] EXISTS (sub-interrogação)

Quais os departamentos que têm pelo menos um empregado ?
SELECT D.*
FROM DEPARTAMENTO AS D
WHERE EXISTS
( SELECT *
FROM EMPREGADO AS E
WHERE E.codDep = D.codDep )

A condição do WHERE é verdadeira se,

o resultado da sub-interrogação for não vazio.
SI1 - 07
79
EXISTS (cont. 1)

Quais os departamentos que têm pelo menos um empregado ?

sem utilizar o Predicado EXISTS, temos as seguintes soluções:
select *
from departamento as d
where ( select count( * )
from empregado as e
where e.coddep = d.coddep ) > 0
select distinct d.*
from departamento as d
inner join empregado as e on ( d.coddep = e.coddep )
SI1 - 07
80
NOT EXISTS

Qual o código e nome das categorias que não têm empregados ?
select c.codcat, c. nome
from categoria as c
where not exists
( select *
from empregado as e
where c.codcat = e.codcat )

A condição do WHERE é verdadeira se,


o resultado da sub-interrogação for vazio.
Sem utilizar o Predicado NOT EXISTS, temos outra solução,

“… WHERE 0 = ( SELECT COUNT( E.codCat ) …”
SI1 - 07
81
Exists – exemplo

Quais os departamentos que têm empregados na categoria ‘1’ e na
categoria ‘3’ ?
select *
from departamento d
where exists (
select *
from empregado e1
where e1.codCat = '1' and e1.codDep = d.codDep)
and exists (
select *
from empregado e2
where e2.codCat = '3'and e2.codDep = d.codDep)
SI1 - 07
82
Exists e intersect


Quais os departamentos que têm empregados na categoria ‘1’ e na
categoria ‘3’?
Agora resolvido com o operador INTERSECT

Nota: O intersect pode ser resolvido por exists
select distinct d.*
from (departamento d inner join empregado e
on(d.codDep = e.codDep))
where e.codCat = '1'
intersect
select distinct d.*
from (departamento d inner join empregado e
on(d.codDep = e.codDep))
where e.codCat = '3'
SI1 - 07
83
Comparação ( = )

Quais as categorias com maior salário base ?
select c.codcat
from categoria as c
where c.salariobase =
( select max( c1.salariobase )
from categoria as c1 )

Qual o código e nome dos empregados com maior salário
base ?
select e.codemp, e.nome
from empregado as e
inner join categoria as c on ( e.codcat = c.codcat)
where c.salariobase =
( select max( c1.salariobase )
from categoria as c1 )
DEPARTAMENTO( codDep, nome, localizacao )
CATEGORIA( codCat, nome, salarioBase )
EMPREGADO( codEmp, nome, salarioEfectivo, codCat, codDep )
SI1 - 07
84
Comparação ( > )

Considere-se o seguinte Esquema Relacional


ALUNO( numAluno, nome, dataNascimento, localNascimento )
Pretende-se uma lista onde, para cada local indique todos os alunos
mais novos do que o mais velho que lá nasceu ?
select a1.localnascimento, a1.numaluno, a1.nome
from aluno as a1
where a1.datanascimento >
( select min( a2.datanascimento )
from aluno as a2
where a1.localnascimento = a2.localnascimento )
SI1 - 07
85
Operação de Divisão
r2
r1
CodDisc
t1r2
Há tuplos que não existem em r1 e
não deviam estar em t1, logo,
retiremo-los
t1r2-r1
numAL
CodDisc
2222
LC
numAL
CodDisc
2222
IA
5555
IA
3333
LC
LC
3333
IA
3333
IA
4444
LC
numAL
4444
SI-2
4444
IA
5555
4444
LC
numAL
5555
LC
4444
IA
2222
5555
IA
5555
SI-1
5555
LC
numAL
CodDisc
2222
SI-1
2222
LC
2222
IA
3333
Y
X
LC
IA
X
t1 = Y(r1)
t2 = Y((t1r2)-r1)
q = t1-t2
3333
numAL
4444
2222
5555
3333
Y
4444
SI1 - 07
86
Operação de Divisão
q = r1  r2
r2
r1
numAL
CodDisc
2222
SI-1
2222
LC
2222
IA
3333
LC
3333
IA
4444
SI-2
4444
LC
4444
IA
5555
SI-1
5555
LC
Y
CodDisc
numAL
LC
2222
IA
3333
Interessam-nos as projecções
em Y dos tuplos de r1 que estão
associados com todos os tuplos
de r2.
4444
X
Y
Logo: de r1 interessam-nos as
projecções em Y dos tuplos (t1)
tais que não existe nenhum
tuplo de r2 que não tenha
associação com t1 em r1
SELECT DISTINCT Y FROM r1 AS q
WHERE NOT EXISTS
SELECT * FROM r2
WHERE NOT EXISTS
SELECT * FROM r1
WHERE r1.X = r2.X AND q.Y = r1.Y
X
SI1 - 07
87
Divisão
Ou, recorrendo à lógica de 1ª ordem:
q = r1  r2 = {t : (t2)(t2  r2  (t1)(t1  r1  conc(t,t2,t1)))}
= {t : (t2)(~(t2  r2)  (t1)(t1  r1  conc(t,t2,t1)))}
= {t : ~( t2)~(~(t2  r2)  (t1)(t1  r1  conc(t,t2,t1)))}
= {t : ~( t2)(t2  r2  ~(t1)(t1  r1  conc(t,t2,t1)))}
= {t : ~( t2)(t2  r2  ~(t1)(t1  r1  t1.X=t2.X  t1.Y = t.Y))}
SELECT distinct Y from r1 as q -- q é uma projecção em Y de alguns registos de r1
WHERE NOT EXISTS (
SELECT * FROM r2
WHERE NOT EXISTS (
select * from r1
where r1.X = r2.X AND
q.Y = r1.Y
)
)
SI1 - 07
88
Operação de Divisão

Quais os códigos dos departamentos que têm empregados em todas as
categorias existentes?
EMPREGADO
codEmp
nomeEmp
1
António Abreu
2
Bernardo Bento
3
Carlos Castro
4
Manuel Matos
5
Manuel Matos

CATEGORIA
codCat
1
1
3
2
3
codDep
1
2
3
2
2
codCat
1
2
3
designacao
CategoriaA
CategoriaB
CategoriaC
salarioBase
300
250
160
O resultado desejado pode ser obtido efectuando uma operação de
divisão entre EMPREGADO e CATEGORIA

 3,4 ( EMPREGADO )   1 ( CATEGORIA )
codDep
2
SI1 - 07
89
Operação de Divisão (cont.)

Quais os códigos dos departamentos que têm empregados de todas as
categorias ?

Ou, colocando a questão de outro modo:




Quais os códigos dos departamentos para os quais,
qualquer que seja a categoria,
existe algum empregado desse departamento e dessa categoria
Ou, utilizando uma notação simbólica:
codigoDepartamento : categoria  CATEGORIA,
(  empregado :
EMPREGADO.codDep = codigoDepartamento
and EMPREGADO.codCat = CATEGORIA.codCat )

Vamos designar a expressão que, está escrita entre parêntesis, por:

p(x)
SI1 - 07
90
Operação de Divisão (cont. 1)

Sabendo que,


 x : p(x)   x :  p(x)
Tem-se,
codigoDepartamento :  categoria  CATEGORIA, (  p(x) )

Ou seja,
codigoDepartamento :  categoria  CATEGORIA,
(   empregado :
EMPREGADO.codDep = codigoDepartamento
and EMPREGADO.codCat = CATEGORIA.codCat )
Os departamentos (codDep) onde não exista uma categoria que não tenha um
empregado.
SI1 - 07
91
Operação de Divisão (cont. 2)

Pretende-se a informação completa sobre os departamentos que têm
empregados de todas as categorias ?

DEPARTAMENTO
1=1
(  3, 4 ( EMPREGADO )   1 ( CATEGORIA ) )
select d.*
from departamento d
where not exists
(select *
from categoria c
where not exists
(select *
from empregado e
where e.coddep = d.coddep and e.codcat=c.codcat ) )
Departamento onde não existe
Uma categoria que não possua
Um empregado nessa categoria e nesse departamento
SI1 - 07
92
Comando Select –
order by, group by e having
Select …
from …
where …
group by…
having …
order by …
SI1 - 07
93
Comando SELECT

A sintaxe de uma interrogação SQL inclui as seguintes cláusulas:
SELECT [DISTINCT] <colunas> | *
FROM <tabela>, ...
[WHERE <condição>]
[GROUP BY <expressão de agrupamento>]
[HAVING <condição>]
[ORDER BY <expressão de ordenação> [ASC | DESC], … ]
SI1 - 07
94
ORDER BY

Ordena a relação resultante, por ordem crescente ou decrescente de
uma ou mais expressões de ordenação

é sempre a última cláusula a ser especificada
SELECT [DISTINCT] <colunas> | *
FROM <tabela>, ...
[WHERE <condição>]
[ORDER BY <expressão de ordenação> [ASC | DESC], … ]

ASC: ordenação ascendente (ascending), valor por omissão

DESC: ordenação descendente (descending)
SI1 - 07
95
ORDER BY (cont.)

Pretende-se toda a informação da tabela de empregado, ordenada
lexicograficamente pelo nome do empregado, de modo crescente
select *
from empregado
order by nome asc

Pretende-se a mesma informação que a apresentada no exemplo
anterior, mas neste caso, os nomes repetidos devem aparecer
ordenados de forma decrescente pelo código do empregado
select *
from empregado
order by nome asc, codemp desc
SI1 - 07
96
GROUP BY

Produz uma linha de resultados por cada conjunto de linhas com o
mesmo valor da expressão de agrupamento

Permite agregar grupos de linhas e a estes aplicar funções de agregação
SELECT [DISTINCT] <colunas> | *
FROM <tabela>, ...
[WHERE <condição>]
[GROUP BY <expressão de agrupamento>]

Quando a cláusula GROUP BY está presente, a cláusula SELECT
apenas pode conter:


colunas que apareçam na cláusula GROUP BY, ou
funções de agregação aplicadas a quaisquer outras colunas
SI1 - 07
97
GROUP BY (cont.)

Para cada código de departamento qual o seu salário base mínimo ?
select E.codDep, min ( C.salarioBase ) as salarioMinimo
from empregado as E
inner join categoria as C on ( E.codCat = C.codCat )
group by E.codDep

Agrupamento/agregação múltiplo/a:


quando o critério de agregação é múltiplo, primeiro é executado a
agregação pela primeira coluna, e depois para os tuplos com o mesmo valor
dessa coluna é aplicada a agregação pela segunda coluna (e por aí fora…)
Para cada código de departamento, qual o salário efectivo mínimo praticado
para cada código de categoria ?
select E.codDep, E.codCat, min( E.salarioEfectivo ) as salariominimo
from empregado AS E
inner join categoria as C on ( E.codCat = C.codCat )
group by E.codDep, E.codCat
DEPARTAMENTO( codDep, nome, localizacao )
CATEGORIA( codCat, nome, salarioBase )
EMPREGADO(SI1
codEmp,
nome, salarioEfectivo, codCat, codDep )
- 07
98
GROUP BY (cont.)

Para cada departamento e cada categoria qual o número de empregados
select d.codDep, c.codCat, count(E.codcat) as numEmp
from (departamento D cross join categoria C)
left outer join empregado E
on (d.codDep = E.codDep and C.codCat= E.codCat)
group by d.codDep, c.codCat
order by d.codDep, c.codCat;
SI1 - 07
99
HAVING

Aplica uma ou mais condições a cada grupo de linhas especificado pela
cláusula GROUP BY


Se a cláusula GROUP BY não for utilizada, o grupo considerado é o de todas
as linhas resultantes do SELECT
A cláusula HAVING é usada para definir restrições a grupos, assim
como a cláusula WHERE é usada para definir restrições a linhas.
SELECT [DISTINCT] <colunas> | *
FROM <tabela>, ...
[WHERE <condição>]
[GROUP BY <expressão de agrupamento>]
[HAVING <condição>]

Utilização das cláusulas HAVING e WHERE


a cláusula HAVING deve sempre conter funções de agregação
a cláusula WHERE nunca contém funções de agregação
SI1 - 07
100
HAVING (cont.)

Pretende-se uma lista onde conste, para cada código de departamento
o salário base mínimo nele praticado. Apenas se pretendem os
departamentos com salário base médio superior a 50
select E.codDep, min( C.salarioBase )
from categoria AS C
inner join empregado as E on( C.codCat = E.codCat )
group by E.codDep
having avg( C.salarioBase ) > '50'
SI1 - 07
101
Exemplo clarificador de having
Qual o número de empregados dos departamentos que
têm mais do que dois empregados?
select t1.codDep, t1.numEmps
from (select E.codDep, count(*) as numEmps
from Empregado E
group by E.codDep) as t1
where t1.numEmps > '2'
select E.codDep, count(*) as numEmps
from Empregado E
group by E.codDep
having count(*) > '2';
SI1 - 07
102
WHERE, GROUP BY, HAVING e ORDER BY

Pretende-se uma lista onde conste, para cada código de departamento
o valor do menor salário efectivo pago a empregados da categoria com
código ‘C1’. Só se pretendem os departamentos para os quais esse
menor salário efectivo seja superior ao menor salário base de todas as
categorias. Os valores mais altos devem ser apresentados primeiro.
SELECT E.codDep, MIN( E.salarioEfectivo )
FROM EMPREGADO AS E
WHERE E.codCat = '1'
GROUP BY E.codDep
HAVING MIN( E.salarioEfectivo ) >
( SELECT MIN( salarioBase ) FROM CATEGORIA )
ORDER BY MIN( E.salarioEfectivo ) DESC
SI1 - 07
103
O conceito de valor NULL

O valor NULL permite lidar com situações onde, para determinado tuplo, o
valor de um atributo seja indefinido ou desconhecido.

Situações de utilização do valor NULL:




o atributo não é aplicável para determinado tuplo;
o atributo tem um valor desconhecido para determinado tuplo;
o atributo tem valor conhecido mas o valor está ausente nesse instante, ou
seja, ainda não foi registado na Base de Dados.
Características dos valores NULL:




Independente do domínio - inteiro, real, caracter, data, etc.
Não comparáveis entre si - uma expressão com um operador de comparação
será avaliada como FALSE, se algum dos seus operandos tiver o valor NULL.
Existe função (ISNULL) para substituir o valor NULL por outro valor.
Existe directiva (IS NULL) para encontrar valores NULL numa interrogação
(com colunas definidas como permitindo valores NULL).
SELECT ISNULL(codCat, 1) FROM EMPREGADO WHERE codDep = 3
SELECT * FROM EMPREGADO WHERE salarioEfectivo IS NULL;
SI1 - 07
104
Mais notas acerca do SQL Server

Aconselha-se uma vista a:

SQL Server 2005 Books Online (September 2007)



Transact-SQL Reference (Transact-SQL)
http://msdn.microsoft.com/enus/library/ms189826(SQL.90).aspx
Tópicos abordados:



Schemas
Funções
Top e Case
SI1 - 07
105
Schema – um espaço de nomes
use DB3;
create schema SC1;
create table SC1.T1 (
nome varchar(120) not null,
localidade varchar(40) not null
);
insert into SC1.T1 values ('Joaquim Lopez', 'Madrid');
select * from SC1.T1;
delete from SC1.T1;
drop table SC1.T1;
drop schema SC1;
O schema por omissão é: dbo
Um schema é um espaço de nomes que
pode conter tabelas
SI1 - 07
106
Funções ( Functions ) e Top

SQL Server 2005 Books Online (September 2007)

Functions (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms174318(SQL.90).aspx
Consultar o link para mais informações, esta lista serve apenas para alertar
para a existência de funções


day, Month, Year, getdate
lower, upper, substring
Rand, cos, tan, square, pi
current_user, isdate, isnull

TOP N – devolve as N primeiras linhas




pode ser um valor percentual
Extra / Não standard
SI1 - 07
107
Case

Case - Avalia uma lista de condições e retorna o valor associado à

expressão da condição que sucedeu
Sintaxe:
Simple CASE expression:
CASE input_expression
(WHEN when_expression THEN result_expression)+
[ ELSE else_result_expression ]
END
Searched CASE expression:
CASE
(WHEN Boolean_expression THEN result_expression)+
[ ELSE else_result_expression ]
END
SELECT ProductNumber, Name, 'Price Range' =
CASE
WHEN ListPrice = 0 THEN 'Mfg item - not for resale'
WHEN ListPrice < 50 THEN 'Under $50'
WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
ELSE 'Over $1000'
END
FROM Product
SI1 - 07
108
Vistas (views)
SI1 - 07
109
Vistas (Views) da Base de Dados

Usando a terminologia SQL, uma Vista (normalmente referida como
View) consiste numa única tabela construída a partir de:



Características de uma Vista (View):




outras tabelas ou
outras Vistas anteriormente definidas.
Uma Vista (View) não tem dados próprios.
Os dados de uma Vista (View) são manipulados na(s) tabela(s) base que
servem de suporte a essa Vista.
Uma Vista (View) é armazenada como um comando SELECT.
Uma Vista (View) é uma tabela virtual, isto é, não tem existência física
embora apareça ao utilizador como se o tivesse, pelo que:


origina algumas limitações às operações de actualização (update);
não origina limitações às operações de interrogação (select).
SI1 - 07
110
Tipos de Vistas (Views) e sua utilidade

Tipos de Vistas (Views)

Vistas simples:



Vistas complexas:



construídas com base numa única tabela;
não contêm funções nem grupos de dados.
construídas com base em várias tabelas; ou
contêm funções ou grupos de dados.
Utilidade das Vistas (Views):




restringir o acesso à Base de Dados mostrando apenas parte dos dados;
simplificar a consulta dos dados, substituindo consultas elaboradas
envolvendo várias tabelas, por fáceis consultas sobre a Vista;
permitir que os mesmos dados sejam visualizados de diferentes maneiras
por diferentes utilizadores;
reduzir a possibilidade de incoerências (opção WITH CHECK OPTION alteração de dados através da Vista de acordo com a sua definição).
SI1 - 07
111
Criação de uma Vista – View

Comando CREATE VIEW
CREATE VIEW nome_vista
[ (nome_coluna_1, nome_coluna_2, ...) ]
AS SELECT comando [WITH CHECK OPTION]

“nome_coluna_i”:


nome da coluna usado na vista. Se não for especificado é assumido o
mesmo nome das colunas definidas na cláusula SELECT.
“AS SELECT comando” - consiste na directiva SELECT que define a
vista, podendo usar mais do que uma tabela e outras vistas. Esta
directiva tem algumas limitações:



não pode incluir as cláusulas ORDER BY
não pode incluir a instrução INTO
não pode referenciar uma tabela temporária.
SI1 - 07
112
Criação de uma Vista - View (Cont.)

Pretende-se criar uma vista que permita saber:
Para cada departamento quantos empregados existem e qual o montante
total de salários base.
CREATE VIEW INFORMACAODEPARTAMENTO
(nomeDepartamento, numEmpregados, totalSalarios)
AS SELECT D.nomeDep, COUNT(*), SUM(salarioBase)
FROM (DEPARTAMENTO AS D INNER JOIN EMPREGADO AS E
ON ( D.nomeDep = E.codDep )) inner join categoria C
on ( e.codCat = c.codcat)
GROUP BY D.nomeDep;


Esta vista é complexa: várias tabelas, funções e agregação de dados
DEPARTAMENTO( codDep, nome, localizacao )
CATEGORIA( codCat, nome, salarioBase )
EMPREGADO( codEmp, nome, salarioEfectivo, codCat, codDep )
SI1 - 07
113
Interrogação a uma Vista - View

Pretende-se, utilizando a vista INFORMACAO_DEPARTAMENTO,
saber:

Para o departamento de Informática, quantos empregados existem e qual o
montante total de salários.
SELECT numEmpregados, totalSalarios
FROM INFORMACAO_DEPARTAMENTO
WHERE nomeDepartamento = ‘Informática’

A vista não é concretizada no momento em que é criada, mas sempre
que é especificada uma interrogação sobre essa vista, permitindo obter
dados sempre actualizados.

As alterações das tabelas originais reflectem-se nas diversas vistas
dessas tabelas.
SI1 - 07
114
Remoção de uma Vista - View

Comando DROP
DROP VIEW nome_vista

Exemplo de remoção da vista INFORMACAO_DEPARTAMENTO
DROP VIEW INFORMACAO_DEPARTAMENTO

Algumas características da acção de remoção de uma vista:



a remoção de uma vista não tem qualquer influência nos dados das tabelas
que lhe serviam de base.
se existirem outras vistas que dependam da vista removida, essas vistas
deixam de ser válidas.
uma vista só pode ser removida por um utilizador com permissões para
efectuar essa operação
SI1 - 07
115
Actualização de dados através de uma Vista

Se a vista for definida sobre:




Se a vista for definida sobre:




uma única tabela e
sem funções de agregação de dados,
é uma operação simples que se traduz na actualização da tabela que lhe
serve de base.
múltiplas tabelas e
com funções de agregação de dados,
é uma operação complicada e que pode ser ambígua.
De uma forma geral, não são actualizáveis as vistas:


definidas sobre múltiplas tabelas utilizando junções (join);
que utilizam agrupamento de dados e funções de agregação.
SI1 - 07
116
Actualização de dados através de uma Vista (cont. 1)

O comando de DELETE não é permitido se a vista incluir:





O comando de UPDATE não é permitido se a vista incluir:



condições de junção (join);
funções de agrupamento;
o comando DISTINCT;
sub-interrogações correlacionadas.
qualquer das limitações do comando DELETE;
colunas definidas por expressões (tal como, salarioAno = 14 * salario).
O comando de INSERT não é permitido se a vista incluir:


qualquer das limitações do comando UPDATE;
colunas com possibilidade de ter valores NOT NULL que não tenham valores
de omissão nas tabelas base e que não estejam incluídas na vista através da
qual se pretende inserir novas colunas.
SI1 - 07
117
Actualização de dados através de uma Vista (cont. 2)

Os comandos de INSERT e UPDATE são permitidos em vistas
contendo várias tabelas base se:


o comando afectar apenas uma das tabelas que serve de base à vista;
no entanto, não é permitido num mesmo comando alterar mais do que uma
tabela.

As vistas permitem executar verificações de integridade sobre os dados
modificados através das vistas.

A opção WITH CHECK OPTION indica que os comandos de INSERT e
UPDATE executados através da vista só podem afectar registos
seleccionáveis pela vista (definidos no WHERE).
SI1 - 07
118
Verificação de Integridade em Vistas

Pretende-se criar uma vista que permita saber:


Quais os empregados que têm um salário inferior a 250000.
Também se pretende que qualquer acção de alteração sobre essa vista
apenas afecte os empregados cujo salário seja inferior a 250000.
CREATE VIEW VISTA_EMPREGADO
AS SELECT cod, nome
FROM EMPREGADO
WHERE salario < 250000
WITH CHECK OPTION

Devido ao Check Option as instruções de INSERT e UPDATE sobre a
vista têm que verificar as condições definidas na cláusula WHERE.

a operação será rejeitada no caso dos dados não verificarem essas
condições.
SI1 - 07
119
Tratamento das Vistas pelo SGBD

O comando CREATE VIEW não origina a execução do comando
SELECT a ele associado.

O comando CREATE VIEW apenas origina o armazenamento da
definição da vista (directiva SELECT) no dicionário de dados.

Ao aceder aos dados através de uma vista, o sistema:



extrai do dicionário de dados, a definição da vista;
verifica as permissões de acesso à vista;
converte a operação sobre a vista numa operação equivalente na tabela ou
tabelas que servem de base à vista.
SI1 - 07
120
Utilizadores e privilégios
LCD - Linguagem de Controlo de Dados
SI1 - 07
121
Segurança no acesso à Base de Dados

Conceder ou retirar, selectivamente aos utilizadores, acesso aos
objectos (ex. tabelas e vistas) da Base de Dados.

O acesso aos objectos da Base de Dados é controlado através de
privilégios.

Privilégio consiste no direito a executar um determinado comando SQL
ou em aceder a um objecto de outro utilizador. Alguns exemplos:






aceder à Base de Dados;
criar Bases de Dados.
criar, remover e alterar tabelas e vistas.
executar comandos de pesquisa de dados em tabelas.
inserir, remover e alterar dados em tabelas.
visualizar tabelas de outros utilizadores.
Uma base de dados é um contexto agrupador de tabelas e outras entidades.
Num SGBD temos os seguintes comando associados às bases de dados: CREATE databasename,
USE databasename, DROP databasename, ALTER databasename, SHOW databases
SI1 - 07
122
Privilégios

Informalmente identificam-se dois níveis para atribuição de privilégios à
utilização do sistema de base de dados:

Nível de utilizador

A este nível o administrador da base de dados (no SQL Server é o utilizador
sa) especifica os privilégios de cada utilizador (ou grupo de utilizadores), que
inclui:



criação de Bases de Dados - (no SQL Server o sa apenas pode conceder este
privilégio a utilizadores da base de dados master);
acesso a Bases de Dados;
criação de tabelas, vistas, regras de integridade e construção de cópias de
segurança (backup) - (no SQL Server este privilégio pode também ser concedido
pelo utilizador que criou a Base de Dados).
SI1 - 07
123
Privilégios (cont.)

Nível de objecto

A este nível o administrador da base de dados ou o utilizador que criou o
objecto (a base de dados, a tabela, a vista, etc) podem especificar os
privilégios de cada utilizador (ou grupo de utilizadores), que inclui:





remoção do objecto (*);
execução de pesquisas de dados sobre tabelas e vistas;
inserção, remoção e alteração de dados em tabelas;
execução de pesquisas e alterações de dados sobre cada uma das colunas de
cada tabela.
(*) no SQL Server a remoção da Base de Dados é um privilégio que é
automaticamente atribuído ao utilizador que criou a Base de Dados e
que não pode ser transferido para mais nenhum utilizador.
SI1 - 07
124
Administração de privilégios

Comando para conceder privilégios - GRANT (nível de utilizador)
GRANT { ALL | lista_privilégios_nível_utilizador }
TO { PUBLIC | lista_de_nomes }

ALL


“lista_ privilégios_nível_utilizador”


No SQL Server esta lista inclui as directivas CREATE DATABASE; CREATE
TABLE; CREATE VIEW; CREATE RULE; DUMP DATABASE
PUBLIC


No SQL Server apenas o utilizador sa pode utilizar esta opção porque só ele pode
conceder o privilégio CREATE DATABASE.
Concede acesso a todos os utilizadores.
“lista_de_nomes”

Especifica o nome de cada utilizador ou grupo, separados por vírgulas.
SI1 - 07
125
Administração de privilégios (cont. 1)

Comando para conceder privilégios - GRANT (nível de objecto)
GRANT { ALL | lista_ privilégios_nível_objecto }
ON nome_tabela_ou_vista [ (lista_colunas) ]
TO { PUBLIC | lista_de_nomes }

ALL


Indica que todos os privilégios aplicáveis ao objecto são concedidos
“lista_ privilégios_nível_objecto”



No SQL Server esta lista inclui as directivas SELECT, INSERT, DELETE,
UPDATE.
No SQL Server se o privilégio se referir a uma tabela inclui a directiva
REFERENCES, que dá ao utilizador a permissão de criar chaves estrangeiras sem
precisar de ter permissão de SELECT na tabela referenciada pela chave
estrangeira.
No SQL Server se o privilégio se referir a uma coluna esta lista apenas inclui as
directivas SELECT e UPDATE.
SI1 - 07
126
Administração de privilégios (cont. 2)

Comando para conceder privilégios - REVOKE (nível de utilizador)
REVOKE { ALL | lista_privilégios_nível_utilizador }
TO { PUBLIC | lista_de_nomes }

Comando para conceder privilégios - GRANT (nível de objecto)
REVOKE { ALL | lista_ privilégios_nível_objecto }
ON nome_tabela_ou_vista [ (lista_colunas) ]
TO { PUBLIC | lista_de_nomes }

As características do comando REVOKE são idênticas às do comando
GRANT, atrás apresentadas.
SI1 - 07
127
Criar um login e um user no SQL Server
-- criar um novo login e user para uma BD já existente *
-- criar um novo login
create login user20 with password = 'user20---';
-- criar um user para uma BD com o schema dbo por omissão
create user user20 for login user20 with default_schema = dbo;
-- no contexto da DB2 dar permissões ALL para o user20 (não ao login)
use db2;
grant all to user20;
grant insert, delete, update, select, alter on schema::dbo to user20;
-- pode-se verificar no management studio, no Object Explorer:
- em Security->Logins->user20 que o login user20 existe e que dentro
dele em User Mapping verificar que em DB2 ele tem o user20 associado
e com o schema dbo por omissão.
- em Databases->DB2->Security->Users->User20 em Securables as
permissões que ele tem para o Schema de nome ‘dbo’
* O utilizador corrente tem de ter permissões para as seguintes acções
SI1 - 07
128
Remover o user e o login
-- remover o user de uma BD e o login
-- seleccionar o contexto da base de dados em questão
use db2;
-- remover as permissões ao user sobre o schema dbo
revoke insert, delete, update, select, alter on schema::dbo to user20;
-- remover as permissões ao user relativos à base de dados
revoke all to user20;
-- remover o user
drop user user20;
-- remover o login
drop login user20;
SI1 - 07
129
Criar um login e user (via stored procedures)
-- Adds a new login account
exec sp_addlogin 'user20', 'user20---', 'db2';
use db2;
-- Adds a security account in the current database to the user
-- creates user 'user20' for login 'user20'
exec sp_grantdbaccess 'user20', 'user20';
-- To run a SELECT statement against any table or view in the database.
exec sp_addrolemember db_datareader, 'user20';
-- To add, delete, or change data in all user tables
exec sp_addrolemember db_datawriter, 'user20';
-- To run any Data Definition Language (DDL) command in a database.
exec sp_addrolemember db_ddladmin, 'user20';
alter user user20 with default_SCHEMA = dbo;
-- remover login e user
use db2;
exec sp_droprolemember db_datareader, 'user20';
exec sp_droprolemember db_datawriter, 'user20';
exec sp_droprolemember db_ddladmin, 'user20';
exec sp_revokedbaccess 'user20';
exec sp_droplogin 'user20';
SI1 - 07
130
Criar/remover bases de dados
-- criar uma base de dados se não existir
if not exists(
select * from sys.databases where name = 'DB7')
create database DB7;
-- Utilizar o contexto da base de dados DB7
use DB7;
-- Criar tabelas, vistas, etc
-- Executar: insert, update, delete, select,
-- remover uma base de dados
use master; -- utilizar outro contexto
drop database DB7;
Nota: para cada problema podem criar uma base de dados diferente no vosso servidor.
Claro que também podem criar a base de dados e apagá-la logo de seguida.
SI1 - 07
131
Sugestão para a estrutura dos scripts SQL
-- criar (caso não exista) a base de dados
if not exists(
select * from sys.databases where name = 'DB7')
create database DB7;
use DB7;
-- destruir (caso exista) todo o modelo físico
if exists(
select * from information_schema.tables where table_name = 't1')
drop table t1;
-- criar todo o modelo físico
create table t1(
col1 int
)
-- inserção de dados
insert into t1 (col1) values ('101');
-- utilização dos dados
select * from t1;
SI1 - 07
132
Desactivar/activar restrições
-- criar uma tabela com uma restrição de Check
create table dbo.t1( c1 int constraint ck_c1 check (c1 > 0) )
insert into dbo.t1 values(-1) -- ERRO
-- desactivar a verificação da restrição
alter table dbo.t1 nocheck constraint ck_c1
insert into dbo.t1 values(-1) – OK
Exemplo de utilização:
No caso em que uma tabela t1 tem uma
FK para outra tabela t2, e t2 tem uma FK
para t1 temos uma situação em que é
impossível inserir o primeiro valor. Neste
caso suspende-se uma FK e já se pode
inserir o primeiro valor.
-- reactivar a verificação da restrição
alter table dbo.t1 with nocheck check constraint ck_c1
-- forçar a verificação da restrição
Neste ponto a restrição fica não
verificada (untrusted)
alter table dbo.t1 with check check constraint ck_c1 – ERRO
delete dbo.t1 where c1 = -1
alter table dbo.t1 with check check constraint ck_c1 -- OK
SI1 - 07
133