---

-- 1
CREATE USER nikita WITH PASSWORD '1243';

CREATE DATABASE mydb;

GRANT ALL PRIVILEGES ON DATABASE mydb TO nikita;

CREATE DATABASE store;

CREATE TABLE products
(
    id       INT,
    name     TEXT,
    quantity INT
);

INSERT INTO products (id, name, quantity)
VALUES (1, 'first product', 20);

SELECT id, name, quantity
FROM products;

SELECT *
FROM products;

UPDATE products
SET quantity=49
WHERE id = 1;

UPDATE products
SET name     = 'new name',
    quantity = 50
WHERE id = 1;

DELETE
FROM products
WHERE id = 1;

DROP DATABASE store;


-- 2

CREATE TABLE clients
(
    id          SERIAL PRIMARY KEY,
    last_name   VARCHAR(746)  NOT NULL,
    first_name  VARCHAR(2253) NOT NULL,
    middle_name VARCHAR(50),
    address     VARCHAR(50),
    phone       VARCHAR(16)
);

CREATE TABLE orders
(
    id         SERIAL PRIMARY KEY,
    order_date DATE,
    amount     NUMERIC(9, 2),
    client_id  INTEGER
);

ALTER TABLE orders
    ADD CONSTRAINT fk_client
        FOREIGN KEY (client_id)
            REFERENCES clients (id);

ALTER TABLE clients
    ADD COLUMN email VARCHAR(75);

ALTER TABLE clients
    DROP COLUMN email;

ALTER TABLE clients
    ALTER COLUMN phone TYPE VARCHAR(12);


-- 3

INSERT INTO clients
    (last_name, first_name, middle_name, address, phone)
VALUES ('Иванов', 'Иван', 'Иванович', 'Москва', '+7999999'),
       ('Петров', 'Петр', 'Петрович', 'Самара', '+7999998'),
       ('Сидоров', 'Сидор', 'Сидорович', 'Казань', '+7999988');

UPDATE clients
SET address = 'Тольятти'
WHERE id = 1;

DELETE
FROM clients
WHERE id = 3;

--
COPY clients (last_name, first_name, middle_name, address, phone)
    FROM '/tmp/clients_import.csv'
    WITH (FORMAT csv, HEADER true);


-- 4
SELECT *
FROM clients;

SELECT *
FROM orders
WHERE order_date >= '2019-01-01'
  AND order_date < '2020-01-01';

SELECT *
FROM clients
WHERE last_name = 'Иванов';

SELECT DISTINCT last_name
FROM clients;


-- 5
SELECT *
FROM clients
WHERE address LIKE '%Москва%';

SELECT *
FROM clients
ORDER BY last_name, first_name;

SELECT *
FROM orders
ORDER BY amount DESC
LIMIT 5;

SELECT *
FROM clients
WHERE last_name LIKE 'М%н';

SELECT o.*, c.last_name
FROM orders o
         JOIN clients c
              ON o.client_id = c.id
WHERE c.address LIKE '%Москва%';

SELECT c.address,
       SUM(o.amount) AS total_sales
FROM orders o
         JOIN clients c
              ON o.client_id = c.id
GROUP BY c.address;

SELECT *
FROM clients
WHERE id IN (SELECT client_id
             FROM orders
             WHERE amount > 1000);