ttitle "Lecturers sharing Rooms"
COLUMN Name1 FORMAT A15 HEADING "Name"
COLUMN Name2 FORMAT A15 HEADING "Name"
COLUMN room_no FORMAT A15 HEADING "Room_no"
select name as "Name1", name as "Name2", r.room_no
from l, r;

create table l
(staff_no varchar2(3) constraint pk_l_no primary key,
initials varchar2(6),
name varchar2(20),
title varchar2(10));

create table r
(room_no varchar2(5) constraint pk_rno primary key,
room_size number);


create table u
(unit_code varchar2(6) constraint pk_u_code primary key,
name varchar2(30),
staff_no varchar2(3),
room_no varchar2(5),
constraint fk_u foreign key (staff_no) references l(staff_no),
constraint fk_u2 foreign key (room_no) references r(room_no));

insert into l values ('L1', 'A.M.','Goscinski', 'Professor');

insert into r values ('SB420', 200);

insert into u values ('SCC103', 'Intro. to I.T.', 'L5', 'SB420');

COLUMN Name1 FORMAT A15 HEADING "Name"
COLUMN Name2 FORMAT A15 HEADING "Name"
COLUMN room_no FORMAT A15 HEADING "Room_no"
select name as "Name1", name as "Name2", r.room_no
from l, r

insert into l values ('L1', 'A.M.','Goscinski', 'Professor');
insert into l values ('L2', 'G.I.','Webb', 'Professor');
insert into l values ('L3', 'B.J.','Garner', 'Professor');
insert into l values ('L4', 'C.Q.','Zhang', 'Ass. Prof.');
insert into l values ('L5', 'M.','Warren', 'Dr.');
insert into l values ('L6', 'B.B.','Zhou', 'Dr.');
insert into l values ('L7', 'D.','De Paoli', 'Dr.');
insert into l values ('L8', 'J.','Coldwell', 'Dr.'); 
insert into l values ('L9', 'J.J.','Silcock', 'Dr.');
insert into l values ('L10', 'D.A.','Newlands', 'Dr.');
insert into l values ('L11', 'P.','Horan', 'Mr.');
insert into l values ('L12', 'R.A.','Dew', 'Mr.');
insert into l values ('L13', 'L.','Liu', 'Ms.');
insert into l values ('L14', 'P.','Padhye', 'Dr.');


insert into r values ('SB420', 200);
insert into r values ('SC422', 80);
insert into r values ('SC424', 80);
insert into r values ('SC426', 80);
insert into r values ('SC429', 40);
insert into r values ('SB129', 50);
insert into r values ('SB137', 40);
insert into r values ('SB138', 50);
insert into r values ('LT1', 270);
insert into r values ('GD27', 130);
insert into r values ('SD464', 80);

insert into u values ('SCC103', 'Intro. to I.T.', 'L5', 'SB420');
insert into u values ('SCC104', 'Intro. to Software Dev.', 'L12', 'SB420');
insert into u values ('SCC105', 'Basic Programming Concepts', 'L12', 'SB420');
insert into u values ('SCC107', 'Concepts and Practice for S.E.', 'L11', 'SB420');
insert into u values ('SCC108', 'Database and Info. Retrieval', 'L10', 'SB420');
insert into u values ('SCC109', 'WWW and Internet Programming', 'L5', 'SB420');
insert into u values ('SCC203', 'Object Oriented Programming', 'L12', 'SB138');
insert into u values ('SCC204', 'Data Structures and Algorithms', 'L10', 'SB420');
insert into u values ('SCC208', 'Operating Systems', 'L7', 'SB137');
insert into u values ('SCC209', 'Systems Analysis and Design', 'L5', 'SC422');
insert into u values ('SCC210', 'Advanced Database', 'L4', 'SB137');
insert into u values ('SCC214', 'Applications of A.I.', 'L2', 'SC426');
insert into u values ('SCC217', 'Business Programming Language', 'L12', 'SC426');
insert into u values ('SCC220', 'Computer Architecture', 'L13', 'SC422');
insert into u values ('SCC238', 'Information Systems in Org.', 'L9', 'SC422');
insert into u values ('SCC303', 'Software Engineering', 'L11', 'SB420');
insert into u values ('SCC306', 'Computers, Society, Ethics', 'L8', 'SC424');
insert into u values ('SCC319', 'Computing Project', 'L9', null);
insert into u values ('SCC321', 'Distributed Systems', 'L1', 'SC426');
insert into u values ('SCC333', 'Practical Software Development', 'L11', 'SC424');
insert into u values ('SCC334', 'Electronic Business Systems', 'L5', 'SC426');
insert into u values ('SCC336', 'Computer Networks', 'L1', 'SC422');
insert into u values ('SCC337', 'Data Mining', 'L11', 'SC429');

SQL> SELECT COUNT(DISTINCT u.staff_no)
  2        ,u.room_no
  3  FROM   u
  4  GROUP  BY u.room_no
  5  HAVING COUNT(DISTINCT u.staff_no) > 1
  6  /

COUNT(DISTINCTU.STAFF_NO) ROOM_NO
------------------------- -------
                        2 SB137
                        4 SB420
                        4 SC422
                        2 SC424
                        4 SC426

SQL> SELECT DISTINCT sub.room_no
  2                 ,sub.NAME
  3  FROM   (SELECT l.NAME
  4                ,u.room_no
  5                ,COUNT(DISTINCT u.staff_no) over(PARTITION BY u.room_no) numb
  6          FROM   u
  7                ,l
  8          WHERE  l.staff_no = u.staff_no) sub
  9  WHERE  sub.numb > 1
 10  ORDER  BY sub.room_no
 11           ,sub.NAME
 12  /

ROOM_NO NAME
------- --------------------
SB137   De Paoli
SB137   Zhang
SB420   Dew
SB420   Horan
SB420   Newlands
SB420   Warren
SC422   Goscinski
SC422   Liu
SC422   Silcock
SC422   Warren
SC424   Coldwell
SC424   Horan
SC426   Dew
SC426   Goscinski
SC426   Warren
SC426   Webb

16 rows selected

SQL> with
  2    data as (
  3      select u.room_no, l.name
  4      from u, l
  5      where l.staff_no = u.staff_no
  6    )
  7  select distinct t1.room_no, t1.name, t2.name
  8  from data t1, data t2
  9  where t2.room_no = t1.room_no
 10    and t2.name > t1.name
 11  order by 1, 2, 3
 12  /
ROOM_ NAME                 NAME
----- -------------------- --------------------
SB137 De Paoli             Zhang
SB420 Dew                  Horan
SB420 Dew                  Newlands
SB420 Dew                  Warren
SB420 Horan                Newlands
SB420 Horan                Warren
SB420 Newlands             Warren
SC422 Goscinski            Liu
SC422 Goscinski            Silcock
SC422 Goscinski            Warren
SC422 Liu                  Silcock
SC422 Liu                  Warren
SC422 Silcock              Warren
SC424 Coldwell             Horan
SC426 Dew                  Goscinski
SC426 Dew                  Warren
SC426 Dew                  Webb
SC426 Goscinski            Warren
SC426 Goscinski            Webb
SC426 Warren               Webb

20 rows selected.