Запрос на выборку данных о товарах красного цвета, которые поставляются в количестве от 100 до 300

CREATE TABLE s(s_no VARCHAR(2) PRIMARY KEY, sname VARCHAR(5), STATUS INTEGER(2), city VARCHAR(6));
 
INSERT INTO s VALUES ('s1', 'smith', '20', 'london');
INSERT INTO s VALUES ('s2', 'jones', '10', 'paris');
INSERT INTO s VALUES ('s3', 'blake', '30', 'paris');
INSERT INTO s VALUES ('s4', 'clark', '20', 'london');
INSERT INTO s VALUES ('s5', 'adams', '30', 'athens');
 
 
CREATE TABLE p(p_no VARCHAR(2) PRIMARY KEY, pname VARCHAR(5), color VARCHAR(5), city VARCHAR(6));
 
INSERT INTO p VALUES ('p1', 'nut', 'red', 'london');
INSERT INTO p VALUES ('p2', 'bolt', 'green', 'paris');
INSERT INTO p VALUES ('p3', 'screw', 'blue', 'rome');
INSERT INTO p VALUES ('p4', 'screw', 'red', 'london');
INSERT INTO p VALUES ('p5', 'cam', 'blue', 'paris');
INSERT INTO p VALUES ('p6', 'cog', 'red', 'london');
 
CREATE TABLE sp(s_no VARCHAR(2), p_no VARCHAR(5), qty INTEGER(3));
 
INSERT INTO sp VALUES ('s1', 'p1', '300');
INSERT INTO sp VALUES ('s1', 'p2', '200');
INSERT INTO sp VALUES ('s1', 'p3', '400');
INSERT INTO sp VALUES ('s1', 'p4', '200');
INSERT INTO sp VALUES ('s1', 'p5', '100');
INSERT INTO sp VALUES ('s1', 'p6', '100');
INSERT INTO sp VALUES ('s2', 'p1', '300');
INSERT INTO sp VALUES ('s2', 'p2', '400');
INSERT INTO sp VALUES ('s3', 'p2', '200');
INSERT INTO sp VALUES ('s4', 'p2', '200');
INSERT INTO sp VALUES ('s4', 'p4', '300');
INSERT INTO sp VALUES ('s4', 'p5', '400');
 
SELECT * FROM s;
SELECT * FROM p;
SELECT * FROM sp;

SELECT DISTINCT(p.p_no), p.pname FROM p AS p JOIN sp AS sp ON sp.p_no = p.p_no WHERE sp.p_no IN (SELECT p_no FROM sp WHERE qty > 100 AND qty < 300);

SELECT p_no, pname FROM p WHERE p_no IN ( SELECT  p.p_no FROM p AS p JOIN sp AS sp ON sp.p_no = p.p_no WHERE sp.p_no IN (SELECT p_no FROM sp WHERE qty > 100 AND qty < 300)) AND p_no IN (SELECT p_no FROM p WHERE color = 'red');

SELECT * FROM p WHERE p_no IN (SELECT p_no FROM sp GROUP BY p_no HAVING COUNT(p_no) <= 2);

SELECT * FROM p WHERE p_no IN (SELECT p_no FROM sp GROUP BY p_no HAVING COUNT(p_no) >=3);

SELECT DISTINCT * FROM s WHERE s_no NOT IN(SELECT  s_no FROM sp WHERE p_no IN (SELECT p_no FROM sp WHERE s_no = 's2'));