create database buyerdb;
use buyerdb;
CREATE TABLE buyer
(
  buyer_id int not null key auto_increment,
  buyer_name varchar(20) not null
);
CREATE TABLE product
(
  product_id int not null primary key auto_increment,
  product_name varchar(20) not null
);
CREATE TABLE orders
(
  order_id int not null primary key auto_increment,
  buyer_id int not null,
  product_id int not null,
  qty int not null
);
alter table orders
	add foreign key (buyer_id)
	references buyer (buyer_id);
alter table orders
	add foreign key (product_id)
	references product (product_id);

insert into buyer values (1,'ABC Trade');
insert into buyer values (2,'DEF Logistics');

insert into product values (1,'Watermelon');
insert into product values (2,'Cabbage');
insert into product values (3,'Banana');

insert into orders values (1,1,1,20);
insert into orders values (2,1,1,15);
insert into orders values (3,1,2,150);
insert into orders values (4,1,3,300);
insert into orders values (5,1,3,100);
insert into orders values (6,2,1,30);
insert into orders values (7,2,2,100);
insert into orders values (8,2,2,120);
insert into orders values (9,2,3,150);
insert into orders values (10,2,3,50);

select * from buyer;
select * from product;
select * from orders;

select *
from buyer cross join orders;

select
	buyer.buyer_id as 'Buyer ID',
    buyer.buyer_name,
    orders.order_id,
    orders.qty
from orders
	inner join buyer
    on orders.buyer_id=buyer.buyer_id;

select
    orders.order_id,
    buyer.buyer_name,
    product.product_name,
    orders.qty
from orders
inner join buyer on orders.buyer_id=buyer.buyer_id
inner join product on orders.product_id=product.product_id;

select
	product.product_id,
    product.product_name,
    orders.order_id,
    orders.buyer_id,
    orders.qty
from product
left outer join orders
on product.product_id=orders.product_id;

select
	product.product_id,
    product.product_name,
    orders.order_id,
    orders.buyer_id,
    orders.qty
from product
left outer join orders
on product.product_id=orders.product_id
and product.product_name<>'Watermelon';

select product_id, buyer_id, qty
from orders
order by buyer_id, product_id;

select buyer_id, product_id, sum(qty)
from orders
group by buyer_id, product_id
order by buyer_id, product_id;

select buyer_id, product_id, sum(qty)
from orders
group by buyer_id, product_id
having sum(qty)>=200
order by buyer_id, product_id;

-- drop table orders;
-- drop table buyer;
-- drop table product;