Profissionais TI - Pra quem respira informação

Esse post irá abranger praticamente toda a parte prática de um curso de Banco de Dados I ministrado em um curso de Sistemas de Informação. Trabalharemos criando um banco de dados em MySQL e logo após trabalhando com consultas simples e outras um pouquinho mais complexas. Para você que está começando, não deixe de fazer todas as queries e praticar. Como tudo em TI, com a prática que aprendemos de verdade 😀

Mão a obra!

Criando o Banco de Dados

Trabalharemos com um banco de dados sobre uma plantação. Sua estrutura está representada nao diagrama abaixo:

Com o digrama fica fácil criar o BD, não é mesmo? Sugiro que você trabalhe somente em linha de comando, essa é a melhor maneira de realmente entender o que está fazendo. Ferramentas gráficas são ótimas, mas é fundamental que você tenha domínio do tema. Os scripts de criação das tabelas que utilizei estão abaixo.

[sourcecode language=”sql”]

CREATE TABLE `canteiro` (
`canteiroid` INTEGER(11) NOT NULL AUTO_INCREMENT,
`nome` CHAR(20) COLLATE DEFAULT NULL,
`luzdiaria` INTEGER(2) DEFAULT NULL,
`agua` INTEGER(3) DEFAULT NULL,
PRIMARY KEY (`canteiroid`),
UNIQUE KEY `canteiroid` (`canteiroid`)
)

CREATE TABLE `funcionario` (
`funcid` INTEGER(11) NOT NULL AUTO_INCREMENT,
`nome` CHAR(80) COLLATE NOT NULL DEFAULT ”,
`idade` INTEGER(2) UNSIGNED DEFAULT NULL,
PRIMARY KEY (`funcid`),
UNIQUE KEY `funcid` (`funcid`)
)

CREATE TABLE `planta` (
`ID` INTEGER(11) NOT NULL AUTO_INCREMENT,
`nome` CHAR(20) COLLATE NOT NULL DEFAULT ”,
`luzdiaria` INTEGER(2) DEFAULT NULL,
`agura` INTEGER(2) DEFAULT NULL,
`peso` INTEGER(2) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `ID` (`ID`)
)

CREATE TABLE `plantio` (
`plantioID` INTEGER(11) NOT NULL AUTO_INCREMENT,
`plantaID` INTEGER(11) NOT NULL,
`funcID` INTEGER(11) NOT NULL ,
`canteiroID` INTEGER(11) NOT NULL,
`Data` DATE DEFAULT NULL,
`sementes` INTEGER(4) DEFAULT 0 NOT NULL,
PRIMARY KEY (`plantioID`),
FOREIGN KEY(`plantaID`) REFERENCES planta(ID),
FOREIGN KEY(`funcID`) REFERENCES funcionario(funcID),
FOREIGN KEY(`plantaID`) REFERENCES canteiro(canteiroID),
UNIQUE KEY `ID` (`plantioID`)
)

CREATE TABLE `colhido` (
`colhidoID` INTEGER(11) NOT NULL AUTO_INCREMENT,
`plantaID` INTEGER(11) NOT NULL,
`funcID` INTEGER(11) NOT NULL ,
`canteiroID` INTEGER(11) NOT NULL,
`Data` DATE DEFAULT NULL,
`quantidade` INTEGER(4) DEFAULT 0 NOT NULL,
`peso` DOUBLE(4,3) DEFAULT 0 NOT NULL,
PRIMARY KEY (`colhidoID`),
FOREIGN KEY(`plantaID`) REFERENCES planta(ID),
FOREIGN KEY(`funcID`) REFERENCES funcionario(funcID),
FOREIGN KEY(`plantaID`) REFERENCES canteiro(canteiroID),
UNIQUE KEY `ID` (`colhidoID`)
)

[/sourcecode]

Lembre-se que ao utilizar esse script a ordem é muito importante! Como estamos trabalhando com chaves estrangeiras, é importante que as tabelas aonde estão localizadas tenham sido criadas para que possa existir o relacionamento.

Caso tenha criado uma tabela incorretamente, você pode exclui-la com o comando:

[sourcecode language=”SQL”]
DROP TABLE nome_da_tabela
[/sourcecode]

Você pode ainda alterar as colunas das suas tabelas já criadas. Para adicionar novas colunas em uma tabela existente

[sourcecode language=”SQL”]
ALTER TABLE funcionario ADD cpf varchar(20),rg varchar(15)
[/sourcecode]

Alterando um campo já existente na tabela

[sourcecode language=”SQL”]
ALTER TABLE funcionario ALTER COLUMN cpf varchar(14)
[/sourcecode]

Removendo colunas em uma tabela:

[sourcecode language=”SQL”]
ALTER TABLE funcionario DROP COLUMN cpf, rg
[/sourcecode]

Para que possamos testar os comandos que serão executados, vamos inserir alguns dados em nossas tabelas. Como exemplos, vamos inserir uma planta na tabela planta:

[sourcecode language=”SQL”]
INSERT INTO planta (nome, luzdiaria, agua,peso )
VALUES (‘abacaxi’, 3.0, 5.25, 1.5)
[/sourcecode]

Adicione outros campos para que possamos testar as queries que executaremos a seguir. Seguem algumas sugestões:

[sourcecode language=”SQL”]
INSERT INTO `planta` (`nome`,`luzdiaria`,`agua`,`peso`)
VALUES (‘tomate’,0.03,0.03,0.03);

INSERT INTO `funcionario`(`funcid`,`nome`,`idade`)
VALUES (25,’Gabriella Fonseca Ribeiro’,20);

INSERT INTO
`plantio`(`plantioID`,`plantaID`,`funcID`,`canteiroID`,`Data`,`sementes`)
VALUES (15,1,25,4,2011-06-07,8);
[/sourcecode]

Fique atento às chaves primárias das tabelas – Caso opte por inseri-las manualmente, elas devem ser ÚNICAS!

Consultas

Primeiramente iniciaremos com uma query simples, excluindo funcionarios com menos de 18 anos. Para isso informe o nome da tabela e na cláusula WHERE personalize o campo a ser observado na exclusão:

[sourcecode language=”sql”]
DELETE FROM funcionarios
WHERE idade< 18
[/sourcecode]

Selecione todos os funcionarios do banco.

[sourcecode language=”sql”]
SELECT * FROM funcionarios
[/sourcecode]

Lembre-se que o * retorna todas as colunas da tabela em questão. Para retornar somente o nome dos funcionários, tente:

[sourcecode language=”sql”]
SELECT nome FROM funcionarios
[/sourcecode]

Como trabalharemos com mais de uma tabela e também com subqueries, aconselho a criar alias, que são como instâncias das tabelas – a tabela receberá “outro nome” em nossa query para que possamos chama-la mais de uma vez, sem problemas. Personalize sua query retornando todos os nomes que contenham ‘Silva’.

[sourcecode language=”sql”]
SELECT tb_func.nome
FROM funcionarios tb_func
WHERE tb_func.nome LIKE ‘%Silva%’
[/sourcecode]

Começaremos agora a trabalhar com subqueries. Devemos identificar todos os plantios de banana e exclui-los de nossa base de dados. Para que possamos excluir algo aconselho sempre a trabalhar com chaves primárias, a fim de não excluir registros indesejados. Como devemos primeiramente identificar quais são os plantios de banana, crie uma query para isso. Na query a seguir retornarei todos os ids em que existam plantas com o nome ‘banana’.

[sourcecode language=”sql”]
SELECT planta.`ID` FROM planta WHERE planta.`nome` LIKE ‘%banana%’
[/sourcecode]

Após identificar quais são os ids que eu quero excluir, utilizando o comando IN para que eu selecione “todos de uma vez”, eu monto minha query de exclusão. Dentro dos parenteses de IN estará o comando que criei anteriormente, aonde estarão selecionados todos os ids de banana.

[sourcecode language=”sql”]
DELETE FROM plantio WHERE
plantio.`plantioID` IN (SELECT planta.`ID` FROM planta WHERE planta.`nome` LIKE ‘%banana%’ )
[/sourcecode]

Agora trabalheremos com uma query um pouco maior: devemos retornar todos os funcionários que trabalharam com milho, seja na colheita ou plantio, em 2010. Utilizando o INNER JOIN retornarei todos os relacionamentos entre funcionários e plantios/colhido caso existem funcionários SOMENTE. Se ainda tem dúvidas sobre comandos com Join, consulte esse post.

Trabalhar com datas em SQL requer que as mesmas estejam dentro de “” e no formato “YYYY-MM-DD”. Caso você esteja precisando de dados de um ano qualquer não se esqueça que terá de selecionar, na cláusula WHERE, as datas entre o último dia do ano anterior e o último dia do ano que está trabalhando.

[sourcecode language=”sql”]
SELECT t1.`nome`
FROM funcionario t1
INNER JOIN plantio ON plantio.`funcID` = t1.`funcid`
INNER JOIN colhido ON colhido.`funcID` = t1.`funcid`
WHERE
((plantio.`plantioID` IN (SELECT planta.`ID` FROM planta WHERE planta.`nome` LIKE ‘%milho%’ ))
OR
(colhido.`colhidoID` IN (SELECT planta.`ID` FROM planta WHERE planta.`nome` LIKE ‘%milho%’ )))
AND
(
((plantio.`Data` > ‘2009-12-31’) AND (plantio.`Data` < ‘2011-01-01’))
OR
((colhido.`Data` > ‘2009-12-31’) AND (colhido.`Data` < ‘2011-01-01’))
)
[/sourcecode]

Utilizando como base o comando acima, retorne todos os funcionários e suas idades, que trabalharam com mamão em 2008.

Selecione agora todos os funcionários que não trabalharam com mandioca em 2008. Utilize o comando NOT IN.

[sourcecode language=”sql”]
SELECT tbfunc.nome
FROM funcionario tbfunc
WHERE tbfunc.`funcid` NOT IN(

SELECT t1.`funcid`
FROM funcionario t1
INNER JOIN plantio ON plantio.`funcID` = t1.`funcid`
INNER JOIN colhido ON colhido.`funcID` = t1.`funcid`
WHERE
(
(plantio.`plantioID` IN (SELECT planta.`ID` FROM planta WHERE planta.`nome` LIKE ‘%mandioca%’ ))
OR
(colhido.`colhidoID` IN (SELECT planta.`ID` FROM planta WHERE planta.`nome` LIKE ‘%mandioca%’ )))

AND
(
((plantio.`Data` > ‘2007-12-31’) AND (plantio.`Data` < ‘2009-01-01’))
OR
((colhido.`Data` > ‘2007-12-31’) AND (colhido.`Data` < ‘2009-01-01’))
)

)

)
[/sourcecode]

Informe o peso total de tomate que foi colhido em 2010. Utilize o comando SUM para somar os valores de uma coluna – lembre-se que esse comando aplica-se somente à campos numéricos!

[sourcecode language=”sql”]
SELECT SUM(colhido.`peso`) AS ‘Peso Total’
FROM colhido
WHERE
((colhido.`colhidoID` IN (SELECT planta.`ID` FROM planta WHERE planta.`nome` LIKE ‘%tomate%’ )))
AND
((colhido.`Data` > ‘2009-12-31’) AND (colhido.`Data` < ‘2011-01-01’))
[/sourcecode]

Por fim, para encerrar, screva uma instrução SQL que informe para cada tipo de hortaliças/legumes quantos quilos de produto se pode colher a partir de cada quilograma de semente. Utilizando AS nos podemos renomear os nomes das colunas do resultado da query – essa alteração é visível somente no resultado da query, e não influencia no resultado ou estrutura das tabelas. Em relação à conta, estou aplicando uma média comum, dividindo a soma total de Kgs colhidos ao longo dos anos, pelas sementes plantadas.

[sourcecode language=”sql”]
SELECT planta.`nome` as ‘Planta’, (SUM(colhido.`peso`)) as ‘Total colhido em KG’,
(SUM(plantio.`sementes`)) AS ‘Total de Sementes Plantadas’,
(‘Total colhido em KG’/’Total de Sementes Plantadas’) AS ‘Estimativa de KG/semente’
FROM planta
LEFT JOIN colhido ON ID
LEFT JOIN plantio ON ID
GROUP BY planta.`ID`
ORDER BY `planta`.`nome`[/sourcecode]

Bom, é isso! Caso tenha alguma dúvida, deixem comentários nesse post. Responderei o mais rápido possível. Espero que tenha gostado!

Com informações de Eu Faço Programas.