MySQL Practice Exercises with Solutions – Part 7

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 first name, the reward amount for employees even if they did not receive any rewards, and set a reward amount equal to 0 for the employees who did not receive rewards.

Select First_name, IFNULL(amount,0) 
FROM Employee E 
LEFT JOIN Reward R 
ON E.employee_id = R.employee_ref_id;

Replace All NULL Values with 0 in MySQLWhen you execute a Left Join or a UNION query, DBMS places NULL where there is no match in the set. In this tutorial, we…Read More

2. Get the employee’s first name, the reward amount for employees who have rewards.

SELECT First_name, amount 
FROM Employee E 
INNER JOIN Reward R 
ON E.employee_id = R.employee_ref_id;

MySQL 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

3. Get the first name, the reward amount for employees who have rewards using “Right Join”.

SELECT First_name, IFNULL(amount,0) 
FROM Employee E 
RIGHT JOIN Reward R 
ON E.employee_id = R.employee_ref_id;

MySQL RIGHT JOINIn the SQL language, the RIGHT JOIN command (also called RIGHT OUTER JOIN) is a type of join between 2 tables. It allows listing all…Read More

4. Get the maximum reward per employee using subquery.

SELECT Departement, 
      (
         SELECT IFNULL(MAX(amount),0) 
         FROM Reward 
         WHERE employee_ref_id = employee_id
      ) AS max_reward
FROM Employee;

5. Get the TOP salary of two employees

SELECT * 
FROM employee 
ORDER BY salary DESC 
LIMIT 2;

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 MySQL LIMITLIMIT keyword is used to limit the number of rows returned in the result of a query. It can be used in conjunction with SELECT,…Read More

6. Get the TOP salary of N employees.

SELECT * 
FROM employee 
ORDER BY salary DESC 
LIMIT N;

7. Get the 2nd highest salary of an employee.

SELECT MIN(salary) 
FROM (
        SELECT * 
        FROM employee 
        ORDER BY salary DESC 
        LIMIT 2
     ) AS S;

8. Get the Nth highest salary of an employee.

SELECT MIN(salary) 
FROM (
        SELECT * 
        FROM employee 
        ORDER BY salary DESC 
        LIMIT N
     ) AS S;

9. Get the employee’s first name and last name in separate lines.

SELECT First_name 
FROM employee 
UNION 
SELECT Last_name 
FROM employee;

MySQL UNIONIn this tutorial, we are going to see how to use the MySQL UNION operator to combine two or more result sets from multiple SELECT…Read More

10. What is the difference between UNION and UNION ALL?

UNION and UNION ALL are used to select data from tables that have similar structures. This means that the columns specified in the union must have the same data type. Example:

SELECT First_name 
FROM employee 
UNION 
SELECT Last_name 
FROM employee;

In the above query, if “FIRST_NAME” is DOUBLE and “LAST_NAME” is STRING, the query will not work. Since the data type of both columns is VARCHAR, the union is possible. The difference between UNION and UNION ALL is that the UNION query returns only distinct values.
MySQL UNIONIn this tutorial, we are going to see how to use the MySQL UNION operator to combine two or more result sets from multiple SELECT…Read More SQL UNION ALLThe UNION ALL command in SQL is very similar to the UNION command. It allows you to concatenate the records of multiple queries, the only…Read More

Employee table

Reward table

Questions

You May Also Like

MYSQL MCQ and Answers – Query optimization

MYSQL MCQ and Answers – Part 5

MySQL Practice Exercises with Solutions – Part 2

Leave a Reply Cancel reply