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_selects_all_columns(self):
        """Check if query selects all columns from products table."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        columns = [desc[0] for desc in self.cursor.description]
        self.cursor.execute("SELECT * FROM products LIMIT 1;")
        expected_columns = [desc[0] for desc in self.cursor.description]
        condition = set(columns) == set(expected_columns)
        _dynamic_test(
            self,
            condition,
            "Query selects all columns from products table.",
            f"Query does not select all columns from products table. Found: {columns}"
        )

    def test_only_electronics_category(self):
        """Check if only products with category 'Electronics' are returned."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        columns = [desc[0] for desc in self.cursor.description]
        df = pd.DataFrame(rows, columns=columns)
        if len(df) == 0:
            condition = False
            failure_msg = "No rows returned. Should return products in 'Electronics' category."
        else:
            non_electronics = df[df['category'] != 'Electronics']
            condition = len(non_electronics) == 0
            if not condition:
                failure_msg = f"Found products not in 'Electronics' category: {non_electronics.to_dict('records')}"
            else:
                failure_msg = ""
        _dynamic_test(
            self,
            condition,
            "Only products in 'Electronics' category are returned.",
            failure_msg
        )

    def test_ordered_by_price_desc(self):
        """Check if results are ordered by price descending."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        columns = [desc[0] for desc in self.cursor.description]
        df = pd.DataFrame(rows, columns=columns)
        if len(df) == 0:
            condition = False
            failure_msg = "No rows returned. Cannot check order."
        else:
            prices = df['price'].tolist()
            sorted_prices = sorted(prices, reverse=True)
            condition = prices == sorted_prices
            failure_msg = f"Prices are not ordered descending: {prices}"
        _dynamic_test(
            self,
            condition,
            "Results are ordered by price descending.",
            failure_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

A comprehensive beginner-friendly course designed to teach SQL from the ground up, focusing on practical data analysis skills. Through engaging explanations, real-world examples, and hands-on challenges, learners will master querying, transforming, and analyzing data using SQL.

Learn the foundational concepts of relational databases and SQL. Explore how to retrieve, filter, and sort data from tables, setting the stage for deeper data analysis.

Dive into SQL's powerful aggregation features. Learn to summarize, group, and analyze data for deeper insights.

Unlock the power of combining data from multiple tables. Learn to perform different types of joins to answer complex business questions.

Master advanced SQL techniques for powerful data analysis, including subqueries, window functions, and data transformation.

Challenge: Product Table Exploration

Solution