declare @tCategory table (categoryCd varchar(100), descrp varchar(100));
insert into @tCategory values('A', 'SPM');
insert into @tCategory values('B', 'SPMV');
insert into @tCategory values('C', 'STPM');
insert into @tCategory values('D', 'HND');
/* categoryCd is unique */

declare @tSubject table (subjectCd varchar(100), descrp varchar(100));
insert into @tSubject values('PY', 'PHYSIC');
insert into @tSubject values('HI', 'HISTORY');
insert into @tSubject values('MA', 'MATH');
insert into @tSubject values('EN', 'ENGLISH');
/* subjectCd is unique */

declare @tRules table (categoryCd varchar(100), subjectCd varchar(100), gredMin tinyint);
insert into @tRules values('A', 'PY', 30);
insert into @tRules values('A', 'HI', 45);
insert into @tRules values('A', 'MA', 74);
insert into @tRules values('B', 'PY', 67);
insert into @tRules values('B', 'HI', 34);
insert into @tRules values('B', 'EN', 50);
/* Combination of categoryCd and subjectCd is unique */
/* @tRules(categoryCd) is a foreign key for @tCategory(categoryCd) */
/* @tRules(subjectCd) is a foreign key for @tSubject(subjectCd) */



declare @tStudent table (myID varchar(100), categoryCd varchar(100));
insert into @tStudent values('1925', 'A');
insert into @tStudent values('4474', 'A');
insert into @tStudent values('3389', 'A');
insert into @tStudent values('1199', 'B');
insert into @tStudent values('2879', 'B');
/* myID is unique */
/* @tStudent(categoryCd) is a foreign key for @tCategory(categoryCd) */


declare @tSubjectList table (myID varchar(100),
subjectCd varchar(100), myGredMin tinyint, isPass bit null);
insert into @tSubjectList(myID,subjectCd,myGredMin) values('1925', 'PY', 34);
insert into @tSubjectList(myID,subjectCd,myGredMin) values('1925', 'EN', 86);
insert into @tSubjectList(myID,subjectCd,myGredMin) values('4474', 'HI', 78);
insert into @tSubjectList(myID,subjectCd,myGredMin) values('3389', 'MA', 46);
insert into @tSubjectList(myID,subjectCd,myGredMin) values('1199', 'PY', 87);
insert into @tSubjectList(myID,subjectCd,myGredMin) values('4474', 'PY', 34);
insert into @tSubjectList(myID,subjectCd,myGredMin) values('2879', 'EN', 66);
insert into @tSubjectList(myID,subjectCd,myGredMin) values('3389', 'HI', 57);
/* Combination of myID and subjectCd is unique */
/* @tSubjectList(subjectCd) is a foreign key for @tSubject(subjectCd) */

myID| subjectCd| myGredMin
--------------------------------------
1925        PY             34

myID| subjectCd| myGredMin
--------------------------------------
3389        MA             46 < 74

myID| subjectCd| myGredMin
--------------------------------------
1925        EN             86