MySQL Practice Exercises with Solutions – Part 1

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 all employees.

SELECT * FROM employee;

MySQL SELECTSELECT statement is used to get data from a MySQL database. You can use this command at the mysql> command prompt as well as in…Read More

2. Display the first name and last name of all employees.

SELECT First_name, Last_name FROM employee;

3. Display all the values of the “First_Name” column using the alias “Employee Name”

SELECT First_name AS "Employee Name" FROM employee;

How to Use Column Alias in Select Clause – MySQLAliases in MySQL are used to give a temporary name to a table or a column in a table. Aliases are often used to make…Read More

4. Get all “Last_Name” in lowercase.

SELECT LOWER(Last_name) FROM employee;

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

5. Get all “Last_Name” in uppercase.

SELECT UPPER(Last_name) FROM employee;

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. Get unique “DEPARTMENT”.

SELECT DISTINCT Departement FROM employee;

MySQL SELECT DISTINCTYour table may contain duplicate values in a column, and in some scenarios, you may need to get only unique records from a table. To…Read More

7. Get the first 4 characters of “FIRST_NAME” column.

SELECT SUBSTRING(First_name,1,4) FROM employee;

MySQL SUBSTRING() with ExamplesSUBSTRING() function in SQL language (or SUBSTR()) is used to split a string. In other words, it allows to extract a part of a string,…Read More

8. Get the position of the letter ‘h’ in ‘John’.

SELECT LOCATE('h',First_name) FROM employee where First_name='John';

MySQL LOCATE()In SQL, the LOCATE() function is used to search for the position of a string in a record. The function returns zero if the string…Read More

9. Get all values from the “FIRST_NAME” column after removing white space on the right.

SELECT RTRIM(First_name) FROM employee;

MySQL RTRIM()In SQL language, the RTRIM() function allows you to delete characters at the end of a string. Most often the RTRIM() function is used to…Read More

10. Get all values from the “FIRST_NAME” column after removing white space on the left.

SELECT LTRIM(First_name) FROM employee;

MySQL LTRIM()In SQL language, the LTRIM() function allows you to delete characters at the beginning of a string. Most often the LTRIM() function is used to…Read More

11. Write the syntax to create the “employee” table.

CREATE TABLE Employee(
  employee_id int NOT NULL,
  First_name varchar(50) NULL,
  Last_name varchar(50) NULL,
  salary decimal(18, 0) NULL,
  joining_date datetime2(7) default getdate(),
  departement varchar(50) NULL
);

MySQL Practice Exercises with Solutions – Part 1

Employee table

Reward table

Questions

One thought on “MySQL Practice Exercises with Solutions – Part 1”

Leave a Reply Cancel reply

Employee table

Reward table

Questions

You May Also Like

MySQL Practice Exercises with Solutions – Part 3

MySQL Practice Exercises with Solutions – Part 6

MySQL Practice Exercises with Solutions – Part 7

One thought on “MySQL Practice Exercises with Solutions – Part 1”

Leave a Reply Cancel reply