drop table u purge;
drop table d purge;
create table u (user_id number primary key, name varchar2(100));
create table d (user_id number, dis_id number, role_name varchar2(100), constraint d_pk primary key (user_id, dis_id, role_name));
insert into u values (1,'Кузовков');
insert into u values (2,'Пузиков');
insert into d values (2,2,'student');
insert into d values (2,1,'tutor');
insert into d values (1,1,'student');

select d1.role_name, d2.role_name, s.name
  from u s
        cross join d d1
        cross join u t 
        cross join d d2
where 
        d1.user_id = s.user_id and d1.role_name = 'student'
    and t.user_id = s.user_id 
    and t.user_id = d2.user_id and d2.role_name = 'tutor';