---

CREATE TABLE users (
    id    INT            PRIMARY KEY,
    name  VARCHAR(100)   NOT NULL,
    email VARCHAR(100)   NOT NULL,
    phone VARCHAR(20)    NULL
);

CREATE TABLE products (
    id    INT              PRIMARY KEY,
    name  VARCHAR(100)     NOT NULL,
    price DECIMAL(10, 2)   NOT NULL CHECK (price >= 0)
);

CREATE TABLE orders (
    id          INT              PRIMARY KEY,
    user_id     INT              NOT NULL,
    product     VARCHAR(100)     NOT NULL,
    amount      INT              NOT NULL CHECK (amount > 0),
    price       DECIMAL(10, 2)   NOT NULL CHECK (price >= 0),
    order_date  DATE             NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

INSERT INTO users VALUES (1, 'Ivan Ivanov', 'ivan@mail.com', '12345');
INSERT INTO users VALUES (2, 'Petr Petrov', 'petr@mail.com', NULL);
INSERT INTO users VALUES (3, 'Anna', 'anna@mail.ru', '99999');

INSERT INTO products VALUES (1, 'Phone', 10000);
INSERT INTO products VALUES (2, 'Laptop', 50000);

INSERT INTO orders VALUES (1, 1, 'Phone',  1, 10000, '2024-01-10');
INSERT INTO orders VALUES (2, 1, 'Phone',  2, 10000, '2024-01-10');
INSERT INTO orders VALUES (4, 2, 'Laptop', 1, 50000, '2024-03-01');
INSERT INTO orders VALUES (5, 2, 'Laptop', 1, 50000, '2024-03-05');

SELECT users.name, orders.product
FROM users
JOIN orders ON users.id = orders.user_id;

SELECT SUM(price * amount) AS total_revenue
FROM orders;

SELECT *
FROM orders
WHERE user_id = (SELECT id FROM users WHERE name = 'Ivan Ivanov');

SELECT name
FROM users
WHERE id NOT IN (SELECT user_id FROM orders);

SELECT product, AVG(price) AS avg_price
FROM orders
GROUP BY product;