How to extract data in SQL?

In the context of SQL, you can extract easily the names of the products that have sold more than 100 units in the past month by using the SQL SELECT statement with the SUM, GROUP BY, and HAVING clauses. The WHERE Clause is used to filter the sales data within the last month. Here is the SQL-based query of how you can accomplish this:-

SELECT 

    Product_name, 

    SUM(units_sold) AS total_units_sold

FROM 

    Sales

WHERE 

    Sale_date >= DATEADD(MONTH, -1, GETDATE())

GROUP BY 

    Product_name

HAVING 

    SUM(units_sold) > 100;

Here is a detailed Java-based program that connects to a particular database, implements an SQL query for retrieving products that have sold more than 100 units in the past month, and then prints the results. This example would use the JDBC (JAVA DATABASE CONNECTIVITY) for database operations:-

Import java.sql.Connection;

Import java.sql.DriverManager;

Import java.sql.PreparedStatement;

Import java.sql.ResultSet;

Import java.sql.SQLException;

Import java.time.LocalDate;

Import java.time.format.DateTimeFormatter;

Public class SalesReport {

    Public static void main(String[] args) {

        // Database connection parameters

        String jdbcURL = “jdbc:mysql://localhost:3306/your_database_name”;

        String username = “your_username”;

        String password = “your_password”;

        // SQL query to select product names and total units sold in the past month

        String sql = “SELECT product_name, SUM(units_sold) AS total_units_sold “ +

                     “FROM Sales “ +

                     “WHERE sale_date >= ? “ +

                     “GROUP BY product_name “ +

                     “HAVING SUM(units_sold) > 100”;

        // Calculate the date one month ago

        LocalDate oneMonthAgo = LocalDate.now().minusMonths(1);

        String oneMonthAgoString = oneMonthAgo.format(DateTimeFormatter.ISO_LOCAL_DATE);

        // Establishing connection to the database

        Try (Connection connection = DriverManager.getConnection(jdbcURL, username, password);

             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {

            // Set the date parameter for the SQL query

            preparedStatement.setString(1, oneMonthAgoString);

            // Execute the query and get the result set

            Try (ResultSet resultSet = preparedStatement.executeQuery()) {

                // Print the results

                System.out.println(“Product Name		Total Units Sold”);

                System.out.println(“----------------------------------------“);

                While (resultSet.next()) {

                    String productName = resultSet.getString(“product_name”);

                    Int totalUnitsSold = resultSet.getInt(“total_units_sold”);

                    System.out.printf(“%-20s	%d%n”, productName, totalUnitsSold);

                }

            }

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

}