query question

[slok]

query question for 3 tables...
==
Table Student
Student_NO NOT NULL CHAR(10)
NAME NOT NULL CHAR(30)
STREET NOT NULL CHAR(20)
POSTCODE NOT NULL CHAR(20)
DOB NOT NULL DATE
Stuent_TYPE NOT NULL CHAR(10)
Student_EXP_DATE NOT NULL DATE
Student_STATUS NOT NULL CHAR(1)
SUSP_CEASE_DATE DATE


Table Offence
OFF_DATETIME NOT NULL DATE
DEMERIT_CODE NOT NULL NUMBER(2)
MOD_DATETIME NOT NULL DATE
MOD_NAME NOT NULL CHAR(30)
Studnet_NO NOT NULL CHAR(10)


Table Demerit
DEMERIT_CODE NOT NULL NUMBER(2)
DESCRIP NOT NULL VARCHAR2(200)
POINTS NOT NULL NUMBER(1)
==

given the student_no, how can I find out if the student
have incur infringement points (points) which total 10 or more for the last 2 years?

thanks

 

[karluk]

You need to go through the offence table and sum the points for each offence. This requires a join to the demerit table. Something like this should work, assuming that demirit_code is a unique key for the demerit table.

select a.student_no, sum(b.points) from offence a, demerit b
where a.demerit_code = b.demerit_code
group by a.student_no
having sum(b.points) >= 10;

Obviously you can also add a specific student_no to your where clause.

 

[karluk]

Sorry, I didn't notice the time constraint. You also need to restrict the select to offenses committed in the past two years.

select a.student_no, sum(b.points) from offence a, demerit b
where a.demerit_code = b.demerit_code
and off_datetime > add_months(sysdate, -24)
group by a.student_no
having sum(b.points) >= 10;

Please note also that you may need to add a join to the student table if you need student name or other info.