Olet saanut selville tuotteiden määrän jokaisessa kategoriassa. Nyt yritys pyysi sinua hakemaan ne kategoriat, joissa tuotteiden hinta ylittää tietyn rahasumman, jotta niihin voidaan asettaa alennus.

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

Vie tietämyksesi seuraavalle tasolle edistyneillä SQL-tekniikoilla, joita käytetään todellisessa datatyössä. Opit ryhmittelemään ja suodattamaan tietoja GROUP BY- ja HAVING-lauseilla, kirjoittamaan sisäkkäisiä alikyselyitä sekä yhdistämään tauluja INNER-, LEFT-, RIGHT- ja FULL JOIN -tyypeillä. Lisäksi luot, muutat ja muokkaat tauluja DDL-lauseilla sekä lisäät, päivität ja poistat tietueita DML-lauseilla.

Opi ryhmittelemään tietoja GROUP BY -lauseella ja tiivistämään niitä aggregaattifunktioilla, kuten COUNT, SUM ja AVG. Tutustu ryhmiteltyjen tulosten suodattamiseen HAVING-lauseella sekä SQL-lauseiden oikeaan järjestykseen kyselyssä.



Opi kirjoittamaan sisäkkäisiä alikyselyitä WHERE- ja FROM-lausekkeissa monimutkaisempien kysymysten ratkaisemiseksi. Ymmärrä, miten yhdistetään kyselyiden tuloksia useista tauluista UNION-lauseen avulla.



Opi yhdistämään useita tauluja käyttämällä INNER-, LEFT-, RIGHT- ja FULL JOIN -tyyppejä. Harjoittele liitosten kirjoittamista, joiden avulla voit hakea toisiinsa liittyvää dataa tauluista yhtenäisenä tuloksena.

Tutustu SQL:n DDL- ja DML-perusteisiin: taulujen luominen CREATE-komennolla ja rajoitteilla sekä niiden muokkaaminen ALTER-, DROP- ja TRUNCATE-komennoilla. Tietueiden lisääminen, päivittäminen ja poistaminen INSERT-, UPDATE- ja DELETE-lauseilla.

Haaste: Hae kategoria tuotteen hinnan perusteella

Haaste: Hae kategoria tuotteen hinnan perusteella

Lyhyet ohjeet

Ratkaisu