Die Anzahl der Produkte in jeder Kategorie wurde bereits ermittelt. Nun sollst du die Kategorien abrufen, in denen die Produkte mehr als einen bestimmten Geldbetrag kosten, damit das Unternehmen dort Rabatte gewähren kann.

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_category_names_with_price_above_450(self):
        """Check that only unique category names with products priced above 450 are returned."""
        with open("query.sql", "r") as file:
            query = file.read()

        # Get the expected result using reference query
        reference_query = '''
            SELECT DISTINCT category.name
            FROM category
            JOIN product ON category.id = product.category_id
            WHERE price > 450
        '''
        self.cursor.execute(reference_query)
        expected_rows = self.cursor.fetchall()
        expected_set = set(row[0] for row in expected_rows)

        # Run user's query
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        user_set = set(row[0] for row in rows)

        condition = user_set == expected_set
        missing = expected_set - user_set
        extra = user_set - expected_set
        fail_msg = ""
        if missing:
            fail_msg += f"Missing expected categories: {', '.join(missing)}. "
        if extra:
            fail_msg += f"Unexpected categories returned: {', '.join(extra)}."
        _dynamic_test(
            self,
            condition,
            "Correct unique category names are returned for products with price > 450.",
            fail_msg or "Incorrect category names returned."
        )

    def test_output_only_category_name_column(self):
        """Check that only one column (category.name) is returned."""
        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 and normalize_text(columns[0]) == "name"
        _dynamic_test(
            self,
            condition,
            "Only one column 'name' is returned.",
            f"Expected only one column named 'name', got: {columns}"
        )

    def test_no_duplicate_category_names(self):
        """Check that there are no duplicate category names in the result."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        names = [row[0] for row in rows]
        condition = len(names) == len(set(names))
        _dynamic_test(
            self,
            condition,
            "No duplicate category names in the result.",
            "Duplicate category names found in the result."
        )

    def test_no_category_with_price_450_or_below(self):
        """Ensure no category is returned where all products have price <= 450."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        returned_names = set(row[0] for row in rows)
        # All categories that have at least one product with price > 450
        self.cursor.execute('''
            SELECT DISTINCT category.name
            FROM category
            JOIN product ON category.id = product.category_id
            WHERE price > 450
        ''')
        valid_names = set(row[0] for row in self.cursor.fetchall())
        # All categories that have only products with price <= 450
        self.cursor.execute('''
            SELECT DISTINCT category.name
            FROM category
            WHERE category.name NOT IN (
                SELECT DISTINCT category.name
                FROM category
                JOIN product ON category.id = product.category_id
                WHERE price > 450
            )
        ''')
        invalid_names = set(row[0] for row in self.cursor.fetchall())
        found_invalid = returned_names & invalid_names
        condition = not found_invalid
        _dynamic_test(
            self,
            condition,
            "No category with only products priced 450 or below is returned.",
            f"Categories returned with only products priced 450 or below: {', '.join(found_invalid)}"
        )


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

Dieser Kurs eignet sich ideal für alle, die bereits über grundlegende SQL-Kenntnisse verfügen und sich mit fortgeschritteneren Konzepten beschäftigen möchten, um leistungsfähigere Abfragen zu erstellen. Im Verlauf des Kurses lernen Sie das Gruppieren von Daten sowie das Filtern gruppierter Daten kennen. Sie erfahren außerdem, wie Sie mit mehreren Tabellen gleichzeitig arbeiten und diese miteinander kombinieren können. Darüber hinaus werden verschiedene Arten von Tabellenverknüpfungen behandelt und deren praktische Anwendung erläutert.

In diesem Abschnitt lernen Sie, wie Sie Daten effektiv gruppieren und verwalten. Wir führen den GROUP BY-Operator zur Gruppierung von Daten ein. Zusätzlich erfahren Sie, wie gruppierte Daten mit dem HAVING-Operator gefiltert werden.

Sie lernen verschachtelte Unterabfragen kennen, deren Erstellung sowie deren effektiven Einsatz.

Hier lernen Sie, wie Sie mehrere Tabellen kombinieren, um mit einer einheitlichen Tabelle zu arbeiten und die Erstellung von Abfragen zu vereinfachen.

Erlernen Sie die Grundlagen der Data Definition Language (DDL) und der Data Manipulation Language (DML) in SQL, einschließlich der Erstellung, Änderung und Löschung von Datenbankobjekten sowie des Einfügens, Aktualisierens und Löschens von Daten in Tabellen.

Herausforderung: Kategorie Nach Produktpreis Ermitteln

Kurzanleitung

Lösung