Examples of a user question Q, SQL translation S, and non-nested form N(S).
| Case 1 | Q | Find the names of scientists who are not working on the project with the highest hours. | |
| S | SELECT name FROM scientists EXCEPT (SELECT T3.name FROM assignedto AS T1 JOIN projects AS T2 ON T1.project = T2.code JOIN scientists AS T3 ON T1.scientist = T3.SSN WHERE T2.hours = ( SELECT max(hours) FROM projects ) ) | ||
| N(S) | P1 | [ NONE ] | |
| S1 | SELECT name FROM scientists EXCEPT S2 | ||
| P2 | [ EXCEPT ] | ||
| S2 | SELECT T3.name FROM assignedto AS T1 JOIN projects AS T2 ON T1.project = T2.code JOIN scientists AS T3 ON T1.scientist = T3.SSN WHERE T2.hours = S3 | ||
| P3 | [ EXCEPT, WHERE ] | ||
| S3 | SELECTmax(hours)FROMprojects | ||
| Case 2 | Q | Find the names of accounts whose checking balance is above the average checking balance, but savings balance is below the average savings balance. | |
| S | SELECT T1.name FROM accounts AS T1 JOIN checking AS T2 ON T1.custid = T2.custid WHERE T2.balance > (SELECT avg(balance) FROM checking) INTERSECTSELECT T1.name FROM accounts AS T1 JOIN savings AS T2 ON T1.custid = T2.custid WHERE T2.balance < (SELECT avg(balance) FROM savings) | ||
| N(S) | P1 | [ NONE ] | |
| S1 | SELECT; T1.name; FROM accountsAS T1 JOIN checking AS T2 ON T1.custid = T2.custid INTERSECT S3 | ||
| P2 | [ WHERE ] | ||
| S2 | SELECTavg(balance)FROMchecking | ||
| P3 | [ INTERSECT ] | ||
| S3 | SELECT T1.name FROM accounts AS T1 JOIN savings AS T2 ON T1.custid = T2.custid WHERE T2.balance <S4 | ||
| P4 | [ INTERSECT, WHERE ] | ||
| S4 | SELECTavg(balance)FROMsavings | ||
| Case 1 | Q | Find the names of scientists who are not working on the project with the highest hours. | |
| S | SELECT name FROM scientists EXCEPT (SELECT T3.name FROM assignedto AS T1 JOIN projects AS T2 ON T1.project = T2.code JOIN scientists AS T3 ON T1.scientist = T3.SSN WHERE T2.hours = ( SELECT max(hours) FROM projects ) ) | ||
| N(S) | P1 | [ NONE ] | |
| S1 | SELECT name FROM scientists EXCEPT S2 | ||
| P2 | [ EXCEPT ] | ||
| S2 | SELECT T3.name FROM assignedto AS T1 JOIN projects AS T2 ON T1.project = T2.code JOIN scientists AS T3 ON T1.scientist = T3.SSN WHERE T2.hours = S3 | ||
| P3 | [ EXCEPT, WHERE ] | ||
| S3 | SELECTmax(hours)FROMprojects | ||
| Case 2 | Q | Find the names of accounts whose checking balance is above the average checking balance, but savings balance is below the average savings balance. | |
| S | SELECT T1.name FROM accounts AS T1 JOIN checking AS T2 ON T1.custid = T2.custid WHERE T2.balance > (SELECT avg(balance) FROM checking) INTERSECTSELECT T1.name FROM accounts AS T1 JOIN savings AS T2 ON T1.custid = T2.custid WHERE T2.balance < (SELECT avg(balance) FROM savings) | ||
| N(S) | P1 | [ NONE ] | |
| S1 | SELECT; T1.name; FROM accountsAS T1 JOIN checking AS T2 ON T1.custid = T2.custid INTERSECT S3 | ||
| P2 | [ WHERE ] | ||
| S2 | SELECTavg(balance)FROMchecking | ||
| P3 | [ INTERSECT ] | ||
| S3 | SELECT T1.name FROM accounts AS T1 JOIN savings AS T2 ON T1.custid = T2.custid WHERE T2.balance <S4 | ||
| P4 | [ INTERSECT, WHERE ] | ||
| S4 | SELECTavg(balance)FROMsavings | ||