Olemme saaneet selville tuotteiden määrän jokaisessa kategoriassa. Nyt yritys on pyytänyt meitä hakemaan ne kategoriat, joissa tuotteiden hinta ylittää tietyn rahamäärän, jotta he voivat asettaa niihin alennuksen.

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

Tämä kurssi on täydellinen niille, joilla on jo perustiedot SQL:stä ja jotka haluavat syventyä edistyneempiin käsitteisiin luodakseen tehokkaampia kyselyitä. Kurssin aikana tutustut tietojen ryhmittelyyn ja ryhmiteltyjen tietojen suodattamiseen. Opit myös työskentelemään useiden taulukoiden kanssa samanaikaisesti, mukaan lukien niiden yhdistäminen. Lisäksi tutustut erilaisiin taulukoiden yhdistämistyyppeihin ja niiden soveltamiseen käytännössä.

This course is perfect for those who already have a basic understanding of SQL and want to delve into more advanced concepts to craft more powerful queries. Throughout the course, you will become familiar with data grouping and filtering grouped data. You will also learn how to work with multiple tables simultaneously, including how to combine them. Additionally, you will explore different types of table joins and how to apply them in practice.

Haaste: Hae Kategoria Tuotteen Hinnan Perusteella

Lyhyet ohjeet

Ratkaisu