Summary  
This chapter covers the use of an aggregate function to compute the minimum value in a specified column.

General domain of usage  
Relational database querying

`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

Focuses on practical SQL skills for filtering data with WHERE, sorting results with ORDER BY, and summarizing information using GROUP BY and aggregate functions. Hands-on challenges reinforce each concept, preparing learners to analyze and summarize real-world datasets effectively.

The MIN() Function

Ratkaisu