text: /*
  CREATE TABLE  "H_PERSON_HISTORY"     
  (
  "HIST_ID" NUMBER(6,0) NOT NULL ENABLE,   
  "PERSON_ID_H" NUMBER (6,0) NOT NULL ENABLE,  
  "ADDRESS_ID" NUMBER(6,0) NOT NULL ENABLE,   
  "ADMIN_NAME" VARCHAR2(25) NOT NULL ENABLE,
  "DATE_UPDATED" TIMESTAMP NOT NULL ENABLE,
  CONSTRAINT "H_PERSON_HISTORY_HIST_ID_PK" PRIMARY KEY ("HIST_ID")  
  USING INDEX  ENABLE
  )
  /
  */
  -------+++++++++--------SOME USE FULL JOIN AND PROCEDURE-------+++++++++++------------------------
  -------------------- THIS PROCEDURE WILL HELP TO  UPDATE ADDRESS ---------------------
  CREATE OR REPLACE PROCEDURE HM_PERSON_ADDRESS_UPDATED(
  P_PERSON_ID IN h_person_address.PERSON_ID%TYPE,
  P_ADD_LINE1 IN h_person_address.address_line_1%TYPE,
  P_ADD_LINE2 IN h_person_address.address_line_2%TYPE,
  P_CITY IN h_person_address.CITY%TYPE,
  P_ZIP IN h_person_address.zip_code%TYPE,
  P_STATE IN H_PERSON_ADDRESS.STATE%TYPE,
  P_DATE_IN IN H_PERSON_ADDRESS.DATE_IN%TYPE
  )
  IS
  V_COUNT INTEGER;
  V_ADDRESS_ID h_person_address.address_id%TYPE;
  P_ACTIVE  h_person_address.active%TYPE:=1;
  P_DATE_OUT  H_PERSON_ADDRESS.DATE_OUT%TYPE:='';
  BEGIN
          UPDATE H_PERSON_ADDRESS
          SET DATE_OUT=P_DATE_IN, ACTIVE=0
          WHERE ADDRESS_ID = (SELECT MAX(ADDRESS_ID) FROM H_PERSON_ADDRESS 
          WHERE PERSON_ID=P_PERSON_ID
          GROUP BY PERSON_ID);
                 
          SELECT COUNT(ADDRESS_ID) INTO V_COUNT FROM H_PERSON_ADDRESS;
          IF V_COUNT=0
              THEN V_ADDRESS_ID:=1;
          ELSE
              SELECT MAX(ADDRESS_ID)+1 INTO V_ADDRESS_ID FROM H_PERSON_ADDRESS; 
          END IF;
           
          INSERT INTO H_PERSON_ADDRESS(address_id, person_id,address_line_1,address_line_2,CITY,zip_code,STATE,DATE_IN,DATE_OUT,ACTIVE)
          VALUES (V_ADDRESS_ID ,P_PERSON_ID,P_ADD_LINE1,P_ADD_LINE2,P_CITY,P_ZIP,P_STATE,P_DATE_IN,P_DATE_OUT,P_ACTIVE);
          
  END  HM_PERSON_ADDRESS_UPDATED;
  /
   
  BEGIN
  HM_PERSON_ADDRESS_UPDATED(10,'14 FELUDA ST','APT 24','CAMBRIDGE',12145,'MA','12-DEC-2019');
  END;
  /
  ---------------------THIS PROCEDURE HELP TO FIND  THE LAST ADDRESS------------------------
  CREATE OR REPLACE PROCEDURE HM_LAST_ADDRESS(
  P_PERSON_ID IN H_PERSON_ADDRESS.PERSON_ID%TYPE
  )
  IS
  V_PERSON_ID H_PERSON_ADDRESS.PERSON_ID%TYPE;
  V_ADDRESS_LINE_1  H_PERSON_ADDRESS.ADDRESS_LINE_1%TYPE;
  V_ADDRESS_LINE_2  H_PERSON_ADDRESS.ADDRESS_LINE_2%TYPE;
  V_CITY h_person_address.city%TYPE;
  V_ZIP_CODE H_PERSON_ADDRESS.ZIP_CODE%TYPE;
  V_STATE H_PERSON_ADDRESS.STATE%TYPE;
  V_DATE_IN H_PERSON_ADDRESS.DATE_IN%TYPE;
  
  BEGIN
          SELECT PERSON_ID, ADDRESS_LINE_1,  ADDRESS_LINE_2, CITY, ZIP_CODE,  STATE, DATE_IN INTO 
          V_PERSON_ID,V_ADDRESS_LINE_1, V_ADDRESS_LINE_2,V_CITY, V_ZIP_CODE,V_STATE,V_DATE_IN FROM H_PERSON_ADDRESS
          WHERE ADDRESS_ID=(SELECT MAX(ADDRESS_ID) FROM H_PERSON_ADDRESS 
          WHERE PERSON_ID=P_PERSON_ID
          GROUP BY PERSON_ID) ;
          DBMS_OUTPUT.PUT_LINE('PERSON_ID= '||V_PERSON_ID||'  ' || 'ADDRESS= '|| V_ADDRESS_LINE_1 || ', '|| V_ADDRESS_LINE_2 ||', '||' ' ||V_CITY ||', ' || V_ZIP_CODE ||', '||''|| V_STATE ||'.  '|| 'MOVING DATE= '||V_DATE_IN);
  END HM_LAST_ADDRESS;
  /
  
  BEGIN
  HM_LAST_ADDRESS(10);
  END;
  /
  
  ----------------THIS PROCEDURE HELP TO FIND CAR OWNER NAME, ADDRESS AND PHONE NUMBER FROM NUMBER PLATE-----------------
  CREATE OR REPLACE PROCEDURE HM_NUMBER_PLATE_TO_ADDRESS(
  P_CARPLATE_NUMBER IN H_CAR_INFO.CARPLATE_NUMBER%TYPE
  )
  IS
  V_PERSON_ID H_PERSON_ADDRESS.PERSON_ID%TYPE;
  V_ADDRESS_LINE_1  H_PERSON_ADDRESS.ADDRESS_LINE_1%TYPE;
  V_ADDRESS_LINE_2  H_PERSON_ADDRESS.ADDRESS_LINE_2%TYPE;
  V_CITY h_person_address.city%TYPE;
  V_ZIP_CODE H_PERSON_ADDRESS.ZIP_CODE%TYPE;
  V_STATE H_PERSON_ADDRESS.STATE%TYPE;
  V_DATE_IN H_PERSON_ADDRESS.DATE_IN%TYPE;
  V_FIRST_NAME H_PERSON_NAME.FIRST_NAME%TYPE;
  V_LAST_NAME H_PERSON_NAME.LAST_NAME%TYPE;
  V_PHONE_NUMBER H_PHONE_NUMBER.PHONE_NUMBER%TYPE;
  
  BEGIN
          SELECT PERSON_ID, ADDRESS_LINE_1,  ADDRESS_LINE_2, CITY, ZIP_CODE,  STATE, DATE_IN INTO 
          V_PERSON_ID,V_ADDRESS_LINE_1, V_ADDRESS_LINE_2,V_CITY, V_ZIP_CODE,V_STATE,V_DATE_IN FROM H_PERSON_ADDRESS
          WHERE ADDRESS_ID=(SELECT  MAX( ADDRESS_ID)  AS ADDRESS FROM
          (SELECT C.CARPLATE_NUMBER, A. PERSON_ID, B.ADDRESS_ID, B.ADDRESS_LINE_1 FROM H_CAR_INFO C
          INNER JOIN H_PERSON_NAME A  ON A.PERSON_ID=C.PERSON_ID
          INNER JOIN H_PERSON_ADDRESS B ON B.PERSON_ID=A.PERSON_ID)
          WHERE CARPLATE_NUMBER= P_CARPLATE_NUMBER
          GROUP BY  CARPLATE_NUMBER);
          
          SELECT FIRST_NAME, LAST_NAME INTO V_FIRST_NAME, V_LAST_NAME FROM H_PERSON_NAME
          WHERE PERSON_ID= V_PERSON_ID;
          
          SELECT PHONE_NUMBER INTO v_phone_number FROM
          (SELECT  X.PERSON_ID, Y.PHONE_NUMBER FROM H_PERSON_NAME X  
          INNER JOIN H_PHONE_NUMBER Y ON X.PERSON_ID=Y.PERSON_ID)
          WHERE PERSON_ID=V_PERSON_ID;
          DBMS_OUTPUT.PUT_LINE('PERSON_ID= '||V_PERSON_ID);
          DBMS_OUTPUT.PUT_LINE('NAME= '||V_FIRST_NAME|| ' '||V_LAST_NAME);
          DBMS_OUTPUT.PUT_LINE('ADDRESS= '|| V_ADDRESS_LINE_1 || ', '|| V_ADDRESS_LINE_2 ||', '||' ' ||V_CITY ||', ' || V_ZIP_CODE ||', '||''|| V_STATE ||'.  '|| 'MOVING DATE= '||V_DATE_IN);
          DBMS_OUTPUT.PUT_LINE('PHONE NUMBER= '|| V_PHONE_NUMBER);
  END HM_NUMBER_PLATE_TO_ADDRESS;
  /
  BEGIN
  HM_NUMBER_PLATE_TO_ADDRESS('8J8K93');
  END;
  /
  
  
  -- COUNT PEOPLE WITH TATTOO 
  
  SELECT COUNT(PERSON_ID) AS TOTAL_PEOPLE_WITH_TATTOO FROM
  (SELECT A.PERSON_ID, B.MARK_DESCRIPTION FROM H_BODY_DECORATION_MARK_N  B
  INNER JOIN H_PHYSICAL_DESCRIPTION A ON A.BODY_DECORATION_MARK_ID=B.BODY_DECORATION_MARK_ID
  WHERE MARK_DESCRIPTION IN('TATTOO')
  );
  /
  --------------------- this views  help to find Email address who live in same zip code. To infom the resident any kind of notice or emergency or construction work-----------
  create view construction_email as
  select z.zip_code,p.e_mail from h_person_address z
  inner join h_person_name p on p.person_id=z.person_id
  where zip_code=45784;
  
  select * from construction_email;