Unidade 2 SQL

Prévia do material em texto

Unidade 2: SQL 
SQL 
 Manipulação e controle de bancos de 
dados relacionais 
 Acesso ao banco de dados: 
– Ambiente interativo de consultas 
– Embutida em linguagens hospedeiras 
 Recursos: 
– Alto poder de consulta 
– Gerenciamento de índices 
– Construção de visões 
– Execução de instruções em blocos 
2 
Grupos de comandos SQL 
 Linguagem de Definição de Dados (DDL): 
comandos para definir, alterar e remover 
tabelas e índices; 
 Linguagem de Manipulação de Dados 
(DML): comandos para inserir, remover, 
atualizar e consultar os dados armazenados 
nas tabelas; 
 Linguagem de Controle de Dados (DCL): 
comandos para se trabalhar em ambiente 
multi-usuário, permitindo estabelecer níveis 
de segurança e manipular transações. 
 3 
Tabelas Exemplo 
CodC 
 
NomeC 
 
DuracaoC 
 
MensC 
 
C1 
 
Análise Sist 
 
4 
 
400 
 
C2 
 
Eng Mecatrônica 
 
5 
 
600 
 
C3 
 
Ciência Comp 
 
4 
 
450 
 
C4 
 
Eng Elétrica 
 
4 
 
600 
 
C5 
 
Turismo 
 
3 
 
350 
 
Curso 
4 
Tabelas Exemplo 
Disciplina 
CodD 
 
NomeD 
 
CargaD 
 
AreaD 
 
PreReqD 
 
D1 
 
TLP1 
 
2 
 
Computação 
 
D2 
 
D2 
 
Cálculo1 
 
4 
 
Matemática 
 
null 
 
D3 
 
Inglês 
 
2 
 
Humanas 
 
null 
 
D4 
 
Ed Física 
 
3 
 
Saúde 
 
null 
 
D5 
 
G Analítica 
 
5 
 
Matemática 
 
D2 
 
D6 
 
Projeto Final 
 
6 
 
null 
 
D1 
 
5 
Tabelas Exemplo 
CodP 
 
NomeP 
 
CidadeP 
 
TituloP 
 
P1 
 
Joaquim 
 
Rib Preto 
 
Mestre 
 
P2 
 
Paulo 
 
Batatais 
 
Espec 
 
P3 
 
André 
 
Rib Preto 
 
Doutor 
 
P4 
 
Gil 
 
S Carlos 
 
Doutor 
 
P5 
 
Juliana 
 
S Carlos 
 
Pós Doc 
 
Professor 
6 
Tabelas Exemplo 
CodC 
 
CodD 
 
CodP 
 
Sala 
 
C1 
 
D6 
 
P1 
 
305 
 
C2 
 
D2 
 
P2 
 
305 
 
C3 
 
D2 
 
P2 
 
305 
 
C4 
 
D1 
 
P5 
 
201 
 
C4 
 
D3 
 
P3 
 
204 
 
C5 
 
D4 
 
P3 
 
204 
 
C5 
 
D4 
 
P4 
 
207 
 
Grade 
7 
Linguagem de Definição de Dados (DDL)‏ 
 Tipos de dados mais usuais: 
– - CHAR(n) ou CHARACTER(n): cadeia de caracteres de tamanho fixo, 
com o tamanho n definido pelo usuário; 
– - VARCHAR(n): cadeia de caracteres de tamanho variável, com o 
tamanho máximo n definido pelo usuário; 
– - INT ou INTEGER: número inteiro, geralmente de -32768 a 32767; 
– - SMALLINT: número inteiro pequeno, geralmente de 0 a 65535; 
– - NUMERIC(t,d): número de ponto decimal fixo, que consiste de t dígitos 
totais e d dígitos à direita do ponto decimal, com t e d definidos pelo 
usuário; 
– - REAL: número de ponto flutuante, geralmente de 1.17E-38 a 3.4E+38; 
– - DOUBLE PRECISION: número de ponto flutuante de dupla precisão, 
geralmente de 2.2E-308 a 1.7E+308; 
– - FLOAT(n): número de ponto flutuante com a precisão n definida pelo 
usuário; 
– - DATE: formato de data, contendo dia, mês e ano; 
– - TIME: formato de horário, contendo horas, minutos e segundos. 
 
8 
Criação de uma tabela 
Sintaxe: 
 
CREATE TABLE nome_tabela 
 (nome_coluna tipo [NOT NULL] [SET DEFAULT valor], 
 .... , 
 PRIMARY KEY (nome_colunas), 
 [UNIQUE (nome_coluna)], 
 [FOREIGN KEY (nome_coluna)‏ 
 REFERENCES nome_tabela (nome_coluna)‏ 
 ON DELETE CASCADE/SET NULL/SET DEFAULT 
 ON UPDATE CASCADE/SET NULL/SET DEFAULT ] 
 [CHECK condição])‏ 
9 
Criação de uma tabela 
 Exemplo 1 : Criação da tabela Disciplina 
CREATE TABLE Disciplina 
( CodD CHAR(5), 
 NomeD VARCHAR(20) NOT NULL, 
 CargaD INTEGER NOT NULL, 
 AreaD VARCHAR(20), 
 PreReqD CHAR(5), 
 UNIQUE (NomeD), 
 PRIMARY KEY (CodD) 
 CHECK CargaD > 0 AND CargaD <= 6)‏ 
10 
Criação de uma tabela 
– Exemplo 2: Criação da tabela Grade 
 CREATE TABLE Grade 
 ( CodC CHAR(5), 
 CodD CHAR(5), 
 CodP CHAR(5), 
 Sala INTEGER, 
 PRIMARY KEY (CodC, CodD, CodP), 
 FOREIGN KEY (CodC) REFERENCES Curso (CodC)‏ 
 ON DELETE CASCADE, 
 FOREIGN KEY (CodD) REFERENCES Disciplina (CodD)‏ 
 ON DELETE CASCADE, 
 FOREIGN KEY (CodP) REFERENCES Professor (CodP)‏ 
 ON DELETE SET NULL)‏ 
OOOPS !!! 
11 
Alteração de uma tabela 
Sintaxe: 
 
 ALTER TABLE nome_tabela 
 [ADD COLUMN nome_coluna tipo [NOT NULL] ] 
 [DROP COLUMN nome_coluna 
[CASCADE/RESTRICT] ] 
 
Exemplo : Adição da coluna MensC à tabela 
Curso 
 ALTER TABLE Curso 
 ADD COLUMN MensC NUMERIC(6,2); 
12 
Remoção de uma tabela 
Sintaxe: 
 
 DROP TABLE nome_tabela 
CASCADE/RESTRICT ; 
 
13 
Truncando uma tabela 
Sintaxe: 
 
 TRUNCATE TABLE nome_tabela; 
 
14 
Criação e remoção de Índices 
Sintaxe para criação: 
 CREATE INDEX nome_índice 
 ON nome_tabela ( nome_coluna [ASC|DESC],...); 
 
Exemplo : Criação de índice sobre a coluna 
CidadeP da tabela Professor 
 CREATE INDEX Xcidadeprof 
 ON Professor (CidadeP ASC); 
 
Sintaxe para remoção: 
 DROP INDEX nome_índice 
 
 15 
Linguagem de Manipulação de Dados (DML) 
 Inclusão de tuplas 
 Alteração de tuplas 
 Remoção de tuplas 
 Consultas ao banco 
16 
Inclusão de tuplas em uma tabela 
Sintaxe: 
 INSERT 
 INTO nome_tabela ( nome_coluna, .....)‏ 
 VALUES (valor, ...)‏ 
 
Exemplo: Inserir a linha 1 da tabela Professor 
 INSERT 
 INTO Professor (CodP, NomeP, CidadeP, TituloP)‏ 
 VALUES‏(‘P1', 'Joaquim', 'Rib Preto', 'Mestre') 
 
17 
Atualização dos dados de uma tabela 
Sintaxe: 
 UPDATE nome_tabela 
 SET nome_coluna = valor, ..... 
 WHERE (condição de localização)‏ 
 
Exemplo : Alterar o valor da mensalidade do 
curso de Ciência da Computação para 650,00 
 UPDATE Curso 
 SET MensC = 650 
 WHERE‏‏(NomeC‏=‏‘Ciência‏Comp’)‏ 
 
18 
Remoção de dados de uma tabela 
Sintaxe: 
 DELETE FROM nome_tabela 
 WHERE (condição de localização)‏ 
 
Exemplo 1 : Remover da tabela Professor todos 
os professores que têm título de Doutor 
 DELETE FROM Professor 
 WHERE (TituloP‏=‏‘Doutor’)‏ 
 
19 
Exercícios 
 Considere o esquema da Empresa: 
Func (cod_func, nome, dta_nasc, salário, 
 nro_depto, cod_superv)‏ 
Depto (nro_depto, nome, cod_ger)‏ 
Projeto (cod_proj, nome, duração, 
nro_depto)‏ 
Participa (cod_proj, cod_func, horas_trab) ‏ 
20 
Exercícios 
1. Criar as tabelas Func1, Projeto1 e Participa1 
com o mesmo layout das tabelas Func, Projeto 
e Participa. 
2. Adicionar a coluna dta_inicio à tabela Projeto1. 
3. Criar índice para o atributo dta_nasc da tabela 
Func1. 
4. Inserir valores na tabela Projeto1 e Func1. 
5. Zerar a duração de todos os projetos do depto 
5. 
6. Remover todas as participações em projetos do 
funcionário 20. 
7. Remover as tabelas Projeto1, Func1 e Partipa1. 
21 
Conceito de Transação 
 Uma TRANSAÇÃO é uma unidade de execução 
programada que acessa e provavelmente atualiza vários 
itens de dados. 
 Uma transação deve acessar uma base de dados 
consistente. 
 Durante a execução da transação é possível que a base de 
dados passe por um estado de inconsistência 
 Quando a transação é confirmada (COMMIT), a base de 
dados deverá estar consistente. 
 O gerenciamento de transações lida com 2 situações 
principais: 
– Falhas de vários tipos: hardware, energia, travamento 
de aplicações, etc. 
– Execução concorrente de múltiplas transações 22 
Propriedades ACID 
 Atomicidade. Ou todas ou nenhuma das operações da 
transação serão devidamente refletidas na base de dados. 
 Consistência. Após a execução de uma transação, a 
base de dados deverá retornar a um estado consistente 
dos dados. 
 Isolamento. Apesar de múltiplas transações poderem ser 
executadas concorrentemente, cada uma delas deverá 
estar isolada dos efeitos da execução das outras. 
Resultados intermediários de uma transação deverão ficar 
ocultos para as outras. 
 Durabilidade. Depois que uma transação termina com 
sucesso, as alterações que ela realizou sobre o bd 
persistirão mesmo que haja falhas do sistema. 
Para preservar a integridade dos dados, o SGBD deve assegurar:23 
Controle de Transações 
 Todas as transações realizadas com o banco 
deverão ser confirmadas pelo usuário para que 
sejam realmente efetivadas, ou então 
descartadas, caso o usuário deseje. 
 Para confirmar uma transação - ou um bloco de 
transações - utiliza-se o comando COMMIT. 
 Para descartar uma transação - ou um bloco de 
transações - utiliza-se o comando ROLLBACK. 
 O bloco de transações é definido a partir da última 
ocorrência de COMMIT, ou a partir do início da 
seção. 
24 
Comando SELECT 
Sintaxe: 
 
 SELECT [DISTINCT] nome_coluna,.... 
 FROM nome_tabela, .... 
 [WHERE (condições) [ sub-select] ] 
 [GROUP BY nome_coluna, ....] 
 [HAVING (condições)] 
 [{INTERSECT | MINUS | UNION} comando_select] 
 [ORDER BY nome_coluna {ASC | DESC}, ....] 
 
25 
SELECT - Operadores 
Aritméticos 
 
+ 
 
- 
 
* 
 
/ 
 
Lógicos 
 
AND 
 
OR 
 
NOT 
 
Relacionais 
 
= 
 
<> 
 
< 
 
<= 
 
> 
 
>= 
 
LIKE 
 
BETWEEN 
 
Conjuntu-
rais 
 
IN 
 
IS NULL 
 
ANY 
 
ALL 
 
EXISTS 
 
Conjuntos 
 
UNION 
 
INTERSECT 
 
MINUS 
 
26 
SELECT – Algumas Funções 
Grupo 
 
Função 
 
Descrição 
 
Agregação 
 
AVG (col)‏ 
 
média dos valores da coluna 
 SUM (col)‏ 
 
soma de valores da coluna 
 MAX (col)‏ 
 
valor máximo da coluna 
 MIN (col)‏ 
 
valor mínimo da coluna 
 COUNT 
 
total de tuplas 
 
Caracter 
 
UPPER (col)‏ 
 
converte caracteres minúsculos em maiúsculos 
 LOWER (col)‏ 
 
converte caracteres maiúsculos em minúsculos 
 SUBSTR (col, pos, n)‏ 
 
substring da coluna, iniciando em pos, com n caracteres 
 
Números 
 
ROUND (col/const, n)‏ 
 
arredondamento em n da coluna (ou da constante 
 TRUNC (col/const, n)‏ 
 
truncamento em n da coluna (ou da constante)‏ 
 ABS (col/const)‏ 
 
valor absoluto da coluna ou da constante 
 
Data/Hora 
 
MONTH (data)‏ 
 
mês da data 
 YEAR (data)‏ 
 
ano da data 
 MINUTE (hora)‏ 
 
minuto da hora 
 
Conversões 
 
TO_CHAR (num/data)‏ 
 
número (ou data) para caracter 
 
TO_NUMBER (char)‏ 
 
caracter para número 
 TO_DATE (char)‏ caracter para data 
SELECT – Consulta Simples 
 Nomes das disciplinas: 
 SELECT NomeD 
 FROM Disciplina NomeD 
TLP1 
Cálculo1 
Inglês 
Ed Física 
G Analítica 
Projeto Final 
 
28 
SELECT – Consultas Simples 
 Salas onde as aulas serão ministradas: 
 
 SELECT Sala 
 FROM Grade 
Sala 
305 
305 
305 
201 
204 
204 
207 
 
29 
SELECT – Consultas Simples 
 Salas onde as aulas serão ministradas, 
sem repetição: 
 
 SELECT DISTINCT Sala 
 FROM Grade 
Sala 
305 
201 
204 
207 
 
30 
SELECT – Consultas Simples 
 Nome e código dos professores de 
Ribeirão Preto: 
 SELECT NomeP, CodP 
 FROM Professor 
 WHERE CidadeP = 'Rib Preto' 
NomeP CodP 
Joaquim P1 
André P3 
 
31 
SELECT – Consultas Simples 
 Todas as colunas da grade do curso C4: 
 SELECT * 
 FROM Grade 
 WHERE CodC = 'C4' 
CodC CodD CodP Sala 
C4 D1 P5 201 
C4 D3 P3 204 
 
32 
SELECT – Uso de Operadores 
 Nome e duração em meses de cada curso: 
 SELECT NomeC, (DuracaoC * 12)‏ 
 FROM Curso 
NomeC DuracaoC * 12 
Análise Sist 48 
Eng Mecatrônica 60 
Ciência Comp 60 
Eng Elétrica 60 
Turismo 36 
 
33 
SELECT – Uso de Operadores 
 Nomes dos cursos cuja mensalidade é 
inferior a 500 reais: 
 SELECT NomeC 
 FROM Curso 
 WHERE MensC < 500 
NomeC 
Análise Sist 
Ciência 
Comp 
Turismo 
 
34 
SELECT – Uso de Operadores 
 Código e carga horária das disciplinas da 
área de Matemática, com carga horária 
maior ou igual a 5: 
 SELECT CodD, CargaD 
 FROM Disciplina 
 WHERE AreaD = 'Matemática' AND 
 CargaD >= 5 
CodD CargaD 
D5 5 
 
35 
SELECT – Uso de Operadores 
 O código e o nome de todos os cursos de 
engenharia: 
 SELECT CodC, NomeC 
 FROM Curso 
 WHERE NomeC LIKE 'Eng%' 
CodC NomeC 
C2 Eng Mecatrônica 
C4 Eng Elétrica 
 
36 
SELECT – Uso de Operadores 
 As salas do segundo andar (nro fica na casa 
dos 200) onde serão ministradas aulas: 
 SELECT DISTINCT Sala 
 FROM Grade 
 WHERE TO_CHAR(Sala) LIKE '2__' Sala 
201 
204 
207 
 
37 
SELECT – Uso de Operadores 
 Código dos cursos cuja mensalidade está 
entre 400 e 550 reais: 
 SELECT CodC 
 FROM Curso 
 WHERE MensC BETWEEN 400 AND 550 
CodC 
C1 
C3 
38 
SELECT – Uso de Operadores 
 Nome das disciplinas que são da área de 
Computação ou de Humanas ou de Saúde: 
 SELECT NomeD 
 FROM Disciplina 
 WHERE AreaD IN ('Computação', 
 'Humanas', 'Saúde') 
NomeD 
TLP1 
Inglês 
Ed Física 
 
39 
SELECT – Uso de Operadores 
 Nome das disciplinas que não pertencem a 
nenhuma área específica: 
 SELECT NomeD 
 FROM Disciplina 
 WHERE AreaD IS NULL 
NomeD 
Projeto Final 
 
40 
SELECT – Uso de Alias 
 Nome e duração em meses de cada curso: 
 SELECT NomeC AS Curso, 
 (DuracaoC * 12) AS Duração_Meses 
 FROM Curso 
Curso Duracao_Meses 
Análise Sist 48 
Eng Mecatrônica 60 
Ciência Comp 60 
Eng Elétrica 60 
Turismo 36 
 41 
SELECT – Concatenação de campos 
 Códigos dos cursos com seu nome e 
mensalidade concatenados: 
 SELECT CodC, 
 NomeC || MensC || ' reais' AS Info_Curso 
 FROM Curso 
CodC Info_Curso 
C1 Análise Sist 400 reais 
C2 Eng Mecatrônica 600 reais 
C3 Ciência Comp 450 reais 
C4 Eng Elétrica 600 reais 
C5 Turismo 350 reais 
42 
SELECT – Ordenação do resultado 
 Nomes dos cursos ordenados de forma 
ascendente: 
 SELECT NomeC 
 FROM Curso 
 ORDER BY NomeC ASC 
NomeC 
Análise Sist 
Ciência Comp 
Eng Elétrica 
Eng 
Mecatrônica 
Turismo 
 43 
Exercícios 
 Considere o esquema da Empresa: 
Func (cod_func, nome, dta_nasc, salário, 
 nro_depto, cod_superv)‏ 
Depto (nro_depto, nome, cod_ger)‏ 
Projeto (cod_proj, nome, duração, nro_depto)‏ 
Participa (cod_proj, cod_func, horas_trab) 
44 
Exercícios 
1. Nome e salário dos funcionários do departamento 5. 
2. Nome e salário dos funcionários do departamento 5, 
com salário na faixa entre 1000 e 2000. 
3. Todas as informações sobre projetos do departamento 
5. 
4. Salário líquido (85% do salário bruto) dos funcionários 
supervisionados pelo funcionário 23. 
5. Código dos funcionários que são supervisores. 
6. Código dos funcionários que não têm supervisores. 
7. Nome completo, em ordem alfabética, dos funcionários 
que têm “MAR” no nome. 
8. Nome, duração e depto dos projetos “FATURAMENTO”, 
“ÍNTRANET” e “NOVOS PRODUTOS”. 
 
 
 
45 
SELECT – Funções 
 Valor mínimo, máximo e médio das mensalidades 
dos cursos cuja duração é de 4 anos: 
 SELECT MIN(MensC), MAX(MensC), AVG(MensC)‏ 
 FROM Curso 
 WHERE DuracaoC = 4 
 
MIN(MensC) MAX(MensC) AVG(MensC)‏ 
400 600 483 
 
46 
SELECT - Agrupamentos 
 Carga horária total de cada área: 
 SELECT AreaD, SUM(CargaD)‏ 
 FROM Disciplina 
 GROUP BY AreaD 
AreaD SUM(CargaD)‏ 
Computação 2 
Matemática 9 
Humanas 2 
Saúde 3 
Null 6 
 47 
SELECT - Agrupamentos 
 Salas que são utilizadas para mais de uma 
aula e as respectivas quantidades de aulas: 
 SELECT Sala, COUNT(*)‏ 
 FROM Grade 
 GROUP BY Sala 
 HAVING COUNT(*) > 1 Sala COUNT(*)‏ 
305 3 
204 2 
48 
Exercícios 
 Considere o esquema da Empresa: 
Func (cod_func, nome, dta_nasc, salário, 
 nro_depto, cod_superv)‏ 
Depto (nro_depto, nome, cod_ger)‏ 
Projeto (cod_proj, nome, duração, nro_depto)‏ 
Participa (cod_proj, cod_func, horas_trab) 
49 
Exercícios 
1. O maior e o menor salário da 
empresa. 
2. O maior e o menor salário de cada 
depto. 
3. O maior e o menor salário dos deptos 
cuja média salarial é maior ou igual a 
2000. 
4. Total de horas trabalhadas de cada 
projeto. 
 
 
50 
SELECT - Junção 
 Todas asinformações sobre a grade e as 
disciplinas do curso C4: 
SELECT * 
FROM Grade, Disciplina 
WHERE CodC = 'C4' AND 
Disciplina.CodD = Grade.CodD 
CodC CodD CodP Sala CodD NomeD CargaD AreaD 
 C4 D1 P5 201 D1 TLP1 2 Computação 
 C4 D3 P3 204 D3 Inglês 2 Humanas 
 
51 
SELECT – Junção Natural 
 Todas as informações sobre a grade e as 
disciplinas do curso C4, sem repetição de 
colunas: 
 SELECT * 
 FROM Grade NATURAL JOIN Disciplina 
 ON Grade.CodD = Disciplina.CodD 
 WHERE CodC = 'C4' 
CodC CodD CodP Sala NomeD CargaD AreaD 
 C4 D1 P5 201 TLP1 2 Computação 
 C4 D3 P3 204 Inglês 2 Humanas 
 
52 
SELECT - Junção 
 Código, nome, carga horária, sala e curso das 
disciplinas que estão na grade: 
 SELECT Grade.CodD, NomeD, CargaD, Sala, CodC 
 FROM Disciplina, Grade 
 WHERE Disciplina.CodD = Grade.CodD 
CodD NomeD CargaD Sala CodC 
D1 TLP1 2 201 C4 
D2 Cálculo1 4 305 C2 
D2 Cálculo1 4 305 C3 
D3 Inglês 2 204 C4 
D4 Ed Física 3 204 C5 
D4 Ed Física 3 207 C5 
D6 Projeto Final 6 305 C1 
53 
SELECT - Junção 
 Nome das disciplinas e dos respectivos 
professores do curso C4: 
 SELECT NomeD, NomeP 
 FROM Disciplina, Grade, Professor 
 WHERE CodC = 'C4' AND 
 Disciplina.CodD = Grade.CodD AND 
 Grade.CodP = Professor.CodP 
NomeD NomeP 
TLP1 Juliana 
Inglês André 
54 
SELECT – Auto-Junção 
 Nomes das disciplinas cujo pré-requisito‏é‏“Cálculo1”: 
 SELECT Disc1.NomeD 
 FROM Disciplina AS Disc1, Disciplina AS Disc2 
 WHERE Disc2.CodD = Disc1.PreReqD AND 
 Disc2.NomeD = “Cálculo1” 
Disc1.NomeD 
LP1 
G Analítica 
55 
SELECT – Auto-Junção 
 Pares de professores que são da mesma cidade: 
 SELECT X.NomeP, Y.NomeP 
 FROM Professor AS X, Professor AS Y 
 WHERE X.CidadeP = Y.CidadeP AND 
 X.CodP <> Y.CodP 
X.NomeP Y.NomeP 
Joaquim André 
André Joaquim 
Gil Juliana 
Juliana Gil 
56 
Exercícios 
 Considere o esquema da Empresa: 
Func (cod_func, nome, dta_nasc, salário, 
 nro_depto, cod_superv)‏ 
Depto (nro_depto, nome, cod_ger)‏ 
Projeto (cod_proj, nome, duração, nro_depto)‏ 
Participa (cod_proj, cod_func, horas_trab) 
57 
Exercícios 
1. Nomes dos deptos e nomes dos respectivos gerentes. 
2. Nomes dos funcionários que participaram de projetos 
e respectivos códigos dos projetos e horas 
trabalhadas, somente das participações que 
excederam 50 horas. 
3. Nomes dos funcionários que participaram de projetos 
e respectivos totais de horas trabalhadas. 
4. Nomes de todos os funcionários e respectivos totais 
de horas trabalhadas. 
5. Nomes dos funcionários que participaram de projetos 
e nomes dos respectivos projetos. 
6. Nomes dos funcionários e nomes dos respectivos 
supervisores. 
7. Pares de deptos que possuem o mesmo gerente. 
58 
SELECT - Subconsulta 
 Nome e área das disciplinas do curso C4: 
SELECT NomeD, AreaD 
FROM Disciplina 
WHERE CodD IN 
 (SELECT CodD 
 FROM Grade 
 WHERE CodC = 'C4') 
CodD 
D1 
D3 
 
NomeD AreaD 
TLP1 Computação 
Inglês Humanas 
 
59 
SELECT - Subconsulta 
 Nome das disciplinas que são ministradas 
simultaneamente pelos professores P3 e P4: 
 SELECT NomeD 
 FROM Disciplina 
 WHERE CodD IN 
 (SELECT CodD 
 FROM Grade 
 WHERE CodP = 'P3')‏ 
 AND CodD IN 
 (SELECT CodD 
 FROM Grade 
 WHERE CodP = 'P4') 
NomeD 
Ed Física 
 
CodD 
D3 
D4 
 
CodD 
D4 
 
60 
SELECT - Exists 
 Nomes dos professores que ministram 
alguma disciplina: 
SELECT NomeP 
FROM Professor 
WHERE EXISTS 
 (SELECT * 
 FROM Grade 
 WHERE Grade.CodP = Professor.CodP) 
NomeP 
Joaquim 
Paulo 
André 
Gil 
Juliana 
 
61 
SELECT - Exists 
 Nomes das disciplinas que não fazem parte 
da grade: 
SELECT NomeD 
FROM Disciplina 
WHERE NOT EXISTS 
 (SELECT * 
 FROM Grade 
 WHERE Grade.CodD = Disciplina.CodD) 
NomeD 
G Analítica 
 
62 
SELECT - ALL 
 Nomes dos cursos que possuam duração maior 
que todos os cursos de mensalidade inferior a 500 
reais: 
 SELECT NomeC 
 FROM Curso 
 WHERE DuracaoC > ALL 
 (SELECT DuracaoC 
 FROM Curso 
 WHERE MensC < 500) 
Nomec 
Eng Mecatronica 
 
 
DuracaoC 
4 
4 
3 
63 
SELECT - ANY 
 Nomes dos cursos que possuam duração maior 
que qualquer um dos cursos de mensalidade 
inferior a 500 reais: 
 SELECT NomeC 
 FROM Curso 
 WHERE DuracaoC > ANY 
 (SELECT DuracaoC 
 FROM Curso 
 WHERE MensC < 500)‏ 
 
Nomec 
Análise Sist 
Eng Mecatrônica 
Ciência Comp 
Eng Elétrica 
 
DuracaoC 
4 
4 
3 
 
64 
Exercícios 
 Considere o esquema da Empresa: 
Func (cod_func, nome, dta_nasc, salário, 
 nro_depto, cod_superv)‏ 
Depto (nro_depto, nome, cod_ger)‏ 
Projeto (cod_proj, nome, duração, nro_depto)‏ 
Participa (cod_proj, cod_func, horas_trab) 
65 
Exercícios 
1. Lista com nome e salário dos funcionários 
cujo salário é maior que a média salarial da 
empresa. 
2. Códigos dos projetos onde trabalharam 
juntos os funcionários 19 e 20. 
3. Nomes dos funcionários que não são 
gerentes. 
4. Códigos dos projetos cuja duração foi 
menor que a duração de todos os projetos 
do depto 5. 
5. Códigos dos funcionários que são 
supervisores e são gerentes de depto. 
66 
Linguagem de Controle de Dados (DCL)‏ 
 Comandos de administração de acesso: 
– Grant 
– Revoke 
 Privilégios para cada usuário, de acesso 
a um banco de dados, definidos para 
diferentes operações sobre o mesmo: 
– SELECT 
– INSERT 
– UPDATE 
– ALTER 
– etc 
67 
GRANT 
 Sintaxe: 
 GRANT privilégio(s)‏ 
 ON objeto 
 TO usuário(s)‏ 
 Exemplo: Liberar o acesso ao usuário Joao 
para realizar consultas à tabela Disciplina: 
 GRANT SELECT 
 ON Disciplina 
 TO Joao 
68 
GRANT 
 Permitir a todos os usuários realizar atualizações das 
colunas CidadeP e TituloP da tabela Professor: 
 GRANT UPDATE (CidadeP, TituloP)‏ 
 ON Professor 
 TO PUBLIC 
 Permitir todos os privilégios na tabela Grade ao usuário 
Joao, permitindo também que o mesmo possa liberar esses 
privilégios a outros usuários: 
 GRANT ALL 
 ON Grade 
 TO Joao 
 WITH GRANT OPTION 
 69 
REVOKE 
 Sintaxe: 
 REVOKE privilégio(s)‏ 
 ON tabela ou visão 
 FROM usuário(s)‏ 
 Exemplo: Retirar todos os privilégios na 
tabela Grade para os usuários Joao e Paulo: 
REVOKE ALL 
ON Grade 
FROM Joao, Paulo 
 
70 
VISÕES 
 Uma visão é um objeto do Banco de Dados criado a partir 
da execução de uma consulta sobre outras tabelas 
existentes. 
 Após a sua criação, as consultas à visão podem ser 
realizadas de forma idêntica às tabelas comuns do banco. 
 As visões podem ser muito úteis em várias situações: 
 - substituição de consultas longas e complexas por 
outras mais simples, obtendo-se o mesmo resultado; 
 - utilização de funções de agregação na definição de 
condições de pesquisa; 
 - delimitação prática do acesso dos usuários a um 
conjunto específico dos dados contidos nas tabelas do 
banco, tornando os outros inacessíveis e invisíveis; 
 
71 
CRIAÇÃO DE VISÕES 
 Sintaxe: 
 CREATE VIEW nome_visão [(nome_coluna, ...)] 
 AS SELECT ... 
– O comando SELECT de criação da visão não pode conter 
a cláusula ORDER BY. 
 Exemplo 1 : Visão para alunos consultarem os 
dados dos cursos, sem mostrar os valores das 
respectivas mensalidades 
 CREATE VIEW Vis_Curso_Aluno (Código, 
Curso, Duração)‏ 
 AS SELECT CodC, NomeC, DuracaoC FROM 
Curso 
 72 
CRIAÇÃO DE VISÕES 
 Exemplo 2 : Visão paraa secretária consultar 
a grade de forma amigável e e eficiente 
 CREATE VIEW Vis_Grade_Secr (Cod_Curso, 
Curso, Cod_Disciplina, Disciplina, 
Cod_Professor, Professor, Sala)‏ 
 AS SELECT CodC, NomeC, CodD, NomeD, 
 CodP, NomeP, Sala 
 FROM Grade, Curso, Disciplina, Professor 
 WHERE Grade.CodC = Curso.CodC AND 
 Grade.CodD = Disciplina.CodD AND 
 Grade.CodP = Professor.CodP 
 73 
ATUALIZAÇÕES ATRAVÉS DAS VISÕES 
 Os dados do banco podem ser atualizados através da 
visão, ou seja, uma vez executado um comando INSERT, 
UPDATE ou DELETE sobre a visão, os dados da tabela 
que originou a mesma também serão atualizados. 
 Porém essas operações somente poderão ser realizadas 
se a visão atender aos seguintes requisitos: 
 - não possuir junções, ou seja, ter sido criada sobre uma 
única tabela; 
 - não possuir colunas derivadas de funções de agregação; 
 - não possuir a cláusula DISTINCT na sua consulta; 
 - não possuir as cláusulas GROUP BY ou HAVING na sua 
consulta. 
74 
ATUALIZAÇÕES ATRAVÉS DAS VISÕES 
 Caso contrário, a visão será "read-only" e 
as atualizações deverão ser realizadas 
sobre as tabelas originais. 
 A visão do exemplo 1 poderá ser usada 
para atualizar a tabela Curso, desde que o 
usuário possua privilégios para isso. 
Porém, a visão do exemplo 2 é "read-only", 
pois é formada a partir de uma junção. 
 
75 
REMOÇÃO DE VISÕES 
 Sintaxe: 
 DROP VIEW nome_visão 
 Exemplo: Remover a visão 
Vis_Curso_Aluno 
DROP VIEW Vis_Curso_Aluno 
 
76 
Exercícios 
 Considere o esquema da Empresa: 
Func (cod_func, nome, dta_nasc, salário, 
 nro_depto, cod_superv)‏ 
Depto (nro_depto, nome, cod_ger)‏ 
Projeto (cod_proj, nome, duração, nro_depto)‏ 
Participa (cod_proj, cod_func, horas_trab) 
77 
Exercícios 
1. Crie uma visão chamada Func_Proj, contendo o nome 
do funcionário, nome do seu departamento e nome 
dos projetos que ele participou. 
2. Crie um usuário chamado Pedro. 
3. Crie privilégios para o usuário Pedro poder se 
conectar, acessar recursos e consultar dados da visão 
Func_Proj. 
4. Crie privilégios para o usuário Pedro poder se inserir 
dados na tabela Func, podendo repassar estes direitos. 
5. Crie privilégios para que todos possam consultar a 
coluna nome da tabela projeto. 
6. Elimine todos os privilégios do usuário Pedro. 
78 
EXERCÍCIOS PROPOSTOS 
1) Crie a tabela Aluno, contendo dados pessoais dos alunos e o 
curso onde estão inscritos. 
2) Crie a tabela Matricula, relacionando cada aluno com as 
disciplinas que o mesmo já cursou ou está cursando. 
3) Armazene, altere e remova dados das tabelas criadas. 
4) Altere a tabela Disciplina, acrescentando uma coluna para 
armazenar o código da disciplina que é pré-requisito da disciplina 
corrente (suponha que uma disciplina possui no máximo um pré-
requisito). 
5) Crie um índice para a coluna Pre_req da tabela Disciplina. 
6) Crie uma visão para a consulta dos alunos ao seu histórico. 
7) Crie privilégios para o usuário Sílvio poder consultar e inserir 
dados na tabela Matricula. 
8) Elimine todos os privilégios do usuário Sílvio. 
 
79 
EXERCÍCIOS PROPOSTOS 
9) Crie as seguintes consultas em SQL: 
a) Código e nome das disciplinas com carga horária entre 3 e 5 
inclusive e que não sejam da área de Saúde. 
b) Nome das disciplinas com carga horária menor que 5 e que 
sejam ministradas por professores doutores. 
c) Salas onde haverá aulas cujos cursos possuam duração 
superior a 3 anos e cujas disciplinas não sejam nem da área 
de Matemática, nem de Saúde e nem de Humanas e cujo 
professor seja o Paulo, o Joaquim ou a Juliana. 
d) Nome dos cursos e das disciplinas oferecidas em cada curso. 
e) Nomes dos professores que não ministram nenhuma 
disciplina. 
f) Nomes das disciplinas e o nome dos respectivos pré-
requisitos. Considere a tabela Disciplina resultante do 
exercício 4. 
80 
EXERCÍCIOS PROPOSTOS 
g) Nome das disciplinas que possuam carga horária maior que 
todas as disciplinas da área de Matemática. 
h) Código de cada disciplina, com a quantidade de cursos em 
que a mesma é oferecida. 
i) Carga horária média das áreas com carga horária média 
maior ou igual a 3. 
j) Nome das disciplinas que não têm pré-requisito e que não são 
pré-requisito. 
k) Nome dos cursos que têm a mesma duração do curso de 
Engenharia Elétrica. 
l) Nome dos cursos que têm a mesma duração do curso de 
Engenharia Elétrica e que têm mensalidade maior que a sua. 
m) Nome dos cursos que têm mensalidade maior que a média 
de todos os cursos. 
 81