SQLize | PHPize | SQLtest

create table if not exists client ( client_id serial primary key, first_name varchar(40) not null, last_name varchar(40) not null, email VARCHAR(100) NOT NULL CHECK (email ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$'), phone VARCHAR(20) NOT NULL CHECK (phone ~ '^[0-9]+$') ); create table if not exists grape ( grape_id serial primary key, name varchar(40) not null ); create table if not exists country ( country_id serial primary key, name varchar(40) not null ); create table if not exists producer ( producer_id serial primary key, name varchar(40) not null, country_id integer not null, foreign key(country_id) references country(country_id), address text, phone VARCHAR(20) NOT NULL CHECK (phone ~ '^[0-9]+$') ); create table if not exists ordering ( order_id serial primary key, client_id integer not null, foreign key(client_id) references client(client_id), order_date date not null, total_price INTEGER NOT NULL CHECK (total_price >= 0) ); create table if not exists visit ( visit_id serial primary key, client_id integer not null, foreign key(client_id) references client(client_id) ); create table if not exists product ( product_id serial primary key, name varchar(40) not null, description text, producer_id integer not null, foreign key(producer_id) references producer(producer_id), grape_id integer not null, foreign key(grape_id) references grape(grape_id), country_id integer not null, foreign key(country_id) references country(country_id), price INTEGER NOT NULL CHECK (price >= 0), stock_count integer check (stock_count >= 0) ); create table if not exists ordered_product ( ordered_product_id serial primary key, order_id integer not null, foreign key(order_id) references ordering(order_id), product_id integer not null, foreign key(product_id) references product(product_id), quantity integer check (quantity >= 0), price integer not null check (price >= 0) ); INSERT INTO client (first_name, last_name, email, phone) VALUES ('John', 'Doe', 'johndoe@example.com', '1234567890'); INSERT INTO client (first_name, last_name, email, phone) VALUES ('Jane', 'Smith', 'janesmith@example.com', '9876543210'); INSERT INTO client (first_name, last_name, email, phone) VALUES ('Michael', 'Johnson', 'michael@example.com', '5555555555'); INSERT INTO client (first_name, last_name, email, phone) VALUES ('Sarah', 'Davis', 'Sarah@example.com', '1111111111'); INSERT INTO client (first_name, last_name, email, phone) VALUES ('Robert', 'Wilson', 'robert@example.com', '1234567890'); INSERT INTO client (first_name, last_name, email, phone) VALUES ('Emily', 'Anderson', 'emily@example.com', '9999999999'); INSERT INTO client (first_name, last_name, email, phone) VALUES ('David', 'Brown', 'david@example.com', '1234567890'); INSERT INTO client (first_name, last_name, email, phone) VALUES ('Jessica', 'Miller', 'JESSICA@EXAMPLE.COM', '5555555555'); INSERT INTO client (first_name, last_name, email, phone) VALUES ('Elizabeth', 'White', 'elizabeth@example.com', '1234567890'); INSERT INTO client (first_name, last_name, email, phone) VALUES ('Sarah', 'Johnson', 'sarah@example.com', '9876543210'); INSERT INTO grape (name) VALUES ('Cabernet Sauvignon'); INSERT INTO grape (name) VALUES ('Chardonnay'); INSERT INTO grape (name) VALUES ('Pinot Noir'); INSERT INTO grape (name) VALUES ('Merlot'); INSERT INTO grape (name) VALUES ('RIESLING'); INSERT INTO grape (name) VALUES ('Sémillon'); INSERT INTO grape (name) VALUES ('Sauvignon Blanc 2019'); INSERT INTO grape (name) VALUES ('Grenache/Syrah'); INSERT INTO grape (name) VALUES ('Shuyka'); INSERT INTO grape (name) VALUES ('KIndzmarauli'); INSERT INTO country (name) VALUES ('United States'); INSERT INTO country (name) VALUES ('France'); INSERT INTO country (name) VALUES ('Italy'); INSERT INTO country (name) VALUES ('Spain'); INSERT INTO country (name) VALUES ('Argentina'); INSERT INTO country (name) VALUES ('Australia'); INSERT INTO country (name) VALUES ('Germany'); INSERT INTO country (name) VALUES ('Portugal'); INSERT INTO country (name) VALUES ('New Zealand'); INSERT INTO country (name) VALUES ('Chile'); INSERT INTO producer (name, country_id, address, phone) VALUES ('Winery A', 1, '123 Main St, Anytown, USA', '5551234'); INSERT INTO producer (name, country_id, address, phone) VALUES ('Vineyard B', 2, '456 Vineyard Ln, Somewhere, France', '5555678'); INSERT INTO producer (name, country_id, address, phone) VALUES ('Cellar C', 3, '789 Cellar Rd, Anywhere, Italy', '5559012'); INSERT INTO producer (name, country_id, address, phone) VALUES ('Bodega D', 4, '321 Bodega Ave, Nowhere, Spain', '5553456'); INSERT INTO producer (name, country_id, address, phone) VALUES ('Winery E', 5, '654 Winery Dr, Anyplace, Argentina', '5557890'); INSERT INTO producer (name, country_id, address, phone) VALUES ('Vineyard F', 6, '987 Vineyard Blvd, Elsewhere, Australia', '5552345'); INSERT INTO producer (name, country_id, address, phone) VALUES ('Cellar G', 7, '210 Cellar Ln, Anycity, Germany', '5556789'); INSERT INTO producer (name, country_id, address, phone) VALUES ('Bodega H', 8, '543 Bodega Rd, Noway, Portugal', '5550123'); INSERT INTO producer (name, country_id, address, phone) VALUES ('Winery I', 9, '876 Winery Ave, Anyvillage, New Zealand', '5554567'); INSERT INTO producer (name, country_id, address, phone) VALUES ('Vineyard J', 10, '109 Vineyard St, Elsevilla, Chile', '5558901'); INSERT INTO ordering (client_id, order_date, total_price) VALUES (1, '2023-05-23', 50); INSERT INTO ordering (client_id, order_date, total_price) VALUES (2, '2023-05-24', 75); INSERT INTO ordering (client_id, order_date, total_price) VALUES (3, '2023-05-25', 100); INSERT INTO ordering (client_id, order_date, total_price) VALUES (4, '2023-05-26', 125); INSERT INTO ordering (client_id, order_date, total_price) VALUES (5, '2023-05-27', 150); INSERT INTO ordering (client_id, order_date, total_price) VALUES (6, '2023-05-28', 175); INSERT INTO ordering (client_id, order_date, total_price) VALUES (7, '2023-05-29', 200); INSERT INTO ordering (client_id, order_date, total_price) VALUES (8, '2023-05-30', 225); INSERT INTO ordering (client_id, order_date, total_price) VALUES (9, '2023-05-31', 250); INSERT INTO ordering (client_id, order_date, total_price) VALUES (10, '2023-06-01', 275); INSERT INTO visit (client_id) VALUES (1); INSERT INTO visit (client_id) VALUES (2); INSERT INTO visit (client_id) VALUES (3); INSERT INTO visit (client_id) VALUES (4); INSERT INTO visit (client_id) VALUES (5); INSERT INTO visit (client_id) VALUES (6); INSERT INTO visit (client_id) VALUES (7); INSERT INTO visit (client_id) VALUES (8); INSERT INTO visit (client_id) VALUES (9); INSERT INTO visit (client_id) VALUES (10); INSERT INTO product (name, description, producer_id, grape_id, country_id, price, stock_count) VALUES ('Product 1', 'Description 1', 1, 1, 1, 50, 10); INSERT INTO product (name, description, producer_id, grape_id, country_id, price, stock_count) VALUES ('Product 2', 'Description 2', 2, 2, 2, 75, 20); INSERT INTO product (name, description, producer_id, grape_id, country_id, price, stock_count) VALUES ('Product 3', 'Description 3', 3, 3, 3, 100, 30); INSERT INTO product (name, description, producer_id, grape_id, country_id, price, stock_count) VALUES ('Product 4', 'Description 4', 4, 4, 4, 125, 40); INSERT INTO product (name, description, producer_id, grape_id, country_id, price, stock_count) VALUES ('Product 5', 'Description 5', 5, 5, 5, 150, 50); INSERT INTO product (name, description, producer_id, grape_id, country_id, price, stock_count) VALUES ('Product 6', 'Description 6', 6, 6, 6, 175, 60); INSERT INTO product (name, description, producer_id, grape_id, country_id, price, stock_count) VALUES ('Product 7', 'Description 7', 7, 7, 7, 200, 70); INSERT INTO product (name, description, producer_id, grape_id, country_id, price, stock_count) VALUES ('Product 8', 'Description 8', 8, 8, 8, 225, 80); INSERT INTO product (name, description, producer_id, grape_id, country_id, price, stock_count) VALUES ('Product 9', 'Description 9', 9, 9, 9, 250, 90); INSERT INTO product (name, description, producer_id, grape_id, country_id, price, stock_count) VALUES ('Product 10', 'Description 10', 10, 10, 10, 275, 100); INSERT INTO ordered_product (order_id, product_id, quantity, price) VALUES (1, 1, 1, 50); INSERT INTO ordered_product (order_id, product_id, quantity, price) VALUES (2, 2, 2, 75); INSERT INTO ordered_product (order_id, product_id, quantity, price) VALUES (3, 3, 3, 100); INSERT INTO ordered_product (order_id, product_id, quantity, price) VALUES (4, 4, 4, 125); INSERT INTO ordered_product (order_id, product_id, quantity, price) VALUES (5, 5, 5, 150); INSERT INTO ordered_product (order_id, product_id, quantity, price) VALUES (6, 6, 6, 175); INSERT INTO ordered_product (order_id, product_id, quantity, price) VALUES (7, 7, 7, 200); INSERT INTO ordered_product (order_id, product_id, quantity, price) VALUES (8, 8, 8, 225); INSERT INTO ordered_product (order_id, product_id, quantity, price) VALUES (9, 9, 9, 250); INSERT INTO ordered_product (order_id, product_id, quantity, price) VALUES (10, 10, 10, 275); INSERT INTO client (first_name, last_name, email, phone) VALUES ('John', 'Smith', 'johnsmith@example.com', '1234567890'); SELECT * FROM client WHERE client_id = 1; UPDATE client SET email = 'john.smith@example.com' WHERE client_id = 1; -- Эта функция возвращает список заказов (идентификатор заказа, дата заказа, общая стоимость) для указанного идентификатора клиента. CREATE FUNCTION GetClientOrders(client_id INTEGER) RETURNS TABLE ( order_id INTEGER, order_date DATE, total_price INTEGER ) AS $$ BEGIN RETURN QUERY SELECT order_id, order_date, total_price FROM ordering WHERE client_id = client_id; END; $$ LANGUAGE plpgsql;