Esta é a segunda aula de banco de dados que posto. Praticamente é mais do mesmo. Depois da primeira aula que postei já tive umas 3 ou 4 aulas na faculdade, tudo com basicamente a mesma coisa. Resumi um pouco de tudo aqui. Se você entendeu a primeira aula, então não terá problemas para essa. O que farei aqui será apenas passar alguns exercícios e dar umas dicas para cada um. Se não viu ou não entendeu a primeira aula, basta dar uma olhada de novo clicando aqui.

Não sei se já disse isso, mas na faculdade, infelizmente, é usado o M$ SQL Server em todas as aulas o que acho uma enorme irresponsabilidade e falta de respeito. Irresponsabilidade porque acho que as faculdades deveriam ensinar com Software Livre, especialmente uma faculdade pública. E dai que pode sair de graça um software da Microsoft para as faculdade se no final quando os alunos se formarem eles terão que pagar por ele caso queiram usá-lo em suas empresas ou algo do tipo. Falta de respeito porque a M$ como tem sempre a mania de não seguir padrões e fazer as coisas bem diferentes de todos os outros (provavelmente para dificultar migrações e manter seu monopólio) e isso acaba criando alunos limitados e sem capacidade de usar outro software. Se querem ensinar usando o SQL Server tudo bem, mas pelo menos deveriam ter a finesse de ensinar como as mesmas coisas são feitas em outros bancos como o MySQL. Como sou um cara que acredita na liberdade e no Software Livre estudo e faço tudo usando ferramentas livres. Por enquanto são poucas as coisas que preciso mudar para fazer meus estudos de banco no MySQL, mas sei que a faculdade vai inventar de passar Trigger e essas outras coisas e que vão complicar na hora de estudar usando outro software que não seja o da M$. Quando isso acontecer vou procurar a forma correta de implementar essas coisas aqui no MySQL e dar um jeito de postar tudo aqui no blog (ou então simplesmente paro de postar as aulas de banco). Por enquanto quero dizer que as aulas de banco aqui do blog serão feitas usando o MySQL sempre. Ah, coloquei a foto do golfinho porque o golfinho é o símbolo do MySQL

Melhor eu parar de falar e começar com a aula!

Em primeiro lugar vamos abrir o MySQL. Se o serviço não estiver iniciado basta digitar em algum terminal:

sudo /etc/init.d/mysql start

Dentro do MySQL vamos criar um banco chamado aulas.

create database aula;

Agora vamos selecioná-lo:

use aula;

Criando as tabelas

Vamos agora criar as tabelas que usaremos para fazer os exercícios. Essas aulas não pretendem ensinar ou detalhar a criação de tabelas, portanto não vou falar nada sobre elas, apenas passar a “receita do bolo” aqui para não perdermos tempo na criação das tabela e podermos focar nos exercícios.

create table clientes(
cli_codigo int not null primary key,
cli_nome varchar(40) not null,
cli_cpf varchar(12) not null Unique);

create table vendedores(
ved_codigo int not null primary key auto_increment,
ved_nome varchar(40) not null,
ved_cpf varchar(12) not null unique);

create table pedidos(
ped_numero int not null primary key auto_increment,
ped_data datetime not null,
cli_codigo int not null,
ved_codigo int not null,
foreign key (cli_codigo) references clientes (cli_codigo),
foreign key (ved_codigo) references vendedores (ved_codigo));

create table produtos(
prd_codigo int not null primary key auto_increment,
prd_descricao varchar(50) not null,
prd_valor decimal(7,2),
prd_qtd int);

create table itens_pedidos(
ped_numero int not null,
prd_codigo int not null,
itp_qtd int not null,
itp_valor decimal(7,2) not null,
primary key (ped_numero, prd_codigo),
foreign key (ped_numero) references pedidos (ped_numero),
foreign key (prd_codigo) references produtos (prd_codigo));

Inserindo os dados nas tabelas

insert into clientes values (1, 'Superman', 100), (2, 'Porra Loka', 200), (3, 'Batiman', 300);

insert into vendedores values (1, 'Super Pinga', 10), (2, 'Super Cerva', 20), (3, 'Super Wine', 30);

insert into produtos values (1, 'Caneta', 3.2, 150), (2, 'Apontador', 0.75, 50), (3, 'CD', 1.15, 100);

insert into pedidos values (1, "2007/11/08 10:00:00",1,1), (2, "2007/12/08 11:00:00",2,2);

insert into itens_pedidos values (1, 1, 20, 1.2), (1, 2, 10, 30), (1, 3, 15, 0.78), (1, 4, 20, 3.5), (1, 5, 10, 0.6),  (1, 6, 20, 1.0), (2, 2, 30, 2.0), (3, 3, 5, 1.99);

Não reparem nos nomes e produtos usados aqui. Estou usando o que foi passado na aula. Tenho mais bom humor na hora de escolher os nomes de clientes, vendedores, produtos etc (uso nomes prováveis de filhos de empregadas domésticas, pedreiros, jogadores de futebol… Nome como Bucetildes, Robisvaldieldson Junior etc).

Exercícios

Agora que já está tudo pronto, vamos começar a fazer alguns exercícios. Estes primeiros são bem simples. Praticamente é a mesma coisa que foi passada na primeira aula. Só consultas com join (junção).

1. Consultar todos os pedidos
2. Consultar o nome do cliente do pedido 2
3. Consultar o nome do cliente e o nome do vendedor do pedido 1.
4. Consultar o nome de todos os produtos e quantidades vendidas do pedido 2.

Resolução dos exercícios

1. select * from pedidos;

2. select c.cli_nome, p.ped_numero
   from clientes c, pedidos p
   where c.cli_codigo = p.cli_codigo AND p.ped_numero = 2;

3. select c.cli_nome, v.ved_nome
   from clientes c, vendedores v, pedidos p
   where c.cli_codigo = p.cli_codigo AND v.ved_codigo = p.ved_codigo AND p.ped_numero = 1;

4. select p.prd_descricao, i.itp_qtd
   from produtos p, itens_pedidos i
   where p.prd_codigo = i.prd_codigo AND i.ped_numero = 2;

Antes de seguirmos…

Para os próximos exercícios será necessário usar SUM e COUNT, portanto tentarei explicar um pouco sobre essas funções aqui antes de seguirmos.

SUM

Essa função retorna a soma de uma expressão. É necessário também usarmos o GROUP BY quando usarmos o SUM. Ele funciona da seguinte maneira: Faz a conta que pedimos e depois agrupa as colunas que escolhemos com o GROUP BY.

Por exemplo: Se temos uma coluna chamada numero que ao fazermos uma consulta retorne 1, 1 e 2, ao usarmos o GROUP BY nessa coluna, os números repetidos serão agrupados e a consulta retornará 1, 2. Como os números repetidos foram agrupados, o SUM além de fazer a expressão que pedimos, somou as colunas que foram agrupadas.

COUNT

Conta o número de linhas ou dos valores das linhas. Depende de você. Basta pedir com jeitinho

Por exemplo, se temos a tabela jumentos (sim, imagine só os campos: jum_codigo, jum_nome, jum_cor) e nesta tabela temos 4 jumentos cadastrados, ao fazermos uma consulta usando count(*) da seguinte maneira:

select count(*) from jumentos;

Será feita a conta de quantos jumentos temos cadastrados no total. Obviamente será retornado o número 4.

Agora vamos imaginar que temos 2 jumentos diferentes mas com o mesmo nome. Podemos fazer nossa consulta de forma mais específica usando o group by:

select count(*)
from jumentos
group by jum_nome;

Então se tivermos 4 jumentos, um chamada Virgulino, outro chamada Mula Guerreira e 2 chamados Severino Calvacanti, a consulta retornaria 3 linhas, mostrando 1, 1 e 2, ou seja, mostrando que temos dois jumentos de nomes iguais.

GROUP BY

Acho que nem preciso falar muito sobre o GROUP BY já que usamos nos dois exemplos acimas. Ele agrupa o resultado em subconjuntos que possuem valores correspondentes em uma ou mais colunas.

Mais exercícios

Agora que já vimos SUM, COUNT e GROUP BY podemos passar para os próximos exercícios onde estas funções serão utilizadas.

1. Consultar os valore totais dos pedidos
2. Consultar o número de vendas de cada produto
3. Consultar o faturamento (total vendido) de cada produto

Resolução destes exercícios

1. select i.ped_numero, sum(i.itp_qtd * i.itp_valor)
   from pedidos p, itens_pedidos i
   where p.ped_numero = i.prd_codigo
   group by i.ped_numero;

2. select p.prd_codigo, count(*)
   from produtos p, itens_pedidos i
   where p.prd_codigo = i.prd_codigo
   group by i.prd_codigo;

3. select p.prd_codigo, p.prd_descricao, sum(i.itp_qtd * i.itp_valor) Faturamento
   from produtos p, itens_pedidos i
   where p.prd_codigo = i.prd_codigo
   group by p.prd_codigo, p.prd_descricao;

Um pouco sobre ORDER BY e HAVING

A cláusula ORDER BY (como o próprio nome dá a entender) serve para ordenar os registros em ordem crescente ou decrescente (você escolhe). Por padrão os valores são mostrados em ordem crescente. Podemos usar DESC, depois que inserimos o ORDER BY em nossa consulta para ordenarmos em ordem decrescente.

Exemplo: Temos a nossa tabela de jumentos e fazemos a mesma consulta que já foi mostrada antes, mas desta vez queremos ordenar pelo nome da mula. Normalmente seria ordenado por ordem alfabética, ou seja, se usarmos ORDER BY jum_nome, a famosíssima Mula Guerreira apareceria antes do nosso querido jumento Severino Cavalcanti, pois a letra M vem antes da letra S. Mas se quisermos fazer o Severino aparecer primeiro, basta fazer a consulta desta forma:

select count(*)
from jumentos
group by jum_nome
order by jum_nome desc;

O HAVING já serve para quando precisamos fazer algum teste lógico que não podemos usar no WHERE. Por exemplo, na mesma tabela de jumentos, usamos os exemplos com count(*) e group by jum_nome para vermos qual jumento tem o mesmo nome de outro. Se quisermos fazer um teste para mostrar em nossa consulta quantos jumentos com o mesmo nome aparecem duas ou mais vezes por exemplo, usamos o HAVING da seguinte forma:

select count(*)
from jumentos
group by jum_nome
having count(*) >= 2;

Devemos lembrar que o HAVING é aplicado em cima do agrupamento (GROUP BY). O HAVING é como se fosse um WHERE do agrupamento. Por isso não podemos usar HAVING sem GROUP BY (a menos que estejamos querendo um erro na consulta, e acho que ninguém quer isso ).

Últimos exercícios desta aula (prometo)

1. Consultar a quantidade vendida de cada produto, apresentando em ordem decrescente
2. Consultar os produtos que foram vendidos em 2 ou mais pedidos
3. Consultar a quantidade de pedidos de cada cliente

Resolução dos últimos exercícios

1. select p.prd_codigo, p.prd_descricao, sum(i.itp_qtd) qtd_vendidas
   from produtos p, itens_pedidos i
   where i.prd_codigo = p.prd_codigo
   group by p.prd_codigo, p.prd_descricao
   order by qtd_vendidas desc;

2. select p.prd_codigo, p.prd_descricao, count(*) vendas
   from produtos p, itens_pedidos i
   where i.prd_codigo = p.prd_codigo
   group by p.prd_codigo, p.prd_descricao
   having count(*) >= 2
   order by vendas desc;

3. select c.cli_codigo, c.cli_nome, count(*) qtd_pedido
   from pedidos p, clientes c
   where c.cli_codigo = p.cli_codigo
   group by c.cli_codigo, c.cli_nome;

Bom galera, por enquanto é só! Podem recolher os materiais e irem para o recreio Até a próxima aula!

Update: Para o xarope do Danilo que comentou aqui, vou criar um link para o site do professor Sérgio da onde tem provavelmente as mesmas tabelas… O xarope vem comentar besteira aqui no blog e falar que fiz ctrl-c e ctrl-v do site do professor, mas na verdade ele apenas está mostrando ser semi-analfabeto com seu comentário, pois foi incapaz de ler o que disse que usei as tabelas e nomes que foram passados na aula pelo mesmo professor do site que me dá aula também e na época eu nem lembrava o endereço do site dele ou sabia que estaria no site essa mesma tabela. Danilo querido, larga de ser xarope e não reclama, pois eu disse que foi a tabela passada na aula. Aprenda a ler com calma antes de sair escrevendo besteira.

Ah, o site do professor Sérgio é esse: http://www.ensinalegal.com.br

Prontinho Danilo… Ficou feliz?

Technorati Tags: SQL, Banco de Dados, MySQL, database, banco, aula, segunda, segunda aula, join, junção, agrupamento, group by, cláusula, jumento, severino cavalcanti, mula guerreira, cedral, cedrar, vô kiko, sum, order by, having, order, where, insert, consulta, jumentos, mula, código, codigo, id, cod, exercícios, exercício, golfinho, dolphin, faculdade, escola, estudos, post, Software Livre, liberdade, blog, M$, SQL Server, $QL $erver, inserir, tabelas, table

Powered by ScribeFire.