1

Topic: The task about layout

There are two essences - departments (deps) and little tables (little tables). In frames  it is necessary to allocate employees of each of departments behind a separate little table. Employees of each department can should be allocated no more than behind one little table. And on the contrary: employees of one department can sit at one little table only.
id - the department or little table identifier. qty - an amount of employees of department or an amount of places behind a little table.
Example of the data:

drop table deps;
drop table tables;
create table deps (id number, qty number);
create table tables (id number, qty number);
insert into deps values (1,100);
insert into deps values (2,50);
insert into deps values (3,40);
insert into deps values (4,10);
insert into tables values (101,100);
insert into tables values (102,100);
insert into tables values (103,40);
insert into tables values (104,10);

Prompt , whether probably to solve the given task by means of SQL WITHOUT recursion usage?

2

Re: The task about layout

artemiuss;
Well I at all do not know... I.e. I did not understand a question. I.e. I do not see a question in the task, but I can offer the decision for example the such:

with deps (id, qty) as
(select 1,100 from dual union all
select 2,50 from dual union all
select 3,40 from dual union all
select 4,10 from dual
);
tables (t_id, t_qty) as
(select 101,100 from dual union all
select 102,100 from dual union all
select 103,40 from dual union all
select 104,10 from dual
)
select d.id, t.t_id,
case when d.qty <= t.t_qty then ' ok'
else ' no'
end fit_for
from
(select id, qty, row_number () over (order by qty desc) drn from deps) d
left join
(select t_id, t_qty, row_number () over (order by t_qty desc) trn from tables) t on (d.drn = t.trn)

I.e. If will be all fit_for = ok - that can be allocated. If a question it will be optimal-whether allocate? - Is not present not optimally

3

Re: The task about layout

MaximaXXL;
Yes, all is true, thanks. It is a problem from interview. A counter, just in absence requirements to allocate optimally. And I, as the normal person, tried to solve it by default taking into account such requirement. Now only  that this problem was on attentiveness of reading of a condition including smile)).