12.10 An example of simplified SQL double negation
Based on a certain table, find records that can match another table. In SQL, we can use double negation to reduce computation amount.
According to Student table, SelectCourse table and Course table, find students who select all courses.
| Student |
|---|
| ID |
| Name |
| Class |
| … |
| SelectCourse |
|---|
| ID |
| CourseID |
| StudentID |
| … |
| Course |
|---|
| ID |
| Name |
| TeacherID |
| … |
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query(“select * from Student”) |
| 3 | =A1.query(“select * from Course”) |
| 4 | =A1.query@x(“select * from SelectCourse”) |
| 5 | =A4.groups(StudentID; icount(CourseID):CourseCount) |
| 6 | =A5.select(CourseCount==A3.len()) |
| 7 | =A2.join@i(ID, A6:StudentID) |
A1 Connect to the database.
A2 Query Student table.
A3 Query Course table.
A4 Query SelectCourse table.
A5 Group SelectCourse table by StudentID and count courses each student selects.
A6 Select IDs of students from SelectCourse table who select all courses.
A7 The A.join@i() function performs filtering join.
Execution result:
| ID | Name | Class |
|---|---|---|
| 4 | Emily Smith | Class 1 |
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
