Explain the use of MIN() and MAX() using MySQL in Python?

The MIN() and MAX() functions are MySQL aggregate functions used to find the smallest and largest values from a selected column. In Python, we can execute these functions through MySQL connector to perform database operations.

The MIN() function returns the smallest value from the selected column, while the MAX() function returns the largest value from the selected column.

Syntax

MIN() Function:

SELECT MIN(column_name) FROM table_name

MAX() Function:

SELECT MAX(column_name) FROM table_name

Steps to Find Minimum and Maximum Values

To execute MIN() and MAX() functions using MySQL in Python, follow these steps:

• Import MySQL connector

• Establish connection with the database using connect()

• Create the cursor object using cursor() method

• Create a query using the appropriate MySQL statements

• Execute the SQL query using execute() method

• Fetch results and close the connection

Sample Data

Let us consider a table named "Students" which contains the name and marks of students:

+----------+-----------+
|   name   |   marks   |
+----------+-----------+
|   Rohit  |    62     |
|   Rahul  |    75     |
|   Inder  |    99     |
|  Khushi  |    49     |
|   Karan  |    92     |
+----------+-----------+

Example

Here's how to find the lowest and highest marks from the Students table:

import mysql.connector

# Establish database connection
db = mysql.connector.connect(
    host="your_host",
    user="your_username", 
    password="your_password",
    database="database_name"
)

cursor = db.cursor()

# Query to find minimum marks
query1 = "SELECT MIN(marks) FROM Students"
cursor.execute(query1)
lowest = cursor.fetchone()
print("Lowest marks:", lowest[0])

# Query to find maximum marks  
query2 = "SELECT MAX(marks) FROM Students"
cursor.execute(query2)
highest = cursor.fetchone()
print("Highest marks:", highest[0])

# Close the database connection
db.close()

Output

Lowest marks: 49
Highest marks: 99

Key Points

• Use fetchone() instead of fetchall() since MIN() and MAX() return single values

• Access the result using index [0] as the cursor returns a tuple

• Always close the database connection after completing operations

• These functions work with numeric, date, and string data types

Conclusion

MIN() and MAX() functions are essential for finding extreme values in database columns. When using Python with MySQL connector, remember to properly handle the connection and fetch results appropriately.