En este capítulo, se trabajará con las tablas `employees` y `department`. 



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_columns_are_correct(self):
        """Check that only first_name, last_name, and salary columns are selected."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        columns = [desc[0] for desc in self.cursor.description]
        expected_columns = set(["first_name", "last_name", "salary"])
        condition = set(columns) == expected_columns
        _dynamic_test(
            self,
            condition,
            "Query returns only first_name, last_name, and salary columns.",
            f"Returned columns are incorrect: {columns}."
        )

    def test_only_non_tech_departments(self):
        """Ensure all returned employees work in non-tech departments."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        columns = [desc[0] for desc in self.cursor.description]
        result_df = pd.DataFrame(rows, columns=columns)
        # Get all non-tech department names
        self.cursor.execute("SELECT name FROM department WHERE type = 'non-tech'")
        non_tech_departments = set(row[0] for row in self.cursor.fetchall())
        # Get mapping of employee to department
        self.cursor.execute("SELECT first_name, last_name, department FROM employees")
        emp_dept = {(row[0], row[1]): row[2] for row in self.cursor.fetchall()}
        # Check all returned employees are from non-tech departments
        failed = []
        for _, row in result_df.iterrows():
            emp = (row['first_name'], row['last_name'])
            dept = emp_dept.get(emp, None)
            if dept not in non_tech_departments:
                failed.append(emp)
        condition = len(failed) == 0
        fail_msg = "The following employees are not from non-tech departments: " + ", ".join([f"{f[0]} {f[1]}" for f in failed]) if failed else ""
        _dynamic_test(
            self,
            condition,
            "All employees in result are from non-tech departments.",
            fail_msg
        )

    def test_nested_query_filters_non_tech(self):
        """Ensure the nested query restricts to type = 'non-tech' departments."""
        with open("query.sql", "r") as file:
            query = file.read()
        # Check for the presence of type = 'non-tech' in the query
        condition = "type = 'non-tech'" in query
        _dynamic_test(
            self,
            condition,
            "Nested query filters for non-tech departments.",
            "Your query does not filter for non-tech departments in the nested query."
        )

    def test_salary_descending_order(self):
        """Check that results are ordered by salary descending."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        columns = [desc[0] for desc in self.cursor.description]
        result_df = pd.DataFrame(rows, columns=columns)
        salaries = result_df['salary'].tolist()
        condition = salaries == sorted(salaries, reverse=True)
        _dynamic_test(
            self,
            condition,
            "Results are ordered by salary descending.",
            "Results are not ordered by salary descending."
        )

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):
    import re
    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

Este curso es ideal para quienes ya poseen conocimientos básicos de SQL y desean profundizar en conceptos avanzados para crear consultas más potentes. A lo largo del curso, se familiarizarán con la agrupación de datos y el filtrado de datos agrupados. También aprenderán a trabajar con varias tablas simultáneamente, incluyendo cómo combinarlas. Además, explorarán los diferentes tipos de uniones de tablas y su aplicación práctica.

En esta sección, aprenderá a agrupar y gestionar datos de manera efectiva. Se presentará el operador GROUP BY para la agrupación de datos. Además, aprenderá a filtrar datos agrupados utilizando el operador HAVING.

Aprenderá sobre subconsultas anidadas, cómo crearlas y cómo utilizarlas de manera efectiva.

Aquí aprenderá cómo combinar varias tablas, lo que le permitirá trabajar con una tabla unificada y simplificar la creación de consultas.

Aprende los fundamentos del Lenguaje de Definición de Datos (DDL) y del Lenguaje de Manipulación de Datos (DML) en SQL, incluyendo cómo crear, modificar y eliminar objetos de base de datos, así como insertar, actualizar y eliminar datos dentro de las tablas.

Desafío: Empleados de Departamentos No Técnicos

Instrucciones breves

Solución