· 6 years ago · May 18, 2019, 09:56 PM
1Host name/address: labsql.fapce.edu.br = vou usar em casa / 10.100.0.7 = usar na maquina laboratório(wifi)
2Port: 3024 = usar em casa / 5432 = = usar na maquina laboratório
3Maintenance database: fap_2019_1
4obd_2017210289
5
6ExercÃcios banco de dados
7
8-- trazer o nome de todos os clientes que tem documentos recebidos vencidos
9
10tipo 1
11select c.id,c.nome from cliente c
12where exists (
13 select distinct r.id_cliente from recebimento r
14 where r.dt_vencimento < now()
15 and r.dt_pgto is null
16 and c.id_cliente = r.id_cliente
17
18)
19
20
21--trazer os recebimentos que tem vinculo com algum PDVs
22
23select p.id from pdv p
24intersect
25select distinct id_pdv from recebimento r
26where r.id_pdv is not null
27
28
29-- Retorne a diferença existente entre as tabelas cliente e pdv
30
31select c.id from cliente c
32
33_____________________________________________________________________________
34
35REVISÃO AVP1
36
37-Restrições
38 -Chave primaria
39 -Chave extrangeira - Integridade referencial
40
41-Ãndice
42 -Ganho na consulta
43 -Perco na atualização
44 -Garanto a unicidade
45
46-View
47 -Já é compilado, mais rapido;
48 -Consigo criar visões pra usuários especÃficos;
49
50-Store Procedure
51 -Consultas;
52 -Linguagem estruturada;
53 -Funciona como sub-rotinas;
54
55-Trigger
56 -
57
58-Domain
59
60
61
62
63
64UNION = É o resultado de 2 consultas, que possuem o mesmo campo e tipo.
65
66Join: Obter dados de 2 ou mais tabelas, baseado em colunas que possuem chave primaria em uma e estrangeira na outra.
67
68 *INNER JOIN: Vai retornar quando houver uma correspondencia entre tabelas.
69
70 SELECT a.nome, n.nota, d.disciplina from aluno AS a
71 INNER JOIN nota AS n ON a.id = n.id_aluno
72 INNER JOIN discplina as d ON d.id = n.id_disciplina
73 WHERE disciplina = 'Algoritmos';
74
75
76 *OUTER JOIN: Retorna linhas, mesmo se não tiver correspondencia entre tabelas.
77 Left JOIN: Retorna dados da tabela esquerda
78 Right JOIN: Retorna dados da tabela a direita
79 FULL JOIN: Retorna quando houver correspondencia em ambas
80
81
82_________________________________________________________________________________-
83Algebra relacional
84
85#Exercio algebra Relacional
86
871. Converta a operação da álgebra relacional abaixo para comando SQL:
88• π ID, RazaoSocial (σ ID_Munic = 1010 AND Qt_Funcion > 100 (Empresa))
89
90R = select ID, RazaoSocial from Empresa Where(sigma) id_munic = 100 AND qt_funcion > 100;
91
92
932. Converta o comando SQL abaixo para álgebra relacional:
94
95 select CRM, Nome, ID_Especializacao from Medico where ID_Especializacao <> 10 and Dt_CRM < '2005/01/01'
96
97pi crm,nome, id_especializacao (sigma id_especializacao <> 10 and dt_crm < '2005/01/01'(medico))
98
99
1003. Converta a operação da álgebra relacional abaixo para comando SQL:
101• aprovado ↠σ nota >= 7 (aluno)
102matriculado ↠σ ano = 2016 (aluno)
103sub_resultado_1 ↠π(projeção) id_aluno, nome (aprovado)
104sub_resultado_2 ↠π id_aluno, nome (matriculado)
105resultado ↠sub_resultado_1 u sub_resultado_2
106
107select id_aluno,nome from aluno where nota >=7
108UNION
109select id_aluno,nome from aluno
110WHERE ano = 2016
111
112
1134. Converta o comando SQL abaixo para álgebra relacional:
114• select id, nome from professor
115where id_disciplina = 'PBD' or id_disciplina = 'BD'
116
117pi id,nome (sigma id_disciplina = 'PBD' OR id_dispiciplina = 'BD'(professor))
118
119
120
1215. Qual das duas operações da álgebra relacional abaixo terá melhor desempenho?
122Por quê? R- Letra a
123a) π ID, RazaoSocial (σ ID_Munic = 1010 AND Qt_Funcion > 100 (Empresa))
124Primeiro ele filtra, depois consulta
125b) σ ID_Munic = 1010 AND Qt_Funcion > 100 (π ID, RazaoSocial (Empresa))
126
1276. Converta o comando SQL abaixo numa operação da álgebra relacional
128equivalente:
129• select(pi) ID_Aluno, Nome from Aluno
130where Dt_Nasc < 1998 and Sexo = 'Masculino'
131
132pi id_aluno,nome (sigma dt_nasc < 1998 AND sexo = 'Masculino'(Aluno))
133
1347. Reescreva em álgebra relacional o comando SQL mostrado abaixo:
135• select matricula, nome from aluno where matricula = 100 and dt_nasc >
13601/01/1981
137
138pi matricula,nome (sigma matricula = 100 AND dt_nasc > 01/01/1981(Aluno))
139
1408. A álgebra relacional afirma que R ― S é uma operação comutativa? Prove?
141R- Não
142
1439. Escreva o comando SQL equivalente a operação relacional abaixo:
144• π Nome, ID_Setor ( σ ID_Setor = 1 (Funcion) )
145
146select nome, id_setor from Funcion where id_setor = 1;
147
148
14910.Escreva a operação relacional equivalente ao comando SQL abaixo:
150• select Nome, ID_Setor from Funcion where ID_Setor = 1;
151
152pi nome, Id_setor (sigma id_setor=1 (Funcion))
153
15411.Converta de álgebra relacional para SQL:
155• π nome, dt_nasc ( σ salario > 1500 AND salario < 5000 (funcion))
156
157select nome, dt_nasc FROM Funcion where salario > 1500 AND salario < 5000;
158
15912.Converta de SQL para álgebra relacional:
160• select a.nome, b.titulo from aluno a
161join biblioteca_livro b on (a.id = b.id_aluno)
162
163pi a.nome, b.titulo Aluno join id= id_aluno biblioteca_livro
164
16513.Converta o comando abaixo de SQL para álgebra relacional:
166• select * from Cliente where ID = 10 or ID = 20.
167
168pi * (sigma id = 10 OR id = 20 (Cliente))
169
17014.Converta o comando abaixo de álgebra relacional para SQL:
171• π Matricula, Nome (σ Matricula = 1 (Aluno))
172
173select matricula, nome from Aluno WHERE matricula = 1;
174
17515.Escreve o equivalente do comando SQL abaixo em álgebra relacional:
176a) select a.id_aluno, a.nome, n.avp1, n.avp2 from aluno a, nota n
177where a.nome = ‘MARIA’ and a.id_aluno = n.id_aluno;
178
179pi a.id_aluno, a.nome, n.avp1, n.avp2 (sigma a.nome = 'MARIA' AND a.id_aluno = n.id_aluno(Aluno))
180
181b) select a.id_aluno, a.nome, n.avp1, n.avp2 from aluno a,
182join nota n on (a.id_aluno = n.id_aluno)
183where a.nome = ‘MARIA’;
184
185pi a.id_aluno, a.nome, n.avp1, n.avp2 ()
186
18716.Converta o comando abaixo de SQL para álgebra relacional:
188• select * from Cliente where ID = 10 or ID = 20
189
190pi * (sigma where id = 10 OR id = 20(Cliente))
191
19217.Converta o comando abaixo de álgebra relacional para SQL:
193• π Matricula, Nome (σ Matricula = 1 (Aluno))
194
195select matricula,nome from Aluno WHERE matricula = 1;
196
19718.Sobre as operações relacionais unárias SELEÇÃO e PROJEÇÃO:
198a) Qual a diferença entre elas?
199b) Dê um exemplo de SELEÇÃO.
200c) Dê um exemplo de PROJEÇÃO.
201
20219.Escreva a operação relacional equivalente ao comando SQL abaixo:
203• select fnc.Nome, st.Setor from Funcion fnc
204join Setor st on (fnc.ID_Setor = st.ID)
205where fnc.ID_Setor = 1
206
207
208
209
210
211
21220.Escreva o comando SQL equivalente a operação relacional abaixo:
213• ALUNO_ID ↠σ ID = “2012007†(ALUNO)
214ALUNO_ATRIB ↠π(projeção) ID, NOME (ALUNO_ID)
215
216select id,nome from Aluno where id = "2012007"
217
21821.Escreva a operação relacional equivalente ao comando SQL abaixo:
219• select max(nome) from professor
220where nome > “MARIAâ€
221
222pi maior(nome) (sigma nome > "MARIA"(professor))
223
224
225
226
227
228____________________________________________
229
230*VIEW
231-Funciona como uma tabela virtual;
232-É um ResultSet de uma ou mais tabelas retornadas;
233
234**SINTAXE
235
236create VIEW vw_cliente_pedido(id,nome,soma) as
237select cl.id(cl.nome || '' || cl.sobrenome), sum(pd.valor) from cliente cl
238join pedido pd.id_cliente = cl.id
239group by cl.id, cl.nome, cl.sobrenome;
240
241USANDO:
242
243select *from vw_cliente_pedido
244where soma_pedido > 2000
245order by nome_completo;
246
247*DOMAIN
248-Funciona como template de tipos de dados;
249-Padroniza e centraliza os tipos de dados;
250
251
252create domain dm_valor numeric(15,2)
253default 0 not null check(value > 0);
254
255USANDO:
256create table conta(
257id_conta integer not null primary key,
258agencia varchar(5),
259conta varchar(10),
260saldo numeric(15, 2) default 0 not null,
261limite_conta dm_valor,
262limite_emprestimo dm_valor
263);
264
265
266STORE PROCEDURES
267-Sub-rotinas no banco de dados, que ficam armazenadas dentro do bd;
268-Declarações de variáveis;
269
270**SINTAXE:
271create function sp_tipo_salario(integer)
272returns varchar
273as
274$$
275declare
276 p_id integer;
277 linha numeric(15,2);
278 retorno varchar;
279begin
280 p_id=1;
281 select salario from funcion where id = p_into
282 linha;
283
284
285 if ( linha < 1000 ) then
286 retorno := 'Salário menor que R$ 1000,00';
287 elseif ( linha = 1000 ) then
288 retorno := 'Salário igual a R$ 1000,00';
289 else
290 retorno := 'Salário maior que R$ 1000,00';
291 end if;
292 return retorno;
293 end;
294 $$
295 language plpgsql;
296
297
298
299**TRIGGER
300
301 create trigger trg_funcion before insert on funcion
302 for each row execute procedure
303 spt();
304
305
306
307
308**SEQUENCE
309
310 -Gerar e armazenar numeros sequenciais positivos ou negativos;
311 -Incrementar valor das colunas;
312
313
314 SINTAXE:
315
316 create sequence seq_usuario;
317
318 create table obd.usuario(
319 id_usuario integer not null primary key
320 default nextval('seq_usuario'),
321 login varchar(10) not null,
322 senha varchar(10) not null
323 );
324
325
326**INDICIE
327 create index idx_aluno_nome on Aluno(nome);
328
329
330
331
332_________________________________________________________________________________________________________________________
333AVP2
334
33522/04/2019
336Processamento e Otimização de Consultas
337
338
339-Linguagem consulta alto nivel(Linguagem de humana)
340 São declaritavas;
341
342
34329/04/2019
344Melhorando o Desempenho
345
346
34703/05/2019
348
34913/05/2019
350Metados
351 - São dados sobre dados, caracteristicas;