Je hebt het aantal producten in elke categorie verkregen. Nu heeft het bedrijf je de taak gegeven om de categorieën op te halen waarin de producten meer dan een bepaald bedrag kosten, zodat ze daar korting op kunnen geven.

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

Deze cursus is ideaal voor wie al een basiskennis van SQL heeft en zich wil verdiepen in geavanceerdere concepten om krachtigere query's te maken. Tijdens de cursus maak je kennis met het groeperen van gegevens en het filteren van gegroepeerde data. Je leert ook hoe je met meerdere tabellen tegelijk werkt, inclusief het combineren ervan. Daarnaast verken je verschillende soorten joins tussen tabellen en hoe je deze in de praktijk toepast.

In deze sectie leert u hoe u gegevens effectief kunt groeperen en beheren. We introduceren de GROUP BY-operator voor het groeperen van gegevens. Daarnaast leert u hoe u gegroepeerde gegevens kunt filteren met behulp van de HAVING-operator.

U leert over geneste subquery's, hoe u deze maakt en hoe u ze effectief gebruikt.

Hier leert u hoe u meerdere tabellen kunt combineren, zodat u met een samengestelde tabel kunt werken en het opstellen van query's kunt vereenvoudigen.

Leer de basisprincipes van Data Definition Language (DDL) en Data Manipulation Language (DML) in SQL, inclusief het aanmaken, wijzigen en verwijderen van databaseobjecten, evenals het invoegen, bijwerken en verwijderen van gegevens binnen tabellen.

Uitdaging: Verkrijg Categorie op Basis van Productprijs

Korte instructies

Oplossing