Exemplos de consulta e criação de tabelas em SQL

junho 13, 2011

Olá! Esse post irá abranger praticamente toda a parte prática de um curso de Banco de Dados I ministrado na PUC. 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 o todos 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.


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`)
)

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:

DROP TABLE nome_da_tabela

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

ALTER TABLE funcionario ADD cpf varchar(20),rg varchar(15)

Alterando um campo já existente na tabela

ALTER TABLE funcionario ALTER COLUMN cpf varchar(14)

Removendo colunas em uma tabela:

ALTER TABLE funcionario DROP COLUMN cpf, rg

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:

INSERT INTO planta (nome, luzdiaria, agua,peso )
VALUES ('abacaxi', 3.0, 5.25, 1.5)

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

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);

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:

DELETE FROM funcionarios
WHERE idade< 18

Selecione todos os funcionarios do banco.

SELECT * FROM funcionarios

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

SELECT nome FROM funcionarios

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’.

SELECT tb_func.nome
FROM funcionarios tb_func
WHERE tb_func.nome LIKE '%Silva%'

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’.

SELECT planta.`ID` FROM planta WHERE planta.`nome` LIKE "%banana%"

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.

DELETE FROM plantio  WHERE
plantio.`plantioID` IN (SELECT planta.`ID` FROM planta WHERE planta.`nome` LIKE "%banana%" )

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.

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'))
)

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.

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'))
)

)

)

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!

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'))

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.

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`

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