DATABASE_PROJECT_EASECENTRAL

#TABLE SCHEMA
Persons : personID, firstName, lastName
Addresses : addressId, streetAddress1, streetAddress2, state, city, zip, country, personId
PhoneNumbers : phoneNumberId, value, personId 

#Display people and their phone numbers
SELECT persons.firstname, persons.lastname, phonenumbers.value 
FROM persons LEFT JOIN phonenumbers on 
persons.personId=phonenumbers.personId;

#Display people and their addresses
SELECT persons.firstname, persons.lastname, addresses.streetaddress1, addresses.streetaddress2, addresses.city, addresses.state, addresses.country 
FROM persons LEFT JOIN addresses on 
persons.personId=addresses.personId;

#Display people and their addresses only if they are in the state of California
SELECT persons.firstname, persons.lastname, addresses.streetaddress1, addresses.streetaddress2, addresses.city, addresses.state, addresses.country 
FROM persons INNER JOIN addresses on 
persons.personId=addresses.personId AND addresses.state='CA';

#Show how many people have addresses in each state
SELECT addresses.state, count(persons.personId) as 'PersonCount'
FROM persons INNER JOIN addresses on 
persons.personId=addresses.personId GROUP BY addresses.state;

#Show the % of people that have multiple addresses
SELECT count(*) * 100.0/(select count(*) from persons)
from 
(SELECT persons.personId, count(*)
FROM persons INNER JOIN addresses on 
persons.personId=addresses.personId GROUP BY persons.personId
HAVING count(addresses.addressId) > 1) MultiAddress;