In dit hoofdstuk werk je met de tabellen `employees` en `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

Deze cursus is ideaal voor wie al een basiskennis van SQL heeft en zich wil verdiepen in geavanceerdere concepten om krachtigere query's te maken. Tijdens de cursus maak je kennis met het groeperen van gegevens en het filteren van gegroepeerde data. Je leert ook hoe je met meerdere tabellen tegelijk werkt, inclusief het combineren ervan. Daarnaast verken je verschillende soorten joins tussen tabellen en hoe je deze in de praktijk toepast.

In deze sectie leert u hoe u gegevens effectief kunt groeperen en beheren. We introduceren de GROUP BY-operator voor het groeperen van gegevens. Daarnaast leert u hoe u gegroepeerde gegevens kunt filteren met behulp van de HAVING-operator.

U leert over geneste subquery's, hoe u deze maakt en hoe u ze effectief gebruikt.

Hier leert u hoe u meerdere tabellen kunt combineren, zodat u met een samengestelde tabel kunt werken en het opstellen van query's kunt vereenvoudigen.

Leer de basisprincipes van Data Definition Language (DDL) en Data Manipulation Language (DML) in SQL, inclusief het aanmaken, wijzigen en verwijderen van databaseobjecten, evenals het invoegen, bijwerken en verwijderen van gegevens binnen tabellen.

Uitdaging: Niet-Technische Afdelingsmedewerkers

Korte instructies

Oplossing