MySQL Exercise

SECTION 1st.

Intro:-

                Hi guys, It's Patel Asif Khan, And welcome to Study Center, So here are some of the Demo databases and some of the exercise question that will make you perfect in MySQL query In the last you can also download the .sql script of my solutions but first try your self first and then see the solution. So are you ready, I think so. First, download the DEMO HR database we are going to do all the exercises on this HR database.

Structure:-

      Before working on any database first we have to understand its structure and relationships, so first study the database and relationships between all the tables. There are 7 tables are in the HR database

1)regions

2)countries

3)locations

4)departments

5)job_history

6)jobs

7)employees 

all are connected with a foreign keys constraint, and you can understand the relationship between these tables by the above image.

 

👉 Download HR Database 👈

First:- 

            After downloading the HR.zip Extract it, then goto user MySQL Workbench, logon to your connection, when the editor opens then go to File>Open SQL Script or just use shortcut CTRL+SHIFT+O Then select the extracted HR.sql file and open it, You can see there is lots of SQL quarry in it, don't change anything and run the script, in 1 to 2 sec you have HR database in your database. Then examine the database.

Exercise:-  This exercise is simple just to make you familiar with the database.

1. Write a query to display the names (first_name, last_name using alias name “First Name", "Last Name"

2.Write a query to get unique department ID from employee table.

3.Write a query to get all employee details from the employee table order by first name, descending.

4.Write a query to get the names (first_name, last_name),salary, PF of all the employees (PF is calculated as 15% of salary).

5.Write a query to get the employee ID, names (first_name, last_name),salary in ascending order of salary.

6.Write a query to get the total salaries payable to employees.

7.Write a query to get the maximum and minimum salary from employees table.

8.Write a query to get the average salary and number of employees in the employees table.

9.Write a query to get the number of employees working with the company.

10.Write a query to get the number of jobs available in the employees table.

11.Write a query to get all first name from employees table in upper case.

12.Write a query to get the first 3 characters of the first name from employees table.

13.Write a query to calculate 171*214+625.

14.Write a query to get the names (for example Ellen Abel, Sundar Ande, etc.) of all the employees from employees table.

15.Write a query to get first name from employees table after removing white spaces from both sides.

16.Write a query to get the length of the employee names (first_name, last_name) from the employees table.

17.Write a query to check if the first_name fields of the employees table contains numbers. 
Write a query to select the first 10 records from a table.

18.Write a query to get the monthly salary (round 2 decimal places) of each and every employee.

Note: Assume the salary field provides the 'annual salary' information.

Solution:- 

                        First Try to solve the queries with your own methods and it's not necessary to have the exact same query as I provided, there will different methods to achieve the same results.

👉 Download The Solution 👈

Section 2nd

Do share and leave a Comment

Blog By  Patel Aasif J. Khan Official