# Resolução Exercício 01

1. Selecione todos os produtos
* SELECT * FROM produto;
* SELECT idproduto,nome FROM produto;

1. Selecione todos os produtos da categoria cereais
* SELECT * FROM produto WHERE categoria='cereais'

1. Listar produtos que não possuem estoque na loja
* SELECT * FROM produto WHERE estoque = 0

1. Listar todos os produtos que tenham o id par
* SELECT * FROM produto WHERE idproduto%2=0
* Selecione produtos aptos a participar de uma promoção pague 1, leve 2
* SELECT * FROM produto WHERE estoque%2=0 AND estoque>0
1. Mostrar os produtos que tenham estoque na loja e custem mais de 17 reais
* SELECT nome,estoque,valor_venda FROM produto WHERE estoque<>0 AND valor_venda>17
1. Selecione os produtos que custam entre 8 e 10 reais (responder com o operador relacional e o between)
* SELECT * FROM produto WHERE (valor_venda>=8) AND (valor_venda<=10);
* SELECT * FROM produto WHERE valor_venda BETWEEN 8 AND 10;
1. Selecione os produtos da categoria cereais e biscoitos que custam entre 20 e 30 reais
* SELECT * FROM produto WHERE (categoria='cereais' OR categoria='biscoitos') AND (valor_venda>=20 AND valor_venda<=30)
* SELECT nome, categoria FROM produto WHERE categoria in ('cereais','biscoitos') AND valor_venda BETWEEN 20 AND 30;

1. Selecione todos os produtos sabor chocolate
* SELECT * FROM produto WHERE nome LIKE '%chocolate%'

1. Selecione todas as margarinas que possuem alguma quantidade em estoque
* SELECT nome, estoque FROM produto WHERE nome LIKE '%margarina%' AND estoque>0
1. Selecione os produtos que foram inseridos entre os dias 17/03 e 25/03 de 2018 (responder com o operador relacional e o between)
* SELECT * FROM produto WHERE data_cadastro BETWEEN '2018-03-17' AND '2018-03-25'
* SELECT * FROM produto WHERE data_cadastro >= '2018-03-17' AND data_cadastro <= '2018-03-25'

1. Mostrar todas as categorias presentes na tabela produto
* SELECT DISTINCT categoria FROM produto
1. Selecione os produtos que caso recebam um desconto de 50% custarão mais de 15 reais
* SELECT nome FROM produto WHERE (valor_venda*0.5>15);
1. Número de produtos que foram inseridos na data de 12 de março de 2018
* CONTEÚDO NÃO ABORDADO
1. Mostre os produtos que caso tenham um aumento de 2 unidades no estoque passarão de 8 unidades
* SELECT * FROM produto WHERE estoque+2>8;
1. Listar os itens ou que pertençam a categoria cereais ou estejam com o estoque zerado
* SELECT idproduto, nome, categoria, estoque FROM produto WHERE (categoria='cereais' XOR estoque=0);

# Resolução Exercício 02

1. Selecione os produtos da categoria cereais e biscoitos utilizando o IN - Christin
* SELECT nome FROM produto WHERE categoria IN ("cereais", "biscoitos");

1. Selecione os produtos que foram cadastrados nos dias 2018-03-17, 2018-03-12,2018-03-29 - 15 - Rafael
* select * from produto where data_cadastro in('2018-03-17', '2018-03-12', '2018-03-29');

1. Apresente os 2 produtos mais caros do setor de cereais - 13 - Gustavo Sovrani
* SELECT * FROM mercado.produto WHERE categoria='cereais' ORDER BY valor_venda DESC LIMIT 2;

1. Qual o valor do terceiro item mais caro do setor de cereais - 25 - Vinicius CAntu
* SELECT * FROM produto WHERE categoria = 'cereais' ORDER BY valor_venda DESC LIMIT 2,1;

1. Mostre o produto que foi cadastrado a mais tempo - 17 - Leonardo Geremia
* SELECT * FROM produto ORDER BY data_cadastro LIMIT 1;

1. Apresente o top 3 de produtos mais caros do banco - 14 - Henrique
* SELECT * FROM produto ORDER BY valor_venda DESC LIMIT 3;

1. Mostre o produto que venceu a mais tempo no banco - 18 - Julia
* SELECT nome, data_vencimento FROM produto ORDER BY data_vencimento ASC LIMIT 1

1. Quantas unidades tem no estoque do setor de biscoitos - 20 - Winicios
* SELECT SUM(estoque) FROM produto WHERE categoria='biscoitos';

1. Quantas unidades de margarina se tem no estoque - 5 - Christian
* SELECT SUM(estoque) FROM produto WHERE nome LIKE "%margarina%";

1. Qual o preço médio de uma aveia - Guilher A.
* SELECT AVG(valor_venda) as Preco_Medio FROM produto WHERE nome LIKE "%aveia%"

1. Qual o valor médio de um produto da categoria laticínios - Érick Scur
* SELECT AVG(valor_venda) FROM produto WHERE categoria = 'laticínios';

<!--
11. Qual o preço médio de uma aveia - Guilherme A.
* ; -->

# Resolução Exercício 03

1. Inserir as seguintes linhas, de forma individual modelo (nome, categoria, valor_compra, valor_venda, estoque, data_cadastro, data_vencimento,fabricante) - Rafael Alan

* Bolacha de ãgua e sal, biscoitos, 2.8, 3.8, 5, 2018-07-06, 2019-06-11, Mabel;
* INSERT INTO produto (nome, categoria, valor_compra, valor_venda, estoque, data_cadastro, data_vencimento, fabricante) VALUES ('Bolacha de ãgua e sal', 'biscoitos', 2.8, 3.8, 5, '2018-07-06','2019-06-11', 'Mabel');

* Suco de uva integral, bebidas, 7, 9.7, 8, 2018-05-16, 2020-03-02 Serra;
* INSERT INTO produto (nome, categoria, valor_compra, valor_venda, estoque, data_cadastro, data_vencimento, fabricante) VALUES ('Suco de uva integral', 'bebidas', 7, 9.7, 8, '2018-05-16','2020-03-02', 'Serra');

* Café Torrado, bebidas, 4, 5.5, 12, 2018-08-21, 2021-03-12, Pilão;
* INSERT INTO produto (nome, categoria, valor_compra, valor_venda, estoque, data_cadastro,data_vencimento, fabricante) VALUES ('Café Torrado', 'bebidas', 4, 5.5, 12, '2018-08-21', '2021-03-12', 'Pilão');

* Chã mate torrado, bebidas, 1.6, 2.5, 6, 2018-08-04, 2020-06-25, Otto;
* INSERT INTO produto (nome, categoria, valor_compra, valor_venda, estoque, data_cadastro,data_vencimento, fabricante) VALUES ('Chã mate torrado', 'bebidas', 1.6, 2.5, 6, '2018-08-04','2020-06-25', 'Otto');

* Páprica doce, temperos vermelhos, 1.2, 2.1, 3, 2018-10-08, 2020-05-08, Ilfo;
* INSERT INTO produto (nome, categoria, valor_compra, valor_venda, estoque, data_cadastro,data_vencimento, fabricante) VALUES ('Páprica doce', 'temperos vermelhos', 1.2, 2.1, 3, '2018-10-08', '2020-05-08', 'Ilfo');

* Açafrão, temperos amarelos, 2.7, 3.5, 3, 2018-08-04, 2019-12-12, Ilfo;
* INSERT INTO produto (nome, categoria, valor_compra, valor_venda, estoque, data_cadastro,data_vencimento, fabricante) VALUES ('Açafrão', 'temperos amarelos', 2.7, 3.5, 3, '2018-08-04','2019-12-12', 'Ilfo');
* INSERT INTO produto SET nome='Açafrão', categoria='temperos amarelos', valor_compra=2.7, valor_venda=3.5, estoque=3, data_cadastro='2018-08-04',data_vencimento='2019-12-12', fabricante='Ilfo';

1. Atualizar o valor dos produtos que custem R$5 para R$4,99 - Guilherme Chaves
* UPDATE produto SET valor_venda = 4,99 WHERE valor_venda=5
1. Conferir um desconto de 15% a todos os produtos que custarem menos de R$10,00 - Julia
* UPDATE produto SET valor_venda= valor_venda - (valor_venda * 0.15) WHERE valor_venda < 10;
1. Atualizar para três itens o estoque do Suco Integral de Uva
* UPDATE produto SET estoque = 3 WHERE nome="Suco de Uva integral";
* UPDATE produto SET estoque = 3 WHERE idproduto=10;

1. Ajustar para 3 o estoque dos itens que já haviam sido zerados, aplicando o [dia atual](https://www.w3schools.com/mysql/func_mysql_now.asp) na data de cadastro e data de vencimento para [daqui a dois anos](https://www.w3schools.com/mysql/func_mysql_adddate.asp) - Gabriel.
* UPDATE produto SET estoque = 3 AND data_cadastro = '2021-10-06' AND data_vencimento = '2023-10-06' WHERE estoque = 0;
* UPDATE produto SET estoque=3, data_cadastro=NOW(), data_vencimento=ADDDATE(NOW(), INTERVAL 2 YEAR) WHERE estoque=0

1. Renomear a categoria laticínios para derivados de leite - Vinícius Cantu
* UPDATE mercado.produto SET categoria='derivados de leite' WHERE categoria='laticínios';

1. Ajustar os itens que foram inseridos no banco e estão com o acento errado (usar [replace](https://www.w3resource.com/mysql/string-functions/mysql-replace-function.php)) - Érick
* UPDATE produto SET nome=REPLACE(nome,'ã','á') WHERE categoria IN 'bebidas','biscoitos');
1. Criar uma única categoria temperos, para todas as categorias que façam menção a tempero - Gabrielle
* UPDATE produto SET (categoria = "temperos") WHERE categoria LIKE "%tempero%";
1. Alterar para natural onde contiver integral em determinados produtos - Guilherme Pereira
* UPDATE produto SET nome=REPLACE(nome, 'integral', 'natural') WHERE nome LIKE '%integral%';

1. Acrescentar mais um mês nos itens que foram cadastrados no dia 04 de agosto. - William Dutra
* UPDATE produto SET data_vencimento = ADDDATE(data_vencimento,INTERVAL 1 MONTH) WHERE data_cadastro= '2018-08-04';
* UPDATE produto SET data_vencimento = ADDDATE(data_vencimento,INTERVAL 1 MONTH) WHERE data_cadastro LIKE '%08-04';
* UPDATE produto SET data_vencimento = ADDDATE(data_vencimento,INTERVAL 1 MONTH) WHERE MONTH(data_cadastro)=08 AND DAY(data_cadastro)=04;

1. Consertar para Elfo os registros que foram inseridos erroneamente tendo a marca como Ilfo - CHistian
* UPDATE produto SET fabricante ="Elfo" WHERE fabricante = "Ilfo";
1. Deletar todos os registros dacategoria bebidas - Henrique
* DELETE FROM mercado.produto WHERE categoria='bebidas';
1. Deletar todos os itens cadastrados depois do dia 15/03/2018 e que são da categoria biscoitos - Leonardo
* DELETE FROM produto WHERE data_cadastro > '2018-03-15' AND categoria = 'biscoitos';
1. Deletar os registros dos fabricantes Nestle e Yoki - Bruno
* DELETE FROM produto WHERE fabricante="Nestle" OR fabricante="Yoki";
1. Deletar os itens que foram cadastrados
* DELETE FROM produto
1. Deletar todas as Aveias presentes no banco
* DELETE FROM produto WHERE nome LIKE "%aveia%"
1. Deletar registros da categoria cereais que estejam com o estoque zerado ou custem mais que R$20,00
* DELETE FROM produto WHERE categoria='cereais' AND (estoque=0 OR valor_venda>20)

# Resolução Exercício 04

1. Listar a quantidade de amigos do usuário de id 5 Leonardo Geremia
* select count(idsolicitante) from amizade where (idsolicitante=5 OR iddestinatario=5) AND idstatus=2;
* select count(u.idusuario) from usuario as u, amizade as a where (u.idusuario=a.idsolicitante OR u.idusuario=a.iddestinatario) AND u.idusuario=5 AND a.idstatus=2;
1. Listar os usuários com a maior quantidade de solicitações enviadas que ainda estão pendentes - Henrique Pinheiro
* SELECT u.nome,count(u.idusuario) AS qtdSolicitacoes FROM usuario AS u, amizade AS a WHERE u.idusuario = a.idsolicitante AND (a.idstatus=1) GROUP BY u.idusuario ORDER BY count(u.idusuario) DESC;
1. Listar quantidade de usuários por estado
* SELECT es.nome, count(u.idusuario) as qtdUsuario FROM usuario AS u, endereco AS en, cidade AS c, estado AS es WHERE u.idusuario = en.idendereco AND en.idcidade = c.idcidade AND c.idestado = es.idestado GROUP BY es.idestado ORDER BY es.idestado asc;
1. Apresentar a média de idade dos usuários por cidade
* select c.nome, avg(CEIL(DATEDIFF(now(), u.datanascimento)/365))AS idade from usuario as u, cidade as c, endereco as e where u.idendereco=e.idendereco and e.idcidade=c.idcidade group by c.idcidade order by c.idcidade;
* select teste.nome, avg(teste.idade) from (select c.idcidade,c.nome, ABS(CEIL(DATEDIFF(u.datanascimento, now())/365)) AS idade from usuario as u, cidade as c, endereco as e where u.idendereco=e.idendereco and e.idcidade=c.idcidade) as teste group by teste.idcidade;

1. Listar média de idade dos usuários nascidos em Fraiburgo
* SELECT AVG (TIMESTAMPDIFF(YEAR, usuario.datanascimento, curdate())) as media from usuario, endereco, cidade where usuario.idendereco = endereco.idendereco and endereco.idcidade = cidade.idcidade and cidade.nome = 'fraiburgo';

1. Listar nome dos usuários que não responderam aos pedidos de amizade feito pelo Pedro Thiago da Costa (id=4)
* select usuario.nome from usuario, amizade where usuario.idusuario = amizade.iddestinatario and amizade.idsolicitante = 4 and amizade.idstatus = 2;
1. Listar os três usuários com maior quantidade de amigos
* select u.nome,count(u.idusuario) as qtd_amigos from usuario as u, amizade as a where (u.idusuario=a.idsolicitante OR u.idusuario=a.iddestinatario) AND a.idstatus=2 GROUP BY u.idusuario order by qtd_amigos desc limit 3;

1. Listar quantidade de usuários maiores de idade por cidade - bruno
* SELECT c.nome, count((YEAR(NOW()) - YEAR(u.datanascimento))>=18) as maioresIdade FROM usuario as u, endereco as e, cidade as c WHERE u.idendereco = e.idendereco AND e.idcidade = c.idcidade GROUP BY c.idcidade;
* SELECT c.nome, count(u.idusuario) as maioresIdade FROM usuario as u, endereco as e, cidade as c WHERE u.idendereco = e.idendereco AND e.idcidade = c.idcidade AND (YEAR(NOW()) - YEAR(u.datanascimento))>=18 GROUP BY c.idcidade;
1. Listar estado do usuário mais velho do banco
* select u.nome, es.nome as estado, u.datanascimento from usuario as u, endereco as en, cidade as c, estado as es where u.idendereco=en.idendereco AND en.idcidade=c.idcidade AND c.idestado=es.idestado ORDER BY u.datanascimento LIMIT 1;
1. Listar o estado onde há o maior número de solicitações pendentes
* select es.nome as estado, count(a.idamizade) as qtd_solicitacoes from usuario as u, endereco as en, cidade as c, estado as es, amizade as a where u.idendereco=en.idendereco AND en.idcidade=c.idcidade AND c.idestado=es.idestado AND u.idusuario=a.idsolicitante AND a.idstatus=1 group by es.idestado order by qtd_solicitacoes desc limit 1;

# QUESTÕES PROVA 2020

1 INSERIR - Leonardo Geremeria
* Insert 01:
* INSERT INTO usuario(nome, email, senha, telefone, cpf, data_nascimento,tipo_usu) VALUES('Carlos Antônio', '[e-mail]', '54321','5549985476321', '36596585289', '1991-11-23', 2);
* INSERT INTO caderno(nome, idusuario) VALUES('Estudos', 1);
* INSERT INTO nota(titulo,texto,data,idcaderno) VALUES('Assuntos para estudar – exame banco', 'Dia 01', NOW(), 1);

* Insert 02:
* INSERT INTO usuario(nome, email, senha, telefone, cpf, data_nascimento,tipo_usu) VALUES('Geraldo Santos','[e-mail]','12345','5549988523625','25463556985','1986-06-15',1);
* INSERT INTO lembrete (nome, data_criacao, data_alarme, idusuario) VALUES('Regar as plantas', NOW(), CONCAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTHY), '07:15:00'), 1);

2. Listar a quantidade de usuários com cartões que vencerão esse mês - Henrique Martinelli
* SELECT count(c.idcartaocredito) FROM usuario as u, cartaocredito as c where u.idusuario=c.car_idusuario AND (mes_vencimento=MONTH(CURDATE()) AND ano_vencimento=YEAR(CURDATE()));

3. Listar todas as notas que pertençam as categorias com ids entre 5 e 35 - Erick Scur
* SELECT n.titulo, nc.idcategoria FROM nota as n, nota_categoria as nc
WHERE nc.idnota = n.idnota
AND nc.idcategoria BETWEEN 5 and 35;
* SELECT n.titulo, nc.idcategoria FROM nota as n, nota_categoria as nc
WHERE nc.idnota = n.idnota
AND (nc.idcategoria>= 5 and nc.idcategoria<= 35);

4. Listar todos os cadernos dos usuários que são premium - Winicius da rocha
* SELECT u.nome,u.tipo_usu, c.nome FROM caderno as c, usuario as u WHERE c.idusuario = u.idusuario AND u.tipo_usu = 2;

5. Apresentar a média de idade dos usuários que possuem notas criadas no último mês e notificações não lidas - Guilherme Amarilho
* select avg(idade) from (select u.nome, TIMESTAMPDIFF(YEAR, u.data_nascimento, now()) as idade from usuario as u, caderno as c, nota as nt, notificacao as nf WHERE (u.idusuario=c.cad_idusuario AND c.idcaderno=nt.not_idcaderno AND u.idusuario=nf.not_idusuario) AND (YEAR(nt.data)=YEAR(NOW()) AND MONTH(nt.data)=MONTH(NOW())) AND nf.data_leitura is null GROUP BY u.idusuario) as idade_usu

6. Apresentar os 10 usuário que tem a maior quantidade de lembretes criados no mês passado - Christian API
* SELECT u.nome count(l.idlembrete) from usuario as u, lembrete as l where u.idusuario = l.idusuario and l.data_criacao beteew data_sub(curdate(), interval 2 month) and date_sub(curdate(), interval 1 month) grope by u.nome order by count(l.idlembrete) desc limite 10;
* select u.nome, count(l.idlembrete) as quantidade from usuario as u, lembrete as l where u.idusuario=l.lem_idusuario AND (YEAR(l.data_criacao)=YEAR(NOW()) AND MONTH(l.data_criacao)=MONTH(NOW())) GROUP BY u.idusuario order by quantidade desc limit 10

7. Apresentar o valor médio da quantidade de notas associadas a cada categoria - Julia Schaedler
* SELECT AVG(qtd) FROM (SELECT c.nome, count(n.idnota) AS qtd FROM nota AS n, nota_categoria AS nc WHERE n.idnota= nc.idnota GROUP BY nc.idcategoria) AS x
* select avg(qtd) from (SELECT not_cat_idcategoria as categoria, count(not_cat_idnota) as qtd FROM evernote.nota_categoria group by not_cat_idcategoria) as quantidade

8. Substituir de @hotmail.com para @outlook.com em cada usuário que possuir esse tipo de email - Rafael Alan
* UPDATE usuario SET email = replace(email, '@hotmail.com', '@outlook.com');

9. Aumentar uma hora, na data de alarme de todos os lembretes que seriam para despertar no dia 25/12/2019 - Bruno Pergher
* UPDATE lembrete SET data_alarme = (DATE_ADD(data_alarme, INTERVAL 1 HOUR)) WHERE DATE_FORMAT(data_alarme, "%Y-%m-%d")="2021-11-05";

10. Alterar para categoria de id 5 todas as notas criadas pelo usuário de id 22 - Rafael Bressan
* UPDATE usuario as u, caderno as c, nota as n, nota_categoria as nc SET nc.not_cat_idcategoria=5 WHERE u.idusuario=c.cad_idusuario and c.idcaderno=n.not_idcaderno and n.idnota=nc.not_cat_idnota and u.idusuario=

11. Para as assinaturas feitas esse mês sem desconto, atualizar a coluna desconto para 20% e ajustar o valor da coluna valor.
* UPDATE assinatura SET desconto=0.2, valor=valor*0.8 WHERE DATE_FORMAT(data_assinatura, "%Y-%m")=DATE_FORMAT(NOW(), "%Y-%m")

12. Deletar todas as notas do usuário de id 7
* DELETE n, nt FROM usuario as u, caderno as c, nota as n, nota_categoria as nt WHERE u.idusuario=c.cad_idusuario AND c.idcaderno=n.not_idcaderno AND n.idnota=nt.not_cat_idnota AND u.idusuario=1

13. Deletar todos os cadernos dos usuários menores de idade
* DELETE c FROM usuario as u, caderno as c WHERE u.idusuario=c.cad_idusuario AND TIMESTAMPDIFF(YEAR, u.data_nascimento, now())<18