wiki

BDA - Aula 21 - 2015/1

DML


Linguagem de Manipulação de Dados (ou DML, de Data Manipulation Language) é uma família de linguagens de computador utilizadas para a recuperação, inclusão, remoção e modificação de informações em bancos de dados. Pode ser procedural, que especifica como os dados devem ser obtidos do banco; pode também ser declarativa (não procedural), em que os usuários não necessitam especificar o caminho de acesso, isto é, como os dados serão obtidos. O padrão SQL é não procedural. DMLs foram utilizadas inicialmente apenas por programas de computador, porém (com o surgimento da SQL) também têm sido utilizadas por pessoas.


INSERT


  • É utilizado o comando INSERT para inserir linhas nas tabelas:
INSERT INTO clima VALUES ('São Francisco', 46, 50, 0.25, '1994-11-27');
  • Reparem que todos os tipos de dado possuem formato de entrada de dados bastante óbvios
    • As constantes, que não são valores numéricos simples, geralmente devem estar entre apóstrofos ('), como no exemplo acima
    • O tipo date é, na verdade, muito flexível em relação aos dados que aceita


  • O tipo point requer um par de coordenadas como entrada, como mostrado abaixo:
INSERT INTO cidades VALUES ('São Francisco', '(-194.0, 53.0)');


  • A sintaxe usada até agora requer que seja lembrada a ordem das colunas. Uma sintaxe alternativa permite declarar as colunas explicitamente:
INSERT INTO clima (cidade, temp_min, temp_max, prcp, data)
VALUES ('São Francisco', 43, 57, 0.0, '1994-11-29');


  • Se for desejado, pode-se declarar as colunas em uma ordem diferente, ou mesmo, omitir algumas colunas. Por exemplo, se a precipitação não for conhecida:
INSERT INTO clima (data, cidade, temp_max, temp_min)
VALUES ('1994-11-29', 'Hayward', 54, 37);


  • Muitos desenvolvedores consideram que declarar explicitamente as colunas é melhor que confiar na ordem implícita.



SELECT


  • Para trazer os dados de uma tabela, a tabela deve ser consultada
  • Para esta finalidade é utilizado o comando SELECT do SQL. Este comando é dividido em:
    • lista de seleção (a parte que especifica as colunas a serem trazidas)
    • lista de tabelas (a parte que especifica as tabelas de onde os dados vão ser trazidos)
    • uma qualificação opcional (a parte onde são especificadas as restrições)



  • Por exemplo, para trazer todas as linhas da tabela clima digite:
SELECT * FROM clima;

Aqui o * é uma forma abreviada de "todas as colunas". 

SELECT cidade, temp_min, temp_max, prcp, data FROM clima;

A saída deve ser: 

    cidade      | temp_min | temp_max | prcp |    data
----------------+----------+----------+------+------------
São Francisco   |       46 |       50 | 0.25 | 1994-11-27
São Francisco   |       43 |       57 |    0 | 1994-11-29
Hayward         |       37 |       54 |      | 1994-11-29
(3 linhas)

Na lista de seleção podem ser especificadas expressões, e não apenas referências a colunas. Por exemplo, pode ser escrito 

SELECT cidade, (temp_max+temp_min)/2 AS temp_media, data FROM clima;

    cidade      | temp_media |    data
----------------+------------+------------
São Francisco   |         48 | 1994-11-27
São Francisco   |         50 | 1994-11-29
Hayward         |         45 | 1994-11-29
(3 linhas)

Perceba que a cláusula AS foi utilizada para mudar o nome da coluna de saída (a cláusula AS é opcional).

A consulta pode ser "qualificada", adicionando a cláusula WHERE para especificar as linhas desejadas. A cláusula WHERE contém expressões booleanas (valor verdade), e somente são retornadas as linhas para as quais o resultado da expressão booleana for verdade. São permitidos os operadores booleanos usuais (AND, OR e NOT) na qualificação. Por exemplo, o comando abaixo mostra o clima de São Francisco nos dias de chuva: 

SELECT * FROM clima
WHERE cidade = 'São Francisco' AND prcp > 0.0;

Resultado:
    cidade      | temp_min | temp_max | prcp |    data
----------------+----------+----------+------+------------
São Francisco   |       46 |       50 | 0.25 | 1994-11-27
(1 linha)

Pode ser solicitado que os resultados da consulta sejam retornados em uma determinada ordem: 

SELECT * FROM clima
   ORDER BY cidade;
    cidade      | temp_min | temp_max | prcp |    data
----------------+----------+----------+------+------------
Hayward         |       37 |       54 |      | 1994-11-29
São Francisco   |       43 |       57 |    0 | 1994-11-29
São Francisco   |       46 |       50 | 0.25 | 1994-11-27

Neste exemplo a ordem de classificação não está totalmente especificada e, portanto, as linhas de São Francisco podem retornar em qualquer ordem. Mas sempre seriam obtidos os resultados mostrados acima se fosse executado: 

SELECT * FROM clima
ORDER BY cidade, temp_min;

Pode ser solicitado que as linhas duplicadas sejam removidas do resultado da consulta: 

SELECT DISTINCT cidade
FROM clima;
   cidade
---------------
Hayward
São Francisco
(2 linhas)

Novamente, neste exemplo a ordem das linhas pode variar. Pode-se garantir resultados consistentes utilizando DISTINCT e ORDER BY juntos: 

SELECT DISTINCT cidade
FROM clima
ORDER BY cidade;



Até agora as consultas somente acessaram uma tabela de cada vez. As consultas podem acessar várias tabelas de uma vez, ou acessar a mesma tabela de uma maneira que várias linhas da tabela sejam processadas ao mesmo tempo. A consulta que acessa várias linhas da mesma tabela, ou de tabelas diferentes, de uma vez, é chamada de consulta de junção. Como exemplo, suponha que se queira listar todas as linhas de clima junto com a localização da cidade associada. Para fazer isto, é necessário comparar a coluna cidade de cada linha da tabela clima com a coluna nome de todas as linhas da tabela cidades, e selecionar os pares de linha onde estes valores são correspondentes.

Nota: Este é apenas um modelo conceitual, a junção geralmente é realizada de uma maneira mais eficiente que realmente comparar cada par de linhas possível, mas isto não é visível para o usuário.

Esta operação pode ser efetuada por meio da seguinte consulta: 

SELECT *
FROM clima, cidades
WHERE cidade = nome;
    cidade      | temp_min | temp_max | prcp |    data    |     nome      | localizacao
----------------+----------+----------+------+------------+---------------+-------------
São Francisco   |       46 |       50 | 0.25 | 1994-11-27 | São Francisco | (-194,53)
São Francisco   |       43 |       57 |    0 | 1994-11-29 | São Francisco | (-194,53)
(2 linhas)

Duas coisas devem ser observadas no conjunto de resultados produzido:

Não existe nenhuma linha de resultado para a cidade Hayward. Isto acontece porque não existe entrada correspondente na tabela cidades para Hayward, e a junção ignora as linhas da tabela clima sem correspondência. Veremos em breve como isto pode ser corrigido.

Existem duas colunas contendo o nome da cidade, o que está correto porque a lista de colunas das tabelas clima e cidades estão concatenadas. Na prática isto não é desejado, sendo preferível, portanto, escrever a lista das colunas de saída explicitamente em vez de utilizar o *:

Exercício A


  • 01. Abra seu SGBD
  • 02. Crie a tabela Climas
  • 03. Insira dados coletados na Web sobre as seguintes cidades
    • Belo Horizonte
    • Cuiabá
    • Curitiba
    • Fortaleza
    • Uberlândia
  • 04. Crie a tabela Cidades com os seguintes campos:
    • Nome(Str) , Habitantes (Int) , NomeEstado (Str), Aniversário (Date) , Area (Km2) (Float)
  • 05. Insira dados na tabela acima
  • 06. Crie os índices nome e Aniversário
  • 07. Altere o nome do Campo Habitantes para NroHabitantes
  • 08. Crie a tabela Eventos com os seguintes campos:
    • NomeEvento (Str) , Data (Date) , Horário (Int), Local (Str), Preço (Float)
  • 09. Insira dados de eventos quaisquer para as cidades
  • 10. Gere um relatório mostrando os dados da Cidade
  • 11. Gere um relatório mostrando apenas Nome, Estado e Area das Cidades
  • 12. Gere um relatório mostrando Nome, NomeEstado e Area das Cidades em ordem alfabética de Nome
  • 13. Gere um relatório mostrando Nome, NomeEstado e Area das Cidades em ordem descendente de Nome
  • 14. Gere um relatório com as cidades que possuem eventos (Sem duplicidade)
  • 15. Gere uma view com as cidades de Minas Gerais
  • 16. Gere uma view com todos os dados de Cidades (Nome e Data Fundação) com seus eventos
  • 17. Exclua a cidade de Curitiba
  • 18. Recupere a tabela com a cidade de Curitiba
  • 19. Inclua a tabela Estados com os seguintes campos:
    • UF (Int), NomeEstado (Str), Regiao (Str)
  • 20. Insira 2 estados da região sudeste na tabela Estados
  • 21. Gere uma view que mostre os novos dados apenas da Região Sudeste
  • 22. Inclua os demais estados do Brasil
  • 23. Mostre novamente os dados da view Estados

Exercício B


  • Desenvolver uma seleção de registros com mais de duas tabelas envolvidas.
    • A. Crie a estrutura de 3 tabelas
    • B. Insira dados quaisquer em cada uma delas
    • C. Mostre o Select * from Tabela em cada uma delas
    • D. Feito isso, escreva o comando que permite gerar o resultados sobre as 3 ou mais tabelas ao mesmo tempo.
    • E. Mostre o resultado.
  • Use a visão abaixo:





  • Mostrar Isbn, Titulo do Livro e Nome da Editora
SELECT Livro.isbn, Livro.titulo, Editora.Nome
FROM Livro
INNER JOIN Editora
ON Livro.Editora = Editora.CodEditora


  • Mostrar Isbn, Titulo do Livro, Nome da Area e Nome da Editora
SELECT Livro.isbn, Livro.titulo, Editora.Nome, Area.Area
FROM Livro
INNER JOIN Editora, Area
ON Livro.Editora = Editora.CodEditora and Livro.Area = Area.CodArea


  • Mostrar Isbn, Titulo do Livro, Nome da Area, Nome e CodBairro da Editora
SELECT Livro.isbn, Livro.titulo, Editora.Nome, Area.Area, Cep.CodBairro
FROM Livro
INNER JOIN Editora, Area, Cep
ON Livro.Editora = Editora.CodEditora and Livro.Area = Area.CodArea and Editora.Cep = Cep.Cep


  • Usando "LEFT JOIN" - Vítor e Marcelo
1° query:
Select livro.isbn, livro.titulo, editora.nome
from livro
left join editora
on livro.editora = editora.codeditora;
2° query:
Select livro.isbn, livro.titulo, editora.nome, area.area
from livro
left join editora
on livro.editora = editora.codeditora left join area on livro.area = aera.cdoarea;
3° query:
Select livro.isbn, livro.titulo, editora.nome, area.area, cep.codbairro
from livro
left join editora
on livro.editora = editora.codeditora left join area on livro.area = aera.cdoarea left join cep on editora.cep = cep.cep;


Exercício C


  • Desenvolver uma seleção de registros com mais de duas tabelas envolvidas.
    • A. Crie a estrutura de 3 tabelas
    • B. Insira dados quaisquer em cada uma delas
    • C. Mostre o Select * from Tabela em cada uma delas
    • D. Feito isso, escreva o comando que permite gerar o resultados sobre as 3 tabelas ao mesmo tempo.
    • E. Mostre o resultado.
  • Use a segunda visão acima.


Arquivo:BDA - SQL - Planilha de Dados.pdf

Exercício D


  • Avalie e entenda a diferença entre os dois Selects abaixo:


W3Schools Usando Innerjoin

  • Mostrar Nome do Empregado, Nro do Pedido e Nome do Cliente
SELECT Employees.FirstName, Employees.LastName, Orders.OrderID, Orders.CustomerID, Customers .CustomerName
FROM Employees
Inner join Orders, Customers
where Employees.EmployeeID = Orders.EmployeeID and Orders.CustomerID = Customers .CustomerID


W3Schools Usando Lef tJoin

SELECT Employees.FirstName, Employees.LastName, Orders.OrderID, Orders.CustomerID, Customers .CustomerName
FROM Employees
left join Orders, Customers
where Employees.EmployeeID = Orders.EmployeeID and Orders.CustomerID = Customers .CustomerID


  • O left join seleciona todos os dados da primeira tabela, independente se houver ou não um dado correspondente nas outras.
    • Por exemplo, se existir um empregado na tabela Employees que não estiver na tabela Orders, ele será mostrado com o left join, mas não será mostrado com o inner join.


W3Schools Usando CREATE VIEW

CREATE VIEW Orders_Customers AS SELECT * FROM Orders,Customers WHERE Orders.CustomerID = Customers.CustomerID;
SELECT Employees.FirstName, Employees.LastName, Orders_Customers.OrderID, Orders_Customers.CustomerID, Orders_Customers.CustomerName
FROM Employees, Orders_Customers
WHERE Employees.EmployeeID = Orders_Customers.EmployeeID
Disponível em “http://3.143.69.156/wiki/index.php?title=BDA_-_Aula_21_-_2015/1&oldid=37861”