Duplicate Listing

drop table usr;
drop table team;

create table usr (
    usr_id int,
    usr_firstname varchar(50),
    usr_lastname varchar(50),
    usr_jobtitle varchar(50),
    usr_department varchar(50),
    usr_email varchar(50),
    match_firstname int,
    match_lastname int,
    match_jobtitle int,
    match_department int,
    match_email int,
    team_id int,
    primary key(usr_id)
);
create index ixusr_firstname on usr(usr_firstname);
create index ixusr_lastname on usr(usr_lastname);
create index ixusr_jobtitle on usr(usr_jobtitle);
create index ixusr_department on usr(usr_department);
create index ixusr_email on usr(usr_email);

create table team (
    team_id int,
    team_name varchar(50),
    team_streetno varchar(50),
    team_city varchar(50),
    match_team_name int,
    match_team_streetno int,
    match_team_city int,
    primary key(team_id)
);
create index ixteam_name on team(team_name);
create index ixteam_streetno on team(team_streetno);
create index ixteam_city on team(team_city);

delete from usr;

with recursive q(A) as (
    select 1
    union all
    select A+1
    from q
    where A < 70000
), rnd as (
    select ARRAY['John','Jim','Bob','Charles','Tom','Jane','Jill','Betty','Helen','Courtney'] vals
    --select ARRAY['John','Charles','Jill'] vals
)
insert into usr (usr_id, usr_firstname, usr_lastname, usr_jobtitle, usr_department, usr_email)
select row_number() over (order by 1),
       vals[1+floor(random()*6)],
       vals[1+floor(random()*6)],
       vals[1+floor(random()*6)],
       vals[1+floor(random()*6)],
       vals[1+floor(random()*6)]
from q, rnd;

update usr set
    match_firstname = ascii(usr_firstname)*10000 + ascii(substring(usr_firstname from 3))*100 + ascii(substring(usr_firstname from 4)),
    match_lastname = ascii(usr_lastname)*10000 + ascii(substring(usr_lastname from 3))*100 + ascii(substring(usr_lastname from 4)),
    match_jobtitle = ascii(usr_jobtitle)*10000 + ascii(substring(usr_jobtitle from 3))*100 + ascii(substring(usr_jobtitle from 4)),
    match_department = ascii(usr_department)*10000 + ascii(substring(usr_department from 3))*100 + ascii(substring(usr_department from 4)),
    match_email = ascii(usr_email)*10000 + ascii(substring(usr_email from 3))*100 + ascii(substring(usr_email from 4)),
    team_id = mod(usr_id, 50) + 1;

delete from team;

with recursive q(A) as (
    select 1
    union all
    select A+1
    from q
    where A < 50
), rnd as (
    select ARRAY['Alpha','Beta','Gamma','Delta','Epsilon'] vals
)
insert into team (team_id, team_name, team_streetno, team_city)
select row_number() over (order by 1),
       vals[1+floor(random()*5)],
       vals[1+floor(random()*5)],
       vals[1+floor(random()*5)]
from q, rnd;

update team set
    match_team_name = ascii(team_name)*10000 + ascii(substring(team_name from 3))*100 + ascii(substring(team_name from 4)),
    match_team_streetno = ascii(team_streetno)*10000 + ascii(substring(team_streetno from 3))*100 + ascii(substring(team_streetno from 4)),
    match_team_city = ascii(team_city)*10000 + ascii(substring(team_city from 3))*100 + ascii(substring(team_city from 4));