开始使用免费开始使用

Filtering the groups

The Metropolitan Public Library's new rating statistics feature is a hit! However, patrons have complained that books with only one or two reviews can have misleading averages. A book with a single 5-star review appears just as highly rated as a beloved classic with hundreds of positive reviews.

The library director has asked you to enhance the system by allowing users to filter out books with insufficient reviews.

本练习是课程的一部分

Querying a PostgreSQL Database in Java

查看课程

练习说明

  • Complete the query to filter books with a specified number of reviews.
  • Securely set the parameter only to display books with at least three reviews.

交互式实操练习

通过完成这段示例代码来试试这个练习。

public class Main {
    public static void main(String[] args) throws SQLException {
        HikariDataSource ds = HikariSetup.createDataSource();
        String query = """
            SELECT book_id, COUNT(*), AVG(rating)
            FROM book_reviews
            GROUP BY book_id
            -- Filter books with a specified number of reviews
            ____ count(*) >= ?
            """;

        try (Connection conn = ds.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(query)) {
            // Display books with at least three reviews
            pstmt.____(____, ____);
            try (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"));
                }
            }
        }
    }
}
编辑并运行代码