-- look for constraint violations in the SB database

select ainst,aname from people where not exists
(select ainst from mastinst where people.ainst=mastinst.ainst);
-- found CINAR_S from SUNSYB - corrected typo in peo_load.ctl

select institute,name from whod0 where not exists
(select institute from instit where whod0.institute=instit.institute);
-- I found many people where institute is null in whod0 (425 people)
select name from whod0 where institute is null;

select name,aname from people where not exists
(select name from whod0 where whod0.name=people.name);
-- this finds 133 people who don't have a whod0 name

select aname from links where not exists
(select aname from people where people.aname=links.aname);
-- this finds GENIK_RJ - corrected the lin_load.ctl file typo (GENIK~II_RJ)

select aconf from links where not exists
(select aconf from confs where confs.aconf=links.aconf);

select sgroup from subgroup where not exists
(select sgroup from pgroup where subgroup.pgroup=pgroup.pgroup);
-- no violations found

select sgroup from ssgroup where not exists
(select sgroup from subgroup where subgroup.sgroup=ssgroup.sgroup);
-- no violations found (ssgroup contains no data at this point

-- look for people with the same whod0 name
SELECT COUNT(*),NAME FROM PEOPLES
GROUP BY NAME HAVING NVL(NAME,'?')<>'?' AND COUNT(*)>1;
-- another way
SELECT count(*),NAME FROM PEOPLES GROUP BY NAME HAVING COUNT(*)=2;

-- find mastinst if it has no people in people table
SELECT AINST FROM MASTINST WHERE NOT EXISTS
(SELECT AINST FROM PEOPLES WHERE PEOPLES.AINST=MASTINST.AINST);
