I am not able to solve this problem in solo learn for sql query

Question

User
You are working on the library database, which contains the Books and Authors tables.

Columns of the Books table: id, name, year, author_id.

Columns of the Authors table: id, name.

Write a query to get the author names and the number of books they have in the Books table.

Note that some authors do not have any books associated with them. In this case, the result needs to include their names and have 0 as the count. The count column should be called books in the result.

Answer 1

You need to use a left outer join, and connect the two tables by the author_id from the books table, and the id from the authors table.

Review the previous lessons and try to use the patterns and techniques that were presented.

If still having troubles, then please post your attempts for us to check.

Answer 2

This code worked out:

SELECT Authors.name, COUNT(Books.id) AS books
FROM Authors LEFT JOIN Books
ON Authors.id = Books.author_id
GROUP BY Authors.name
ORDER BY books DESC;

Answer 3

try to see the comments in that part of the lesson. There are a lot of people to help.

All the best

Answer 4

What is the code of the program? I am facing the same issue.

SELECT Authors. name AS author_name,
COUNT(Books.id) AS books
FROM Authors
LEFT JOIN Books ON Authors. id = Books.author_id
GROUP BY Authors. name
ORDER BY books DESC;
The code is not working.

Answer 5

SELECT a.name as name,COUNT (b.id) as books
from Authors as a
LEFT JOIN Books as b
ON a.id = b.author_id
GROUP by a.name
order by books DESC;