sidorenkov

Answer #7

Aug 3rd, 2021
2,250
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DROP TABLE IF EXISTS teachers, сontests, participants;
  2.  
  3. CREATE TABLE teachers (
  4.     teacher_id serial PRIMARY KEY,
  5.     fio text
  6. );
  7.  
  8. CREATE TABLE сontests (   
  9.     сontest_id serial PRIMARY KEY,  
  10.     start_date DATE,
  11.     name text
  12. );
  13.  
  14. CREATE 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.  
  22. INSERT INTO teachers(fio) VALUES
  23. ('Иванова И.'),
  24. ('Петорва П.'),
  25. ('Сидорова С.');
  26.  
  27. INSERT INTO сontests(start_date, name) VALUES
  28. (to_date('2020', 'YYYY'), 'New Vasuki conference'),
  29. (to_date('2021', 'YYYY'), 'New Vasuki online-conference');
  30.  
  31. INSERT 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.  
  39. SELECT teachers.teacher_id FROM teachers
  40. JOIN participants USING(teacher_id)
  41. WHERE participants.RESULT NOT BETWEEN 1 AND 3
  42. GROUP BY teachers.teacher_id
  43. HAVING COUNT(participants.teacher_id) = ALL(SELECT COUNT(*) FROM participants GROUP BY participants.teacher_id);
  44.  
  45. SELECT teachers.teacher_id FROM teachers JOIN
  46. participants 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)
  51. GROUP BY teachers.teacher_id, bad_count
  52. HAVING COUNT(participants.teacher_id) = bad_count;
RAW Paste Data