Summarizing the data
You are a developer for the Metropolitan Public Library. The head librarian has requested a new feature for the online catalog: displaying rating statistics for each book to help patrons make informed reading choices. Currently, the system stores individual reviews in the book_reviews table; however, there's no way to view the total number of reviews a book has received or its average rating.
Your task is to write a query that summarizes review data by calculating the total number of reviews and the average rating for each book.
This exercise is part of the course
Querying a PostgreSQL Database in Java
Exercise instructions
- Complete the query to count the number of reviews and calculate the average rating.
- Group the results by
book_idto get statistics for each book.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
public class Main {
public static void main(String[] args) throws SQLException {
HikariDataSource ds = HikariSetup.createDataSource();
String query = """
-- Count the number of reviews and the average rating
SELECT book_id, ____(*), ____(rating)
FROM book_reviews
-- Group reviews by book_id
____ ____ ____;
""";
try (Connection conn = ds.getConnection();
PreparedStatement pstmt = conn.prepareStatement(query);
ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
System.out.printf("ID: %d, Count: %d, Average: %.0f%n",
rs.getInt("book_id"), rs.getInt("count"), rs.getDouble("avg"));
}
}
}
}