MySQL Practice Exercises with Solutions – Part 4

We have split this collection of exercises on MYSQL into seven parts, this is the first one, then you will find the second part, the third part, the fourth part, the fifth part, the sixth part, seventh part, eighth part, and finally the ninth part these exercises are based on simple, advanced and finally complex MYSQL queries. These questions are based on the following two tables, Employee table and Reward table.

Employee table
| Employee_id | First_name | Last_name |  Salary  | Joining_date | Departement |
|     1       |     Bob    |   Kinto   | 1000000  |  2019-01-20  |   Finance   |
|     2       |    Jerry   |  Kansxo   | 6000000  |  2019-01-15  |     IT      |
|     3       |    Philip  |   Jose    | 8900000  |  2019-02-05  |   Banking   |
|     4       |    John    |  Abraham  | 2000000  |  2019-02-25  |  Insurance  |
|     5       |   Michael  |  Mathew   | 2200000  |  2019-02-28  |   Finance   |
|     6       |    Alex    |  chreketo | 4000000  |  2019-05-10  |     IT      |
|     7       |    Yohan   |   Soso    | 1230000  |  2019-06-20  |   Banking   |
Reward table
| Employee_ref_id | date_reward | amount |
|         1       | 2019-05-11  |  1000  | 
|         2       | 2019-02-15  |  5000  |
|         3       | 2019-04-22  |  2000  |
|         1       | 2019-06-20  |  8000  |
Download the file containing the SQL statements allowing the creation of these tables. (Click here)




1. Get all the details on employees whose participation month (Joining_date) is “January”

SELECT * FROM employee WHERE MONTH(joining_date) = '01';

How To Get Month From Date In MySQLHow To Get Month From Date In MySQLIn this tutorial, we are going to see how to get month from date in MySQL. In SQL language MONTH() function allows extracting month number…Read More


2. Get all the details of the employees who joined before March 1, 2019

SELECT * FROM employee WHERE joining_date < '2019-03-01';


3. Get all the details on employees who joined after March 31, 2019

SELECT * FROM employee WHERE joining_date > '2019-03-31';


4. Get the date and time of the employee’s enrollment.

SELECT CONVERT(DATE_FORMAT(joining_date,'%Y-%m-%d-%H:%i:00'),DATETIME) FROM employee;

MySQL CONVERT()MySQL CONVERT()The SQL CONVERT() function, allows to convert a data of one type into another, similar to CAST(). For example, the function allows you to convert…Read More MySQL DATE_FORMAT()MySQL DATE_FORMAT()In SQL language, DATE_FORMAT() function allows formatting a DATE data in the indicated format. It is the ideal function if you want to define the…Read More


5. Get the date and time, including milliseconds of the employee’s membership.

SELECT MICROSECOND(joining_date) FROM employee;


6. Get the difference between the “Joining_date” and “date_reward” column

SELECT R.date_reward - E.joining_date 
FROM employee E 
INNER JOIN reward R ON E.employee_id = R.employee_ref_id;

MySQL INNER JOIN with ExamplesMySQL INNER JOIN with ExamplesInner JOIN is used to return rows from two tables that meet a given condition. In MySQL, INNER JOIN selects all rows from two participating…Read More


7. Get the current date and time.

SELECT now();

MySQL NOW()MySQL NOW()In SQL, NOW() function returns the system date and time. This function is very useful to record the date and time of insertion or update…Read More


8. Get the first names of employees who have the character ‘%’. Example: ‘Jack%’.

Select First_name FROM employee WHERE First_name LIKE '%\%%';

We use the escape character ‘ \ ‘ for special characters in a query.
MySQL LIKEMySQL LIKEThe LIKE operator is a logical operator used in WHERE clause that tests whether a string contains a specified pattern or not. Two wildcards are…Read More


9. Get the employee name (Last_name) after replacing the special character with white space.

SELECT REPLACE(Last_name,'%',' ') FROM employee;
MySQL REPLACEMySQL REPLACEREPLACE() function in MySQL replaces all occurrences of a substring in a string.   Example 1: In the example below, we replace “Java” with “MySQL”…Read More


10. Get the employee’s department and total salary, grouped by department.

SELECT Departement, SUM(Salary) AS total 
FROM employee 
GROUP BY Departement;

MySQL SUM() with ExampleMySQL Select SUM() with ExampleIn SQL language, the SUM() aggregation function allows calculating the total sum of a column containing numerical values. This function only works on columns of…Read More


mcqMCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More

One thought on “MySQL Practice Exercises with Solutions – Part 4

  • for ex 6 the your query is wrong (SELECT R.date_reward – E.joining_date
    FROM employee E
    INNER JOIN reward R ON E.employee_id = R.employee_ref_id;)

    you have have to use datediff for a correct difference:

    select employee.Joining_date, reward.date_reward, datediff(reward.date_reward,employee.Joining_date) as dif from employee join reward on employee.Employee_id=reward.Employee_ref_id;


Leave a Reply

Your email address will not be published. Required fields are marked *