Question

Write a query that will return the first name, last name, housing status and family status from the Person and PersonDemographics tables. Note that there may be missing data in some of the fields due to this being a ‘live’ database that was developed over time. If you like, you can fill in some of the missing demographics information, if that will help you in your project.

The Volunteers work for:

• Food Shelf
• Administration
• Housing
• Outreach
• Fund Raising

Job functions by Organization:

• Food Shelf: Intake Worker, Warehouse worker, Inventory, Vendor Management
• Administration: Administrative Assistant, Financial Assistant
• Housing: Coordinator, Client Outreach
• Outreach: Media Relations, Marketing Assistant
• Fundraising: Charity Outreach, Fundraiser, Event Coordinator, Event Volunteer

Answer 1

Count number of rows is null
select count(*) from Person p where p.firstname is null;
select count(*) from Person p where p.lastname is null;

select p.firstname, p.lastname, pd.housingstatus, pd.familystatus from Person p, PersonDemographics pd
where p.firstname is not null and p.lastname is not null and pd.housingstatus is not null and pd.familystatus is not null
and p.firstname = pd.firstname and p.lastname = pd.lastname groupby pd.familstatus;