gRdeU8C2

1drop table if exists teachers, сontests, participants;
2
3create table teachers (	
4	teacher_id serial PRIMARY KEY,
5	fio text
6);
7
8create table сontests (	
9	сontest_id serial PRIMARY key,  
10  	start_date DATE,
11  	name text
12);
13
14create table participants (
15  	participant_id serial,  
16  	fio text,
17  	contest_id INTEGER REFERENCES сontests(сontest_id),
18   	teacher_id INTEGER REFERENCES teachers(teacher_id),
19    result INTEGER
20);
21
22insert into teachers(fio) VALUES
23('Иванова И.'),
24('Петорва П.'),
25('Сидорова С.');
26
27insert into сontests(start_date, name) VALUES
28(to_date('2020', 'YYYY'), 'New Vasuki conference'),
29(to_date('2021', 'YYYY'), 'New Vasuki online-conference');
30
31insert into participants(fio, contest_id, teacher_id, result) VALUES
32('Карцев', 2, 1, 1),
33('Бендер', 1, 1, 1),
34('Угольников', 2 , 2, 8),
35('Хазанов', 2, 2, 4),
36('Арлазоров', 1, 3, 4),
37('Задорнов', 1, 3, 5);
38
39select teachers.teacher_id from teachers
40join participants using(teacher_id)
41where participants.result not BETWEEN 1 and 3
42GROUP by teachers.teacher_id
43HAVING count(participants.teacher_id) = ALL(SELECT COUNT(*) FROM participants GROUP BY participants.teacher_id);
44
45select teachers.teacher_id from teachers JOIN
46participants using(teacher_id) join (
47  select teacher_id, count(*) as bad_count from participants
48  where result not BETWEEN 1 and 3
49  group by teacher_id
50) as tt using(teacher_id) 
51group by teachers.teacher_id, bad_count
52having count(participants.teacher_id) = bad_count;