35+ Essential and Powerful SQL Practice Queries for Every Aspiring Developer

SQL practice queries

• The development of a complete SQL practice queries guide with successful ones is an excellent method to upgrade your database management and SQL practice queries skills.
• Here you will find a set of SQL practice queries aimed at learning key topics along with their solutions, which range from the beginner level to the advanced one.
• This guide provides a variety of exercises for you to practice and become a master of SQL concepts through SQL practice queries.
• These SQL practice queries allow you to learn the essentials of database management and explore both introductory and advanced topics while you are at it.
• By the end of this ongoing practice, you will inevitably gather the competency to manage real challenges in the scientific field.
• Engage yourself with these SQL practice queries to accelerate your knowledge of SQL acumen.

1.Basic SQL Practice Queries

• With the help of these SQL practice queries, you can learn different functions like SELECT, WHERE, and basic aggregation which are some SQL foundation concepts.

1.1. Query 1: Simple SELECT Query

• Question: Get employees’ names from the “employees” table.
• Solution:

SELECT employee_name

FROM employees;

1.2. Query 2: SELECT with WHERE Clause

• Question: Retrieve the names of employees whose age is over 30 and are from the “employees” table.
• Solution:

SELECT employee_name

FROM employees

WHERE age > 30;

1.3. Query 3: Using AND and OR

• Question: Retrieve the names of employees older than 30 years or people belonging to the “HR” department.
• Solution:

SELECT employee_name

FROM employees

WHERE age > 30 OR department = ‘HR’;

1.4. Query 4: ORDER BY Clause

• Question: Retrieve the names and ages of employees, according to their age, in descending order.
• Solution:

SELECT employee_name, age

FROM employees

ORDER BY age DESC;

1.5. Query 5: Aggregate Function – COUNT

• Question: How many employees are there in the “employees” table?
• Solution:

SELECT COUNT(*) AS employee_count

FROM employees;

2. Intermediate SQL Queries

• The mentioned queries are the ones through which the concepts of GROUP BY, HAVING, and JOINS can be learned.

2.1. Query 6: GROUP BY and COUNT

• Question: Bring the employees in each department through SELECT department, COUNT (*).
• Solution:

SELECT department, COUNT(*) AS employee_count

FROM employees

GROUP BY department;

2.2. Query 7: HAVING Clause

• Question: In how to retrieve the departments that contain more than 5 employees.
• Solution:

SELECT department, COUNT(*) AS employee_count

FROM employees

GROUP BY department

HAVING COUNT(*) > 5;

2.3. Query 8: INNER JOIN

• Question: List the employees’ names and their managers according to the relation in which the manager_id is equal to the employee_id in the managers table.
• Solution:

SELECT e.employee_name, m.manager_name

FROM employees e

INNER JOIN managers m ON e.manager_id = m.employee_id;

2.4. Query 9: LEFT JOIN

• Question: Fetch all employees and their department information, including the employees even if they are not included in a department.
• Solution:

SELECT e.employee_name, e.department

FROM employees e

LEFT JOIN departments d ON e.department_id = d.department_id;

2.5. Query 10: Combining Aggregates with JOIN

• Question: Access the total salary of each department.
• Solution:

SELECT d.department_name, SUM(e.salary) AS total_salary

FROM employees e

INNER JOIN departments d ON e.department_id = d.department_id

GROUP BY d.department_name;

3. Advanced SQL Queries

• These queries include several advanced concepts like subqueries, window functions, and complex joins.

3.1. Query 11: Subquery with IN

• Question: Give the names of employees who are employed in the areas the total salary of which goes over 1,000,000.
• Solution:

SELECT employee_name

FROM employees

WHERE department_id IN (

SELECT department_id

FROM employees

GROUP BY department_id

HAVING SUM(salary) > 1000000

);

3.2. Query 12: Subquery with EXISTS

• Question: Retrieve the names of employees whose managers exist.
• Solution:

SELECT employee_name

FROM employees e

WHERE EXISTS (

SELECT 1

FROM managers m

WHERE e.manager_id = m.employee_id

);

3.3. Query 13: Window Functions – ROW_NUMBER()

• Question: Create a rank for employees in decreasing order based on their salaries.
• Solution:

SELECT employee_name, salary,

ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank

FROM employees;

3.4. Query 14: Window Functions – RANK()

• Question: Rank employees according to their salary. If the earnings of two employees are equal, they shall each get the same rank.
• Solution:

SELECT employee_name, salary,

RANK() OVER (ORDER BY salary DESC) AS salary_rank

FROM employees;

3.5. Query 15: Window Functions – SUM()

• Question: Find the cumulative sum of employees’ salaries, sorted by their join date.
• Solution:

SELECT employee_name, salary,

SUM(salary) OVER (ORDER BY join_date) AS running_total

FROM employees;

4.Practice Queries on SQL Joins

• You will need the following questions to guide more advanced join operations such as LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

4.1. Query 16: RIGHT JOIN

• Question: Fetch department names as well as employee names of only those departments that have employees left.
• Solution:

SELECT d.department_name, e.employee_name

FROM departments d

RIGHT JOIN employees e ON d.department_id = e.department_id;

4.2. Query 17: FULL OUTER JOIN

• Question: Get all the names of employees in addition to all projects’ names where an employee is not assigned to any project or a project has no one assigned to it.
• Solution:

SELECT e.employee_name, p.project_name

FROM employees e

FULL OUTER JOIN projects p ON e.employee_id = p.employee_id;

4.3. Query 18: SELF JOIN

• Question: List the employees and their bosses in the “employees” table when the manager is also a part of the employees table.
• Solution:

SELECT e.employee_name AS Employee, m.employee_name AS Manager

FROM employees e

LEFT JOIN employees m ON e.manager_id = m.employee_id;

5. Advanced Aggregation and Grouping

• These are the most challenging queries we deal with as they are the ones that deal with many distinct grouping and aggregation principles in SQL.

5.1. Query 19: GROUP BY with Multiple Columns

• Question: For each department and job title, fetch the total salary and the employee count.
• Solution:

SELECT department, job_title, COUNT(*) AS employee_count, SUM(salary) AS total_salary

FROM employees

GROUP BY department, job_title;

5.2. Query 20: Nested Aggregates

• Question: In which of these departments is the average salary higher than the company’s average salary?
• Solution:

SELECT department_name

FROM departments d

JOIN employees e ON d.department_id = e.department_id

GROUP BY department_name

HAVING AVG(e.salary) > (

SELECT AVG(salary)

FROM employees

);

6. Optimization and Performance Tuning

• Learn how to tune the performance of a specific item that is causing problems.

6.1. Query 21: Using Indexes

• Question: Show me the employee names who joined after ‘2022-01-01’, achieving an indexed column query.
• Solution:

SELECT employee_name

FROM employees

WHERE join_date > ‘2022-01-01’;

Tip: Ensure join_date is indexed for better performance.

6.2. Query 22: LIMIT and OFFSET

• Question: Retrieve the best paying employees top 5 and ignore the first 3.
• Solution:

SELECT employee_name, salary

FROM employees

ORDER BY salary DESC

LIMIT 5 OFFSET 3;

6.3. Query 23: Analyzing with EXPLAIN

• Question: Evaluate the performance of a query that is going to fetch such employees with a salary above 100000.
• Solution:

EXPLAIN SELECT employee_name

FROM employees

WHERE salary > 100000;

7. Real-World Scenarios

• Use the queries provided below to simulate business requirements in practice.

7.1. Query 24: Data Pivoting

• Question: Show in tabular form the cumulative sales for each employee of a monthly basis.
• Solution:

SELECT employee_id,

SUM(CASE WHEN month = ‘January’ THEN sales ELSE 0 END) AS January,

SUM(CASE WHEN month = ‘February’ THEN sales ELSE 0 END) AS February

FROM sales

GROUP BY employee_id;

7.2. Query 25: Removing Duplicates

• Question: Delete duplicate records from the “employees” table according to the “email” column.
• Solution:

DELETE FROM employees

WHERE employee_id NOT IN (

SELECT MIN(employee_id)

FROM employees

GROUP BY email

);

7.3. Query 26: Handling NULL Values

• Question: Substitute with zero all NULLs existing in “salary” column.
• Solution:

SELECT employee_name, COALESCE(salary, 0) AS salary

FROM employees;

8. Advanced Querying with CTEs and Recursion

• CTEs and recursive structures are methods to simplify complex and nested queries with CTEs and recursive structures.

8.1. Query 27: Recursive Query

• Question: List the hierarchy of employees reporting to a manager recursively.
• Solution:

WITH RECURSIVE EmployeeHierarchy AS (

SELECT employee_id, employee_name, manager_id

FROM employees

WHERE manager_id IS NULL

UNION ALL

SELECT e.employee_id, e.employee_name, e.manager_id

FROM employees e

INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id

)

SELECT * FROM EmployeeHierarchy;

8.2. Query 28: Aggregation with CTEs

• Question: Calculate the total salary per department using a CTE.
• Solution:

WITH DepartmentSalary AS (

SELECT department_id, SUM(salary) AS total_salary

FROM employees

GROUP BY department_id

)

SELECT d.department_name, ds.total_salary

FROM departments d

JOIN DepartmentSalary ds ON d.department_id = ds.department_id;

9. JSON and XML Data Handling

• Handle semi-structured data in SQL.

9.1. Query 29: JSON Functions

• Question: What is the name of employees and what are their skills in the JSON column?
• Solution:

SELECT employee_name, JSON_EXTRACT(skills, ‘$.primary’) AS primary_skill

FROM employees;

9.2. Query 30: XML Functions

• Question: What are the names of the employees and their roles in an XML column?
• Solution:

SELECT employee_name, ExtractValue(role_data, ‘/role/name’) AS role_name

FROM employees;

10. Practical Projects and Case Studies

• Different ideas should be brought together to solve entire problems.

10.1 Query 31: E-commerce Sales Analysis

• Question: Produce only the top 3 items in sales in each category.
• Solution:

SELECT category_id, product_id, SUM(price * quantity) AS revenue

FROM sales

GROUP BY category_id, product_id

ORDER BY category_id, revenue DESC

LIMIT 3;

10.2 Query 32: Social Network Analysis

• Question: Discover mutual friends of two users.
• Solution:

SELECT f1.friend_id AS mutual_friend

FROM friendships f1

JOIN friendships f2 ON f1.friend_id = f2.friend_id

WHERE f1.user_id = 1 AND f2.user_id = 2;

11. Temporal Data Queries

• The main interest in the subject is focused on how efficiently the use of the date and time data in a program is made.

11.1. Query 33: Extracting Date Parts

• Question: Show the employee names and their involvement year in the company.
• Solution:

SELECT employee_name, YEAR(join_date) AS join_year

FROM employees;

11.2. Query 34: Calculating Time Differences

• Question: To give the names of the employees and the days they have been in the company.
• Solution:

SELECT employee_name, DATEDIFF(CURDATE(), join_date) AS days_with_company

FROM employees;

11.3. Query 35: Aggregating by Month

• Question: What is the cumulative salary of employees which has to be paid to them in the month in which they have joined?
• Solution:

SELECT MONTH(join_date) AS join_month, SUM(salary) AS total_salary

FROM employees

GROUP BY MONTH(join_date);

11.4. Query 36: Using DATE_ADD and DATE_SUB

• Question: Give the employees whose first performance reviews are due a year after their start date.
• Solution:

SELECT employee_name, DATE_ADD(join_date, INTERVAL 1 YEAR) AS review_date

FROM employees

WHERE DATE_ADD(join_date, INTERVAL 1 YEAR) <= CURDATE();