--1.this is for creating the occupationtyptype table

select distinct ocd as OccupationType into OccupationType from resident where ocd is not null;

-- 2. create the resident table
-- we  need to create the resident table with the correct column types
-- and then we also need a column for the residentid as the primary key
-- and then the others from the resident table.

CREATE TABLE ResidentTable (
  ResidentID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  LName nvarchar(20),
  FName nvarchar(15),
  BirthYear smallint,
  BirthMonth smallint,
  BirthDay   smallint,
  MiddleName nvarchar(15),
  Suffix nvarchar(3),
  Sex nvarchar(1),
  Occupation  nvarchar(12),
  FirstYearDB nvarchar(2),
  LastYearDB nvarchar(2),
  HHID int,
  OccupationType nvarchar(50)
)
INSERT INTO ResidentTable (LName, FName, BirthYear, BirthMonth, BirthDay,
             MiddleName, Suffix, Sex, Occupation, FirstYearDB, LastYearDB,
             HHID, OccupationType)
             SELECT lname, fname, yr, mo, Resident.day, 
             midname, suffix, sex, occ, db1, dbl, hhnum, ocd 
             from Resident
             where ocd is not NULL and sex is not null;

-- 3.create the street table 

select distinct stname as StreetName into Street from Resident where stname is not null;

-- 4. create the district

select distinct dist as District into District from Resident where dist is not null;

-- 5. create the precinct

select distinct pct as Precinct into Precinct from Resident where pct is not null;

-- 6. create the carrierroute

select distinct car_rte as CarrierRoute into CarrierRoute from Resident where car_rte is not
null;

--7. create household table

CREATE TABLE Household (
  HHID INT  NOT NULL PRIMARY KEY,
  StreetNum smallint,
  StreetLetter nvarchar(1),
  StreetName nvarchar(20) NOT NULL,
  Apt nvarchar(4),
  CarrierRoute nvarchar(2) NOT NULL,
  District nvarchar(2) NOT NULL,
  Precinct nvarchar(2) NOT NULL
 )

Insert into Household (HHID, StreetNum, StreetLetter, StreetName, Apt, 
                       CarrierRoute, District, Precinct) select distinct hhnum,
                        stnum, stlet, stname, apt, car_rte, dist, pct 
                        from resident where car_rte is not null and dist
                        is not null;

-- need to delete the rows in the resident table that are not 
-- present in the household table.
-- need to remove these rows from the residenttable
-- using the sql aid given in the problem set statement.

SELECT distinct ResidentTable.HHID into temp4
FROM ResidentTable LEFT JOIN Household 
ON ResidentTable.HHID = Household.HHID
WHERE (Household.HHID Is Null) order by residenttable.hhid; 

delete from residenttable where residenttable.hhid in (select temp4.hhid from temp4);

-- 8. create the phone table

select distinct household.hhid, resident.ph as Phone from resident, household where household.hhid = resident.hhnum 
order by resident.ph;


--9. create service table
CREATE TABLE Service (
  Service nvarchar(10)  NOT NULL PRIMARY KEY,
   )
-- filled the table manually

--10. created the Household_Service table manually. Futher, I created a
-- table called year, which has the values of 3,6,and 9. I use these
-- values for populating the Household_Service table.

INSERT into Household_Service
select distinct household.hhid, service.service, year.year 
from household, resident, service, year where household.hhid = resident.hhnum and  
resident.fios1 is not NULL and service.service = 'fios' and year.year = '3';

INSERT into Household_Service
select distinct household.hhid, service.service, year.year 
from household, resident, service, year where household.hhid = resident.hhnum and  
resident.fios2 is not NULL and service.service = 'fios' and year.year = '6';

INSERT into Household_Service
select distinct household.hhid, service.service, year.year 
from household, resident, service, year where household.hhid = resident.hhnum and  
resident.fios3 is not NULL and service.service = 'fios' and year.year = '9';

INSERT into Household_Service
select distinct household.hhid, service.service, year.year 
from household, resident, service, year where household.hhid = resident.hhnum and  
resident.catv1 is not NULL and service.service = 'catv' and year.year = '3';

INSERT into Household_Service
select distinct household.hhid, service.service, year.year 
from household, resident, service, year where household.hhid = resident.hhnum and  
resident.catv2 is not NULL and service.service = 'catv' and year.year = '6';

INSERT into Household_Service
select distinct household.hhid, service.service, year.year 
from household, resident, service, year where household.hhid = resident.hhnum and  
resident.catv3 is not NULL and service.service = 'catv' and year.year = '9';

INSERT into Household_Service
select distinct household.hhid, service.service, year.year 
from household, resident, service, year where household.hhid = resident.hhnum and  
resident.gas1 is not NULL and service.service = 'gas' and year.year = '3';

INSERT into Household_Service
select distinct household.hhid, service.service, year.year 
from household, resident, service, year where household.hhid = resident.hhnum and  
resident.gas2 is not NULL and service.service = 'gas' and year.year = '6';

INSERT into Household_Service
select distinct household.hhid, service.service, year.year 
from household, resident, service, year where household.hhid = resident.hhnum and  
resident.gas3 is not NULL and service.service = 'gas' and year.year = '9';

-- 11. table for creating domain entity sex. Values entered manually.
CREATE TABLE Sex (
  Sex nvarchar(5)  NOT NULL PRIMARY KEY,
   )


