Example Table Creation and Data Grouping

1. Create the employees table:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    job_title VARCHAR(50),
    salary DECIMAL(10, 2)
);

2. Insert sample data into the employees table:

INSERT INTO employees (first_name, last_name, department, job_title, salary)
VALUES 
('John', 'Doe', 'Engineering', 'Engineer', 70000),
('Jane', 'Smith', 'Marketing', 'Manager', 80000),
('Alice', 'Johnson', 'HR', 'Specialist', 50000),
('Bob', 'Brown', 'Engineering', 'Manager', 90000),
('Tom', 'Harris', 'Marketing', 'Specialist', 60000);

Performing Grouping

Group by Department and Count Employees:

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Calculate Total Salary by Department:

SELECT department, SUM(salary)
FROM employees
GROUP BY department;

Get Average Salary by Department:

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

Group by Department and Job Title and Count Employees:

SELECT department, job_title, COUNT(*)
FROM employees
GROUP BY department, job_title;

Filter Groups with HAVING Clause:

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;

Find Maximum Salary by Department:

SELECT department, MAX(salary)
FROM employees
GROUP BY department;

Find Minimum Salary by Department:

SELECT department, MIN(salary)
FROM employees
GROUP BY department;