Summary  
This chapter demonstrates how to filter out duplicate rows and return only unique column values by using the DISTINCT keyword in SELECT statements.

General domain of usage  
Database querying

Note that the `SELECT` statement returns all rows in a given column. However, what if we don't need all the values from a column, especially when they are duplicated, and we only need **unique values**? 

For such cases, it is convenient to use the keyword `DISTINCT`, placed immediately before the column names. Let's take a look at an example:

import unittest
import psycopg2
import pandas as pd

class TestTask(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_correct_distinct_continents(self):
        """Check that the query returns the correct set of distinct continents from country table."""
        with open("query.sql", "r") as file:
            query = file.read()

        # Get expected distinct continents from the table
        self.cursor.execute("SELECT DISTINCT continent FROM country;")
        expected_rows = self.cursor.fetchall()
        expected_set = set([row[0] for row in expected_rows])

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

        condition = user_set == expected_set
        _dynamic_test(
            self,
            condition,
            "Query returns the correct set of distinct continent values.",
            f"Query did not return the correct set of distinct continent values.\\nExpected: {expected_set}\\nGot: {user_set}"
        )

    def test_query_uses_distinct_keyword(self):
        """Check that the query uses the DISTINCT keyword."""
        with open("query.sql", "r") as file:
            query = file.read()
        condition = "distinct" in query.lower()
        _dynamic_test(
            self,
            condition,
            "Query uses the DISTINCT keyword.",
            "Query does not use the DISTINCT keyword."
        )

    def test_query_selects_continent_column(self):
        """Check that the query selects the continent column."""
        with open("query.sql", "r") as file:
            query = file.read()
        # Accepts "continent" or "(continent)"
        condition = "continent" in query.lower()
        _dynamic_test(
            self,
            condition,
            "Query selects the 'continent' column.",
            "Query does not select the 'continent' column."
        )

    def test_query_from_country_table(self):
        """Check that the query selects from the country table."""
        with open("query.sql", "r") as file:
            query = file.read()
        condition = "from country" in query.lower().replace("\\n", " ").replace("\t", " ")
        _dynamic_test(
            self,
            condition,
            "Query selects from the 'country' table.",
            "Query does not select from the 'country' table."
        )

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

Learn the core fundamentals of SQL and relational databases. Understand how databases store and organize data, write basic SQL queries, work with tables and perform essential operations. Build a strong foundation for working with databases in backend development.

Retrieving Distinct Rows

Ratkaisu