Product and orders examples database using PostgreSQL

product_and_order.sql

CREATE TYPE products_unit AS ENUM ('Килограмм', 'Грамм', 'Литр', 'Метр', 'Пара', 'Штука');

CREATE TABLE IF NOT EXISTS products (
    product_no bigint PRIMARY KEY,
    price numeric,
    unit products_unit,
    active bool,
    name text
);

CREATE SEQUENCE "public"."product_no_seq" INCREMENT 1 MINVALUE 0 START 1;
ALTER TABLE "public"."products" ALTER COLUMN "product_no" SET DEFAULT nextval('product_no_seq'::regclass);
ALTER SEQUENCE "public"."product_no_seq" OWNED BY "public"."products"."product_no";

INSERT INTO products VALUES (nextval('product_no_seq'), 330.0, 'Килограмм', true, 'Яблоки гренни смит');
INSERT INTO products VALUES (nextval('product_no_seq'), 310.0, 'Килограмм', true, 'Яблоки малинка');
INSERT INTO products VALUES (nextval('product_no_seq'), 294.0, 'Килограмм', true, 'Яблоки гала');
INSERT INTO products VALUES (nextval('product_no_seq'), 268.0, 'Килограмм', true, 'Яблоки глостер');
INSERT INTO products VALUES (nextval('product_no_seq'), 303.0, 'Килограмм', true, 'Яблоки голден');
INSERT INTO products VALUES (nextval('product_no_seq'), 260.0, 'Килограмм', true, 'Яблоки джонагоред');
INSERT INTO products VALUES (nextval('product_no_seq'), 270.0, 'Килограмм', true, 'Яблоки женева');
INSERT INTO products VALUES (nextval('product_no_seq'), 270.0, 'Килограмм', true, 'Яблоки крипс пинк');
INSERT INTO products VALUES (nextval('product_no_seq'), 327.0, 'Килограмм', true, 'Яблоки пинк леди');
INSERT INTO products VALUES (nextval('product_no_seq'), 294.0, 'Килограмм', true, 'Яблоки пирус');
INSERT INTO products VALUES (nextval('product_no_seq'), 223.0, 'Килограмм', true, 'Яблоки смиренко');
INSERT INTO products VALUES (nextval('product_no_seq'), 266.0, 'Килограмм', true, 'Яблоки спартан');
INSERT INTO products VALUES (nextval('product_no_seq'), 313.0, 'Килограмм', true, 'Яблоки фуджи');
INSERT INTO products VALUES (nextval('product_no_seq'), 268.0, 'Килограмм', true, 'Яблоки чемпион');
INSERT INTO products VALUES (nextval('product_no_seq'), 251.0, 'Килограмм', true, 'Яблоки штрифель');
INSERT INTO products VALUES (nextval('product_no_seq'), 225.0, 'Килограмм', true, 'Яблоки айдаред');
INSERT INTO products VALUES (nextval('product_no_seq'), 268.0, 'Килограмм', true, 'Яблоки лигол');
INSERT INTO products VALUES (nextval('product_no_seq'), 305.0, 'Килограмм', true, 'Яблоки ред принц');
INSERT INTO products VALUES (nextval('product_no_seq'), 305.0, 'Килограмм', true, 'Яблоки ред чиф');
INSERT INTO products VALUES (nextval('product_no_seq'), 247.0, 'Килограмм', true, 'Яблоки сезонные');
INSERT INTO products VALUES (nextval('product_no_seq'), 277.0, 'Килограмм', true, 'Яблоки амброзия');
INSERT INTO products VALUES (nextval('product_no_seq'), 223.0, 'Килограмм', true, 'Яблоки антоновка');
INSERT INTO products VALUES (nextval('product_no_seq'), 349.0, 'Килограмм', true, 'Яблоки богатырь');
INSERT INTO products VALUES (nextval('product_no_seq'), 227.0, 'Килограмм', true, 'Яблоки бреберн');
INSERT INTO products VALUES (nextval('product_no_seq'), 211.0, 'Килограмм', true, 'Яблоки зимний банан');
INSERT INTO products VALUES (nextval('product_no_seq'), 199.0, 'Килограмм', true, 'Яблоки квин');
INSERT INTO products VALUES (nextval('product_no_seq'), 134.0, 'Килограмм', true, 'Яблоки мантет');
INSERT INTO products VALUES (nextval('product_no_seq'), 215.0, 'Килограмм', true, 'Яблоки прима');
INSERT INTO products VALUES (nextval('product_no_seq'), 179.0, 'Килограмм', true, 'Яблоки роза');
INSERT INTO products VALUES (nextval('product_no_seq'), 208.0, 'Килограмм', true, 'Яблоки синап');
INSERT INTO products VALUES (nextval('product_no_seq'), 194.0, 'Килограмм', true, 'Яблоки слава');
INSERT INTO products VALUES (nextval('product_no_seq'), 246.0, 'Килограмм', true, 'Яблоки флорина');

CREATE TYPE orders_status AS ENUM ('new', 'waiting-payment', 'paid', 'waiting-delivery', 'delivered', 'canceled', 'completed');

CREATE TABLE IF NOT EXISTS orders (
    order_id bigint PRIMARY KEY,
    status orders_status NOT NULL,
    shipping_address text,
    comments text
);

CREATE SEQUENCE "public"."order_id_seq" INCREMENT 1 MINVALUE 0 START 1;
ALTER TABLE "public"."orders" ALTER COLUMN "order_id" SET DEFAULT nextval('order_id_seq'::regclass);
ALTER SEQUENCE "public"."order_id_seq" OWNED BY "public"."orders"."order_id";

CREATE TABLE IF NOT EXISTS order_items (
    product_no bigint REFERENCES products ON DELETE RESTRICT,
    order_id bigint REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

SELECT * FROM products ORDER BY product_no;

INSERT INTO orders VALUES (nextval('order_id_seq'), 'new', 'г.Челябинск, Бульварный 6-ой переулок, д.3, кв.4', null);

SELECT * FROM orders ORDER BY order_id;

INSERT INTO order_items VALUES (1, 1, 1);
INSERT INTO order_items VALUES (12, 1, 2);
INSERT INTO order_items VALUES (23, 1, 1);

UPDATE products SET active = false WHERE product_no = 12;

SELECT o.order_id, o.status, o.shipping_address, p.name, oi.quantity, p.unit
    FROM orders o 
    LEFT JOIN order_items oi ON o.order_id = oi.order_id 
    LEFT JOIN products p ON p.product_no = oi.product_no
    WHERE p.active = true;

UPDATE orders SET status = 'canceled' WHERE order_id = 1;

SELECT * FROM orders ORDER BY order_id;

DELETE FROM orders WHERE order_id = 1;

SELECT * FROM orders;

SELECT * FROM order_items;

DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TYPE IF EXISTS orders_status;
DROP TYPE IF EXISTS products_unit;