Lesson 30: Intermediate SQL Project

Congratulations 🎉 You have completed the core Intermediate SQL concepts.

In this lesson, we will work on a real-world intermediate SQL project that combines multiple topics into one scenario.

Project Scenario

You are working as a Database Analyst for a mid-sized company.

The company stores employee and department data and wants meaningful reports with performance, safety, and data integrity.

Database Tables

departments

+----+----------------+
| id | name           |
+----+----------------+
| 1  | IT             |
| 2  | HR             |
| 3  | Finance        |
+----+----------------+
  

employees

+----+----------+-------------+--------+
| id | name     | department_id | salary |
+----+----------+-------------+--------+
| 1  | Alice    | 1           | 75000  |
| 2  | Bob      | 1           | 72000  |
| 3  | Charlie  | 2           | 60000  |
| 4  | Diana    | 3           | 80000  |
| 5  | Evan     | NULL        | 55000  |
+----+----------+-------------+--------+
  

Project Tasks

You are asked to perform the following tasks:

1. List all employees with their department names
2. Find employees without departments
3. Calculate average salary per department
4. Create a reusable view for employee details
5. Improve query performance using indexes
6. Safely update salaries using transactions

Task 1: Employees with Department Names

SELECT e.name, d.name AS department, e.salary
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.id;
  

LEFT JOIN ensures employees without departments are still included.

Task 2: Employees Without Departments

SELECT name
FROM employees
WHERE department_id IS NULL;
  

This identifies unassigned employees.

Task 3: Average Salary per Department

SELECT d.name AS department,
       AVG(e.salary) AS avg_salary
FROM departments d
JOIN employees e
ON d.id = e.department_id
GROUP BY d.name;
  

Task 4: Create a View

Create a view to simplify reporting.

CREATE VIEW employee_report AS
SELECT e.name, d.name AS department, e.salary
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.id;
  

Now query the view:

SELECT * FROM employee_report;
  

Task 5: Improve Performance with Indexes

Create an index on department_id to speed up joins.

CREATE INDEX idx_department_id
ON employees(department_id);
  

This improves JOIN and WHERE performance.

Task 6: Safe Salary Update Using Transactions

Give a salary bonus to IT department employees.

START TRANSACTION;

UPDATE employees
SET salary = salary + 3000
WHERE department_id = 1;

COMMIT;
  

If an error occurs, use ROLLBACK to undo changes.

Concepts Used in This Project

• LEFT JOIN
• GROUP BY & aggregation
• Views
• Indexes
• Transactions

Real-World Takeaway

This project demonstrates how intermediate SQL:

• Solves business problems
• Protects data integrity
• Improves performance
• Encourages reusable design

You are now ready to move into Advanced SQL topics.

What’s Next?

In the next lesson, we will enter Advanced SQL and start with Stored Procedures.