import unittest
import psycopg2
import pandas as pd
import re

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_sum_bonus_column(self):
        """Check if the query calculates the sum of the bonus column."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        result = self.cursor.fetchone()
        total_bonus = result[0]
        self.cursor.execute("SELECT SUM(bonus) FROM employees;")
        expected = self.cursor.fetchone()[0]
        _dynamic_test(
            self,
            total_bonus == expected,
            "Sum of bonus column is correct.",
            f"Expected sum of bonus {expected}, got {total_bonus}."
        )

    def test_count_employees(self):
        """Check if the query counts the number of employees."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        result = self.cursor.fetchone()
        num_employees = result[1]
        self.cursor.execute("SELECT COUNT(employee_id) FROM employees;")
        expected = self.cursor.fetchone()[0]
        _dynamic_test(
            self,
            num_employees == expected,
            "Count of employees is correct.",
            f"Expected employee count {expected}, got {num_employees}."
        )

    def test_nullif_used_in_division(self):
        """Check if NULLIF is used to prevent division by zero in the query."""
        with open("query.sql", "r") as file:
            query = file.read()
        normalized_query = normalize_text(query)
        has_nullif = 'nullif' in normalized_query
        _dynamic_test(
            self,
            has_nullif,
            "NULLIF is used in division to prevent division by zero.",
            "NULLIF is not used in the division expression."
        )

    def test_division_result_null_when_no_employees(self):
        """Check that division result is NULL if there are zero employees."""
        # Create a temp table with zero employees
        self.cursor.execute("DROP TABLE IF EXISTS temp_employees;")
        self.cursor.execute("CREATE TABLE temp_employees AS SELECT * FROM employees WHERE 1=0;")
        with open("query.sql", "r") as file:
            query = file.read().replace('employees', 'temp_employees')
        self.cursor.execute(query)
        result = self.cursor.fetchone()
        avg_bonus = result[2] if len(result) > 2 else None
        _dynamic_test(
            self,
            avg_bonus is None,
            "Division result is NULL when there are zero employees.",
            f"Expected division result to be NULL, got {avg_bonus}."
        )

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

Master the use of built-in SQL functions to manipulate strings, dates, numbers, logical values, nulls, and JSON data. This course provides a hands-on approach to understanding and applying SQL functions in real-world scenarios, enhancing your data querying and transformation skills.

Explore SQL's powerful string functions for manipulating and analyzing textual data in databases.

Master SQL's date and time functions to work with temporal data effectively.

Learn to use SQL's numeric functions for calculations and data analysis.

Harness SQL's logical functions to control query flow and make decisions based on data.

Master handling of NULL values in SQL using specialized functions.

Learn to work with JSON data in SQL using specialized functions for querying and manipulation.

Challenge: Prevent Division by Zero

Solution