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_only_employees_from_large_departments(self):
        """Check that only employees from departments with more than 5 employees are returned."""
        with open("query.sql", "r") as file:
            query = file.read()

        # Find department_ids with more than 5 employees
        self.cursor.execute("""
            SELECT department_id
            FROM employees
            GROUP BY department_id
            HAVING COUNT(*) > 5
        """)
        qualifying_departments = set(row[0] for row in self.cursor.fetchall())

        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        columns = [desc[0] for desc in self.cursor.description]
        result_df = pd.DataFrame(rows, columns=columns)

        if not result_df.empty:
            unique_depts = set(result_df['department_id'].unique())
        else:
            unique_depts = set()
        only_large_depts = unique_depts.issubset(qualifying_departments)

        _dynamic_test(
            self,
            only_large_depts,
            "All returned employees belong to departments with more than 5 employees.",
            "Some returned employees are from departments with 5 or fewer employees."
        )

    def test_query_returns_all_columns(self):
        """Check that all columns from employees table are returned."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        columns = [desc[0] for desc in self.cursor.description]
        self.cursor.execute("SELECT * FROM employees LIMIT 1")
        employees_columns = [desc[0] for desc in self.cursor.description]
        _dynamic_test(
            self,
            set(columns) == set(employees_columns),
            "Query returns all columns from employees table.",
            "Query does not return all columns from employees table."
        )

    def test_query_uses_nested_subquery(self):
        """Check that query uses a nested subquery in the WHERE clause."""
        with open("query.sql", "r") as file:
            query = file.read().lower().replace('\\n', ' ')
        uses_in_subquery = "where" in query and "in (" in query
        _dynamic_test(
            self,
            uses_in_subquery,
            "Query uses a nested subquery in the WHERE clause.",
            "Query does not use a nested subquery in the WHERE clause."
        )

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

Master the art of writing and optimizing nested subqueries in SQL. This course explores advanced querying techniques using subqueries in WHERE, FROM, and SELECT clauses, and teaches best practices for performance optimization. Each section blends engaging theory with real-world challenges to solidify your understanding.

Explore how to use nested subqueries within the WHERE clause to filter data based on complex conditions and relationships.

Learn how to use nested subqueries in the FROM clause to create derived tables for complex data analysis.

Discover how to use nested subqueries in the SELECT clause to compute values for each row in your result set.

Master best practices and techniques for optimizing nested subqueries for performance and maintainability.

Challenge: Filter Employees by Department Size

解答