PostgreSQL: เก็บสินค้าและออเดอร์

create table products (
  id          bigint generated always as identity primary key,
  name        text not null,
  price       numeric(10, 2) not null check (price >= 0),
  stock       int not null default 0,
  created_at  timestamptz not null default now()
);

create table orders (
  id             bigint generated always as identity primary key,
  customer_name  text not null,
  total          numeric(10, 2) not null default 0,
  status         text not null default 'pending',
  created_at     timestamptz not null default now()
);

create table order_items (
  id          bigint generated always as identity primary key,
  order_id    bigint not null references orders(id) on delete cascade,
  product_id  bigint not null references products(id),
  quantity    int not null check (quantity > 0),
  price       numeric(10, 2) not null
);

-- เพิ่มข้อมูล (Create)
insert into products (name, price, stock)
values ('Latte', 65, 20), ('Croissant', 55, 10);

-- อ่านข้อมูล (Read)
select * from products;
select name, price from products where price < 60 order by price;

-- แก้ข้อมูล (Update)
update products set stock = stock - 1 where id = 1;

-- ลบข้อมูล (Delete)
delete from products where id = 2;

-- รวมตาราง: ออเดอร์นี้มีสินค้าอะไรบ้าง
select o.id, o.customer_name, p.name, oi.quantity
from orders o
join order_items oi on oi.order_id = o.id
join products p on p.id = oi.product_id;