MySQL Practice Exercises with Solutions – Part 5

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)

Questions

1. Get the department and total salary, grouped by department, and sorted by total salary in descending order.

SELECT Departement, SUM(salary) AS total 
FROM employee 
GROUP BY Departement 
ORDER BY total DESC;

MySQL GROUP BYThe GROUP BY clause is used to group rows with the same values, and is used in SELECT statement with aggregation functions such as COUNT,…Read More MySQL ORDER BYThe ORDER BY keyword sorts the result set in ascending or descending order. To sort records in descending order, use DESC keyword. To sort in…Read More

2. Get the department, the number of employees in that department, and the total salary grouped by department, and sorted by total salary in descending order.

SELECT Departement, COUNT(First_name), SUM(salary) AS total 
FROM employee 
GROUP BY Departement 
ORDER BY total DESC;

MySQL 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 MySQL COUNT()In SQL, the COUNT() aggregation function is used to count the number of records in a table. Knowing the number of rows in a table…Read More

3. Get the average salary by department in ascending order of salary.

SELECT Departement, AVG(salary) AS AvgSalary 
FROM employee 
GROUP BY Departement 
ORDER BY AvgSalary ASC;

MySQL Average AVG()The AVG() aggregation function in the SQL language is used to calculate the average value over a set of non-zero numeric records. Syntax: The…Read More

4. Get the maximum salary by department in ascending order of salary.

SELECT Departement , MAX(salary) AS MaxSalary
FROM employee 
GROUP BY Departement  
ORDER BY MaxSalary ASC;

5. Get the minimum salary by department in ascending order of salary.

SELECT Departement, MIN(salary) AS MinSalary 
FROM employee 
GROUP BY Departement 
ORDER BY MinSalary ASC;

MySQL Select MIN()In SQL language, the MIN() aggregation function is used to return the minimum value of a column in a recordset. The function can be applied…Read More

6. Get the number of employees grouped by year and month of membership.

SELECT YEAR(joining_date) AS "Membership year", 
       MONTH(joining_date) AS "Membership month", 
       count(*) AS total_emp
FROM employee 
GROUP BY YEAR(joining_date), MONTH(joining_date);

7. Get the department and total salary grouped by the department, where the total salary is greater than 1,000,000, and sorted in descending order of the total salary.

SELECT Departement, SUM(salary) AS total_salary 
FROM employee 
GROUP BY Departement 
HAVING SUM(salary) > 1000000 
ORDER BY total_salary DESC;

MySQL HAVINGHAVING clause is used in SELECT statement to specify filter conditions for a group of rows or aggregates. HAVING clause is often used with GROUP…Read More

8. Get all the details of an employee if the employee exists in the Reward table? Or in other words, find employees with bonuses.

SELECT e.* FROM Employee AS e
WHERE EXISTS(
         SELECT r.Employee_ref_id FROM reward AS r 
         WHERE e.Employee_id = r.Employee_ref_id
);

How to check if a record exists in another table in MySQLIn this tutorial, we are going to see how to use MySQL EXISTS operator to check if a data exists in a table and when…Read More MySQL EXISTSIn SQL language, the EXISTS statement is used in a conditional clause to find out whether or not rows are present when using a subquery.…Read More

9. How to get common data in two query results?

SQL Syntax:

SELECT * FROM employee WHERE employee_id 
INTERSECT 
SELECT * FROM employee WHERE employee_id < 3;

Note: MySQL does not support the INTERSECT operator.
INTERSECT in MySQLIn this tutorial, we are going to see the INTERSECT operator and show you how to simulate the MySQL INTERSECT operator. You should know that…Read More

10. Get the IDs of employees who did not receive rewards without using subqueries?

SQL Syntax:

SELECT employee_id FROM Employee
MINUS
SELECT employee_ref_id FROM Reward;

Note: MySQL does not support the MINUS operator.
MINUS in MySQLIn this tutorial, we are going to see the MINUS operator and show you how to simulate the MySQL MINUS operator. You should know that…Read More

Employee table

Reward table

Questions

You May Also Like

MySQL Practice Exercises with Solutions – Part 1

MySQL Practice Exercises with Solutions – Part 6

MySQL Practice Exercises with Solutions – Part 2

Leave a Reply Cancel reply