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_index_created_on_customers_customer_id(self):
        """Check if index on customers(customer_id) was created."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        self.cursor.execute("""
            SELECT indexname, indexdef
            FROM pg_indexes
            WHERE tablename = 'customers';
        """)
        indexes = self.cursor.fetchall()
        found = False
        for idx_name, idx_def in indexes:
            if 'customer_id' in idx_def and 'customers' in idx_def:
                found = True
                break
        _dynamic_test(
            self,
            found,
            "Index on customers(customer_id) exists.",
            "Index on customers(customer_id) does not exist or is not defined correctly."
        )

    def test_no_select_or_explain_statements(self):
        """Check that no SELECT or EXPLAIN statements are present in the code."""
        with open("query.sql", "r") as file:
            query = file.read().lower()
        has_select = 'select' in query
        has_explain = 'explain' in query
        _dynamic_test(
            self,
            not (has_select or has_explain),
            "No SELECT or EXPLAIN statements present.",
            "SELECT or EXPLAIN statements found in the code."
        )

    def test_no_drop_or_alter_statements(self):
        """Check that no DROP or ALTER statements are present in the code."""
        with open("query.sql", "r") as file:
            query = file.read().lower()
        has_drop = 'drop' in query
        has_alter = 'alter' in query
        _dynamic_test(
            self,
            not (has_drop or has_alter),
            "No DROP or ALTER statements present.",
            "DROP or ALTER statements found in the code."
        )

    def test_no_table_or_column_modifications(self):
        """Check that no CREATE TABLE or column modification statements are present (other than CREATE INDEX)."""
        with open("query.sql", "r") as file:
            lines = [line.strip().lower() for line in file.readlines() if line.strip() != '']
        forbidden = any(
            line.startswith('create table') or
            line.startswith('add column') or
            line.startswith('rename column') or
            line.startswith('drop column')
            for line in lines if not line.startswith('create index')
        )
        _dynamic_test(
            self,
            not forbidden,
            "No forbidden table or column modifications present.",
            "Forbidden table or column modifications found in the code."
        )

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: Spotting Inefficiencies

Solution