Dans ce chapitre, utilisation des tables `employees` et `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

Ce cours s'adresse à ceux qui possèdent déjà une compréhension de base du SQL et souhaitent approfondir des concepts plus avancés afin de concevoir des requêtes plus puissantes. Tout au long du cours, vous vous familiariserez avec le regroupement des données et le filtrage des données groupées. Vous apprendrez également à travailler avec plusieurs tables simultanément, y compris comment les combiner. De plus, vous explorerez différents types de jointures de tables et leur application pratique.

Dans cette section, vous apprendrez à regrouper et à gérer les données de manière efficace. Nous présenterons l'opérateur GROUP BY pour le regroupement des données. De plus, vous découvrirez comment filtrer les données regroupées à l'aide de l'opérateur HAVING.

Vous apprendrez les sous-requêtes imbriquées, leur création et leur utilisation efficace.

Ici, vous apprendrez à combiner plusieurs tables, ce qui vous permettra de travailler avec une table unifiée et de simplifier la création de requêtes.

Apprenez les principes fondamentaux du Data Definition Language (DDL) et du Data Manipulation Language (DML) en SQL, y compris la création, la modification et la suppression d’objets de base de données, ainsi que l’insertion, la mise à jour et la suppression de données dans les tables.

Défi : Employés des Départements Non Techniques

Instructions brèves

Solution