Resumo rápido – Consulta e criação de tabelas em SQL

AGRADEÇA AO AUTOR COMPARTILHE!

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 :D

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.

AGRADEÇA AO AUTOR COMPARTILHE!

Gabii Fonseca

Mais artigos deste autor »

Gabriella Fonseca Ribeiro tem 21 anos e cursa Sistemas de Informação. Trabalha com desenvolvimento, pesquisa e otimização de websites - SEO, marketing digital, redes sociais e comunicação interativa. || www.eufacoprogramas.com


8 Comentários

@gabii_fonseca
3

Jessy, para esses casos que descrevi, você consegue rodar da mesma maneira no SQL SERVER. Na verdade as mudanças de um motor de banco de dados com outro são bem pequenas, geralmente em relação a datas. Quando você escreve queries no ACESS, por exemplo, você utiliza o caracter # entre datas. No Oracle eu não me lembro, pois trabalhei pouco tempo com esse tipo de BD.

Paulo Marinho
6

Gabi, seu post é ótimo para o pessoal que não tem muita noção de banco, mas faltou o comando CREATE database plantacao, antes de começar a criar as tabelas, para ficar certinho.

@gabii_fonseca
7

Paulo, geralmente a criação eu faço pela ferramenta visual mesmo, aí até esqueci :P
Já a criação de tabelas, relações e tudo mais prefiro fazer por comandos. Acho importante, até porque, nos projetos, gente é meio que obrigado a saber digitar mesmo, né?

Abraços,

Paulo Marinho
8

Com certeza, nos projetos geralmente temos um acesso restrito ao banco, e a criação de um banco, mas no contexto do seu projeto exemplo, criar pela linha de comando seria o mais didático.

Abraços;

Deixe seu comentário

Seu endereço de e-mail não será publicado. Campos com * são obrigatórios!

Você pode usar estas tags e atributos de HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">