with ORDERS as 
(select 1 as order_id, 1 as client_id from dual
 union all  
 select 1, 1 from dual
 union all   
 select 1, 2 from dual
 union all  
 select 1, 2 from dual
 union all  
 select 1, 3 from dual),
 Client as /*таблица с клиентами*/
 (select 1 as client_id, 'Иванов Иван' as client_name, 2 as seller_id from dual
  union all
  select 2, 'Пупкин Василий', 3 from dual
  union all
  select 3, 'Аристарх Перьеславский', 1 from dual),
 Seller as /*таблица с продавцами*/
 (select 1 as Seller_id, 'Продавец 1' as  Seller_name from dual
  union all
  select 2, 'Продавец 2' from dual
  union all
  select 3, 'Продавец 3' from dual) 
select count(o.CLIENT_ID) as "Кол-во заказов",
       max(c.client_name) as "Имя клиента",
       max(s.seller_name) as "Имя продавца"
  from ORDERS o
  join Client c 
    on c.client_id = o.CLIENT_ID
  join Seller s 
    on s.seller_id = c.seller_id
 group by o.CLIENT_ID
having count(o.CLIENT_ID) > 1;