wiki

BDA - Aula 25 - 2015/1

Revisão de 17h56min de 23 de junho de 2015 por Alessandro Gontijo (discussão | contribs) (→Augusto)

DCL


  • Linguagem de Controle de Dados
    • Subconjunto da SQL utilizado para controlar o acesso aos dados, basicamente com dois comandos que permite ou bloqueia o acesso de usuários a dados.


  • GRANT: Autoriza um usuário a executar alguma operação.
    • Exemplo:
Dar permissão de consulta na tabela cliente para o usuário carlos)
GRANT select ON cliente TO carlos;


  • REVOKE: Restringe ou remove a permissão de um usuário executar alguma operação
    • Exemplo:
Não permitir que o usuário carlos crie tabelas no banco de dados
REVOKE CREATE TABLE FROM carlos;



  • SET: Define parâmetros em tempo de execução, como por exemplo, o tipo de codificação do cliente e o estilo de representação de data e hora.
  • LOCK: Bloqueia explicitamente uma tabela fazendo o controle de acessos concorrente.


TCL


  • Linguagem de Controle de Transações:
    • São usadas para controlar mudanças feitas pelos comandos DML, podendo, por exemplo, voltar as instâncias ao modo que eram antes de um comando DML.
    • Exemplo de comandos TCL do SQL:
COMMIT
SAVEPOINT
ROLLBACK



Exercício A


  • Leia atentamente o enunciado
  • Implemente o comando no W3Schools que resolva o problema
  • Tendo sucesso, copie o comando para a Wiki, logo abaixo do enunciado
  • Para criar a tabulação, antes do comando mantenha o string ::
  • Salve


  • Caso, seu exercício dependa de um anterior (por exemplo, criar tabela ou inserir dados), copie o comando do colega e execute no seu browser.


Bruna


  • Desenhar o DER envolvendo todas as tabelas


Marcelo



  • 01. Abra seu SGBD
Postgresql, Oracle ou MySQL?
Postgresql
  • 02. Crie a tabela Climas
Descreva o comando aqui
CREATE TABLE climas(
cod_clima SERIAL,
temp_max int,
temp_min int,
precipitacao int,
CONSTRAINT climas_pkey PRIMARY KEY(cod_clima)
);
  • 03. Insira dados coletados na Web sobre as seguintes cidades
    • Belo Horizonte
    • Cuiabá
    • Curitiba
    • Fortaleza
    • Uberlândia
Descreva os comandos aqui
INSERT INTO climas(temp_max, temp_min, precipitacao) VALUES(26, 12, 0);


INSERT INTO climas(temp_max, temp_min, precipitacao) VALUES(34, 18, 0);


INSERT INTO climas(temp_max, temp_min, precipitacao) VALUES(23, 10, 5);


INSERT INTO climas(temp_max, temp_min, precipitacao) VALUES(31, 24, 22);


INSERT INTO climas(temp_max, temp_min, precipitacao) VALUES(30, 14, 0);


  • 04. Crie a tabela Cidades com os seguintes campos:
    • Nome(Str) , Habitantes (Int) , NomeEstado (Str), Aniversário (Date) , Area (Km2) (Float)
Descreva o comando aqui
CREATE TABLE cidades(
cod_cidade SERIAL,
nome varchar(50),
habitantes int,
cod_estado int references estados(cod_estado),
aniversario date,
area float,
cod_clima int references climas(cod_clima),
CONSTRAINT cidades_pkey PRIMARY KEY(cod_cidade)
);
  • 05. Insira dados na tabela acima
Descreva o comando aqui
INSERT INTO cidades(nome, habitantes, cod_estado, aniversario, area, cod_clima) VALUES('Belo Horizonte', 2479175, 1, '1897-12-12', 330.95, 1);
INSERT INTO cidades(nome, habitantes, cod_estado, aniversario, area, cod_clima) VALUES('Cuiabá', 575480 , 2, '1719-04-08', 3538.167, 2);
INSERT INTO cidades(nome, habitantes, cod_estado, aniversario, area, cod_clima) VALUES('Fortaleza', 8166.56 , 3, '1726-04-13', 314.930, 3);
INSERT INTO cidades(nome, habitantes, cod_estado, aniversario, area, cod_clima) VALUES('Uberlândia', 15712 , 1, '1888-08-31', 4115822.0, 4);
INSERT INTO cidades(nome, habitantes, cod_estado, aniversario, area, cod_clima) VALUES('Curitiba', 1864416 , 4, '1693-03-29', 435036, 5);
  • 06. Crie os índices nome e Aniversário
Descreva o comando aqui
CREATE INDEX on cidades(nome);
CREATE INDEX on cidades(aniversario);
  • 07. Altere o nome do Campo Habitantes para NroHabitantes
Descreva o comando aqui
ALTER TABLE cidades RENAME COLUMN habitantes TO nrohabitantes;


Cecília


  • 08. Crie a tabela Eventos com os seguintes campos:
    • NomeEvento (Str) , Data (Date) , Horário (Int), CodCidade (Int), Preço (Float)
create table Eventos (NomeEvento char(50), Data date, Horario integer , CodCidade integer, Preco float);
  • 09. Insira dados de eventos quaisquer para as cidades
insert into Eventos values ('Sarau Literario', '2015-04-03', 1001, 01, 200.00);
insert into Eventos values ('Arraia', '2014-04-03', 1300, 01, 40.00);
insert into Eventos values ('Feira de Vinhos', '2015-06-07', 2200, 02, 150.00);
insert into Eventos values ('Feijoada', '2015-05-05', 1400, 03, 100.00);
insert into Eventos values ('feira de Vinhos', '2015-05-05', 1400, 03, 100.00);
insert into Eventos values ('Show', '2015-10-05', 2300, 04, 50.00);
  • 10. Gere um relatório mostrando os dados dos eventos das Cidades
Descreva o comando aqui
  • 11. Gere um relatório mostrando apenas Nome, Estado e Area das Cidades
Descreva o comando aqui
  • 12. Gere um relatório mostrando Nome, NomeEstado e Area das Cidades em ordem alfabética de Nome
Descreva o comando aqui
  • 13. Gere um relatório mostrando Nome, NomeEstado e Area das Cidades em ordem descendente de Nome
Descreva o comando aqui


Laura


  • 14. Gere um relatório com as cidades que possuem eventos (Sem duplicidade)
SELECT DISTINCT local FROM eventos;
  • 15. Gere uma view com as cidades de Minas Gerais
SELECT "nome" FROM "cidades" WHERE nomeestado = 13;
  • 16. Gere uma view com todos os dados de Cidades (Nome e Data Fundação) com seus eventos
SELECT * FROM cidades
INNER JOIN eventos ON cidades.cod_cidade = eventos.local;
  • 17. Exclua a cidade de Curitiba
DELETE FROM cidades WHERE cidade = 'Curitiba';
  • 18. Recupere a tabela com a cidade de Curitiba
ROLLBACK;
  • 19. Inclua a tabela Estados com os seguintes campos:
    • UF (Int), NomeEstado (Str), Regiao (Str)
CREATE TABLE "estados" ("uf" integer primary key, "nomeestado" char(20) not null, "regiao" char(20) not null);


Letícia



  • 20. Insira 2 estados da região sudeste na tabela Estados
INSERT INTO "estados" values(8,'Espirito Santo', 'Sudeste');
INSERT INTO "estados" values(13,'Minas Gerais', 'Sudeste');
  • 21. Gere uma view que mostre os novos dados apenas da Região Sudeste
CREATE VIEW regiaosudeste AS
SELECT nomeestado
FROM estados
WHERE regiao= 'Sudeste';
SELECT * FROM "regiaosudeste";
  • 22. Inclua os demais estados do Brasil
INSERT INTO "estados" values(1,'Acre', 'Norte');
INSERT INTO "estados" values(2,'Alagoas', 'Nordeste');
INSERT INTO "estados" values(3,'Amapa', 'Norte');
INSERT INTO "estados" values(4,'Amazonas', 'Norte');
INSERT INTO "estados" values(5,'Bahia', 'Nordeste');
INSERT INTO "estados" values(6,'Ceara', 'Nordeste');
INSERT INTO "estados" values(7,'Distrito Federal', 'Centro Oeste');
INSERT INTO "estados" values(9,'Goias', 'Centro Oeste');
INSERT INTO "estados" values(10,'Maranhão', 'Nordeste');
INSERT INTO "estados" values(11,'Mato Grosso', 'Centro Oeste');
INSERT INTO "estados" values(12,'Mato Grosso do Sul', 'Centro Oeste');
INSERT INTO "estados" values(14,'Para', 'Norte');
INSERT INTO "estados" values(15,'Paraíba', 'Nordeste');
INSERT INTO "estados" values(16,'Paraná', 'Sul');
INSERT INTO "estados" values(17,'Pernambuco', 'Nordeste');
INSERT INTO "estados" values(18,'Piauí', 'Nordeste');
INSERT INTO "estados" values(19,'Rio de Janeiro', 'Sudeste');
INSERT INTO "estados" values(20,'Rio Grande do Norte', 'Nordeste');
INSERT INTO "estados" values(21,'Rio Grande do Sul', 'Sul');
INSERT INTO "estados" values(22,'Rondonia', 'Norte');
INSERT INTO "estados" values(23,'Roraima', 'Norte');
INSERT INTO "estados" values(24,'Santa Catarina', 'Sul');
INSERT INTO "estados" values(25,'São Paulo', 'Sudeste');
INSERT INTO "estados" values(26,'Sergipe', 'Nordeste');
INSERT INTO "estados" values(27,'Tocantins', 'Norte');
  • 23. Mostre novamente os dados da view Estados
SELECT * FROM "estados";
  • 24. Inclua na tabela Eventos, registros coletados na Web para:
    • Rio de Janeiro
INSERT INTO eventos values ('Feijoada da Tia Surica', '27/06/2015', 13, 10, 50.00);
    • São Paulo
INSERT INTO eventos values ('26ª ExpoAço', '12/07/2015', 8, 11, 00.00);
  • 25. Exclua um evento qualquer colocando data e hora como condição
DELETE FROM "eventos" WHERE data = '12/07/2015 ' AND horario = '8';


Gabriel


  • 26. Aumente o valor de todos os preços de todos os eventos em 10%
Descreva o comando aqui
  • 27. Aumente o valor dos preços de todos os eventos em 5% para São Paulo
Descreva o comando aqui
  • 28. Altere a data de qualquer evento de Uberlândia postergando 3 dias
Descreva o comando aqui
  • 29. Exclua o Estado de Minas Gerais da tabela Estados
Descreva o comando aqui
  • 30. Inclua novamente Minas Gerais
Descreva o comando aqui
  • 31. Mostre os eventos numa faixa de valores
Descreva o comando aqui


Vitor


  • 32. Mostre os eventos e o clima da cidade que sediará os eventos de Dezembro
Descreva o comando aqui
  • 33. Mostre os eventos das seguintes cidades: São Paulo, Rio de Janeiro e Curitiba
Descreva o comando aqui
  • 34. Mostre os eventos agrupados por cidade
Descreva o comando aqui
  • 35. Mostre os eventos agrupados por cidade com preço inferior a R$ 30,00
Descreva o comando aqui
  • 36. Calcule a média de preço de todos os eventos
Descreva o comando aqui
  • 37. Calcule a média de preço dos eventos de São Paulo
Descreva o comando aqui
  • 38. Calcule o evento de menor preço em São Paulo ou Rio de Janeiro
Descreva o comando aqui


Augusto


  • 39. Mostre todos os eventos com o nome da Cidade e Estado
Descreva o comando aqui
  • 40. Mostre todos os eventos ordenados por data com o nome da Cidade e Estado
Descreva o comando aqui
  • 41. Mostre todos os eventos ordenados por data e agrupados por Cidade
Descreva o comando aqui
  • 42. Mostre todos os eventos ordenados por Preço e agrupados por Estado
Descreva o comando aqui
  • 43. Gere e execute uma visão viewEventosMenor35 com os eventos com valores menores a R$ 35,00
CREATE VIEW viewEventosMenor35
SELECT * FROM Eventos
WHERE Preco < 35;
  • 44. Aumente os valores de todos os eventos em 10%
Descreva o comando aqui
  • 45. Execute a visão viewEventosMenor35
CREATE VIEW viewEventosMenor35
SELECT * FROM Eventos
WHERE Preco < 35;


Alessandro


  • 46. Mostre o nome, o estado e a região das cidades que começam com a letra C
SELECT Cidades.Nome, Cidades.NomeEstado, estados.regiao
FROM Cidades,Estados
WHERE Cidades.NomeEstado = estados.nomeestado AND Cidades.Nome LIKE 'C%';
  • 47. Exclua os eventos de Curitiba
DELETE
FROM Eventos
WHERE Eventos.CodCidade = SELECT cidade.cod_cidade FROM cidades WHERE cidades.nome = 'Curitiba';
  • 48. Execute a visão viewEventosMenor35
CREATE VIEW viewEventosMenor35
SELECT * FROM Eventos
WHERE Preco < 35;
  • 49. Gere e execute uma visão viewEventosBH com o nome do evento, nome da cidade e da região
CREATE VIEW viewEventosBH
SELECT Eventos.NomeEvento, cidades.Nome, estados.regiao
FROM Eventos, cidades, estados
WHERE cidades.nome = 'Belo Horizonte' AND Eventos.CodCidade = cidades.cod_cidade AND cidades.cod_estado = estados.uf;
  • 50. Gere e execute um relatório com o nome do evento, nome da cidade e da região para eventos de BH.
SELECT Eventos.NomeEvento, cidades.Nome, estados.regiao
FROM Eventos, cidades, estados
WHERE cidades.nome = 'Belo Horizonte' AND Eventos.CodCidade = cidades.cod_cidade AND cidades.cod_estado = estados.uf;


Disponível em “http://3.143.69.156/wiki/index.php?title=BDA_-_Aula_25_-_2015/1&oldid=37922”