I'm new here and I'm having a database doubt that I've been stuck since yesterday, I tried to solve it and nothing follows the following exercise:
With the Customer Table (customer_id, customer_name, new_document, child_name, etc.), the Transaction Table (customer_id, transaction_d, transaction_id, id_id, etc.) and the Retail Table (store_id, , write a Query to see each customer's spending totals for that month. Type another Query to see the total billing per store in the last month.
My biggest difficulty is in Query's, which 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 someone knows how to do this part of Query's, I appreciate it!