#1 Spot for Defeating Online Exams

sql-min()-and-max()-functions
Ad description

“`html

The MIN() and MAX() functions are frequently utilized to swiftly identify the smallest and largest values in a dataset. These functions assist in decision-making by pinpointing optimal or suboptimal performance, the lowest cost, or the highest temperature. They prove beneficial in numerous real-life scenarios where rapid comparisons are essential to grasp vital data points. Using MIN() and MAX(), individuals can easily and quickly summarize extensive information. In this article, we will delve into the MIN() and MAX() functions comprehensively with illustrations.

Table of Contents:

What are MIN() and MAX() Functions in SQL?

In MySQL, the MAX() function is an aggregate function that provides the highest (maximum) value from a specified column. Conversely, the MIN() function yields the lowest (minimum) value from a designated column. These functions are often applied with the GROUP BY clause to ascertain the minimum or maximum values within specific categories.

Why do we require MIN() and MAX() Functions in SQL?

  • Data Overview: These functions adeptly sift through data to identify minimum and maximum values without the need to manually sort or compare each entry.
  • Informed Decision Making: Determining extreme values (such as the lowest salary or highest sale) enables organizations to make choices based on data.
  • Efficiency: It is less resource-intensive to retrieve data using MIN() and MAX() than to extract all data, sort it, and then identify min/max values.
  • Analysis and Reporting: Reports frequently emphasize best or worst cases, and using MIN() and MAX() facilitates automatic generation of such insights.
Become a Master of SQL: Enhance Your Data Skills Now!
Harness the full potential of databases with practical SQL training and transform into a data-savvy professional
Discover Program
quiz-icon

Syntax of MIN() and MAX() Functions in SQL

Syntax for the MIN() function:

SELECT MIN(clm_name)
FROM table_name 
[WHERE cond];

Syntax for the MAX() function:

SELECT MAX(clm_name) 
FROM table_name 
[WHERE cond];

Before proceeding with the MIN() and MAX() functions, we need to create a table for Device Maintenance.

CREATE TABLE dvc_mnt (
    tckt_id INT PRIMARY KEY,
    dvc_owner VARCHAR(60),
    city VARCHAR(50),
    dvc_type VARCHAR(40),
    issue VARCHAR(100),
    cost DECIMAL(10,2),
    tch_name VARCHAR(50)
);

INSERT INTO dvc_mnt (
    tckt_id, dvc_owner, city, dvc_type, issue, cost, tch_name 
) VALUES
(101, 'Sanjay', 'Mysuru', 'Router', 'Frequent disconnections', 750.00, 'Manoj'),
(102, 'Ritika', 'Kochi', 'Smartphone', 'Battery overheating', 1200.00, 'Neeraj'),
(103, 'Aravind', 'Jodhpur', 'Printer', 'Paper jam issue', 950.00, 'Sowmya'),
(104, 'Ishita', 'Trichy', 'Laptop', 'Screen flickering', 3000.00, 'Karthik');

Select * from dvc_mnt
dvc_mnt table

This is how the table appears once created and populated with values.

Examples of MIN() and MAX() Functions in SQL

The MIN() and MAX() functions in SQL can be employed alongside GROUP BY and HAVING clauses, rendering them crucial for data management and analysis.

Using MIN() Functions in SQL

This MIN() function retrieves the lowest or minimum value from the specified column.

Example:

-- To determine the least cost

SELECT MIN(cost) AS lowest_cost 
FROM dvc_mnt;

Output:

Using MIN() functions in SQL

Explanation: Here, the MIN(cost) function yields the lowest expected cost from the dataset by extracting the smallest value in the cost column.

Using MAX() Functions in SQL

The MAX() function returns the highest (maximum) value from a specified column.

Example:

-- To determine the highest estimated cost

SELECT MAX(cost) AS highest_cost 
FROM dvc_mnt;

Output:

“““html

Utilizing MAX() Functions in SQL

Clarification: In this instance, to identify the highest projected expense in the dataset, the MAX(cost) function retrieves the maximum value from the cost column.

Employing GROUP BY with MIN() and MAX() Functions in SQL

Within MySQL, you can apply the GROUP BY clause in conjunction with aggregation functions to summarize a group of rows sharing the same value in a specific column. With the GROUP BY clause, we are able to compute totals and averages for each collection of rows using aggregate functions.

Format:

SELECT column_name, MIN(column_name)/MAX(column_name)
FROM table_name
GROUP BY column_name;

Sample:

-- To identify the least expense for each city

SELECT city, MIN(cost) AS total_cost  
FROM dvc_mnt  
GROUP BY city;

Result:

Employing GROUP BY with MIN()

Clarification: Here, the lowest costs for each area are ascertained using MIN(cost), and the data is organized by city via GROUP BY city.

Sample:

-- To identify the highest expense for each city

SELECT city, MAX(cost) AS total_cost  
FROM dvc_mnt  
GROUP BY city;

Result:

Utilizing GROUP BY with MAX()

Clarification: In this case, the highest costs for each area are determined via MAX(cost), and the records are categorized by city using GROUP BY city.

Utilizing HAVING with MIN() and MAX() Functions in SQL

In MySQL, once columns have been aggregated, the HAVING clause serves to filter grouped outcomes.

Format:

SELECT column_name, FUN(column_name)   
FROM tab_name  
GROUP BY column_name  
HAVING cond;

Sample:

SELECT 
    city,
    MIN(cost) AS Min_Cost,
    MAX(cost) AS Max_Cost
FROM 
    dvc_mnt
GROUP BY 
    city
HAVING 
    MAX(cost) > 2000
    OR MIN(cost) < 700;

Result:

Employing HAVING with MIN() and MAX() Functions in SQL

Clarification: This query yields the maximum repair costs exceeding 2000, or the minimum repair costs falling below 700.

Practical Example

University System: Imagine a university that aims to monitor the academic performance of students across different departments.

Sample:

CREATE TABLE stud (
    st_id INT PRIMARY KEY,
    st_name VARCHAR(100),
    dept VARCHAR(50),
    gpa DECIMAL(3,2)
);

INSERT INTO stud (st_id, st_name, dept, gpa) VALUES
(101, 'Aarav', 'Computer Science', 3.80),
(102, 'Bhavana', 'Mechanical', 3.40),
(103, 'Chirag', 'Electrical', 3.90),
(104, 'Deepika', 'Computer Science', 3.65),
(105, 'Eshan', 'Mechanical', 3.50),
(106, 'Farah', 'Civil', 3.20),
(107, 'Gautam', 'Electrical', 3.95),
(108, 'Harini', 'Computer Science', 3.70),
(109, 'Ishaan', 'Civil', 3.10),
(110, 'Jhanvi', 'Mechanical', 3.55);

SELECT 
    MAX(gpa) AS highest_gpa,
    MIN(gpa) AS lowest_gpa
FROM stud;

Result:

Practical Example

Clarification:  Here, MAX(GPA) and MIN(GPA) indicate the highest and lowest GPA of the students, respectively.

Frequent Mistakes to Evade

  • Using WHERE instead of HAVING: You cannot utilize WHERE to filter on a MIN() or MAX(). You must employ the HAVING clause post-aggregation.
  • Neglecting GROUP BY: When seeking MIN or MAX per GROUP (such as per city or department), omitting GROUP BY will lead to incorrect or surprising outcomes.
  • Erroneous Column Selection: Mistakes may arise when you choose non-aggregated columns without appropriately grouping them (this is particularly significant in strict SQL mode).

Recommended Practices

  • Utilize GROUP BY when applicable: If you require minimum or maximum values by category (like by city or department), apply MIN() or MAX() with an appropriate GROUP BY clause.
  • Be Mindful of NULLs: Remember that MIN() and MAX() will disregard NULLs by default; if NULLs are crucial in your logic, consider using COALESCE() or other IS NULL evaluations.
  • Utilize HAVING for Aggregate Filtering: When filtering based on MIN() or MAX() outcomes, ensure you use the HAVING clause, not WHERE.
Become a Data Expert – Free SQL Course Available!
Discover how to write effective queries and manage databases with our beginner-friendly free SQL course.
Explore Program
quiz-icon

Summary

MIN() and MAX() are vital SQL functions employed to obtain the minimum and maximum values from datasets. They are also essential tools utilized
“““html

in documentation, evaluation, and decision-making tasks within business sectors. They are, however, most effectively utilized under standard operating procedures such as properly managing NULLs, employing GROUP BY and HAVING when appropriate, and enhancing performance via indexes. In this post, you have acquired insights on the MIN() and MAX() functions comprehensively.

Elevate your expertise by signing up for the SQL Training Course today to benefit from practical experience. Additionally, gear up for job interviews with SQL interview questions crafted by industry professionals.

SQL MIN() and MAX() Functions – FAQs

Q1. What does the MIN() function return?

It yields the smallest value from the designated column.

Q2. What does the MAX() function return?

It provides the largest value from the designated column.

Q3. Is it feasible to utilize text data with the MIN() and MAX() functions?

Indeed, you can apply text data with the MIN() and MAX() functions to discover the alphabetically smallest or largest entries.

Q4. Do MIN() and MAX() disregard NULL values?

Yes, MIN() and MAX() disregard NULL values and focus solely on non-NULL information.

Q5. Can the WHERE clause be used alongside the MIN() and MAX() functions?

Utilize the WHERE clause to narrow down rows prior to aggregation, and the HAVING clause to filter based on aggregation results.

The article SQL MIN() and MAX() Functions first appeared on Intellipaat Blog.

“`

Staff SEO Expert

Leave a Reply

Your email address will not be published. Required fields are marked *

Share This