SQLize Online / PHPize Online  /  SQLtest Online

create table positions(id integer PRIMARY KEY, title text NOT NULL); insert into positions values (1, 'Дизайнер'); insert into positions values (2, 'Технический писатель'); insert into positions values (3, 'Программист'); insert into positions values (4, 'Программист'); select * from positions; -- --create table persons (id int, name text, int pos_id, constaraint foreign key (pos_id) references positions(id)); CREATE TABLE persons ( id SERIAL, name text, pos_id integer REFERENCES positions (id) ); insert into persons values (1, 'Владимир', 1); insert into persons values (2, 'Мария', 3); insert into persons values (1, 'Никита', 2); select * from persons; --- create table transactions( id integer PRIMARY KEY, date_flow DATE NOT NULL, cash_flow integer); insert into transactions values (1, '2022-06-06', -1000); insert into transactions values (2, '2022-06-06', -100); insert into transactions values (4, '2022-06-08', 50); insert into transactions values (5, '2022-06-08', 150); insert into transactions values (6, '2022-06-09', -50); select * from transactions; create table orders ( id int, user_name text, tr_date date, product_id int,count int); insert into orders values (1, 'Виктор', '2024-07-03', 1, 4), (2, 'Михаил', '2024-07-04', 2, 1), (3, 'Михаил', '2024-07-05', 2, 1), (4, 'Мария', '2024-07-05', 1, 2), (5, 'Иван', '2024-07-06', 3, 2), (6, 'Иван', '2024-07-06', 1, 1), (7, 'Мария', '2024-07-06', 1, 2) ; select * from orders; -- Задача 1 select count(*) from ( select user_name, count(distinct tr_date) as dist_date from orders group by user_name having count(distinct tr_date) > 1 ) as T1 -- select title, max(id) as max_id, count(id) as cnt_id from positions group by title having count(id) > 1