12.11 Judge existence of data matching

 

Based on association of two tables, get records through judging existence of data matching.

According to Student table and Score table, find students who have two subjects whose score difference is greater than 30.

Student
ID
Class
Name
Score
StudentID
Subject
Score

We just need to check whether the difference between a student’s highest score and lowest score is greater than 30.

SPL script:

A
1 =connect(“db”)
2 =A1.query(“select * from Student”)
3 =A1.query@x(“select * from Score”)
4 =A3.group(StudentID)
5 =A4.select(~.max(Score)-~.min(Score)>30)
6 =A5.id(StudentID)
7 =A2.join@i(ID,A6)

A1 Connect to the database.
A2 Query Student table.
A3 Query Score table.
A4 Group Score table by StudentID.
A5 Find students whose highest score and lowest has a difference greater than 30.
A6 Perform distinct on StudentID.
A7 The A.join@i() function performs filtering join.

Execution result:

ID Name Class
4 Emily Smith Class 1
8 Megan Class 1