SQL MAX Function

SQL MAX Function

The SQL MAX function is used to find the largest value in a set of values. This function is essential for retrieving the maximum value from a column in a table, allowing for effective data analysis and reporting.

---

Syntax

SELECT MAX(column_name)
FROM table_name
WHERE condition;

The MAX function returns the largest value of the specified column. The WHERE clause is optional and can be used to filter the rows before applying the MAX function.

---

Example SQL MAX Queries

Let's look at some examples of SQL MAX queries using the employees table:

1. Basic MAX Example

SELECT MAX(salary) AS max_salary
FROM employees;

This query uses the MAX function to find the largest salary in the employees table.

2. MAX with WHERE Clause

SELECT MAX(salary) AS max_salary
FROM employees
WHERE department_id = 3;

This query uses the MAX function to find the largest salary in the employees table for department 3.

3. MAX with GROUP BY

SELECT department_id, MAX(salary) AS max_salary
FROM employees
GROUP BY department_id;

This query uses the MAX function to find the largest salary for each department in the employees table.

4. MAX with JOIN

SELECT d.department_name, MAX(e.salary) AS max_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

This query uses the MAX function to find the largest salary for each department by joining the employees and departments tables.

---

Full Example

Let's go through a complete example that includes creating a database, creating tables, inserting data, and using the MAX function.

Step 1: Creating a Database

This step involves creating a new database named example_db.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating Tables

In this step, we create two tables named employees and departments within the previously created database.

USE example_db;

CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Here, we define the departments table with columns for department_id and department_name. We also define the employees table with columns for employee_id, first_name, last_name, department_id, and salary, with a foreign key relationship to the departments table.

Step 3: Inserting Data into the Tables

This step involves inserting some sample data into the departments and employees tables.

INSERT INTO departments (department_name) VALUES ('HR'), ('Finance'), ('IT'), ('Marketing');

INSERT INTO employees (first_name, last_name, department_id, salary) VALUES ('John', 'Doe', 1, 50000.00);
INSERT INTO employees (first_name, last_name, department_id, salary) VALUES ('Jane', 'Smith', 2, 60000.00);
INSERT INTO employees (first_name, last_name, department_id, salary) VALUES ('Alice', 'Johnson', 3, 70000.00);
INSERT INTO employees (first_name, last_name, department_id, salary) VALUES ('Bob', 'Brown', 4, 80000.00);
INSERT INTO employees (first_name, last_name, department_id, salary) VALUES ('Charlie', 'Davis', 1, 90000.00);

Here, we insert data into the departments table and then insert data into the employees table, ensuring that each employee is assigned to a department.

Step 4: Using the MAX Function

This step involves using the MAX function to find the largest salary in the employees table.

SELECT MAX(salary) AS max_salary
FROM employees;

This query retrieves the largest salary from the employees table.

---

Conclusion

The SQL MAX function is a powerful tool for finding the largest value in a set of values. Understanding how to use the MAX function and its syntax is essential for effective data analysis and reporting in a relational database.