realestate.md

Database Schema

Home Owners

id	name	phone	email
1	C0D4	555-258-525	no@email.com
2	Bobby	825-258-481	bobby@email.com

Houses

id	address	suburb	state	postcode	owner
1	123 abc street	google lane	1	53842	1
2	987 xyz road	facebook road	2	18121	2

States

id	name
1	californina
2	florida

Queries:

find owners details per house

SELECT 
	h.id,
	h.address,
	h.suburb,
	s.name AS state_name,
	h.postcode,
	o.name AS owner_name,
	o.phone,
	o.email
FROM houses h
JOIN home_owners o
ON (h.owner = o.id)
JOIN states s
ON (s.id = h.state)
WHERE h.state = 1

find number of houses per state

SELECT
    s.name AS state_name,
    COUNT(h.id) AS houses_in_state
FROM states s
JOIN houses h ON (h.state = s.id)
GROUP BY s.id