create table lessons (
    id number not null,
    name varchar(100),
    time_start varchar(100),
    time_end varchar(200),
    constraint lesson_pk primary key (id)
);

create table days_week (
    id number not null,
    name varchar (50),
    constraint day_pk primary key (id)
);

create table groups (
    id number not null,
    name varchar (50),
    constraint group_pk primary key (id)
);

create table cind_lessons (
    id number not null,
    name varchar (100),
    constraint cind_pk primary key (id)
);

create table subjects (
    id number not null,
    name varchar (100),
    constraint subject_pk primary key (id)
);

create table audiences (
    id number not null,
    name varchar (100),
    corp_id number not null,
    constraint audience_pk primary key (id),
    CONSTRAINT fk_corps
    FOREIGN KEY (corp_id) REFERENCES corps(id);
);

create table corps (
    id  number not null,
    name varchar(100),
    CONSTRAINT pk_corps PRIMARY KEY (id)
);


create table sheldule (
    id number not null,
    day_id number not null,
    lesson_id number not null,
    group_id number not null,
    subject_id number not null,
    cind_lesson_id number not null,
    audience_id number not null,
    CONSTRAINT sheldule_pk PRIMARY KEY (id),
    CONSTRAINT fk_day_week
        FOREIGN key (day_id)
        references days_week (id),
    CONSTRAINT fk_lesson_id
        FOREIGN key (lesson_id)
        references lessons (id),
    CONSTRAINT fk_group_id
        FOREIGN key (group_id)
        references groups (id),
    CONSTRAINT fk_subject_id
        FOREIGN key (subject_id)
        references subjects (id),
    CONSTRAINT fk_cind_id
        FOREIGN key (cind_lesson_id)
        references cind_lessons (id),
    CONSTRAINT fk_audience_id
        FOREIGN key (audience_id)
        references audiences (id)
);

insert into lessons (id, name, time_start, time_end) values (1, 'Перша пара', '08:30', '09:50');
insert into lessons (id, name, time_start, time_end) values (2, 'Друга пара', '10:05', '11:25');
insert into lessons (id, name, time_start, time_end) values (3, 'Третя пара', '11:40', '13:00');
insert into lessons (id, name, time_start, time_end) values (4, 'Четверта пара', '14:00', '15:20');
insert into lessons (id, name, time_start, time_end) values (5, 'П^ята пара', '15:35', '16:55');
insert into lessons (id, name, time_start, time_end) values (6, 'Шоста пара', '17:10', '18:30');

insert into days_week (id, name) values (1, 'Понеділок');
insert into days_week (id, name) values (2, 'Вівторок');
insert into days_week (id, name) values (3, 'Середа');
insert into days_week (id, name) values (4, 'Четвер');
insert into days_week (id, name) values (5, 'П^ятниця');
insert into days_week (id, name) values (6, 'Субота');
insert into days_week (id, name) values (7, 'Неділля');

insert into  groups (id, name) values (1, 'ІН-94-1');
insert into groups (id, name) values (2, 'ІН-91');

insert into  cind_lessons (id, name) values (1, 'онлайн');
insert into  cind_lessons (id, name) values (2, 'лабораторна робота');
insert into  cind_lessons (id, name) values (3, 'лекція');
insert into  cind_lessons (id, name) values (4, 'атестація');

insert into subjects (id, name) values (1, 'Бази даних та інформац. сист.');
insert into subjects (id, name) values (2, 'Первинні мер. та мер.оператор');
insert into subjects (id, name) values (3, 'Військова підготовка');
insert into subjects (id, name) values (4, 'Обр. зобр та мультимедія');
insert into subjects (id, name) values (5, 'Серверні технологій та веб-програм.');
insert into subjects (id, name) values (6, 'Клієнтські технології та веб-програм.');
insert into subjects (id, name) values (7, 'Дисц. за вибр. студента');

insert into corps (id, name) values (1, 'Головний');
insert into corps (id, name) values (2, 'Новий');
insert into corps (id, name) values (3, 'Центральний');

insert into audiences (id, name, corp_id) values (1, '210', 1);
insert into audiences (id, name, corp_id) values (2, '310', 2);
insert into audiences (id, name, corp_id) values (3, '410', 3);

insert into sheldule (id, day_id, audience_id,cind_lesson_id, group_id, lesson_id, subject_id)
VALUES(1, 1, 1, 2, 2, 3, 1);

insert into sheldule (id, day_id, audience_id, cind_lesson_id, group_id, lesson_id, subject_id)
VALUES(2, 1, 2, 2, 2, 4, 2);

insert into sheldule (id, day_id, audience_id,cind_lesson_id, group_id, lesson_id, subject_id)
VALUES(3, 2, 1, 1, 2, 1, 2);

insert into sheldule (id, day_id, audience_id, cind_lesson_id, group_id, lesson_id, subject_id)
VALUES(4, 2, 2, 2, 2, 2, 1);

insert into sheldule (id, day_id, audience_id,cind_lesson_id, group_id, lesson_id, subject_id)
VALUES(5, 3, 3, 1, 2, 1, 3);

insert into sheldule (id, day_id, audience_id, cind_lesson_id, group_id, lesson_id, subject_id)
VALUES(6, 3, 3, 1, 2, 2, 3);

insert into sheldule (id, day_id, audience_id,cind_lesson_id, group_id, lesson_id, subject_id)
VALUES(7, 3, 3, 1, 2, 3, 3);

insert into sheldule (id, day_id, audience_id, cind_lesson_id, group_id, lesson_id, subject_id)
VALUES(8, 3, 3, 1, 2, 4, 3);

insert into sheldule (id, day_id, audience_id,cind_lesson_id, group_id, lesson_id, subject_id)
VALUES(9, 4, 2, 2, 2, 2, 4);

insert into sheldule (id, day_id, audience_id, cind_lesson_id, group_id, lesson_id, subject_id)
VALUES(10, 4, 3, 2, 2, 3, 5);

insert into sheldule (id, day_id, audience_id,cind_lesson_id, group_id, lesson_id, subject_id)
VALUES(11, 5, 1, 1, 2, 1, 7);

insert into sheldule (id, day_id, audience_id, cind_lesson_id, group_id, lesson_id, subject_id)
VALUES(13, 5, 1, 1, 2, 2, 7);


select distinct s.name from sheldule h, subjects s, groups g
where s.id = h.subject_id
and g.id = h.group_id
and g.name = 'ІН-91'