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_row_count_matches_assignments(self):
        """Check that the query returns one row per assignment."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute("SELECT COUNT(*) FROM assignments;")
        expected_count = self.cursor.fetchone()[0]
        self.cursor.execute(query)
        result = self.cursor.fetchall()
        _dynamic_test(
            self,
            len(result) == expected_count,
            f"Returned {len(result)} rows, matching number of assignments.",
            f"Expected {expected_count} rows (one per assignment), but got {len(result)}."
        )

    def test_employee_project_department_correctness(self):
        """Check that each row contains correct employee, project, and department."""
        with open("query.sql", "r") as file:
            query = file.read()
        # Get expected results using solution query
        solution_query = '''
        SELECT e.name AS employee_name, p.project_name, d.department_name
        FROM assignments a
        JOIN employees e ON a.employee_id = e.employee_id
        JOIN projects p ON a.project_id = p.project_id
        JOIN departments d ON p.department_id = d.department_id;
        '''
        self.cursor.execute(solution_query)
        expected_rows = self.cursor.fetchall()
        self.cursor.execute(query)
        user_rows = self.cursor.fetchall()
        # Convert to sets for comparison
        expected_set = set(expected_rows)
        user_set = set(user_rows)
        missing = expected_set - user_set
        extra = user_set - expected_set
        condition = not missing and not extra
        failure_msg = ""
        if missing:
            failure_msg += f"Missing rows: {missing}\\n"
        if extra:
            failure_msg += f"Unexpected extra rows: {extra}\\n"
        _dynamic_test(
            self,
            condition,
            "All rows have correct employee, project, and department.",
            failure_msg.strip()
        )

    def test_no_unassigned_employees(self):
        """Check that no employees without assignments are included."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        user_rows = self.cursor.fetchall()
        # Get all assigned employee IDs
        self.cursor.execute("SELECT DISTINCT employee_id FROM assignments;")
        assigned_ids = set(row[0] for row in self.cursor.fetchall())
        # Get all employee names with assignments
        self.cursor.execute("SELECT employee_id, name FROM employees;")
        id_to_name = {row[0]: row[1] for row in self.cursor.fetchall()}
        included_names = set(row[0] for row in user_rows)
        # All included names must correspond to assigned employee IDs
        assigned_names = set(id_to_name[emp_id] for emp_id in assigned_ids)
        not_assigned = included_names - assigned_names
        _dynamic_test(
            self,
            not not_assigned,
            "No employees without assignments are included.",
            f"Employees without assignments included: {not_assigned}"
        )

    def test_department_matches_project(self):
        """Check that department matches the project assigned."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        user_rows = self.cursor.fetchall()
        # Get mapping from project_name to department_name
        self.cursor.execute("SELECT project_name, d.department_name FROM projects p JOIN departments d ON p.department_id = d.department_id;")
        project_to_dept = {row[0]: row[1] for row in self.cursor.fetchall()}
        mismatches = []
        for employee_name, project_name, department_name in user_rows:
            expected_dept = project_to_dept.get(project_name)
            if department_name != expected_dept:
                mismatches.append((employee_name, project_name, department_name, expected_dept))
        _dynamic_test(
            self,
            not mismatches,
            "Each department matches the project assigned.",
            f"Department mismatches found: {mismatches}"
        )

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処理の詳細を習得し、結合パフォーマンスを最適化し、実際のデータ分析課題に対応します。

チャレンジ：従業員プロジェクト割り当て

解答