Solve SQL: SQL Exercises and Solutions in MySQL

Oindrila Sen
Apr 20 · 9 min read

An expression that caught my attention recently is:

Data is the new oil.

I am not sure who used the term for the first time and who should get the credit for this. But, the quote is so appropriate! Mining or refining is a vital component of DATA. To transform the raw data into some useful insights is a key to some effective business strategies. In this whole process, SQL plays an important role. So, this post is all about data and SQL.

Sometimes, I like to challenge myself with SQL. Hackerank is a very good place for that. But once the simple and medium challenges are over and I was left with only the complex ones, I understood that I need to learn and practice SQL more often. The next challenge is where I can practice some SQL? There are a couple of SQL playgrounds to choose from and I like db-fiddle. I use the free version and I don’t see any option to build my own schema and reuse it. Still, it is a good place to create tables and write queries and practice some SQL. This post is all about SQL exercises for practicing for an interview or just to test your own level on SQLMETER.

So, first, let’s start from the very beginning to create some tables and insert some data, and then we will work on some real exercises.

Employees and Departments is a very common example to solve and practice many data problems. Maybe it is too easy to create a table and insert records, but it is a tiresome job. So, here I am providing a SQL script to create tables and insert records that will work in dB-fiddle MySQL version 5.7.

Create a Table with a primary key column

CREATE TABLE departments (department_id INTEGER PRIMARY KEY ,       
department_name VARCHAR(30) ,
location_id INTEGER ) ;

Create a Table with a Foreign Key

CREATE TABLE employees ( employee_id INTEGER , 
first_name VARCHAR(20) ,
last_name VARCHAR(25) ,
email VARCHAR(25) ,
phone_number VARCHAR(20) ,
hire_date DATE ,
job_id VARCHAR(10) ,
salary INTEGER ,
commission_pct INTEGER ,
manager_id INTEGER ,
department_id INTEGER ,
constraint pk_emp primary key (employee_id) ,
constraint fk_deptno foreign key (department_id) references departments(department_id) ) ;

Insert Records into Tables

## Insert insto Departments table 
INSERT INTO departments VALUES ( 20,'Marketing', 180); INSERT INTO departments VALUES ( 30,'Purchasing', 1700);
INSERT INTO departments VALUES ( 40, 'Human Resources', 2400); INSERT INTO departments VALUES ( 50, 'Shipping', 1500);
INSERT INTO departments VALUES ( 60 , 'IT', 1400);
INSERT INTO departments VALUES ( 70, 'Public Relations', 2700); INSERT INTO departments VALUES ( 80 , 'Sales', 2500 );
INSERT INTO departments VALUES ( 90 , 'Executive', 1700);
INSERT INTO departments VALUES ( 100 , 'Finance', 1700);
INSERT INTO departments VALUES ( 110 , 'Accounting', 1700);
INSERT INTO departments VALUES ( 120 , 'Treasury' , 1700);
INSERT INTO departments VALUES ( 130 , 'Corporate Tax' , 1700 ); INSERT INTO departments VALUES ( 140, 'Control And Credit' , 1700); INSERT INTO departments VALUES ( 150 ,'Shareholder Services', 1700);
INSERT INTO departments VALUES ( 160 , 'Benefits', 1700);
INSERT INTO departments VALUES ( 170 , 'Payroll' , 1700);

## Insert into Employees table

INSERT INTO employees VALUES (100, 'Steven', 'King', 'SKING', '515.123.4567', '1987-06-17' , 'AD_PRES', 24000 , NULL, NULL, 20); INSERT INTO employees VALUES (101, 'Neena' , 'Kochhar' , 'NKOCHHAR' , '515.123.4568' , '1989-11-21' , 'AD_VP' , 17000 , NULL , 100 , 20);
INSERT INTO employees VALUES (102 , 'Lex' , 'De Haan' , 'LDEHAAN' , '515.123.4569' , '1993-09-12' , 'AD_VP' , 17000 , NULL , 100 , 30); INSERT INTO employees VALUES (103 , 'Alexander' , 'Hunold' , 'AHUNOLD' , '590.423.4567' , '1990-09-30', 'IT_PROG' , 9000 , NULL , 102 , 60);
INSERT INTO employees VALUES (104 , 'Bruce' , 'Ernst' , 'BERNST' , '590.423.4568' , '1991-05-21', 'IT_PROG' , 6000 , NULL , 103 , 60); INSERT INTO employees VALUES (105 , 'David' , 'Austin' , 'DAUSTIN' , '590.423.4569' , '1997-06-25', 'IT_PROG' , 4800 , NULL , 103 , 60); INSERT INTO employees VALUES (106 , 'Valli' , 'Pataballa' , 'VPATABAL' , '590.423.4560' , '1998-02-05', 'IT_PROG' , 4800 , NULL , 103 , 40);
INSERT INTO employees VALUES (107 , 'Diana' , 'Lorentz' , 'DLORENTZ' , '590.423.5567' , '1999-02-09', 'IT_PROG' , 4200 , NULL , 103 , 40);
INSERT INTO employees VALUES (108 , 'Nancy' , 'Greenberg' , 'NGREENBE' , '515.124.4569' , '1994-08-17', 'FI_MGR' , 12000 , NULL , 101 , 100);
INSERT INTO employees VALUES (109 , 'Daniel' , 'Faviet' , 'DFAVIET' , '515.124.4169' , '1994-08-12', 'FI_ACCOUNT' , 9000 , NULL , 108 , 170);
INSERT INTO employees VALUES (110 , 'John' , 'Chen' , 'JCHEN' , '515.124.4269' , '1997-04-09', 'FI_ACCOUNT' , 8200 , NULL , 108 , 170);
INSERT INTO employees VALUES (111 , 'Ismael' , 'Sciarra' , 'ISCIARRA' , '515.124.4369' , '1997-02-01', 'FI_ACCOUNT' , 7700 , NULL , 108 , 160);
INSERT INTO employees VALUES (112 , 'Jose Manuel' , 'Urman' , 'JMURMAN' , '515.124.4469' , '1998-06-03', 'FI_ACCOUNT' , 7800 , NULL , 108 , 150);
INSERT INTO employees VALUES (113 , 'Luis' , 'Popp' , 'LPOPP' , '515.124.4567' , '1999-12-07', 'FI_ACCOUNT' , 6900 , NULL , 108 , 140);
INSERT INTO employees VALUES (114 , 'Den' , 'Raphaely' , 'DRAPHEAL' , '515.127.4561' , '1994-11-08', 'PU_MAN' , 11000 , NULL , 100 , 30);
INSERT INTO employees VALUES (115 , 'Alexander' , 'Khoo' , 'AKHOO' , '515.127.4562' , '1995-05-12', 'PU_CLERK' , 3100 , NULL , 114 , 80); INSERT INTO employees VALUES (116 , 'Shelli' , 'Baida' , 'SBAIDA' , '515.127.4563' ,'1997-12-13', 'PU_CLERK' , 2900 , NULL , 114 , 70); INSERT INTO employees VALUES (117 , 'Sigal' , 'Tobias' , 'STOBIAS' , '515.127.4564' , '1997-09-10', 'PU_CLERK' , 2800 , NULL , 114 , 30); INSERT INTO employees VALUES (118 , 'Guy' , 'Himuro' , 'GHIMURO' , '515.127.4565' , '1998-01-02', 'PU_CLERK' , 2600 , NULL , 114 , 60); INSERT INTO employees VALUES (119 , 'Karen' , 'Colmenares' , 'KCOLMENA' , '515.127.4566' , '1999-04-08', 'PU_CLERK' , 2500 , NULL , 114 , 130);
INSERT INTO employees VALUES (120 , 'Matthew' , 'Weiss' , 'MWEISS' , '650.123.1234' ,'1996-07-18', 'ST_MAN' , 8000 , NULL , 100 , 50); INSERT INTO employees VALUES (121 , 'Adam' , 'Fripp' , 'AFRIPP' , '650.123.2234' , '1997-08-09', 'ST_MAN' , 8200 , NULL , 100 , 50); INSERT INTO employees VALUES (122 , 'Payam' , 'Kaufling' , 'PKAUFLIN' , '650.123.3234' ,'1995-05-01', 'ST_MAN' , 7900 , NULL , 100 , 40);
INSERT INTO employees VALUES (123 , 'Shanta' , 'Vollman' , 'SVOLLMAN' , '650.123.4234' , '1997-10-12', 'ST_MAN' , 6500 , NULL , 100 , 50);
INSERT INTO employees VALUES (124, 'Kevin' , 'Mourgos' , 'KMOURGOS' , '650.123.5234' , '1999-11-12', 'ST_MAN' , 5800 , NULL , 100 , 80); INSERT INTO employees VALUES (125, 'Julia' , 'Nayer' , 'JNAYER' , '650.124.1214' , '1997-07-02', 'ST_CLERK' , 3200 , NULL , 120 , 50); INSERT INTO employees VALUES (126, 'Irene' , 'Mikkilineni' , 'IMIKKILI' , '650.124.1224' , '1998-11-12', 'ST_CLERK' , 2700 , NULL , 120 , 50);
INSERT INTO employees VALUES (127, 'James' , 'Landry' , 'JLANDRY' , '650.124.1334' , '1999-01-02' , 'ST_CLERK' , 2400 , NULL , 120 , 90);
INSERT INTO employees VALUES (128, 'Steven' , 'Markle' , 'SMARKLE' , '650.124.1434' , '2000-03-04' , 'ST_CLERK' , 2200 , NULL , 120 , 50);
INSERT INTO employees VALUES (129, 'Laura' , 'Bissot' , 'LBISSOT' , '650.124.5234' ,'1997-09-10' , 'ST_CLERK' , 3300 , NULL , 121 , 50); INSERT INTO employees VALUES (130, 'Mozhe' , 'Atkinson' , 'MATKINSO' , '650.124.6234' , '1997-10-12' , 'ST_CLERK' , 2800 , NULL , 121 , 110);

So, now we have 2 tables and some data ready to run our SQL. It’s time for some exercises.

1. Select employees first name, last name, job_id, and salary whose first name starts with alphabet S

select first_name, 
last_name, job_id,
salary
from employees
where upper(first_name) like 'S%';

2. Write a query to select the employee with the highest salary

select employee_id, 
first_name,
last_name,
job_id,
salary
from employees
where salary = (select max(salary) from employees);

3. Select employee with the second highest salary

select employee_id, 
first_name,
last_name,
job_id,
salary
from employees
where salary != (select max(salary) from employees)
order by salary desc
limit 1;

The above query selects only one person with the second-highest salary. But what if there are more than 1 person with the same salary? Or, what if we want to select the 3rd or 4th highest salary? So, let’s try a generic approach.

4. Fetch employees with 2nd or 3rd highest salary

#change the input for 2nd, 3rd or 4th highest salary set 
@input:=3;
select employee_id,
first_name,
last_name,
job_id,
salary
from employees e
where @input =(select COUNT(DISTINCT Salary)
from employees p
where e.Salary<=p.Salary);

5. Write a query to select employees and their corresponding managers and their salaries

Now, this is a classic example of SELF JOIN in SQL exercises. Also, I am using the CONCAT function to concatenate the first name and last name of each employee and manager.

select concat(emp.first_name,' ',emp.last_name) employee, 
emp.salary emp_sal,
concat(mgr.first_name,' ',mgr.last_name) manager,
mgr.salary mgr_sal
from employees emp
join employees mgr on emp.manager_id = mgr.employee_id;

6. Write a query to show the count of employees under each manager in descending order

select sup.employee_id employee_id, 
concat(sup.first_name,' ', sup.last_name)manager_name,
COUNT (sub.employee_id) AS number_of_reportees
from employees sub
join employees sup on sub.manager_id = sup.employee_id
group by sup.employee_id, sup.first_name, sup.last_name
order by 3 desc;

7. Find the count of employees in each department

select dept.department_name, 
count(emp.employee_id) emp_count
from employees emp
join departments dept on emp.department_id = dept.department_id group by dept.department_name
order by 2 desc;

8. Get the count of employees hired year wise

select year(hire_date) hired_year, 
count(*) employees_hired_count
from employees
group by year(hire_date)
order by 2 desc;

9. Find the salary range of employees

select min(salary) min_sal, 
max(salary) max_sal,
round(avg(salary)) avg_sal
from employees;

10. Write a query to divide people into three groups based on their salaries

select concat(first_name,' ',last_name) employee, salary, 
case
when salary >=2000 and salary < 5000 then "low"
when salary >=5000 and salary < 10000 then "mid"
else "high" end as salary_level
from employees
order by 1;

11. Select the employees whose first_name contains “an”

select (first_name) 
from employees
where lower(first_name) like '%an%';

12. Select employee first name and the corresponding phone number in the format (_ _ _)-(_ _ _)-(_ _ _ _)

select concat(first_name, ' ', last_name) employee, replace(phone_number,'.','-') phone_number 
from employees;

13. Find the employees who joined in August 1994.

select concat(first_name, ' ', last_name) employee, 
hire_date
from employees
where year(hire_date) = '1994'
and month(hire_date) = '08';

14. Write an SQL query to display employees who earn more than the average salary in that company

select concat(emp.first_name,last_name) name, 
emp.employee_id,
dept.department_name department,
dept.department_id,
emp.salary
from departments dept
JOIN employees emp on dept.department_id = emp.department_id
where emp.salary > (select avg(salary) from employees)
order by dept.department_id;

15. Find the maximum salary from each department.

select dept.department_id, 
dept.department_name department,
max(emp.salary)maximum_salary
from departments dept
JOIN employees emp on dept.department_id = emp.department_id
group by dept.department_name, dept.department_id
order by dept.department_id ;

16. Write a SQL query to display the 5 least earning employees

select first_name, 
last_name,
employee_id,
salary
from employees
order by salary limit 5;

17. Find the employees hired in the 80s

select employee_id,
concat(first_name,' ' , last_name) employee,
hire_date
from employees
where year(hire_date) between 1980 and 1989;

18. Display the employees first name and the name in reverse order

select lower(first_name) name, 
lower(reverse(first_name)) name_in_reverse
from employees;

19. Find the employees who joined the company after the 15th of the month

select employee_id, 
concat(first_name, ' ' , last_name) employee,
hire_date
from employees
where day(hire_date)> 15;

20. Display the managers and the reporting employees who work in different departments

select concat(mgr.first_name,' ',mgr.last_name) manager, concat(emp.first_name,' ',emp.last_name) employee, 
mgr.department_id mgr_dept,
emp.department_id emp_dept
from employees emp
join employees mgr on emp.manager_id = mgr.employee_id
where emp.department_id != mgr.department_id
order by 1;

We can provide a lot of insights from data just using SQL. Joining different tables, grouping data by different fields, or using some set operations or functions on data can tell the story hidden inside data.

Finally, if you want to practice some python along with the SQL, here are a few links:

Python Programming Questions for Data Science Interview — Part I

2o Basic Programming Problems and Solutions in Python

20 Behavioral (Non Technical) Interview Questions

As data is the new fuel of this century, let’s keep digging and exploring. Also, let’s learn and keep practicing different SQL techniques since SQL is a powerful tool in the world of DATA.

Thank You!

Originally published at https://oindrilasen.com on April 20, 2021.

MLearning.ai

Data Scientists must think like an artist when finding a solution

MLearning.ai

Data Scientists must think like an artist when finding a solution, when creating a piece of code.Artists enjoy working on interesting problems, even if there is no obvious answer.

Oindrila Sen

Written by

Data Scientist, Story Teller

MLearning.ai

Data Scientists must think like an artist when finding a solution, when creating a piece of code.Artists enjoy working on interesting problems, even if there is no obvious answer.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store