2021-08-23练习

use sql_invoicing;
create view sales_by_client as
select 
	i.client_id,
    c.name,
    sum(i.invoice_total) as invoice
from invoices i
join clients c using(client_id)
group by i.client_id,c.name

create view clients_balance as
select 
	c.client_id,
    c.name,
    sum(invoice_total)-sum(payment_total) as balance
from clients c
join invoices i using(client_id)
group by c.client_id,c.name

create or replace view invoices_with_balance as
select
	invoice_id,
    number,
    client_id, 
    invoice_total,
    payment_total,
    invoice_total - payment_total as balance,
    invoice_date,
    due_date,
    payment_date
from invoices
where (invoice_date - payment_total) > 0

delete from invoices_with_balance 
where invoice_id = 1

update invoices_with_balance 
set due_date = date_add(due_date, interval 2 day)
where invoice_id = 2

update invoices_with_balance        
set payment_total = invoice_total
where invoice_id = 2

DELIMITER $$
CREATE PROCEDURE get_clients()
BEGIN
	SELECT * FROM clients;
END$$
DELIMITER ;

DELIMITER $$
create procedure get_invoices_with_balance()
begin
	select
		invoice_id, number, client_id, invoice_total, (invoice_total-payment_total) as balance, 
		payment_total, invoice_date, due_date, payment_date
	from invoices
	where (invoice_total-payment_total) > 0;
end$$

DELIMITER ;
drop procedure if exists get_clients;

delimiter $$
create procedure get_clients()
begin
	select * from clients;
end $$
delimiter ;


delimiter $$
create procedure get_clients_by_state(state char(2))
begin
select *
from clients c
where c.state = state;
end $$
delimiter ;

delimiter $$
create procedure get_invoices_by_client(client_id int)
begin
select *
from invoices i
where i.client_id = client_id;
end $$
delimiter ;