MySQL Practice Exercises with Solutions – Part 2

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  |
+-----------------+-------------+--------+

Questions

1. Get the length of the text in the “First_name” column.

SELECT LENGTH(First_name) FROM employee;

2. Get the employee’s first name after replacing ‘o’ with ‘#’.

SELECT REPLACE(First_name,'o','#') FROM employee;

3. Get the employee’s last name and first name in a single column separated by a ‘_’.

SELECT CONCAT(First_name,'_',Last_name) FROM employee;

4. Get the year, month, and day from the “Joining_date” column.

SELECT YEAR(joining_date), MONTH(joining_date), DAY(joining_date) 
FROM employee;

5. Get all employees in ascending order by first name.

SELECT * FROM employee ORDER BY First_name ASC;

SELECT * FROM employee ORDER BY First_name DESC;

7. Get all employees in ascending order by first name and descending order by salary.

SELECT * FROM employee ORDER BY First_name ASC, salary DESC;

8. Get employees whose first name is “Bob”.

SELECT * FROM employee WHERE First_name='Bob';

9. Get employees whose first name is “Bob” or “Alex”.

SELECT * FROM employee WHERE First_name IN ('Bob','Alex');

10. Get employees whose first name is neither “Bob” nor “Alex”.

SELECT * FROM employee WHERE First_name NOT IN ('Bob','Alex');

11. What is SQL injection?

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