Test Practice:
Oracle Query For understanding Group join and aggregate function :  For Me 

Count all teacher and show teacher eiin (institute  identifier key ), Count teacher in each institute. the red on may be ok logically but makes an error

This can be done by making multiple select module The bellowed one will select ALL institute  and it’s teacher and count it.

select I.INSTITUTION_NAME , teachers.TEACHERS_EIIN, teachers.COUNT_TEACHER  from

(select   t.eiin as TEACHERS_EIIN, count( T.TEACHERS_NAME ) as COUNT_TEACHER from teacher t

join institute i

on T.EIIN = I.EIIN

group by t.eiin)  teachers, // We can not put as teachers here as will cause an error

institute i

where  teachers.TEACHERS_EIIN = I.EIIN ;

The bellowed one will select only one institute  and it’s teacher and count it.

select I.INSTITUTION_NAME , teachers.TEACHERS_EIIN, teachers.COUNT_TEACHER  from

(select   t.eiin as TEACHERS_EIIN, count( T.TEACHERS_NAME ) as COUNT_TEACHER from teacher t

join institute i

on T.EIIN = I.EIIN

group by t.eiin)  teachers,

  institute i

where  teachers.TEACHERS_EIIN = I.EIIN AND I.EIIN = '118401';

The following query will select all grouped data

select   I.INSTITUTION_NAME,  I.LOC_ID, t.eiin, count( T.eiin)  from teacher t

join institute i

on T.EIIN = I.EIIN

group by t.eiin , I.INSTITUTION_NAME, I.LOC_ID;

The following query will select distinct grouped data

select distinct  I.INSTITUTION_NAME,  I.LOC_ID, t.eiin, count( T.eiin)  from teacher t

join institute i

on T.EIIN = I.EIIN

group by t.EIIN , I.INSTITUTION_NAME, I.LOC_ID;

The above will be error in the bellow/// Need to add all column which I want to select

select distinct  I.INSTITUTION_NAME,  I.LOC_ID, t.eiin, count( T.eiin)  from teacher t

join institute i

on T.EIIN = I.EIIN

group by t.eiin ; /// Need to add all column which I want to select

When we will put where clause before join that is erroneous

select   t.eiin as TEACHERS_EIIN, count( T.TEACHERS_NAME ) as COUNT_TEACHER from teacher t  where T.EIIN = '118401'

join institute i

on T.EIIN = I.EIIN

group by t.eiin

The same as above is . When we will put where clause after join that is ok

select   t.eiin as TEACHERS_EIIN, count( T.TEACHERS_NAME ) as COUNT_TEACHER from teacher t

join institute i

on T.EIIN = I.EIIN  

where T.EIIN = '118401'

group by t.eiin;

Same

If also we put where clause data in having clause that is also ok

select   t.eiin as TEACHERS_EIIN, count( T.TEACHERS_NAME ) as COUNT_TEACHER from teacher t

join institute i

on T.EIIN = I.EIIN  

group by t.eiin having (T.EIIN = '118401');