Solution:Step 1: Use LEFT JOINs to include all authorsLEFT JOIN books and reviews to include authors without books or reviews.Step 2: Aggregate average rating and group resultsUse AVG(r.rating) and GROUP BY author name and book title.Final Answer:SELECT a.name, b.title, AVG(r.rating) FROM authors a

[Solved] Given tables:authors(id, name), books(id, author_id, title), reviews(id, book_id, rating)How to write a query to show each author's name, book title, and average rating, including authors without... | SQL

SQL - LEFT and RIGHT JOIN

Given tables:
authors(id, name), books(id, author_id, title), reviews(id, book_id, rating)
How to write a query to show each author's name, book title, and average rating, including authors without books or reviews?

ASELECT a.name, b.title, AVG(r.rating) FROM authors a INNER JOIN books b ON a.id = b.author_id INNER JOIN reviews r ON b.id = r.book_id GROUP BY a.name, b.title;

BSELECT a.name, b.title, AVG(r.rating) FROM authors a LEFT JOIN books b ON a.id = b.author_id LEFT JOIN reviews r ON b.id = r.book_id GROUP BY a.name, b.title;

CSELECT a.name, b.title, AVG(r.rating) FROM authors a LEFT JOIN books b ON b.author_id = a.id INNER JOIN reviews r ON r.book_id = b.id GROUP BY a.name, b.title;

DSELECT a.name, b.title, AVG(r.rating) FROM authors a RIGHT JOIN books b ON a.id = b.author_id LEFT JOIN reviews r ON b.id = r.book_id GROUP BY a.name, b.title;

Step-by-Step Solution

Solution:

Step 1: Use LEFT JOINs to include all authors
LEFT JOIN books and reviews to include authors without books or reviews.
Step 2: Aggregate average rating and group results
Use AVG(r.rating) and GROUP BY author name and book title.
Final Answer:
SELECT a.name, b.title, AVG(r.rating) FROM authors a LEFT JOIN books b ON a.id = b.author_id LEFT JOIN reviews r ON b.id = r.book_id GROUP BY a.name, b.title; -> Option B
Quick Check:
LEFT JOIN + AVG + GROUP BY = SELECT a.name, b.title, AVG(r.rating) FROM authors a LEFT JOIN books b ON a.id = b.author_id LEFT JOIN reviews r ON b.id = r.book_id GROUP BY a.name, b.title; [OK]

Quick Trick: LEFT JOIN preserves all authors; aggregate ratings with GROUP BY [OK]

Common Mistakes:

MISTAKES

Using INNER JOIN excludes authors without books
Mixing JOIN types incorrectly
Omitting GROUP BY with aggregates

Master "LEFT and RIGHT JOIN" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

More SQL Quizzes

Given tables:

Step 1: Use LEFT JOINs to include all authors

Step 2: Aggregate average rating and group results

Final Answer:

Quick Check:

Want More Practice?