Esercizi Query Avanzate MySQL C++

Ecco degli esercizi semplici con soluzione per praticare l’utilizzo di query avanzate in MySQL utilizzando C++.

Esercizio 1: Query con filtro e ordinamento

Creare una query che filtra i record in base a un campo specifico e li ordina in ordine ascendente.

#include <mysql_driver.h>
#include <mysql_connection.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>
#include <cppconn/resultset.h>
#include <iostream>

int main() {
    sql::mysql::MySQL_Driver* driver = sql::mysql::get_mysql_driver_instance();
    std::unique_ptr<sql::Connection> con(driver->connect("tcp://127.0.0.1:3306", "root", "password"));
    con->setSchema("test_db");

    std::unique_ptr<sql::PreparedStatement> pstmt(con->prepareStatement("SELECT * FROM test_table WHERE age >= ? ORDER BY name ASC"));
    pstmt->setInt(1, 25);
    std::unique_ptr<sql::ResultSet> res(pstmt->executeQuery());

    while (res->next()) {
        std::cout << "ID: " << res->getInt("id") << ", Name: " << res->getString("name") << ", Age: " << res->getInt("age") << std::endl;
    }

    return 0;
}

Esercizio 2: Query con limitazione dei risultati

Creare una query che limita il numero di record restituiti.

#include <mysql_driver.h>
#include <mysql_connection.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>
#include <cppconn/resultset.h>
#include <iostream>

int main() {
    sql::mysql::MySQL_Driver* driver = sql::mysql::get_mysql_driver_instance();
    std::unique_ptr<sql::Connection> con(driver->connect("tcp://127.0.0.1:3306", "root", "password"));
    con->setSchema("test_db");

    std::unique_ptr<sql::PreparedStatement> pstmt(con->prepareStatement("SELECT * FROM test_table LIMIT 5"));
    std::unique_ptr<sql::ResultSet> res(pstmt->executeQuery());

    while (res->next()) {
        std::cout << "ID: " << res->getInt("id") << ", Name: " << res->getString("name") << ", Age: " << res->getInt("age") << std::endl;
    }

    return 0;
}

Esercizio 3: Query con join

Creare una query che utilizza una join tra due tabelle.

#include <mysql_driver.h>
#include <mysql_connection.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>
#include <cppconn/resultset.h>
#include <iostream>

int main() {
    sql::mysql::MySQL_Driver* driver = sql::mysql::get_mysql_driver_instance();
    std::unique_ptr<sql::Connection> con(driver->connect("tcp://127.0.0.1:3306", "root", "password"));
    con->setSchema("test_db");

    std::unique_ptr<sql::PreparedStatement> pstmt(con->prepareStatement(
        "SELECT a.id, a.name, b.department_name FROM employees a JOIN departments b ON a.department_id = b.id"
    ));
    std::unique_ptr<sql::ResultSet> res(pstmt->executeQuery());

    while (res->next()) {
        std::cout << "ID: " << res->getInt("id") << ", Name: " << res->getString("name")
                  << ", Department: " << res->getString("department_name") << std::endl;
    }

    return 0;
}

Esercizio 4: Query con aggregazione

Creare una query che utilizza funzioni di aggregazione come AVG per calcolare la media di un campo.

#include <mysql_driver.h>
#include <mysql_connection.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>
#include <cppconn/resultset.h>
#include <iostream>

int main() {
    sql::mysql::MySQL_Driver* driver = sql::mysql::get_mysql_driver_instance();
    std::unique_ptr<sql::Connection> con(driver->connect("tcp://127.0.0.1:3306", "root", "password"));
    con->setSchema("test_db");

    std::unique_ptr<sql::PreparedStatement> pstmt(con->prepareStatement("SELECT AVG(age) AS average_age FROM test_table"));
    std::unique_ptr<sql::ResultSet> res(pstmt->executeQuery());

    if (res->next()) {
        std::cout << "Età media: " << res->getDouble("average_age") << std::endl;
    }

    return 0;
}

Esercizio 5: Query con condizioni multiple

Creare una query che utilizza condizioni multiple (AND, OR) per filtrare i record.

#include <mysql_driver.h>
#include <mysql_connection.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>
#include <cppconn/resultset.h>
#include <iostream>

int main() {
    sql::mysql::MySQL_Driver* driver = sql::mysql::get_mysql_driver_instance();
    std::unique_ptr<sql::Connection> con(driver->connect("tcp://127.0.0.1:3306", "root", "password"));
    con->setSchema("test_db");

    std::unique_ptr<sql::PreparedStatement> pstmt(con->prepareStatement(
        "SELECT * FROM test_table WHERE (age >= ? AND status = ?) OR name = ?"
    ));
    pstmt->setInt(1, 25);
    pstmt->setString(2, "active");
    pstmt->setString(3, "John Doe");
    std::unique_ptr<sql::ResultSet> res(pstmt->executeQuery());

    while (res->next()) {
        std::cout << "ID: " << res->getInt("id") << ", Name: " << res->getString("name")
                  << ", Age: " << res->getInt("age") << ", Status: " << res->getString("status") << std::endl;
    }

    return 0;
}

Esercizio 6: Query con subquery

Creare una query che utilizza una subquery per filtrare i record.

#include <mysql_driver.h>
#include <mysql_connection.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>
#include <cppconn/resultset.h>
#include <iostream>

int main() {
    sql::mysql::MySQL_Driver* driver = sql::mysql::get_mysql_driver_instance();
    std::unique_ptr<sql::Connection> con(driver->connect("tcp://127.0.0.1:3306", "root", "password"));
    con->setSchema("test_db");

    std::unique_ptr<sql::PreparedStatement> pstmt(con->prepareStatement(
        "SELECT * FROM test_table WHERE age > (SELECT AVG(age) FROM test_table)"
    ));
    std::unique_ptr<sql::ResultSet> res(pstmt->executeQuery());

    while (res->next()) {
        std::cout << "ID: " << res->getInt("id") << ", Name: " << res->getString("name")
                  << ", Age: " << res->getInt("age") << std::endl;
    }

    return 0;
}