Summary  
This chapter covers the use of the MIN() aggregate function to retrieve the smallest value from a specified column in a database table.

General domain of usage  
Database querying and data analysis

`MIN()` - this function returns the lowest value in a particular column. 

Let's look at an example:

Here, `MIN()` returns the smallest 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_min_surfacearea_correct_value(self):
        """Check if the query returns the correct minimum surfacearea value from the country table."""
        with open("query.sql", "r") as file:
            query = file.read()
        
        # Get correct minimum value with reference query
        self.cursor.execute("SELECT MIN(surfacearea) FROM country;")
        expected = self.cursor.fetchone()[0]

        # Run user query
        try:
            self.cursor.execute(query)
            result = self.cursor.fetchone()[0]
            condition = result == expected
            fail_msg = f"Expected minimum surfacearea {expected}, but got {result}."
        except Exception as e:
            condition = False
            fail_msg = f"Query failed to execute: {e}"

        _dynamic_test(
            self,
            condition,
            "Query returns correct minimum surfacearea value.",
            fail_msg
        )

    def test_usage_of_min_function(self):
        """Check if the query uses the MIN function."""
        with open("query.sql", "r") as file:
            query = file.read()
        condition = "min" in query.lower()
        _dynamic_test(
            self,
            condition,
            "Query uses the MIN function.",
            "Query does not use the MIN function."
        )

    def test_usage_of_surfacearea_column(self):
        """Check if the query selects the surfacearea column."""
        with open("query.sql", "r") as file:
            query = file.read()
        condition = "surfacearea" in query.lower()
        _dynamic_test(
            self,
            condition,
            "Query selects the surfacearea column.",
            "Query does not select the surfacearea column."
        )

    def test_no_syntax_error(self):
        """Check that the query does not have syntax errors."""
        with open("query.sql", "r") as file:
            query = file.read()
        try:
            self.cursor.execute(query)
            condition = True
            fail_msg = ""
        except Exception as e:
            condition = False
            fail_msg = f"Query raised a syntax error: {e}"
        _dynamic_test(
            self,
            condition,
            "Query executed without syntax errors.",
            fail_msg
        )

    def test_no_invalid_column(self):
        """Check that the query does not reference a non-existing column."""
        with open("query.sql", "r") as file:
            query = file.read()
        try:
            self.cursor.execute(query)
            condition = True
            fail_msg = ""
        except psycopg2.errors.UndefinedColumn:
            condition = False
            fail_msg = "Query references a non-existing column."
        except Exception:
            condition = True  # Only fail on undefined column
            fail_msg = ""
        _dynamic_test(
            self,
            condition,
            "Query does not reference a non-existing column.",
            fail_msg
        )

    def test_no_invalid_table(self):
        """Check that the query does not reference a non-existing table."""
        with open("query.sql", "r") as file:
            query = file.read()
        try:
            self.cursor.execute(query)
            condition = True
            fail_msg = ""
        except psycopg2.errors.UndefinedTable:
            condition = False
            fail_msg = "Query references a non-existing table."
        except Exception:
            condition = True  # Only fail on undefined table
            fail_msg = ""
        _dynamic_test(
            self,
            condition,
            "Query does not reference a non-existing table.",
            fail_msg
        )


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 MIN() Function

解答