**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

Inicie uma jornada empolgante pelo SQL com Técnicas Avançadas em SQL. Aprenda sobre as importantes propriedades ACID que garantem a precisão dos dados em transações de banco de dados. Descubra métodos avançados para otimizar suas consultas e melhorar o desempenho do banco de dados. Explore recursos especiais do SQL, como funções de janela, gatilhos, Linguagem de Controle de Dados (DCL) e bancos de dados NoSQL. Isso ajudará você a lidar com a gestão moderna de bancos de dados com confiança e eficácia.

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.

Challenge: Creating B-tree Index

Solução