Need this code in PL/SQL for Procedures and Functions, URGENT!! Feature 10: Print out names, phone numbers, house ID, and status of people who live in the same house as anyone whose current status is 1 (tested positive). Feature 11: Given a start and end date, print out names, phone number, status, and flight ID of people who were on the same flight with someone whose status is 1 (tested positive) and the flight date is between the start and end date. Feature 12: Given a start and end date, print out name, phone number, status and event ID of people who attended the same event with someone whose status is 1 (tested positive) and the event date is between the start and end date. Feature 13: Given an input date, print out names of people who are considered new cases on that date. A new case means a person tested positive on that date but never tested positive before that date. For example, if the input is May 5th 2021, and John tested negative on May 3rd 2021 but positive on May 5th 2021, and Alice tested positive on May 5th 2021 but never tested before, then both John and Alice should be counted. Feature 14: print out names of people who have recovered. A person has recovered if the person tested positive before but the current status is 0 (not positive).
Assumptions:
You can make the following assumptions in this project.
- The
database stores information about houses, each with a house ID, address, and zip code.
- The database stores information about person, including person ID, house ID where the person lives, person name, phone number, and status where 1 means currently tested positive (i.e., the most recent test result is positive), 0 means currently tested negative (i.e., the most recent test result is negative), and null (no test so far so status is unknown).
- The database stores information about a person's all past tests, including person ID, test date, and result (1 means positive and 0 negative).
- The database stores information about events (could be gathering, party, etc.). Each event has an event ID, event name, date, and address.
- The database stores information about a person participated in an event.
- The database stores information about flights, including flight ID, flight date, flight number (a varchar type e.g., 'DL 345' is a delta 345 flight).
- The database stores IDs of people on a specific flight.
Need this code in PL/SQL for Procedures and Functions, URGENT!!
Feature 10: Print out names, phone numbers, house ID, and status of people who live in the same house as anyone whose current status is 1 (tested positive).
Feature 11: Given a start and end date, print out names, phone number, status, and flight ID of people who were on the same flight with someone whose status is 1 (tested positive) and the flight date is between the start and end date.
Feature 12: Given a start and end date, print out name, phone number, status and event ID of people who attended the same event with someone whose status is 1 (tested positive) and the event date is between the start and end date.
Feature 13: Given an input date, print out names of people who are considered new cases on that date. A new case means a person tested positive on that date but never tested positive before that date. For example, if the input is May 5th 2021, and John tested negative on May 3rd 2021 but positive on May 5th 2021, and Alice tested positive on May 5th 2021 but never tested before, then both John and Alice should be counted.
Feature 14: print out names of people who have recovered. A person has recovered if the person tested positive before but the current status is 0 (not positive).
Trending now
This is a popular solution!
Step by step
Solved in 3 steps
