---

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

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

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    product_id INT,
    amount INT CHECK (amount > 0),
    order_date DATE,
    
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

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

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

INSERT INTO orders VALUES
(1, 1, 1, 1, '2024-01-10'),
(2, 1, 1, 2, '2024-01-10'),
(3, 2, 2, 1, '2024-02-01');

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

SELECT SUM(products.price * orders.amount) AS total_sum
FROM orders
JOIN products ON products.id = orders.product_id;

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_id, AVG(amount)
FROM orders
GROUP BY product_id;