classrooms, PostgreSQL

Language: Editor: Layout:

drop table if exists classroom cascade;
drop table if exists people cascade;

create table classroom (class_id int, room text, subject text);

insert into classroom
values
(100, '4b', 'geometry'),
(102, '12a', 'biology');

create table people (person_id text, class_id int, role text, name text);

insert into people 
values
('320', '100', 'teacher', 'Clive'),
('002', '100', 'student', 'Jimmy'),
('098', '100', 'student', 'Carl'),
('114', '102', 'teacher', 'Maureen'),
('056', '102', 'student', 'Lyle');

with students as (
  select person_id, class_id, role, name,
         row_number() over (partition by class_id) as st_nr
  from people
  where role = 'student'
)
select cl.class_id, 
       cl.room, 
       cl.subject, 
       t.name as teacher_name, 
       s1.name as student1_name,
       s2.name as student2_name
from people t
  join classroom cl on cl.class_id = t.class_id and role = 'teacher'
  left join students s1 on s1.class_id = t.class_id and s1.st_nr = 1
  left join students s2 on s2.class_id = t.class_id and s2.st_nr = 2


absolute service time: 0,87 sec