SQLize Online / PHPize Online  /  SQLtest Online

-- 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;