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 setUp(self):
        # Clean up any test data that might have been left from previous tests
        self.cursor.execute("DELETE FROM members WHERE member_id = 6;")
        self.connection.commit()
        # Remove all borrowings for member_id = 6 (if any)
        self.cursor.execute("DELETE FROM borrowings WHERE member_id = 6;")
        self.connection.commit()

    def test_returns_only_members_with_no_borrowings(self):
        """Check that only members with no borrowings are returned."""
        # Remove all borrowings
        self.cursor.execute("DELETE FROM borrowings;")
        self.connection.commit()
        # Insert a member who has not borrowed any books
        self.cursor.execute("INSERT INTO members (member_id, name, join_date) VALUES (6, 'Fiona Clark', '2022-11-01');")
        self.connection.commit()
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        result_names = set([r[0] for r in rows])
        # Get all members
        self.cursor.execute("SELECT name FROM members;")
        all_members = set([r[0] for r in self.cursor.fetchall()])
        _dynamic_test(
            self,
            result_names == all_members,
            "All members returned when borrowings table is empty.",
            f"Expected all members ({all_members}), but got: {result_names}"
        )
        # Clean up
        self.cursor.execute("DELETE FROM members WHERE member_id = 6;")
        self.connection.commit()

    def test_returns_zero_if_all_members_have_borrowings(self):
        """Check that zero rows are returned if all members have at least one borrowing and no extra member is added."""
        # Ensure no extra member exists
        self.cursor.execute("DELETE FROM members WHERE member_id = 6;")
        self.connection.commit()
        # Add a borrowing for every member
        self.cursor.execute("SELECT member_id FROM members;")
        member_ids = [row[0] for row in self.cursor.fetchall()]
        self.cursor.execute("DELETE FROM borrowings;")
        for i, member_id in enumerate(member_ids):
            self.cursor.execute(f"INSERT INTO borrowings (borrowing_id, member_id, book_id, borrow_date) VALUES (1000+{i}, {member_id}, 1, '2023-01-01');")
        self.connection.commit()
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        _dynamic_test(
            self,
            len(rows) == 0,
            "Zero rows returned when all members have borrowings.",
            f"Expected zero rows, but got: {rows}"
        )
        # Clean up: remove the inserted borrowings
        self.cursor.execute("DELETE FROM borrowings WHERE borrowing_id >= 1000;")
        self.connection.commit()

    def test_no_member_with_borrowings_in_result(self):
        """Check that no member with borrowings appears in the result."""
        # Add a member with no borrowings
        self.cursor.execute("INSERT INTO members (member_id, name, join_date) VALUES (6, 'Fiona Clark', '2022-11-01');")
        self.connection.commit()
        # Give all other members at least one borrowing
        self.cursor.execute("SELECT member_id FROM members WHERE member_id != 6;")
        other_member_ids = [row[0] for row in self.cursor.fetchall()]
        self.cursor.execute("DELETE FROM borrowings;")
        for i, member_id in enumerate(other_member_ids):
            self.cursor.execute(f"INSERT INTO borrowings (borrowing_id, member_id, book_id, borrow_date) VALUES (2000+{i}, {member_id}, 1, '2023-01-01');")
        self.connection.commit()
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        result_names = set([r[0] for r in rows])
        _dynamic_test(
            self,
            result_names == {'Fiona Clark'},
            "Only Fiona Clark (no borrowings) is returned.",
            f"Expected only Fiona Clark, but got: {result_names}"
        )
        # Clean up
        self.cursor.execute("DELETE FROM members WHERE member_id = 6;")
        self.cursor.execute("DELETE FROM borrowings WHERE borrowing_id >= 2000;")
        self.connection.commit()

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):
    import re
    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におけるサブクエリの記述と応用の技術を習得します。本モジュールでは、サブクエリの理論と実践、SELECT、WHERE、FROM句での利用、相関サブクエリについて解説します。

注文、顧客、商品に焦点を当てたeコマースデータセットを使用してサブクエリを探求します。サブクエリを活用してインサイトを抽出し、ビジネス上の課題を解決する方法を学びます。

チャレンジ：借用履歴のないメンバー

解答