Beginner SQL Mini Project

Congratulations 🎉 You have completed the beginner concepts of SQL. In this lesson, we will apply everything you’ve learned so far in a realistic mini project.

This project follows a simple structure:

• Problem Statement
• Required Output
• SQL Query
• Step-by-Step Explanation

Project Scenario

You are working as a junior data analyst in a company. The HR team asks you to analyze employee data stored in the employees table.

The table structure is shown below:

+----+----------+------------+--------+
| id | name     | department | salary |
+----+----------+------------+--------+
| 1  | Alice    | IT         | 75000  |
| 2  | Bob      | IT         | 72000  |
| 3  | Charlie  | HR         | 60000  |
| 4  | Diana    | Finance    | 70000  |
| 5  | Evan     | HR         | 58000  |
| 6  | Fiona    | IT         | 80000  |
| 7  | George   | Finance    | 68000  |
+----+----------+------------+--------+
  

Question 1: View All Employees

Requirement: Display all employee records from the table.

SELECT * FROM employees;
  

Explanation: This query retrieves all rows and columns from the employees table.

Question 2: Employees from IT Department

Requirement: Show names and salaries of employees working in the IT department.

SELECT name, salary
FROM employees
WHERE department = 'IT';
  

Explanation:

• WHERE filters rows based on department
• Only IT department employees are returned

Question 3: Highest Paid Employees

Requirement: Display the top 3 highest-paid employees.

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;
  

Explanation:

• ORDER BY sorts salaries from highest to lowest
• LIMIT restricts the output to top 3 rows

Question 4: Unique Departments

Requirement: List all unique departments in the company.

SELECT DISTINCT department
FROM employees;
  

Explanation: DISTINCT removes duplicate department names from the result.

Question 5: Average Salary per Department

Requirement: Calculate the average salary for each department.

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
  

Explanation:

• GROUP BY creates one group per department
• AVG calculates average salary for each group

Question 6: Departments with High Average Salary

Requirement: Show only departments where the average salary is greater than 70,000.

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 70000;
  

Explanation:

• GROUP BY groups employees by department
• HAVING filters grouped results

Question 7: Combined Conditions

Requirement: Find employees who work in IT or Finance and earn more than 70,000.

SELECT name, department, salary
FROM employees
WHERE department IN ('IT', 'Finance')
  AND salary > 70000;
  

Explanation:

• IN filters multiple departments
• AND ensures salary condition is met

Beginner Takeaways

By completing this mini project, you have successfully used:

• SELECT and WHERE
• ORDER BY and LIMIT
• DISTINCT
• Aggregate functions
• GROUP BY and HAVING
• IN, AND conditions

What’s Next?

You are now ready to move into the Intermediate Level, where we will explore Joins and work with multiple tables.

Great job reaching this milestone