convert join query into nested query

Question

how to convert this query into a nested query which uses where in condition?

SELECT schedule.subcode,attendance.usn,avg(attendance.ispresent)*100 as Attendance_Percentage FROM schedule JOIN attendance ON schedule.sched_id=attendance.sched_id WHERE usn="4jc14is013" AND subcode="is530";

Your database format is not in normal form. It should be two tables: `Persons(usn, name)` and `Presence(date, usn)`. See https://www.tutorialspoint.com/dbms/database_normalization.htm — lovasoa, Nov 13 '16 at 16:22

gr1zzly be4r · Accepted Answer · 2016-11-13T16:14:14.073

0

You can use HAVING:

SELECT usn, name, AVG(ispresent) attendance FROM table 
GROUP BY usn, name HAVING attendance < 0.75;

edited Nov 13 '16 at 16:14

answered Nov 13 '16 at 16:07

gr1zzly be4r

2,072
1
18
33

2

You forgot the GROUP BY clause – Paul Spiegel Nov 13 '16 at 16:09

convert join query into nested query

1 Answers1