SQL MIN Function

SQL MIN Function

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

---

Syntax

SELECT MIN(column_name)
FROM table_name
WHERE condition;

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

---

Example SQL MIN Queries

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

1. Basic MIN Example

SELECT MIN(salary) AS min_salary
FROM employees;

This query uses the MIN function to find the smallest salary in the employees table.

2. MIN with WHERE Clause

SELECT MIN(salary) AS min_salary
FROM employees
WHERE department_id = 3;

This query uses the MIN function to find the smallest salary in the employees table for department 3.

3. MIN with GROUP BY

SELECT department_id, MIN(salary) AS min_salary
FROM employees
GROUP BY department_id;

This query uses the MIN function to find the smallest salary for each department in the employees table.

4. MIN with JOIN

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

This query uses the MIN function to find the smallest 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 MIN 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 MIN Function

This step involves using the MIN function to find the smallest salary in the employees table.

SELECT MIN(salary) AS min_salary
FROM employees;

This query retrieves the smallest salary from the employees table.

---

Conclusion

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