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 | +-----------------+-------------+--------+
[st_adsense]
Questions
1. Get all the details on employees whose participation month (Joining_date) is “January”
SELECT * FROM employee WHERE MONTH(joining_date) = '01';
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;
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;
7. Get the current date and time.
SELECT now();
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.
9. Get the employee name (Last_name) after replacing the special character with white space.
SELECT REPLACE(Last_name,'%',' ') FROM employee;
10. Get the employee’s department and total salary, grouped by department.
SELECT Departement, SUM(Salary) AS total FROM employee GROUP BY Departement;
- MYSQL MCQ and Answers – Part 1
- MYSQL MCQ and Answers – Part 2
- MYSQL MCQ and Answers – Part 3
- MYSQL MCQ and Answers – Part 4
- MYSQL MCQ and Answers – Part 5
- MYSQL MCQ and Answers – Part 6
- MYSQL MCQ and Answers – Query optimization
- MySQL Practice Exercises with Solutions – Part 1
- MySQL Practice Exercises with Solutions – Part 2
- MySQL Practice Exercises with Solutions – Part 3
- MySQL Practice Exercises with Solutions – Part 4
- MySQL Practice Exercises with Solutions – Part 5
- MySQL Practice Exercises with Solutions – Part 6
- MySQL Practice Exercises with Solutions – Part 7
- MySQL Practice Exercises with Solutions – Country Database – Part 8
- MySQL Practice Exercises with Solutions – Ordering System Database – Part 9
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;