この章では、`employees` および `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

実際のデータ作業に役立つ高度なSQL技術で知識をさらに深めます。GROUP BYやHAVINGを使用したデータのグループ化とフィルタリング、ネストされたサブクエリの記述、INNER、LEFT、RIGHT、FULL JOINによるテーブル結合を学びます。また、DDL文によるテーブルの作成・変更・修正、DML文によるレコードの挿入・更新・削除も行います。



GROUP BY句を使用してデータをグループ化し、COUNT、SUM、AVGなどの集約関数で要約する方法を学習します。HAVING句でグループ化された結果をフィルタリングし、SQLクエリ内で正しい句の順序を適用する方法を習得します。



WHERE句およびFROM句でネストされたサブクエリを記述し、より複雑な問いに対応する方法を学習します。UNION句を使用して複数のテーブルからのクエリ結果を結合する方法を習得します。



INNER、LEFT、RIGHT、FULL 各種 JOIN を使用して複数のテーブルを結合する方法を学習します。複数のテーブルにまたがる関連データを単一の統合結果として取得するための JOIN の記述を実践します。

SQLにおけるDDLおよびDMLの基本を学習。CREATEと制約によるテーブル作成から、ALTER、DROP、TRUNCATEによるテーブルの変更までを網羅。INSERT、UPDATE、DELETE文を用いたレコードの挿入、更新、削除についても解説。

チャレンジ：非技術部門従業員

チャレンジ：非技術部門従業員

簡単な手順

解答