import unittest
import psycopg2
import pandas as pd

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_all_students_listed(self):
        """Check that every student appears in the result."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute("SELECT COUNT(*) FROM students;")
        student_count = self.cursor.fetchone()[0]
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        result_count = len(rows)
        _dynamic_test(
            self,
            result_count == student_count,
            "All students are listed in the result.",
            f"Expected {student_count} students, but got {result_count}."
        )

    def test_students_with_class(self):
        """Check students with a class assigned have correct class name."""
        with open("query.sql", "r") as file:
            query = file.read()
        solution_query = '''
SELECT students.name AS student_name, classes.class_name AS class_name
FROM students
JOIN classes ON students.class_id = classes.class_id;
'''
        self.cursor.execute(solution_query)
        expected = set(self.cursor.fetchall())
        self.cursor.execute(query)
        actual = set()
        for row in self.cursor.fetchall():
            # Only consider those not 'Unassigned'
            if row[1] != 'Unassigned':
                actual.add(row)
        _dynamic_test(
            self,
            expected == actual,
            "Students with classes have correct class names.",
            f"Mismatch for students with classes.\\nExpected: {expected}\\nGot: {actual}"
        )

    def test_students_without_class(self):
        """Check students without a class show 'Unassigned' as class name."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute('''
SELECT name FROM students WHERE class_id IS NULL;
''')
        students_no_class = set([row[0] for row in self.cursor.fetchall()])
        self.cursor.execute(query)
        actual = set()
        for row in self.cursor.fetchall():
            if row[1] == 'Unassigned':
                actual.add(row[0])
        _dynamic_test(
            self,
            students_no_class == actual,
            "Students without a class are shown as 'Unassigned'.",
            f"Mismatch for students without class.\\nExpected: {students_no_class}\\nGot: {actual}"
        )

    def test_no_student_omitted(self):
        """Check no student is omitted from the result."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute("SELECT name FROM students;")
        all_students = set([row[0] for row in self.cursor.fetchall()])
        self.cursor.execute(query)
        student_names = set([row[0] for row in self.cursor.fetchall()])
        _dynamic_test(
            self,
            all_students == student_names,
            "No student is omitted from the result.",
            f"Some students are missing from the result.\\nExpected: {all_students}\\nGot: {student_names}"
        )

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

実践的で現実世界の例やハンズオンチャレンジを通じて、SQL結合の技術を習得します。本コースでは、基礎的な結合の概念から高度な多テーブルクエリまでを網羅し、複数のデータソースからデータを自信を持って分析・統合できる力を身につけます。

実践的なシナリオとハンズオンチャレンジを通じて、主要なSQL結合タイプ（INNER、LEFT、RIGHT、FULL OUTER JOIN）を探求します。

複数テーブルの結合、自己結合、および複雑なデータ分析のための高度な結合条件を習得し、SQLスキルを向上させます。

結合におけるNULL処理の詳細を習得し、結合パフォーマンスを最適化し、実際のデータ分析課題に対応します。

チャレンジ：デフォルト値付き学生・クラスレポート

解答