Doubt with Insert and Select

Question

I’m new around here and I’m having a database doubt that I’m stuck since yesterday, tried to solve and nothing, follows the next exercise:

Com a Tabela de Clientes (id_cliente, nm_cliente, nu_documento, dt_nascimento, etc), a Tabela de Transações (id_cliente, dt_transacao, vl_transacao, id_loja, etc) e a Tabela de Lojas (id_loja, nm_loja, etc), write a Query to see each customer’s total expenses for that month. Write another Query to see the total billing per store on last month.

My biggest difficulty is in Query’s, what I’ve done so far:

create table Clientes(
id_cliente integer(5) auto_increment,
nm_cliente char(10) not null,
nu_documento integer(11) not null,
dt_nascimento date not null,
constraint id_cliente_pk primary key (id_cliente));

create table Lojas(
id_lojas integer(5) auto_increment,
nm_loja char(10) not null,
constraint id_lojas_pk primary key (id_lojas));

create table Transacoes(
id_cliente_transacoes integer(5),
nm_cliente_transacoes char(10),
id_lojas_transacoes integer(5),
nm_loja_transacoes char(10),
dt_transacao date not null,
gasto_cliente integer(6.2) not null,
faturamento_total integer(6.2) not null,
constraint faturamento_total_pk primary key (faturamento_total),
constraint id_cliente_transacoes_fk foreign key (id_cliente_transacoes) references Clientes(id_cliente),
constraint id_lojas_transacoes_fk foreign key (id_lojas_transacoes) references Lojas(id_lojas));

insert into Clientes(nm_cliente, nu_documento, dt_nascimento) 
values ('Pedro', 238412, DATE '1982-04-02');
insert into Clientes(nm_cliente, nu_documento, dt_nascimento) 
values ('Vinicius', 214832, DATE '1999-01-09');

insert into Lojas(nm_loja) values ('Anapalu');
insert into Lojas(nm_loja) values ('Doceria SP');

insert into Transacoes(dt_transacao, gasto_cliente, faturamento_total)
values (DATE '2018-08-20', 1450.19, 2381.93);
insert into Transacoes(dt_transacao, gasto_cliente, faturamento_total) 
values (DATE '2018-07-17', 1094.33, 1928.92);

select nm_cliente, gasto_cliente from Clientes, Transacoes;
select nm_loja, faturamento_total, dt_transacao from Lojas, Transacoes order by dt_transacao;

If anyone knows how to do this part of Query’s I really appreciate it!

Answer 1

Total expenses of each customer in the month:

Select Clientes.nm_cliente, Sum(Transacoes.gasto_cliente) as gastos_mensal from Transacoes 
inner join Clientes on Transacoes.id_cliente_transacoes = Clientes.id_cliente 
group by Month(Transacoes.dt_transacao);

Sales of the Store:

Select Lojas.nm_loja, Sum(Transacoes.faturamento_total) as faturamento_lojas from
Lojas inner join Transacoes on Lojas.id_lojas = Transacoes.id_lojas_transacoes 
gorup by Month(Transacoes.dt_transacao) where 
Month(Transacoes.dt_transacao) = Month('ano/mes/dia') 
AND Year(Transacoes.dt_transacao) = Year('ano/mes/dia')