Technical interview question

Question

Assume you have two tables in a relational database, one named BOOK and one named AUTHOR. There is a column in BOOK named author_id that references the AUTHOR table. The idea here is that a single author may have written multiple books. Write a SQL query that produces a result similar to this:
Author Name total_books
----------- -------------
Charles Dickens 23
Dr. Seuss 51
Norman Mailer 20
Harper Lee 1

My answer:
Select A.author_name, count(B.total_books)
From Author A
Left Join Book B on A.author_id = B.author_id;

Answer 1

Your results would look like this

Author Name books written
----------- -------------
Charles Dickens 95

With COUNT() you must use GROUP BY
"A.author_name" should be just "A.name" since it's located in "author" table.
"A.author_id" should be just "A.id" for the same reason.
And I don't understand where does the "total_books" come from.

SELECT A.name, count(B.id)
FROM Author A
LEFT JOIN Book B ON A.id = B.author_id GROUP BY A.name;

Answer 2

Number of Books
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.

level
Sort the result by the number of books, from highest to lowest.
???????????????????????

Answer 3

Number of Books
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.

level
Sort the result by the number of books, from highest to lowest.

Answer 4

Assume you have two tables in a relational database, one named BOOK and one named AUTHOR. There is a column in BOOK named author_id that references the AUTHOR table. The idea here is that a single author may have written multiple books. Write a SQL query that produces a result similar to this:
Author Name total_books
----------- -------------
Charles Dickens 23
Dr. Seuss 51
Norman Mailer 20
Harper Lee 1

My Answer:

SELECT
A.author_name AS "Author Name",
COUNT(B.author_id) AS "Total Books"
FROM
AUTHOR A
LEFT JOIN
BOOK B ON A.author_id = B.author_id
GROUP BY
A.author_name
ORDER BY
"Total Books" DESC;

Answer 5

select books.name ,year , Authors.name as author
from books , Authors
where Authors.id = books.author_id order by Authors.name, year asc;

Answer 6

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

this code gives the required output for solution but why I am getting test case failed ?

Answer 7

Number of Books
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.

Sort the result by the number of books, from highest to lowest

CODE :

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