In most databases, the `id` column acts as a unique identifier, often referred to as a primary key, for each record. By selecting both the `id` and `name` columns, you can view the unique identifier linked to each country. This is crucial for associating records or when you need to reference specific entries in the database.

Definition

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_query_returns_id_and_name_columns(self):
        """Check that the query returns both 'id' and 'name' columns."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        columns = [desc[0].strip().lower() for desc in self.cursor.description]
        has_id = 'id' in columns
        has_name = 'name' in columns
        missing = []
        if not has_id:
            missing.append('id')
        if not has_name:
            missing.append('name')
        _dynamic_test(
            self,
            has_id and has_name,
            "Query returns both 'id' and 'name' columns.",
            f"Query is missing columns: {', '.join(missing)}."
        )

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

    def test_query_returns_expected_rows(self):
        """Check that the query returns all expected (id, name) pairs from the country table."""
        with open("query.sql", "r") as file:
            query = file.read()
        # Reference solution to get expected rows
        self.cursor.execute("SELECT name, id FROM country")
        expected = set(self.cursor.fetchall())
        self.cursor.execute(query)
        result = set(self.cursor.fetchall())
        condition = expected == result
        if not condition:
            missing = expected - result
            extra = result - expected
            failure_msg = f"Rows missing: {missing}, Extra rows: {extra}"
        else:
            failure_msg = ""
        _dynamic_test(
            self,
            condition,
            "Query returns all correct (name, id) pairs from the country table.",
            failure_msg
        )

    def test_query_syntax_valid(self):
        """Check that the query executes without syntax errors."""
        with open("query.sql", "r") as file:
            query = file.read()
        try:
            self.cursor.execute(query)
            _dynamic_test(
                self,
                True,
                "Query syntax is valid.",
                ""
            )
        except Exception as e:
            _dynamic_test(
                self,
                False,
                "",
                f"Query failed to execute due to syntax error: {e}"
            )

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

This course is perfect for beginners ready to explore the world of SQL. Whether you're just starting out in database management or aiming to use SQL for your application development projects, this course covers the essentials. You'll quickly learn how to leverage the full potential of SQL, from querying and managing data to seamlessly integrating it into real-world applications. By the end of the course, you'll have the confidence and skills to solve practical problems with SQL and enhance your development process.

Challenge: Find All Countries With Their IDs

解答