Print out people's names, phone numbers, and zip code for those whose current status is positive. Finally print out number of people tested positive per zip code.

For this question, houses and person tables are required

DDL:

CREATE TABLE houses(

houseID INT PRIMARY KEY

, address VARCHAR(50)

, zipcode VARCHAR(10)

);

CREATE TABLE person(

personID INT PRIMARY KEY

, houseID INT

, personname VARCHAR(50)

, phonenumber VARCHAR(50)

, status INT

, FOREIGN KEY (houseID) REFERENCES houses(houseID)

);

And consider following test records,

INSERT INTO houses VALUES(100,'Ashford','30354');

INSERT INTO houses VALUES(200,'mount vernon','39878');

INSERT INTO houses VALUES(300,'dundown','23452');

INSERT INTO houses VALUES(400,'imila','43522');

INSERT INTO person VALUES(100,100,'Alice','987246632',0);

INSERT INTO person VALUES(200,200,'Rama','8899776655',1);

INSERT INTO person VALUES(300,100,'Gren','897465364',NULL);

INSERT INTO person VALUES(400,300,'Illia','7454657432',1);

INSERT INTO person VALUES(500,400,'Nivae','994856632',0);

INSERT INTO person VALUES(600,200,'Shar','94758753',1);

Stored Procedure:

CREATE OR REPLACE PROCEDURE usp_print_people_status_details

IS

p_name person.personname%TYPE;

p_phonenumber person.phonenumber%TYPE;

p_zipcode houses.zipcode%TYPE;

p_count INT;

-- Declare cursor to get all the records who tested postive

CURSOR curPersonsAll IS

SELECT personname, phonenumber, zipcode FROM person

INNER JOIN houses ON person.houseID = houses.houseID

WHERE status = 1;

-- Declare cursor to get all the records who tested postive , count by zip code

CURSOR curPersonsCountByZip IS

SELECT zipcode, COUNT(personID) FROM person

RIGHT JOIN houses ON person.houseID = houses.houseID

WHERE status = 1

GROUP BY houses.zipcode;

BEGIN

DBMS_OUTPUT.PUT_LINE('All Tested Positive cases in Zip Codes');

DBMS_OUTPUT.NEW_LINE;

OPEN curPersonsAll;

LOOP

FETCH curPersonsAll INTO p_name, p_phonenumber, p_zipcode;

EXIT WHEN curPersonsAll%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(p_name|| ', ' || p_phonenumber|| ', ' || p_zipcode);

DBMS_OUTPUT.NEW_LINE;

END LOOP;

CLOSE curPersonsAll;

DBMS_OUTPUT.PUT_LINE('Tested Positive Counts by Zip Codes');

DBMS_OUTPUT.NEW_LINE;

OPEN curPersonsCountByZip;

LOOP

FETCH curPersonsCountByZip INTO p_zipcode, p_count;

EXIT WHEN curPersonsCountByZip%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(p_zipcode|| ' - ' || p_count);

DBMS_OUTPUT.NEW_LINE;

END LOOP;

CLOSE curPersonsCountByZip;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('No positive cases found in any zip code');

END;