Sql server

Prévia do material em texto

1 
 
 
 
 
 
Base de Dados 
 
 
SQL - Server 
 
Carlos Ramos 
 
ESGN 
2008/2009 
 
 
2 
Índice 
 
SQL Server - Arquitectura Cliente/Servidor ........................................................................................ 3 
SQL Server - Arquitectura da Base de Dados ...................................................................................... 4 
SQL Server - SQL Server Enterprise Manager. .................................................................................... 8 
SQL Server - Criar uma base de dados ............................................................................................ 12 
SQL Server - Criar uma base de dados (Exemplo) ............................................................................. 15 
SQL Server - Criar uma base de dados (Outro Exemplo) .................................................................... 17 
SQL Server - Criar uma tabela ....................................................................................................... 19 
Restrições de Integridade de Gatilhos ............................................................................................. 21 
Restrições de Chaves ................................................................................................................... 22 
Restrições de Integridade Referencial .............................................................................................. 19 
Restrições Baseadas em Atributos .................................................................................................. 24 
Nomes das Restrições de Integridade .............................................................................................. 25 
Gatilhos em SQL3 ........................................................................................................................ 26 
Gatilhos em SQL-Server ................................................................................................................ 29 
Índices ....................................................................................................................................... 31 
Vistas ........................................................................................................................................ 32 
Vistas e Segurança ..................................................................................................................... 35 
SQL – Linguagem de Manipulação de Dados (LMD) ........................................................................... 37 
SQL Server– Procedures ............................................................................................................... 41 
SQL Server– Anexos ..................................................................................................................... 43 
 
3 
SQL Server - Arquitectura Cliente/Servidor 
 
 
 Microsoft® SQL Server™ foi desenhado para ser um sistema Cliente/Servidor: 
 
- A Base de Dados pode residir num único computador, conhecido como Servidor, e assim está 
garantida a partilha dos dados por múltiplos utilizadores; 
 
Figura 1. 
 
4 
 
SQL Server - Arquitectura da Base de Dados 
 
 
Figura 2. 
 
 Podem existir múltiplas Bases de dados; 
 O SQL Server tem quatro bases de dados de sistema (master, model, tempdb, and msdb); 
 
5 
SQL Server - Arquitectura da Base de Dados (Continuação) 
 
Bases de Dados de Sistema: 
Microsoft® SQL Server™ tem quatro bases de dados de sistema: 
 
 Master , regista: 
- todas as contas e as configurações do sistema. 
- a existência de todas as outras bases de dados e a localização dos ficheiros primários que contêm a informação de 
inicialização para as bases de dados dos utilizadores. 
- A informação de inicialização do SQL Server . 
 
 Tempdb: 
- Regista toda a informação temporária. 
- É recriada sempre que o SQL Server é iniciado. 
- Todas as tabelas temporárias são eliminadas sempre que é terminada a ligação. 
- A base de dados cresce de acordo com as necessidades. 
 
 Model : 
- É usada como um modelo para todas as bases de dados criadas no sistema. 
- Quando a instrução CREATE DATABASE é usada, a primeira parte da nova base de dados é criada copiando o 
conteúdo da base de dados model, o resto da base de dados é preenchida com páginas em brando. 
 
 Msdb: 
- É usada para escalonar sinais de alerta e trabalhos. 
 
6 
SQL Server - Arquitectura da Base de Dados (Continuação) 
 
 No SQL Server 7.0, todas as bases de dados, incluindo as bases de dados de sistema, têm o seu próprio conjunto de 
ficheiros, que não são partilhados com nenhuma outra base de dados. A localização por defeito dos referidos ficheiros é 
C:\Mssql7\Data directory: 
 
Ficheiro da Base de 
dados 
Nome físico do 
Ficheiro 
Tamanho por defeito 
Master primary data Master.mdf 7.5 MB 
Master log Mastlog.ldf 1.0 MB 
tempdb primary data Tempdb.mdf 8.0 MB 
tempdb log Templog.ldf 0.5 MB 
model primary data Model.mdf 0.75 MB 
model log Modellog.ldf 0.75 MB 
msdb primary data Msdbdata.mdf 3.5 MB 
msdb log Msdblog.ldf 0.75 MB 
 
 Cada base de dados no SQL Server contém tabelas de sistema que regista os dados necessários aos componentes do SQL 
Server. 
 Os utilizadores não podem alterar directamente a informação das tabelas de sistema. 
 Existe um conjunto de ferramentas que permitem aos utilizadores administrar o seu sistema. 
 
7 
 Podem ser utilizados utilitários de administração tais como SQL Server Enterprise Manager, para administrar directamente 
o sistema. 
 Os Programadores construindo scripts em Transact-SQL e procedures conseguem garantir todas as funções 
administrativas do seu sistema. 
 Não são suportados triggers definidos nas tabelas de sistema; podem alterar o funcionamento do mesmo. 
 
8 
SQL Server - Arquitectura da Base de Dados – Continuação 
 
 
 
Figura 3. 
 
 
 SQL Server permite o acesso de múltiplos utilizadores a trabalhar em diferentes bases de dados ao mesmo tempo; 
 Quando um utilizador estabelece uma ligação, fica associado a uma base de dados específica no servidor, chamada de 
base de dados corrente; 
 Cada utilizador pode comutar entre diferentes bases de dados recorrendo ao Transact-SQL USE database_name ; 
 
9 
SQL Server - SQL Server Enterprise Manager. 
 
 As aplicações em SQL Server são construídas de acordo com uma importante hierarquia chamada SQL Distributed Management 
Framework ( SQL-DMF ). 
 
- No primeiro nível estão comandos em Transact-SQL. Transact-SQL é uma versão Microsoft do SQL standard. 
- Tudo no SQL Server pode ser feito através de comandos em texto simples ( se souber o que está a fazer…). 
- O segundo nível é um conjunto de objectos de gestão da base de dados que invocam comandos Transact-SQL. 
- No topo da hierarquia está o SQL Server Enterprise manager. 
 
O SQL Server Enterprise Manager é uma ferramenta gráfica que permite efectuar a gestão do universo SQL Server: 
 
- Configurar servidores. 
- Criar e gerir devices. 
- Criar e gerir bases de dados. 
- Fazer cópias de segurança e restaurar bases de dados. 
- Criar, modificar e eliminar objectos. 
- Gerir utilizadores da base de dados. 
- Executar queries. 
- Configurar e modificar tarefas automáticas. 
- Controlar replicação entre servidores. 
- ... 
 
10 
- SQL Server - SQL Server Enterprise Manager (Continuação). 
 
11 Figura 4. 
 
12 
SQL Server - Criar uma base de dados 
 
 No SQL Server, os dados são armazenados em bases de dados e organizados em componentes lógicos visíveis para os utilizadores. 
 Uma base de dados é implementada fisicamente em dois ou mais ficheiros em disco. 
 Existem dois componentes que compõemum sistema cliente/servidor de bases de dados: programas que proporcionam aos utilizadores o 
acesso aos dados (cliente), e a estrutura que permite gerir os dados (servidor). 
 Criar uma base de dados no SQL Server significa planear, criar e gerir um conjunto de componentes que se relacionam: 
 
 Base de Dados - Contém os objectos usados para representar, gerir e aceder aos dados. 
 CREATE DATABASE 
 Tabelas - Armazenam linhas de dados e definem relações entre múltiplas tabelas. 
 CREATE TABLE 
 Diagramas- Representam graficamente os objectos da base de dados. 
 Índices - Optimizam a velocidade de acesso aos dados de uma tabela. 
 CREATE INDEX 
 Vistas - Proporcionam uma maneira alternativa de visualizar os dados em uma ou mais tabelas. 
 CREATE VIEW 
 Stored Procedures - Programas em Transact-SQL que definem regras de negócio, tarefas, e processos no servidor. 
 CREATE PROCEDURE 
 Triggers - São tipos especiais de Stored Procedures que só são executados quando os dados de uma tabela são 
alterados. 
 CREATE TRIGGER 
 Full-Text Indexes - Facilitam a consulta em dados armazenados em colunas do tipo texto (varchar, text). 
 
13 
SQL Server - Criar uma base de dados (Continuação) 
 
 SQL Server executa a instrução CREATE DATABASE em dois passos: 
 
1. SQL Server usa uma cópia da base de dados model para inicializar a nova base de dados. 
 
2. SQL Server preenche o resto da base de dados com páginas vazias. 
 
 
 Existem três tipos de ficheiros para armazenar uma base de dados: 
 
 O Ficheiro primário, que contém a informação de inicialização da base de dados e também pode ser usado para armazenar 
dados; Todas as bases de dados têm obrigatoriamente um Ficheiro Primário. 
 
 Ficheiro Secundário, usado para armazenar os dados que não cabem no ficheiro primário. Uma base de dados não precisa do 
ficheiro secundário se o ficheiro primário tiver capacidade suficientemente. Por outro lado pode-se usar múltiplos ficheiros 
secundários, eventualmente, dispersos por várias unidades de disco. 
 
 Ficheiros de LOG, que armazenam informação necessária à recuperação da base de dados. Tem que existir pelo menos um 
ficheiro de LOG. O tamanho mínimo para um ficheiro de LOG é de 512K. 
 
Tipo de Ficheiro Extensões 
Ficheiro primário .mdf 
Ficheiro secundário .ndf 
Ficheiro de Log .ldf 
 
 
14 
 
 
SQL Server - Criar uma base de dados 
Syntax 
CREATE DATABASE database_name 
 
/* Define o Ficheiro primário, se não se colocar esta primitiva, o primeiro ficheiro especificado é assumido como primário*/ 
[ ON [PRIMARY] 
[ <filespec> [,...n] ] 
] 
/* Para especificar o ficheiro de LOG, se nada for dito é criado um ficheiro de LOG com uma capacidade de 25% do total dos 
tamanhos de todos os ficheiros da base de dados*/ 
 
[ LOG ON { <filespec> } ] 
/* Primitivas usadas por questões de compatibilidade com versões anteriores do SQL Server*/ 
[ FOR LOAD | FOR ATTACH ] 
<filespec> ::= 
( [ NAME = logical_file_name, ] 
FILENAME = 'os_file_name' 
[, SIZE = size] 
[, MAXSIZE = { max_size | UNLIMITED } ] 
[, FILEGROWTH = growth_increment] ) [,...n] 
 
 
15 
SQL Server - Criar uma base de dados (Exemplo) 
 
 
 Este conteúdo faz parte de um ficheiro de script, do tipo texto que deve ser armazenado com extensão .SQL. 
 
 
/* Tem-se que utilizar sempre a base de dados Master*/ 
/* pois esta é que guarda toda a informação relativa à existência de todas as bases de dados do sistema*/ 
 
USE MASTER 
/* Elimina a Base de Dados com o nome MJS_BD1 */ 
DROP DATABASE MJS_BD1 
/* Esta instrução dá ordem de execução*/ 
GO 
 
CREATE DATABASE mjs_bd1 
ON 
/* Nome da base de dados*/ 
( NAME = exerc1, 
 
FILENAME = 'c:\mssql7\mjsbd1.mdf', 
/* Especifica o tamanho do ficheiro em MB*/ 
SIZE = 2, 
/* Especifica o tamanho máximo, em MB, que o ficheiro mjsbd1.mdf pode tomar, se se colocar o sufixo UNLIMITED o ficheiro 
pode crescer até o disco ficar cheio*/ 
 
16 
MAXSIZE = 8, 
/* Tamanho acrescentado ao ficheiro sempre que novo espaço é necessário, podem ser usados os sufixos KB, MB ou %, por 
defeito é MB*/ 
FILEGROWTH = 20% ) 
GO 
 
17 
SQL Server - Criar uma base de dados (Outro Exemplo) 
 
USE master 
GO 
CREATE DATABASE Archive 
ON 
PRIMARY ( NAME = Arch1, 
FILENAME = 'c:\mssql7\data\archdat1.mdf', 
SIZE = 100MB, 
MAXSIZE = 200, 
FILEGROWTH = 20), 
( NAME = Arch2, 
FILENAME = 'c:\mssql7\data\archdat2.ndf', 
SIZE = 100MB, 
MAXSIZE = 200, 
FILEGROWTH = 20), 
( NAME = Arch3, 
FILENAME = 'c:\mssql7\data\archdat3.ndf', 
SIZE = 100MB, 
MAXSIZE = 200, 
FILEGROWTH = 20) 
LOG ON 
( NAME = Archlog1, 
FILENAME = 'c:\mssql7\data\archlog1.ldf', 
SIZE = 100MB, 
MAXSIZE = 200, 
FILEGROWTH = 20), 
( NAME = Archlog2, 
FILENAME = 'c:\mssql7\data\archlog2.ldf', 
SIZE = 100MB, 
 
18 
MAXSIZE = 200, 
FILEGROWTH = 20) 
GO 
 
19 
SQL Server - Criar uma tabela 
 
Tipos de Dados: 
 
Data e Hora: 
- datetime - para armazenar datas desde Janeiro 1, 1753, a Dezembro 31, 9999 
- smalldatetime – para armazenar datas desde Janeiro 1, 1900, a Junho 6, 2079 
Binários: 
- varbinary 
- binary 
Texto: 
- char - até 8K, um nº fixo de caracteres. 
- varchar - até 8K, admite que em cada linha possa existir um nº variável de caracteres 
- text - superior a 8K 
Numéricos: 
- int - 4 bytes para representar um número 
- smallint - 2 bytes 
- tinyint - 1 byte, de 0 a 256. 
- decimal ou numeric 
- float 
- real 
Monetários: 
- money 
- smallmoney 
 
Especiais: 
- bit - Valores booleanos 0 ou 1. 
 
 
20 
SQL Server - Criar uma tabela 
 
CREATE TABLE <nome da relação> (<lista de nomes e tipos de dados>) 
 
Exemplo 
 
/* Usa esta base de dados, pois é nela que vai criar a tabela*/ 
use mjs_bd1 
 
create table unidade 
( 
 codun tinyint not null, 
 descricao char(20), 
 
 /* Inicio da definição das restrições de integridade, PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE */ 
 constraint pk_unidade primary key(codun) 
) 
 
Apagar uma relação: 
 
DROP TABLE <relação>; 
 
Alterar esquemas de uma relação: 
 
Exemplos: 
 ALTER TABLE actor ADD telefone CHAR(16); 
 ALTER TABLE actor DROP nascimento; 
 
21 
Restrições de Integridade de Gatilhos 
 
 
 Restrições de Chave 
 Restrições de Integridade Referencial 
 Restrições Baseadas em Atributos 
 Restrições Baseadas em Tuplos 
 Gatilhos SQL3 
 Asserções SQL3 – (Não são suportadas pelo SQL-Server) 
 
 
 
 
 Restrições (constraints) e gatilhos (Triggers) são elementos activos da BD; 
 Expressões ou comandos que são escritos uma vez e guardados na BD e serão executados como resposta a certos 
eventos; 
 São testadas sempre que há modificações nas relações envolvidas 
 
22 
Restrições de Chaves 
 
 Fazem parte da instrução CREATE TABLE 
 São especificadas usando a primitiva PRIMARY KEY 
 
CREATE TABLE ACTOR(NOME CHAR(30) PRIMARY KEY, ....); 
CREATE TABLE FILME(TITULO CHAR(30), ANO NUMBER(4), 
 PRIMARY KEY(TITULO, ANO)); 
 Chaves também podem ser especificadas utilizando a primitiva UNIQUE 
 Podem existir várias declarações UNIQUE mas só uma chave primária 
 Ao definir-se um determinado conjunto de campos como chave primária, estamos a especificar que 
estes campos não admitem valores nulos e não podem existir valores repetidos. 
 Com a primitiva UNIQUE deixa-se em aberto a possibilidade de existirem valores nulos 
 Pode-se proibir a existência de valores nulos para atributos, com recurso à primitiva NOT NULL. 
 
23 
Restrições de Integridade Referencial 
 
 Chaves estrangeiras fazem sentido para obrigardeterminados atributos a receberem valores que fazem 
parte da chave primária de uma relação referenciada 
 Quando a chave externa possui somente um atributo pode-se utilizar a primitiva REFERENCES 
<tabela>(<atributo>) 
 Para um ou mais atributos utiliza-se a primitiva FOREIGN KEY (<atributos>) REFERENCES 
<tabela>(<atributos>) 
 Exemplo 
 
CREATE TABLE PARTICIPA( 
 FILME CHAR(50), 
 ANO NUMBER(4), 
 ACTOR CHAR(30) REFERENCES ACTOR(NOME), 
 SALARIO DECIMAL(9,2), 
 FOREIGN KEY (FILME, ANO) REFERENCES FILME(FILME, ANO) 
); 
 
24 
Restrições Baseadas em Atributos 
 
 Os valores permitidos para os atributos podem ser restritos com recurso à primitiva CHECK <condição> 
 A verificação da restrição é associada apenas ao atributo em questão. 
 Exemplos 
 
SEXO CHAR(1) CHECK (SEXO IN („F‟, „M‟)), 
PRESIDENTE INT CHECK (PRESIDENTE IN (SELECT PRES FROM EXECUTIVO)), 
 
Não é equivalente a uma restrição de integridade referencial pois a restrição de integridade é apenas verificada para o atributo em questão. Por 
exemplo, se alterar o código do presidente na tabela Presidente, com a instrução CHECK não dá erro. 
 
 
 
25 
Nomes das Restrições de Integridade 
 
 
 Para se efectuar a manutenção de uma restrição de integridade esta deve ter nome 
 
NOME CHAR(30) CONSTRAINT PK_AUTOR PRIMARY KEY 
SEXO CHAR(1) CONSTRAINT CK_SEXO CHECK (SEXO IN („F‟, „M‟)) 
CONSTRAINT FK_PARTICIPA FOREIGN KEY (FILME, ANO) REFERENCES FILME(TITULO, ANO) 
 
 Restrições podem ser apagadas com a primitiva ALTER TABLE ....DROP .... 
ALTER TABLE ACTOR DROP CONSTRAINT PK_ACTOR; 
 
 Restrições podem ser adicionadas com a primitiva ALTER TABLE ....ADD .... 
ALTER TABLE ACTOR ADD CONSTRAINT PK_ACTOR PRIMARY KEY(NOME); 
 
26 
Gatilhos em SQL3 
 
 
 
 São regras Event-Condition-Action (ECA) 
 A acção pode ser executada antes, depois ou em vez do evento que a dispara 
 A acção pode referir-se a valores novos ou aos antigos dos tuplos inseridos, modificados ou apagados no evento que a 
dispara 
 Eventos de modificação podem especificar um atributo ou um conjunto de atributos 
 Uma condição pode ser especificada por uma cláusula WHEN e a execução da acção só tem lugar se a regra é disparada e a 
condição é verdadeira quando o evento ocorre 
 
27 
Gatilhos em SQL3 – Continuação 
 
 EXECUÇÃO da ACÇÃO 
 
 AFTER – WHEN testado depois de executada a operação associada ao evento 
 BEFORE - WHEN testado antes de ser executada a operação associada ao evento 
 INSTEAD OF - a(s) acção(ões) associada(s) ao trigger são executadas caso se verifique a condição WHEN, e a 
operação associada ao evento nunca é executada. 
 
 
 EVENTOS DE DISPARO 
 
 UPDATE 
 INSERT 
 DELETE 
 
 A acção do trigger pode conter várias instruções em SQL separadas por vírgulas 
 A execução da referida acção pode ser por cada tuplo (FOR EACH ROW) ou uma única vez. 
 
28 
Gatilhos em SQL3 – Continuação 
 
EXEMPLO 
 
Pretendemos criar um trigger que impeça a subida da média dos salários dos executivos para além de 50000. 
 
CREATE TRIGGER MEDIASALTRIGGER 
INSTEAD OF UPDATE OF SALARIO ON EXECUTIVO 
REFERENCING 
 OLDTABLE AS VELHOS, 
 NEWTABLE AS NOVOS 
WHEN (50000<=(SELECT AVG(SALARIO) 
 FROM (( EXECUTIVO EXCEPT VELHOS) UNION NOVOS) 
 ) 
 ) 
DELETE FROM EXECUTIVO WHERE (CERTIFICADO, NOME, MORADA, SALÁRIO) IN VELHOS; 
INSERT INTO EXECUTIVO (SELECT * FROM NOVOS); 
 
29 
Gatilhos em SQL-Server 
Para garantir a mesma funcionalidade do trigger supra definido em SQL3 utilizaria a seguinte sintaxe em SQL-Server: 
 
 
use master 
go 
use mjs_bd1 
go 
drop trigger mediasaltrigger 
go 
 
CREATE TRIGGER MEDIASALTRIGGER ON EXECUTIVO for UPDATE, insert /*Este trigger responde aos eventos de update e insert*/ 
as 
declare @media numeric /* Declaração de uma variável local (o nome da variável tem que ser sempre precedida de @*/ 
select @media=avg(salário) from executivo 
 
if (select avg(salário) from executivo)>=5000 /* Equivalente à condição WHEN*/ 
begin 
 print 'vai passear' /*Visualiza uma mensagem no ecrã*/ 
 
 (select * from inserted where exists /* Estas instruções estão a mais só para fazer referência aos tuplos que foram 
inseridos ou actualizados na tebela*/ 
/* Para fazer referência aos tuplos anteriores seria com deleted*/ 
 (select * from EXECUTIVO)) 
 
 rollback /* Esta instrução permite repor a instância anterior*/ 
end 
GO 
 
30 
Gatilhos em SQL-Server - Continuação 
 
Outras instruções que podem ser usadas na acção de um trigger: 
 BEGIN TRANSACTION – INICIA UMA TRANSACÇÃO QUE DEPOIS PODERÁ SER CONCRETIZADA COM A INSTRUÇÃO 
COMMIT TRANSACTION OU NÃO, COM RECURSO À INSTRUÇÃO ROLLBACK; 
 BEGIN....END – CINSCUNSCREVE UMA INSTRUÇÃO COMPOSTA 
 IF....ELSE 
 WHILE... 
 UPDATE(CAMPO) – DETERMINA SE FOI EFECTUADA UMA OPERAÇÃO DE UPDATE SOBRE O CAMPO 
ESPECIFICADO. 
 
 INSERTED – TABELA QUE CONTÉM OS NOVOS TUPLOS DEPOIS DA ALTERAÇÃO POR PARTE DO EVENTO 
QUE ACTIVOU O TRIGGER; 
 DELETED – TABELA QUE OS TUPLOS COM A INSTÂNCIA ANTERIOR À EXECUÇÃO DO EVENTO QUE 
ACTIVOU O TRIGGER 
 
31 
Índices 
 
 Um índice visa tornar mais eficiente a pesquisa de dados cujo critério de selecção envolva um determinado campo. 
 A decisão de usar ou não um índice cabe ao projectista da base de dados, tendo em consideração: 
- melhora muito o desempenho na obtenção de respostas para perguntas envolvendo os atributos especificados no 
índice; 
- torna mais demoradas as inserções, eliminações e alterações de tuplos da relação. Pois tem que proceder à 
reordenação do índice. 
 
 Criação de índices. 
 
CREATE INDEX INDICEANO ON FILME(ANO); 
CREATE INDEX INDICECHAVE ON FILME(TITULO, ANO); 
 
 Eliminar um índice. 
 
DROP INDEX INDICEANO; 
 
32 
Vistas 
 
 Vistas são relações lógicas, sem existência física ao nível da unidade de disco. 
 Relações com existência física são vulgarmente identificadas por tabelas. 
 Criação de Vistas: 
CREATE VIEW <NOME> AS <DEFINIÇÃO>; 
 Exemplos: 
 
CREATE VIEW FILMESPARAMOUNT AS 
 SELECT TITULO, ANO 
 FROM FILME 
 WHERE ESTUDIO=‟PARAMOUNT‟; 
Criou uma vista com o nome “FilmesParamount”, que, posteriormente, pode ser usada tal como se fosse uma 
tabela. Exemplo: 
 
SELECT TITULO 
FROM FILMESPARAMOUNT 
WHERE ANO=1979; 
 
33 
Vistas - continuação 
 
Vistas Modificáveis. 
Uma vista é modificável se: 
- a cláusula WHERE não incluir nenhuma sub-pergunta; 
- os atributos especificados na cláusula SELECT da vista forem suficientes por forma a permitir que os restantes 
atributos possam ficar com valor NULL ou um valor por omissão na tabela base. 
Nota: As operações de inserção, alteração e eliminação efectuadas sobre uma vista podem ser transpostas para a tabela 
física, desde que os pressupostos anteriormente referidos sejam salvaguardados. 
 
Exemplo: 
 Tendo em consideração a relação actor(codigo, nome, nacionalidade): 
 
 CREATE VIEW TESTE1 AS 
 SELECT CODIGO, NOME 
 FROM ACTOR; 
Esta vista poderá ser modificável, desde que o 
campo nacionalidade da tabela física actor, 
admita valores nulos ou um determinado valor 
por defeito, caso contrário, teríamos que incluir o 
campo “nacionalidade” na cláusula SELECT 
 
34 
Vistas - continuação 
 
Manipulação de Vistas: 
 
- Remoções 
 
DELETE FROM FILMESPARAMOUNT 
WHERE TITULO LIKE „%TREK%‟; 
 Nota: Os caracteres % significam que a cadeia de caracteres pode ter qualquer sequência antes e depois da string 
“TREK”. 
 
- Modificações 
 
UPDATE FILMESPARAMOUNTSET ANO=1979 WHERE TITULO LIKE „%TREK%‟; 
 
- Remover uma vista 
 
DROP VIEW FILMESPARAMOUNT; 
 
35 
Vistas e Segurança 
 
 Vistas podem ser usadas para apresentar a informação necessária, escondendo detalhes das tabelas base 
 Comandos GRANT e REVOKE podem ser usados para controlar acessos a tabelas base e vistas. 
 Em conjunto com a possibilidade de definir vistas, proporciona uma mecanismo poderoso de controlo de acessos. 
 A segurança pode chegar até ao nível do campo 
 Criar uma vista que devolve um campo de apenas um tuplo 
 Permitir o acesso (GRANT) a essa vista. 
 
Exemplos: 
 
 GRANT INSERT, SELECT ON ACTOR TO CRAMOS; 
CRAMOS pode inserir e consultar tuplos da tabela ACTOR. 
 
 GRANT DELETE ON ACTOR TO RAMOS WITH GRANT OPTION; 
RAMOS pode apagar tuplos da tabela ACTOR e autorizar outros a fazer o mesmo. 
 
36 
 
Vistas e Segurança - Continuação 
 
 
 GRANT UPDATE (SALARIO) ON EXECUTIVO TO AVENTURA; 
AVENTURA pode modificar o campo SALARIO da tabela EXECUTIVO. 
 
 GRANT SELECT ON ACTOR TO CRAMOS, AVENTURA; 
CRAMOS e AVENTURA podem consultar tuplos da tabela ACTOR. 
 
 REVOKE SELECT ON ACTOR FROM CRAMOS, AVENTURA; 
Retira privilégios de consulta a CRAMOS e AVENTURA sobre a tabela ACTOR. 
 
 
37 
SQL – Linguagem de Manipulação de Dados (LMD) 
 
 
Tendo como base as relações: 
 actor(codigo, nome, nacionalidade) 
 filme(codigo, titulo, ano, realizador) 
 realizador(cod, nome, nacionalidade) 
 actor_filme(filme, actor) 
 
 Seleccionar Tuplos de uma ou mais relações: 
 
SELECT <ATRIBUTOS> 
FROM <RELAÇÕES> 
WHERE <CONDIÇÃO> 
ORDER BY <ATRIBUTOS> 
Operadores de comparação: 
 =; <>; >; >=; <=; < 
 
Operadores aritméticos: 
 +; -; *; / 
 
Operadores lógicos: 
 AND; OR; NOT 
 
38 
SQL – Linguagem de Manipulação de Dados (LMD) 
 
EXEMPLOS: 
 
Seleccionar o nome e a nacionalidade de todos os actores, ordenados pelo nome dos actores 
 
 SELECT NOME, NACIONALIDADE 
 FROM ACTOR 
 ORDER BY NOME; 
 
Seleccionar todos os atributos dos actores residentes nos EUA 
 
 SELECT * 
 FROM ACTOR 
 WHERE NACIONALIDADE LIKE „EUA‟; 
 
 
39 
SQL – Linguagem de Manipulação de Dados (LMD) 
 
Seleccionar o nome do realizador do filme com o código 1. (Temos que fazer a junção de tabelas) 
 
 SELECT REALIZADOR.NOME 
 FROM FILME, REALIZADOR 
 WHERE (FILME.REALIZADOR=REALIZADOR.CODIGO AND 
 FILME.CODIGO=1); 
 
 Inserir tuplos numa relação 
Exemplos: 
INSERT INTO ACTOR(CODIGO,NOME,NACIONALIDADE) VALUES (4,‟NICOLE‟,‟EUA‟); 
INSERT INTO EXECUTIVO SELECT * FROM EMPREGADO; -> Esta instrução é legítima em casos em que o nº de campos e tipos 
de dados são compatíveis entre a origem e o destino. 
 Eliminar tuplos de uma relação 
Exemplos: 
 Eliminar o actor cujo codigo é igual a 1. 
 DELETE FROM ACTOR WHERE CODIGO=1; 
 
40 
SQL – Linguagem de Manipulação de Dados (LMD) 
 
 Alterar tuplos de uma relação 
 
Pretende-se que os actores cuja nacionalidade é „EUA‟ passe a ser „AMERICANA‟. 
 
UPDATE ACTOR SET NACIONALIDADE=’AMERICANA’ 
 WHERE NACIONALIDADE LIKE ‘EUA’; 
 
 Operações com conjuntos 
 
INTERSEPT - Intersecção (No SQL-Server é com EXISTS) 
EXCEPT - Diferença (No SQL-Server é com NOT EXISTS) 
UNION - União (Reunião) 
 
PROFESSOR EXECUTIVO 
 
Codigo Nome Codigo Nome 
1 Carlos 1 Carlos 
2 Rui Marteleiro 3 Ana Rita 
3 Ana Rita 4 Ricardo Baba 
 
41 
SQL – Linguagem de Manipulação de Dados (LMD) 
 
Exemplos 
 
(SELECT * FROM PROFESSOR) 
UNION 
(SELECT * FROM EXECUTIVO); 
 
Codigo Nome 
1 Carlos 
2 Rui Marteleiro 
3 Ana Rita 
4 Ricardo Baba 
 
(SELECT * FROM PROFESSOR) 
EXCEPT 
(SELECT * FROM EXECUTIVO); 
 
Codigo Nome 
2 Rui Marteleiro 
 
(SELECT * FROM PROFESSOR) 
INTERSEPT 
(SELECT * FROM EXECUTIVO); 
 
SQL-SERVER: 
(SELECT * FROM PROFESSOR WHERE NOT EXISTS 
(SELECT * FROM EXECUTIVO)); 
 
SQL-SERVER: 
(SELECT * FROM PROFESSOR WHERE EXISTS 
(SELECT * FROM EXECUTIVO)); 
 
 
42 
Codigo Nome 
1 Carlos 
3 Ana Rita 
 
 
43 
SQL – Linguagem de Manipulação de Dados (LMD) 
 
 
 
 Operações de Agregação 
 
São operações efectuadas sobre conjuntos de tuplos. Estas pode ser: 
 
AVG(<atributo>) - Média dos valores do atributo especificado 
MAX(<atributo>) - Máximo dos valores do atributo especificado 
MIN(<atributo>) - Mínimo dos valores do atributo especificado 
SUM(<atributo>) - Somatório dos valores do atributo especificado 
COUNT(*) - Conta o número de tuplos daquele conjunto 
 
EXEMPLOS: 
 
Número de actores que tenho na minha base de dados. 
 
 SELECT COUNT(*) 
 FROM ACTOR; 
 
Número de actores de cada país. 
 
 SELECT COUNT(*) 
 FROM ACTOR 
 GROUP BY NACIONALIDADE; 
 
 
44 
SQL – Linguagem de Manipulação de Dados (LMD) 
 
 
Número de actores dos EUA 
 
 SELECT COUNT(*) 
 FROM ACTOR 
 WHERE NACIONALIDADE LIKE „EUA‟ 
 GROUP BY NACIONALIDADE; 
 
Titulo do filme mais antigo realizado por um realizador português. 
 
 SELECT MIN(ANO), TITULO 
 FROM FILME, REALIZADOR 
 WHERE (FILME.REALIZADOR=REALIZADOR.COD AND REALIZADOR.NACIONALIDADE LIKE „PORTUGUESA‟) 
 GROUP BY FILME.REALIZADOR; 
 
 Escolha dos Grupos, com recurso à cláusula HAVING 
 
A escolha dos grupos pode ser baseada numa propriedade de agregação do grupo 
HAVING <condição> 
 
Exemplo 
 Actor(nome, morada, sexo, nascimento) 
 Filme(titulo, ano, duracao, a_cores, estudio, produtor) 
 Participa(filme, ano, actor, salario) 
 Estudio(nome, morada, presidente) 
 Executivo(#certificado, nome, morada, salario) 
 
45 
SQL – Linguagem de Manipulação de Dados (LMD) 
 
 
 
 Qual a soma das durações dos filmes produzidos por um produtor que produziu pelo menos um filme antes de 1930? 
 
 
 SELECT NOME, SUM(DURACAO) 
 FROM EXECUTIVO, FILME 
 WHERE PRODUTOR=#CERTIFICADO 
 HAVING MIN(ANO)<1930; 
 
 
Encontrar o salário do actor com o salário mais baixo, superior a 100000, para cada ano com pelo menos 2 actores 
nessas condições. 
 
 
SELECT ANO, MIN(SALARIO) 
FROM PARTICIPA 
WHERE SALARIO>100000 
GROUP BY ANO 
HAVING COUNT(*)>1; 
 
46 
SQL Server– Procedures 
 
 Um procedimento é um conjunto de operações que são armazenadas na base de dados identificados por um nome, e 
que podem ser executados sempre que for necessário. Para além disso, um procedimento pode retornar um 
determinado valor. 
 As operações são especificadas através de instruções em SQL. 
 
Criação de uma PROCEDURE: 
 
 CREATE PROCEDURE <NOME> @<ARGUMENTO1>,@<ARGUMENTO2>,....,@<ARGUMENTON> 
 AS 
 .......... 
 [RETURN <VALOR>] 
 GO 
 
Eliminação de uma PROCEDURE: 
 
 DROP PROCEDURE <NOME> 
 
Execução de uma PROCEDURE: 
 
 DECLARE @REF INTEGER 
 EXEC @REF=<NOME> <ARGUMENTO1>,<ARGUMENTO2>,....,<ARGUMENTON> 
 GO 
 
 
 
47 
SQL Server– Procedures 
 
Mais algumas instruções....... 
 
 
 
SET @i=10; 
 
Permite atribuir um determinado valor a uma variável. 
 
RETURN 2; 
 
Retorna o valor 2 da procedure. 
 
PRINT ‘Mensagem’ 
 
Visualiza uma mensagem no ecrã. 
 
48 
SQL Server– Anexos 
 
 Nesta secção pretende-se apresentar alguns exemplos sobre os conteúdos apresentados anteriormente. 
 Os exemplos apresentados basearam-se no esquema relacional apresentado na página 39 da presente documentação. 
 
 Trigger’s: 
 
 Criar um trigger que impeça a possibilidade de um actor participar em mais do que 4 filmes e auferir um salário médio superior a 
500000 
 
drop trigger exemplo1 
go 
 
create trigger exemplo1 on participa for insert, update 
as 
 declare@actor int 
 select @actor=count(*) from participa order by actor having avg(salario)>500000 
 if @actor>4 
 begin 
 rollback 
 end 
 else 
 begin 
 print „ Inserção Sucedida!‟ 
 end 
go 
 
 
49 
SQL Server– Anexos(Continuação) 
 
Cursores: 
 
 Um cursor é uma relação virtual, decorrente de uma instrução em SQL. 
 
 EXEMPLO: 
 
/* Cria o cursor*/ 
declare actores cursor for select * from actor 
 
/* Abre o cursor*/ 
open actores 
/* Percorre o cursor*/ 
while @@fetch_status=0 
begin 
 /* Avança para o próximo tuplo*/ 
 fetch next from actores 
end 
/* Fecha o cursor*/ 
close actores 
/*Elimina o cursor da memória */ 
deallocate actores 
 
 declare @CustId nchar(5) 
 declare @CustName varchar(50) 
 declare @RowNum int 
 declare CustList cursor for 
 select top 5 CustomerID,CustomerName from Northwind.dbo.Customers 
 OPEN CustList 
 FETCH NEXT FROM CustList 
 INTO @CustId,@CustName 
 set @RowNum = 0 
 WHILE @@FETCH_STATUS = 0 
 BEGIN 
 set @RowNum = @RowNum + 1 
 print cast(@RowNum as char(1)) + ' ' + @CustId + „ „ + @CustName 
 FETCH NEXT FROM CustList 
 INTO @CustId,@CustName 
 END 
 CLOSE CustList 
 DEALLOCATE CustList 
 
 
50 
SQL Server– Anexos(Continuação) 
 
Procedimentos: 
 
Criar um procedimento que permita determinar a média do salário de um determinado empregado cujo código é 
passado como argumento. 
 
create procedure media_sal @num integer as 
 declare @media real 
set @media=select avg(salario) from participa where actor=@num group by actor 
 return media 
go 
 
NOTA: 
 
Podem-se definir argumentos para como OUTPUT, assim, podem receber valores que poderão ser lidos fora do 
procedimento. Exemplo: 
 
create procedure media_sal @num integer, @resultado output as 
 declare @media real 
set @media=select avg(salario) from participa where actor=@num group by actor 
 return media 
go 
 
Invocação: 
 
 
51 
declare @sal real 
set @sal=0 
execute up_PComp c, @sal output 
print @sal 
GO