-- Create product_types table
CREATE TABLE product_types (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
-- Create customers table
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL
);
-- Create products table
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price NUMERIC(10, 2) NOT NULL,
product_types_id INTEGER REFERENCES product_types(id)
);
-- Create orders table
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
date TIMESTAMP NOT NULL,
customer_id INTEGER REFERENCES customers(id)
);
CREATE TABLE sales (
id SERIAL PRIMARY KEY,
product_id INTEGER REFERENCES products(id),
order_id INTEGER REFERENCES orders(id),
quantity INTEGER NOT NULL,
UNIQUE (product_id, order_id)
);
CREATE OR REPLACE FUNCTION generate_sales_id()
RETURNS TRIGGER AS $$
DECLARE
existing_id INTEGER;
BEGIN
-- Find an existing id for the same product_id and order_id
SELECT id INTO existing_id
FROM sales
WHERE product_id = NEW.product_id AND order_id = NEW.order_id;
IF existing_id IS NULL THEN
-- If there is no existing record, use the next value in the sequence for the id
SELECT nextval('sales_id_seq') INTO NEW.id;
ELSE
-- If there is an existing record, use the same id
NEW.id = existing_id;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Drop the existing trigger if it exists
DROP TRIGGER IF EXISTS generate_sales_id_trigger ON sales;
-- Create the trigger
CREATE TRIGGER generate_sales_id_trigger
BEFORE INSERT ON sales
FOR EACH ROW
EXECUTE FUNCTION generate_sales_id();
-- Insert values into product_types
INSERT INTO product_types (name) VALUES
('Online Course'),
('Webinar'),
('Book'),
('Consultation');
-- Insert values into products
INSERT INTO products (name, product_types_id, price) VALUES
('Fundamentals of Artificial Intelligence', 1, 15000),
('Big Data processing technologies', 1, 50000),
('Programming of deep neural networks', 1, 30000),
('Neural networks for text analysis', 1, 50000),
('Neural networks for image analysis', 1, 50000),
('Artificial Intelligence Engineering', 1, 60000),
('How to become a DataScientist', 2, 0),
('Career Planning in DataScience', 2, 2000),
('Areas of application of neural networks: in which to develop expertise', 2, 4000),
('Programming deep neural networks in Python', 3, 1000),
('Mathematics for DataScience', 3, 2000),
('Fundamentals of Data Visualization', 3, 500);
-- Insert values into customers
INSERT INTO customers (name, email) VALUES
('Ivan Petrov', 'petrov@mail.ru'),
('Peter Ivanov', 'ivanov@gmail.com'),
('Timofey Sergeev', 'ts@gmail.com'),
('Dasha Korneeva', 'dasha.korneeva@mail.ru'),
('Ivan Ivan', 'petrov@mail.ru'),
('Sergey Shcherbakov', 'user156@yandex.ru'),
('Katya Samarina', 'kate@mail.ru'),
('Andrey Kotov', 'a.kotoff@yandex.ru');
-- Insert values into orders
INSERT INTO orders (date, customer_id) VALUES
('2021-01-11', 1),
('2021-01-15', 3),
('2021-01-20', 4),
('2021-01-12', 2),
('2021-01-25', 8),
('2021-01-30', 1);
-- Insert values into sales
INSERT INTO sales (product_id, order_id, quantity) VALUES
(3, 1, 1),
(4, 6, 1),
(10, 2, 3),
(11, 2, 3),
(3, 3, 1),
(4, 3, 1),
(5, 3, 1),
(1, 4, 1),
(6, 5, 1),
(7, 5, 1),
(7, 5, 1);
select * from sales;
Telegram: DB developers channel