LoslegenKostenlos loslegen

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.

Diese Übung ist Teil des Kurses

Querying a PostgreSQL Database in Java

Kurs anzeigen

Anleitung zur Übung

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

Interaktive Übung

Vervollständige den Beispielcode, um diese Übung erfolgreich abzuschließen.

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"));
                }
            }
        }
    }
}
Code bearbeiten und ausführen