sqlite sql

create table authors(
    id integer primary key,
    first_name varchar(127),
    last_name varchar(127)
);

create table books(
    id integer primary key,
    title varchar(255),
    author integer,
    foreign key(author) references authors(id)
);

insert into authors(first_name, last_name) values('Mary', 'Shelley');
insert into authors(first_name, last_name) values('Jane', 'Austen');

insert into books(title, author) values('Frankenstein', 1);
insert into books(title, author) values('Jane Eyre', 2);
insert into books(title, author) values('Wuthering Heights', 2);

select books.id, books.title, authors.first_name, authors.last_name from books join authors where books.author == authors.id;

-- id  title                first_name  last_name
-- 1   Frankenstein         Mary        Shelley
-- 2   Jane Eyre            Jane        Austen
-- 3   Wuthering Heights    Jane        Austen