SQL best example ever for learning

 The "best" SQL example depends on the context or the task you're looking to solve. But here's a comprehensive SQL example that covers multiple aspects of SQL queries, such as SELECT, JOIN, GROUP BY, HAVING, and Subqueries. This can showcase SQL power and flexibility in real-world scenarios.

Scenario:

Let's assume we have two tables:

1. Employees

   • employee_id (Primary Key)
   • first_name
   • last_name
   • department_id
   • salary
   • hire_date

2. Departments

   • department_id (Primary Key)
   • department_name

---

Task:

We want to retrieve:

• The department name,
• The average salary of employees in each department,
• The highest salary in each department,
• The total number of employees in each department,
• Only for departments with more than 5 employees,
• Ordered by highest salary.

Additionally, we want to show the top 5 highest-paid employees and their respective department names.

-- Retrieve department statistics

SELECT 

    d.department_name,

    AVG(e.salary) AS avg_salary,

    MAX(e.salary) AS highest_salary,

    COUNT(e.employee_id) AS total_employees

FROM 

    Employees e

JOIN 

    Departments d

ON 

    e.department_id = d.department_id

GROUP BY 

    d.department_name

HAVING 

    COUNT(e.employee_id) > 5

ORDER BY 

    highest_salary DESC;

-- Retrieve top 5 highest-paid employees

SELECT 

    e.first_name, 

    e.last_name, 

    e.salary,

    d.department_name

FROM 

    Employees e

JOIN 

    Departments d

ON 

    e.department_id = d.department_id

ORDER BY 

    e.salary DESC

LIMIT 5;

Explanation:

1. JOIN: Combines the Employees and Departments tables based on the department_id field, so you can access both employee details and their respective department names.

2. Aggregation (AVG, MAX, COUNT):

   • AVG(e.salary): Calculates the average salary of employees within each department.
   • MAX(e.salary): Finds the highest salary within each department.
   • COUNT(e.employee_id): Counts the total number of employees in each department.

3. GROUP BY: Groups the result by department_name so that aggregation functions like AVG, MAX, and COUNT operate on each department separately.

4. HAVING: Filters out departments that have 5 or fewer employees.

5. ORDER BY: Orders the result by the highest_salary in descending order, so the department with the highest salary appears first.

6. LIMIT 5: Retrieves only the top 5 highest-paid employees from the second query.

---

Sample Output:

1. Department Statistics (for departments with more than 5 employees):

department_name	avg_salary	highest_salary	total_employees
IT	75000	120000	8
Marketing	60000	90000	6

2. Top 5 Highest-Paid Employees:

first_name	last_name	salary	department_name
John	Doe	120000	IT
Jane	Smith	115000	IT
Michael	Brown	110000	HR
Emily	Davis	95000	Marketing
Sarah	Wilson	92000	Marketing

---

This example combines several key SQL operations, including joins, grouping, filters, sorting, and limiting, to solve a business problem effectively and is a great demonstration of SQL's capabilities.