-- Suspects based on a person being seen

create table SUSPECT_S as
select PERSON.name as suspect, CRIME.type as crime
from  SAWPERSON, WITNESS, CRIME, PERSON
where SAWPERSON.witness = WITNESS.name
  and CRIME.sector = WITNESS.sector 
  and PERSON.hair = SAWPERSON.hair
  and PERSON.clothes = SAWPERSON.clothes;

-- Suspects based on a car being seen

create table SUSPECT_D as 
select DRIVES.person as suspect, CRIME.type as crime
from  SAWCAR, WITNESS, CRIME, DRIVES
where SAWCAR.witness = WITNESS.name
  and WITNESS.sector = CRIME.sector
  and DRIVES.car = SAWCAR.car; 

-- High suspects

create table HIGH_SUSPECT as
select SUSPECT_D.suspect, SUSPECT_D.crime
from SUSPECT_D, SUSPECT_S
where SUSPECT_D.suspect = SUSPECT_S.suspect 
  and SUSPECT_D.crime = SUSPECT_S.crime
  and conf(SUSPECT_D) >= 0.25;

-- Accomplices

create table ACCOMPLICES as 
select S1.suspect as name1, S2.suspect as name2 
from SUSPECT_D S1, SUSPECT_D S2
where S1.crime = S2.crime
  and S1.suspect < S2.suspect;
