Advanced SQL queries

By following this recipe, we will learn how to use advanced SQL statements such as INNER JOIN, COUNT, LIKE, DISTINCT, and so on.

How to do it…

You can do a lot more than just perform simple queries of SQL database:

  1. First of all, we need to add a few tables to our database before we can dive into the programming part. Open up your web browser and access your phpMyAdmin. We need several tables for this example to work:
    How to do it…
  2. I will show you the structure of each of the tables required for this project and the dummy data inserted to the tables for testing. The first table is called branch, which is used to store the IDs and names of different branches of the dummy company:
    How to do it…
  3. Secondly, we have the department table, which stores the IDs and names of different departments of the dummy company, which is also linked to the branch data by the branch IDs:
    How to do it…
  4. Next, we also have an employee table, which stores the information of all the employees in the dummy company. This table is similar to the one we used in the previous examples, except it has two more extra columns, namely birthday and departmentID:
    How to do it…
  5. Other than that, we also have a table called log, which contains dummy records of the login time for each employee. The loginTime column can be a timestamp or date time variable type:
    How to do it…
  6. Lastly, we have the user table that we also used in the previous examples:
    How to do it…
  7. We are done with the database; let's move on to Qt. Open up Qt Creators, and this time, instead of choosing Qt Widgets Application, we create Qt Console Application:
    How to do it…
  8. After you have done creating your console project, open up your project file (.pro) and add the SQL module to your project:
    QT += core sql
QT -= gui
  9. Next, open up main.cpp and add the following header files to the top of the source file:
    #include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QDate>
#include <QDebug>
  10. Then, add the following function to display employees who are above 30 years old:
    void filterAge()
{
  qDebug() << "== Employees above 30 year old =============";
  QSqlQuery query;
  if (query.exec("SELECT name, age FROM employee WHERE age > 30"))
  {
    while (query.next())
    {
      qDebug() << query.value(0).toString() << query.value(1).toString();
    }
  }
  else
  {
    qDebug() << query.lastError().text();
  }

  qDebug() << "
";
}
  11. After that, add this function for displaying the department and branch information of each employee:
    void getDepartmentAndBranch()
{
  qDebug() << "== Get employees' department and branch =============";

  QSqlQuery query;
  if (query.exec("SELECT myEmployee.name, department.name, branch.name FROM (SELECT name, departmentID FROM employee) AS myEmployee INNER JOIN department ON department.id = myEmployee.departmentID INNER JOIN branch ON branch.id = department.branchID"))
  {
    while (query.next())
    {
      qDebug() << query.value(0).toString() << query.value(1).toString() << query.value(2).toString();
    }
  }
  else
  {
    qDebug() << query.lastError().text();
  }

  qDebug() << "
";
}
  12. Next, add this function, which displays employees who are working in the New York branch and are below 30 years old:
    void filterBranchAndAge()
{
  qDebug() << "== Employees from New York and age below 30 =============";

  QSqlQuery query;
  if (query.exec("SELECT myEmployee.name, myEmployee.age, department.name, branch.name FROM (SELECT name, age, departmentID FROM employee) AS myEmployee INNER JOIN department ON department.id = myEmployee.departmentID INNER JOIN branch ON branch.id = department.branchID WHERE branch.name = 'New York' AND age < 30"))
  {
    while (query.next())
    {
      qDebug() << query.value(0).toString() << query.value(1).toString() << query.value(2).toString() << query.value(3).toString();
    }
  }
  else
  {
    qDebug() << query.lastError().text();
  }

  qDebug() << "
";
}
  13. Then, add this function which counts the total number of female employees in the dummy company:
    void countFemale()
{
  qDebug() << "== Count female employees =============";

  QSqlQuery query;
  if (query.exec("SELECT COUNT(gender) FROM employee WHERE gender = 1"))
  {
    while (query.next())
    {
      qDebug() << query.value(0).toString();
    }
  }
  else
  {
    qDebug() << query.lastError().text();
  }

  qDebug() << "
";
}
  14. Once you're done with that, we will add another function, which filters the employee list and only displays those who have name that starts with Ja:
    void filterName()
{
  qDebug() << "== Employees name start with 'Ja' =============";

  QSqlQuery query;
  if (query.exec("SELECT name FROM employee WHERE name LIKE '%Ja%'"))
  {
    while (query.next())
    {
      qDebug() << query.value(0).toString();
    }
  }
  else
  {
    qDebug() << query.lastError().text();
  }

  qDebug() << "
";
}
  15. Next, we also add another function, which displays employees who have their birthdays in August:
    void filterBirthday()
{
  qDebug() << "== Employees birthday in August =============";

  QSqlQuery query;
  if (query.exec("SELECT name, birthday FROM employee WHERE MONTH(birthday) = 8"))
  {
    while (query.next())
    {
      qDebug() << query.value(0).toString() << query.value(1).toDate().toString("d-MMMM-yyyy");
    }
  }
  else
  {
    qDebug() << query.lastError().text();
  }

  qDebug() << "
";
}
  16. Then, we add the last function, which checks who logged in to the dummy system on 27 April 2016 and displays their names on the terminal:
    void checkLog()
{
  qDebug() << "== Employees who logged in on 27 April 2016 =============";

  QSqlQuery query;
  if (query.exec("SELECT DISTINCT myEmployee.name, FROM (SELECT id, name FROM employee) AS myEmployee INNER JOIN user ON user.employeeID = myEmployee.id INNER JOIN log ON log.userID = user.id WHERE DATE(log.loginTime) = '2016-04-27'"))
  {
    while (query.next())
    {
      qDebug() << query.value(0).toString();
    }
  }
  else
  {
    qDebug() << query.lastError().text();
  }

  qDebug() << "
";
}
  17. Lastly, in our main() function, connect our program to the MySQL database and call all the functions that we have defined in the previous steps. After that, close the database connection and we're done:
    int main(int argc, char *argv[])
{
  QCoreApplication a(argc, argv);

  QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
  db.setHostName("127.0.0.1");
  db.setUserName("reonyx");
  db.setPassword("reonyx");
  db.setDatabaseName("testing");

  if (db.open())
  {
    filterAge();
    getDepartmentAndBranch();
    filterBranchAndAge();
    countFemale();
    filterName();
    filterBirthday();
    checkLog();

    db.close();
  }
  else
  {
    qDebug() << "Failed to connect to database.";
  }

  return a.exec();
}
  18. Compile and run the project now and you should see a terminal window, which displays the filtered results from the database as defined earlier:
    How to do it…

How it works…

A console application does not have any GUI at all and only shows you a text display in a terminal window. This is usually used in a backend system, as it uses fewer resources compared to a widget application. We use it in this example because it's faster to display the result without the need to place any widgets in the program, which we don't need in this case.

We separated the SQL queries into different functions so that it's easier to maintain the code and it doesn't become too messy. Do note that in C++, the functions have to be located before the main() function, or they will not be able to be called by main().

There's more…

The INNER JOIN statement used in the preceding example joins two tables together and selects all rows from both tables, as long as there is a match between the columns in both tables. There are many other types of JOIN statement that you can use in MySQL (and some other types of SQL architecture), such as LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and so on. The following diagram shows the different types of JOIN statements and their effects:

There's more…
  1. The LIKE statement is normally used to search for a string variable in the database without the full word. Notice that there are two % symbols, located before and after the search keyword.
  2. The DISTINCT statement used in the previous example filters out results that have the exact same variable. For example, without the DISTINCT statement, you will see two versions of Larry King appear in the terminal because there are two records of him logging in to the system on the same day. By adding the DISTINCT statement, MySQL will eliminate one of the repeating results and ensure every result is unique.
  3. You may be wondering what d-MMMM-yyyy stands for and why we used it in the preceding example. That is actually an expression supplied to the QDateTime class in Qt to display the date time result using a given format. In this case, it will change the date time data that we get from MySQL, 2016-08-06, to the format that we specified, resulting in 6-August-2016. For more information, check out Qt's documentation at http://doc.qt.io/qt-5/qdatetime.html#toString, which has the full list of expressions that can be used to determine the format of the date and time string.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.217.147.193