Firstly needed to fix some create table elements, then added some queries that demonstrate the steps in the process, each adding an important bit to get closer to the result.

  -- SQLite version 3.37.2  [ Release: 2022-01-06 ]  on  SQLitespeed version 2.1.3.11.
  -- ================================================================================================

create table book (
	id INTEGER PRIMARY KEY,
	name text
);

create table author (
	id INTEGER PRIMARY KEY,
	name text
);

create table book_author (
	id INTEGER PRIMARY KEY,
	book_id int,
	author_id int,
	UNIQUE(book_id,author_id)
);

insert into book(name) values
('Science'),
('Math'),
('History'),
('Filipino'),
('English');

insert into author(name) values
('Harry Potter'),
('Ron Weasley'),
('Hermione Granger');

insert into book_author(book_id,author_id) values
(1,1),
(2,2),
(2,3),
(3,3),
(4,3),
(5,1),
(5,3);



SELECT B.name AS Book, A.name AS Author
  FROM book_author AS L
  JOIN book AS B ON B.id = L.book_id
  JOIN author AS A on A.id = L.author_id
;

  -- Book      |Author            
  -- ----------|------------------
  -- Science   |Harry Potter      
  -- Math      |Ron Weasley       
  -- Math      |Hermione Granger  
  -- History   |Hermione Granger  
  -- Filipino  |Hermione Granger  
  -- English   |Harry Potter      
  -- English   |Hermione Granger  



SELECT B.name AS Book, group_concat(A.name) AS Authors
  FROM book_author AS L
  JOIN book AS B ON B.id = L.book_id
  JOIN author AS A on A.id = L.author_id
 GROUP BY Book
;

  -- Book      |Authors                        
  -- ----------|-------------------------------
  -- English   |Harry Potter,Hermione Granger  
  -- Filipino  |Hermione Granger               
  -- History   |Hermione Granger               
  -- Math      |Ron Weasley,Hermione Granger   
  -- Science   |Harry Potter                   



SELECT B.name AS Book, group_concat(A.name) AS Authors
  FROM book_author AS L
  JOIN book AS B ON B.id = L.book_id
  JOIN author AS A on A.id = L.author_id
 GROUP BY Book
 ORDER BY Book DESC
;

  -- Book      |Authors                        
  -- ----------|-------------------------------
  -- Science   |Harry Potter                   
  -- Math      |Ron Weasley,Hermione Granger   
  -- History   |Hermione Granger               
  -- Filipino  |Hermione Granger               
  -- English   |Harry Potter,Hermione Granger  

Fully agreed - if this turns out to be an actual real-world DB, Keith's suggestions are invaluable for good form, efficiency and maintainability.

Also note that all the queries are incorrect. They should all be based off the following definition (view) if one wishes to have at all the data at once (note that you cannot retrieve a book with no authors, nor an author with no books; although this can be fixed, is it possible to have a book authored by no-one or a book authors with no books? -- this is what is called a "business rule" and has not been discussed and has major implications and can be easily handled).

create view all_books
as select book.book_id,
          book.name as book_name,
          author.author_id,
          author.name as author_name
     from book, author, book_author
    where book.book_id == book_author.book_id
      and author.author_id == book_author.author_id
;

If you were to do this, then the following provides a view that permits authorless books and bookless authors by emulating a full outer join:

create view all_books
as select book.book_id,
          book.name as book_name,
          author.author_id,
          author.name as author_name
     from book, author, book_author
    where book.book_id == book_author.book_id
      and author.author_id == book_author.author_id
 UNION
   select book.book_id,
          book.name as book_name,
          NULL as author_id,
          NULL as author_name
     from book
    where not exists (
                      select *
                        from book_author
                       where book.book_id == book_author.book_id
                     )
 UNION
   select NULL as book_id,
          NULL as book_name,
          author.author_id,
          author.name as author_name
     from author
    where not exists (
                      select *
                        from book_author
                       where author.author_id == book_author.author_id
                     )
;

If the view were used for all the queries, then the "business rule" (whatever it is) will be enforced automagically without having to remember to propagate it everywhere by manual methods.

Note that if RIGHT OUTER JOIN were supported by SQLite3 (it is not) this would simplify to:

create view all_books
as        select book.book_id,
                 book.name as book_name,
                 author.author_id,
                 author.name as author_name
            from book
 left outer join book_author on book.book_id == book_author.book_id
right outer join author on author.author_id == book_author.author_id
;

Since LEFT outer joins are supported and RIGHT outer joins are not, then this would be perhaps a more efficient view (it should be noted, however, that this phrasing constrains the query planner, whereas the long form in the previous post does not).

create view all_books
as select book.book_id,
          book.name as book_name,
          author.author_id,
          author.name as author_name
     from book
left join book_author on book_author.book_id == book.book_id
left join author on author.authorid == book_author.author_id
 UNION
   select NULL as book_id,
          NULL as book_name,
          author.author_id,
          author.name as author_name
     from author
    where not exists (
                      select *
                        from book_author
                       where author.author_id == book_author.author_id
                     )
;

There is no == operator in SQL.

There is in SQLite’s dialect of SQL:

https://sqlite.org/lang_expr.html#binaryops

This looks rather like a homework assignment.

This looks rather like a homework assignment.

It would seem at first glance, but I doubt it. homework assignments are usually correctly stated, have sensible schemata and makes sense altogether.

This has an air of quickly fumbled-together analog to some real-world problem with broken schemata and nonsensical things like character names used for book titles, but done with obviously competent yet incomplete SQL knowledge.

Of course said real-world problem may well also be homework-related, but this feels more like someone who wants to ask "how do you add multiple items within a group together?" but decided to pose it as an analogous query, probably wishing to seem less uninformed and not-yet realizing that here we praise efforts to learn and do not belittle it - so there really is no need.

Or I could be wrong. :)

I have several book and document sqlite DBs and typically have something like this CREATE TABLE authors(id integer primary key autoincrement,author text,authorid integer); CREATE TABLE keywords(id integer primary key autoincrement,keyword text,authorid integer,titleid integer); CREATE TABLE IF NOT EXISTS "books" ( id integer, titleid integer, authorid integer, title text, pubyear date, filename text, notes text, Birthdate text, PRIMARY KEY(id AUTOINCREMENT) ); to handle subject / keyword and tie that to an author and book. Every book has an author, even if it's 'Unknown'.