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.