import unittest
import psycopg2
import re

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_explain_is_used(self):
        """Check that EXPLAIN is used in the query."""
        with open("query.sql", "r") as file:
            query = file.read()
        condition = normalize_text(query).strip().startswith("explain")
        _dynamic_test(
            self,
            condition,
            "EXPLAIN is used at the beginning of the query.",
            "You must use EXPLAIN at the start of your query."
        )

    def test_select_statement_correct(self):
        """Check that the SELECT statement retrieves only name and price from products."""
        with open("query.sql", "r") as file:
            query = normalize_text(file.read())
        # Remove EXPLAIN and whitespace
        select_part = re.sub(r"^explain", "", query).strip()
        # Remove trailing semicolon if present
        if select_part.endswith(';'):
            select_part = select_part[:-1].strip()
        # Accept only SELECT name, price FROM products
        pattern = r"^select\s+name\s*,\s*price\s+from\s+products$"
        condition = bool(re.match(pattern, select_part))
        _dynamic_test(
            self,
            condition,
            "SELECT statement retrieves only name and price from products.",
            "SELECT query must retrieve only name and price columns from products table."
        )

    def test_no_extra_columns_or_tables(self):
        """Ensure no extra columns or tables are included in the SELECT."""
        with open("query.sql", "r") as file:
            query = normalize_text(file.read())
        # Remove EXPLAIN
        select_part = query.replace("explain", "", 1).strip()
        forbidden = ["*", "order_items", "orders", "customers", "description", "stock", "product_id"]
        condition = all(word not in select_part for word in forbidden)
        _dynamic_test(
            self,
            condition,
            "No extra columns or tables are included in the SELECT statement.",
            "SELECT statement must not include extra columns or reference other tables."
        )


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

Dive deep into the world of SQL query optimization by mastering the EXPLAIN statement and understanding how query planners work. This course blends engaging theory with hands-on, real-world tasks to help you analyze, interpret, and optimize SQL queries using EXPLAIN and related tools.

Learn the fundamentals of the EXPLAIN statement, how query planners work, and how to interpret basic query plans.

Delve into more complex query plans, including multi-table joins, subqueries, and the impact of indexes.

Apply your knowledge to complex, real-world scenarios involving large datasets, reporting queries, and performance troubleshooting.

Challenge: Analyzing a Simple Query Plan

Solution

Challenge: Analyzing a Simple Query Plan

Solution