Analyzing iMessage with SQL(arctype.com)

    create table person (
        id serial primary key,
        first_name text not null,
        last_name text not null,
        date_of_birth date not null
    );

    create table address (
        id serial primary key,
        person_id integer not null references person (id),
        street_number text not null,
        apartment_number text,
        city text not null,
        state text,
        postal_code text not null
    );

    insert into person (first_name, last_name, date_of_birth)
    values ('Brian', 'Kernighan', '1950-01-01'),  -- gets id 1
           ('Dennis', 'Ritchie', '1948-01-01');   -- gets id 2

    insert into address (person_id, street_number, city, postal_code)
    values (1, '123 Main St.', 'Berkeley', '94701');

    -- an inner join, only returns `person` rows with a corresponding
    -- row in the `address` table
    select person.first_name,
           person.last_name,
           address.street_number,
           address.city,
           address.postal_code
    from person
      inner join address on person.id = address.person_id
    -- output:
    --  first_name | last_name | street_number |   city   | postal_code
    --  Brian      | Kernighan | 123 Main St.  | Berkeley | 94701



    -- a left outer join, returns all `person` rows, regardless of
    -- the existence of a corresponding row in the `address` table
    select person.first_name,
           person.last_name,
           address.street_number,
           address.city,
           address.postal_code
    from person
      left outer join address on person.id = address.person_id

    -- output:
    --  first_name | last_name | street_number |   city   | postal_code
    --  Brian      | Kernighan | 123 Main St.  | Berkeley | 94701
    --  Dennis     | Ritchie   |               |          |