Skip to content

Instantly share code, notes, and snippets.

@CHERTS

CHERTS/product_and_order.sql

Created July 10, 2024 06:15
Show Gist options
  • Save CHERTS/0a964500d1c803232e92f6e09c64471f to your computer and use it in GitHub Desktop.
Save CHERTS/0a964500d1c803232e92f6e09c64471f to your computer and use it in GitHub Desktop.
Download ZIP
Product and orders examples database using PostgreSQL
Raw
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment