CommencerCommencer gratuitement

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.

Cet exercice fait partie du cours

Querying a PostgreSQL Database in Java

Afficher le cours

Instructions

  • 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.

Exercice interactif pratique

Essayez cet exercice en complétant cet exemple de code.

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"));
                }
            }
        }
    }
}
Modifier et exécuter le code