MySQL Practice Exercises with Solutions – Part 6

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 20% of Bob’s salary, 10% of Alex’s salary, and 15% of other employees’ salaries.

SELECT First_name, 
CASE First_name 
   WHEN 'Bob' THEN SALARY * .20 
   WHEN 'Alex' THEN SALARY * .10 
   ELSE SALARY * .15 
END 
AS "Gross salary" 
FROM employee;

CASE WHEN in MySQL with Multiple ConditionsIn this tutorial, we are going to see how to use CASE expression in MySQL with syntax and examples. CASE expression allows you to add…Read More

2. Display the text

‘IT services’ instead of ‘IT’,
‘Financial services’ instead of ‘Finance’, and
‘Banking services’ instead of ‘Banking’

from the “Department” column.

SELECT 
CASE Departement 
   WHEN 'IT' THEN 'IT services'
   WHEN 'Finance ' THEN 'Financial services' 
   WHEN 'Banking' THEN 'Banking services' 
END AS "Department"
FROM employee;

3. Remove employees who have received rewards.

DELETE 
FROM employee 
WHERE employee_id IN (
    SELECT employee_ref_id 
    FROM Reward
);

4. Insert employee whose name contains a single quote '

INSERT INTO employee (Last_name) VALUES ('Alex''');

MySQL INSERT INTOIn this tutorial, we are going to see how to insert data with MySQL. To insert data into a MySQL table you need to use…Read More

5. Get the name of the employees which contains only numbers.

SELECT * FROM employee WHERE LOWER(Last_name) = UPPER(Last_name);

We have used the two functions Lower and Upper, because the comparison between two numbers, the result will be the same. However, if there are alphabets in the column, the results will be different.
MySQL String To LowercaseThe LOWER() function in SQL language allows you to transform all uppercase characters in a string into lowercase. This function can therefore be useful to…Read More MySQL Select UppercaseThe UPPER() function in SQL language allows you to transform all lowercase characters in a string into uppercase. This function can therefore be useful to…Read More

6. Classify employees according to their reward for one month.

SELECT First_name, 
       amount, 
       DENSE_RANK() OVER (PARTITION BY date ORDER BY amount DESC) AS Rank 
FROM Employee E, Reward R 
WHERE E.employee_id = R.employee_ref_id;

In order to classify the employees according to their rank for a month, the keyword “DENSE_RANK” is used. Here, the PARTITION keyword helps us sort the column with which the filtering is done. The above query ranks employees based on their rewards for a given month.

7. Update the reward of “Bob” to 1000.

UPDATE Reward 
SET amount ='1000' 
WHERE employee_ref_id = (
        SELECT employee_id 
        FROM employee 
        WHERE First_name='Bob'
);

8. Get the first name, the amount of the reward for the 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

9. Get the first name, the reward amount for employees who have rewards with an amount greater than 2000.

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

AND Operator in MySQLIn this tutorial, we are going to see how the AND operator combines multiple Boolean expressions to filter data. The AND operator is a logical…Read More

10. Get the first name, the amount of the reward for the employees even if they have not received any rewards.

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

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

Employee table

Reward table

Questions

You May Also Like

MySQL Practice Exercises with Solutions – Part 1

MYSQL MCQ and Answers – Query optimization

MYSQL MCQ and Answers – Part 4

Leave a Reply Cancel reply