DROP SCHEMA IF EXISTS mysql_test;
CREATE SCHEMA mysql_test;
USE mysql_test;
CREATE TABLE STAFF (EMPNUM   CHAR(3) NOT NULL,
EMPNAME  CHAR(20), GRADE DECIMAL(4), CITY CHAR(15))
ENGINE = <Engine>;
CREATE TABLE PROJ (PNUM CHAR(3) NOT NULL,
PNAME    CHAR(20), PTYPE CHAR(6),
BUDGET   DECIMAL(9),
CITY     CHAR(15))
ENGINE = <Engine>;
CREATE TABLE WORKS (EMPNUM CHAR(3) NOT NULL,
PNUM CHAR(3) NOT NULL, HOURS DECIMAL(5))
ENGINE = <Engine>;
INSERT INTO STAFF VALUES ('E1','Alice',12,'Deale');
INSERT INTO STAFF VALUES ('E2','Betty',10,'Vienna');
INSERT INTO STAFF VALUES ('E3','Carmen',13,'Vienna');
INSERT INTO STAFF VALUES ('E4','Don',12,'Deale');
INSERT INTO STAFF VALUES ('E5','Ed',13,'Akron');
INSERT INTO PROJ VALUES  ('P1','MXSS','Design',10000,'Deale');
INSERT INTO PROJ VALUES  ('P2','CALM','Code',30000,'Vienna');
INSERT INTO PROJ VALUES  ('P3','SDP','Test',30000,'Tampa');
INSERT INTO PROJ VALUES  ('P4','SDP','Design',20000,'Deale');
INSERT INTO PROJ VALUES  ('P5','IRM','Test',10000,'Vienna');
INSERT INTO PROJ VALUES  ('P6','PAYR','Design',50000,'Deale');
INSERT INTO WORKS VALUES  ('E1','P1',40);
INSERT INTO WORKS VALUES  ('E1','P2',20);
INSERT INTO WORKS VALUES  ('E1','P3',80);
INSERT INTO WORKS VALUES  ('E1','P4',20);
INSERT INTO WORKS VALUES  ('E1','P5',12);
INSERT INTO WORKS VALUES  ('E1','P6',12);
INSERT INTO WORKS VALUES  ('E2','P1',40);
INSERT INTO WORKS VALUES  ('E2','P2',80);
INSERT INTO WORKS VALUES  ('E3','P2',20);
INSERT INTO WORKS VALUES  ('E4','P2',20);
INSERT INTO WORKS VALUES  ('E4','P4',40);
INSERT INTO WORKS VALUES  ('E4','P5',80);
COMMIT WORK;
SELECT COUNT(*) FROM PROJ;
COUNT(*)
6
SELECT COUNT(*) FROM STAFF;
COUNT(*)
5
SELECT COUNT(*) FROM WORKS;
COUNT(*)
12
This is the fat SELECT with nested SUBQUERIES from NIST
SELECT EMPNUM, EMPNAME
FROM STAFF
WHERE EMPNUM IN
(SELECT EMPNUM  FROM WORKS
WHERE PNUM IN
(SELECT PNUM  FROM PROJ
WHERE PTYPE IN
(SELECT PTYPE  FROM PROJ
WHERE PNUM IN
(SELECT PNUM  FROM WORKS
WHERE EMPNUM IN
(SELECT EMPNUM  FROM WORKS
WHERE PNUM IN
(SELECT PNUM   FROM PROJ
WHERE PTYPE IN
(SELECT PTYPE  FROM PROJ
WHERE CITY IN
(SELECT CITY  FROM STAFF
WHERE EMPNUM IN
(SELECT EMPNUM  FROM WORKS
WHERE HOURS = 20
AND PNUM = 'P2' )))))))));
EMPNUM	EMPNAME
E1	Alice
E2	Betty
E3	Carmen
E4	Don
-- PASS:0226 If 4 rows selected excluding EMPNUM='E5', EMPNAME='Ed'?

Variation 1
SELECT EMPNUM, EMPNAME
FROM STAFF
WHERE EMPNUM IN
(SELECT EMPNUM  FROM WORKS
WHERE PNUM IN
(SELECT PNUM  FROM PROJ
WHERE PTYPE IN
(SELECT PTYPE  FROM PROJ
WHERE PNUM IN
(SELECT PNUM  FROM WORKS
WHERE EMPNUM IN
(SELECT EMPNUM  FROM WORKS
WHERE PNUM IN
(SELECT PNUM   FROM PROJ
WHERE PTYPE IN
(SELECT PTYPE  FROM PROJ
WHERE CITY IN
(SELECT CITY  FROM STAFF))))))));
EMPNUM	EMPNAME
E1	Alice
E2	Betty
E3	Carmen
E4	Don

Variation 2
SELECT EMPNUM, EMPNAME
FROM STAFF
WHERE EMPNUM IN
(SELECT EMPNUM  FROM WORKS
WHERE PNUM IN
(SELECT PNUM  FROM PROJ
WHERE PTYPE IN
(SELECT PTYPE  FROM PROJ
WHERE PNUM IN
(SELECT PNUM  FROM WORKS
WHERE EMPNUM IN
(SELECT EMPNUM  FROM WORKS
WHERE PNUM IN
(SELECT PNUM   FROM PROJ
WHERE PTYPE IN
(SELECT PTYPE  FROM PROJ)))))));
EMPNUM	EMPNAME
E1	Alice
E2	Betty
E3	Carmen
E4	Don

Variation 3
SELECT EMPNUM, EMPNAME
FROM STAFF
WHERE EMPNUM IN
(SELECT EMPNUM  FROM WORKS
WHERE PNUM IN
(SELECT PNUM  FROM PROJ
WHERE PTYPE IN
(SELECT PTYPE  FROM PROJ
WHERE PNUM IN
(SELECT PNUM  FROM WORKS
WHERE EMPNUM IN
(SELECT EMPNUM  FROM WORKS
WHERE PNUM IN
(SELECT PNUM   FROM PROJ))))));
EMPNUM	EMPNAME
E1	Alice
E2	Betty
E3	Carmen
E4	Don

Cleanup
DROP SCHEMA mysql_test;