**Video Description:**  
This short video introduces the challenge of creating a B-tree index on the `category` column of the `products` table. It demonstrates why such an index is useful for optimizing queries that filter products by category, and walks you through the syntax for index creation and how to write a query that benefits from the new index.

You have learned that indexes, especially **B-tree indexes**, can significantly improve the performance of queries that filter or search for values in large tables. In this challenge, your goal is to identify a query on the `products` table that would benefit from a **B-tree index**. 

Consider a scenario where you frequently need to retrieve all products in a specific category, such as all `"Electronics"` items. Without an index, the database must scan every row in the `products` table to find matching categories. By creating a **B-tree index** on the `category` column, you help the database quickly locate all products in a given category, making these queries much more efficient.

To optimize this kind of query, you should:
- Look for queries that use a `WHERE` clause to filter on the `category` column of the `products` table;
- Recognize that a **B-tree index** is ideal for columns with many repeated values (like product categories), especially when you often search or filter by that column.

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(self):
        """Check if a B-tree index on category column of products table was created."""
        with open("query.sql", "r") as file:
            query = file.read()
    
        # Execute only CREATE INDEX statements from user's file
        index_lines = [
            line.strip().rstrip(";")
            for line in query.splitlines()
            if line.strip().lower().startswith("create index")
        ]
    
        for stmt in index_lines:
            self.cursor.execute(stmt)
    
        self.connection.commit()
    
        self.cursor.execute("""
            SELECT indexdef
            FROM pg_indexes
            WHERE tablename = 'products';
        """)
        defs = [row[0].lower() for row in self.cursor.fetchall()]
    
        found = any(
            ('(category' in d or '( category' in d) and ('products' in d)
            for d in defs
        )
    
        _dynamic_test(
            self,
            found,
            "A B-tree index on the category column of products table was created.",
            "No B-tree index was found on the category column of products table."
        )

    def test_query_selects_electronics(self):
        """Check that the query selects all columns from products where category = 'Electronics'."""
        with open("query.sql", "r") as file:
            query = file.read()
        # Remove index creation line if present
        user_query = "\\n".join([
            line for line in query.splitlines()
            if not line.strip().lower().startswith("create index")
        ]).strip()
        # Run user's select query
        self.cursor.execute(user_query)
        user_result = self.cursor.fetchall()
        user_columns = [desc[0] for desc in self.cursor.description]
        user_df = pd.DataFrame(user_result, columns=user_columns)
        # Get correct result for Electronics
        self.cursor.execute("SELECT * FROM products WHERE category = 'Electronics';")
        sol_result = self.cursor.fetchall()
        sol_columns = [desc[0] for desc in self.cursor.description]
        sol_df = pd.DataFrame(sol_result, columns=sol_columns)
        # Compare results ignoring order
        condition = (
            set(tuple(row) for row in user_df.to_numpy()) == set(tuple(row) for row in sol_df.to_numpy())
            and set(user_df.columns) == set(sol_df.columns)
        )
        _dynamic_test(
            self,
            condition,
            "The query selects all columns from products where category = 'Electronics'.",
            "The query does not correctly select all columns from products where category = 'Electronics'."
        )

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 advanced SQL concepts including ACID properties, query optimization, indexing, triggers, window functions, and data control. This course is designed for learners with a solid SQL foundation who want to deepen their understanding and practical skills in optimizing queries and leveraging advanced database features.

Explore the foundational ACID properties of transactions, their practical implications, and how to manage transaction isolation and consistency in SQL databases.

Learn about different types of indexes, their internal mechanisms, and how to use them for optimizing SQL queries. Explore data caching and general strategies for query performance.

Master advanced SQL features including triggers, window functions, types of window functions, Data Control Language, views, and an introduction to NoSQL.

Challenge: Creating B-tree Index

Рішення