Summary  
This chapter explains how to use the MAX() aggregate function to retrieve the highest value from a specified column in a dataset.  

General domain of usage  
Relational database querying

`MAX()` - This function returns the highest value in a particular column. Let's take a look at the following example:

Here `MAX()` returns the largest population value in the `country` table.

import unittest
import psycopg2
import pandas as pd

class TestPostgresQuery(unittest.TestCase):
    @classmethod
    def setUpClass(cls):
        cls.connection = psycopg2.connect(
            host="localhost",
            user="agent",
            password="qwerty",
            dbname="testdb",
            sslmode="disable"
        )
        cls.cursor = cls.connection.cursor()

    def test_returns_largest_surfacearea(self):
        """Check that the query returns the correct largest surfacearea value."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        user_result = self.cursor.fetchone()[0]
        # Compute expected max value
        self.cursor.execute("SELECT MAX(surfacearea) FROM country;")
        expected = self.cursor.fetchone()[0]
        condition = user_result == expected and expected == 9363520
        _dynamic_test(
            self,
            condition,
            "Query returns the correct largest surfacearea value (9363520).",
            f"Query did not return the correct largest surfacearea. Expected 9363520, got {user_result}."
        )

    def test_query_single_column(self):
        """Check that the query returns only one column (the aggregate)."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        columns = [desc[0] for desc in self.cursor.description]
        condition = len(columns) == 1
        _dynamic_test(
            self,
            condition,
            "Query returns exactly one column.",
            f"Query returned {len(columns)} columns instead of 1."
        )

def _dynamic_test(test_case, condition, success_message, failure_message):
    if condition:
        test_case._testMethodName = success_message
        test_case.assertTrue(True, success_message)
    else:
        test_case._testMethodName = failure_message
        test_case.fail(failure_message)

def normalize_text(text):
    text = text.lower()
    text = re.sub(r"\\s{2,}", " ", text)
    text = re.sub(r"\\s*([,:?])\\s*", r"\\1 ", text)
    return text.strip()

if __name__ == "__main__":
    unittest.main()


test_solution.py

This course is perfect for beginners ready to explore the world of SQL. Whether you're just starting out in database management or aiming to use SQL for your application development projects, this course covers the essentials. You'll quickly learn how to leverage the full potential of SQL, from querying and managing data to seamlessly integrating it into real-world applications. By the end of the course, you'll have the confidence and skills to solve practical problems with SQL and enhance your development process.

The MAX() Function

解答