Summary  
This chapter explains how to filter rows that have no value by using the IS NULL operator in an SQL WHERE clause.

General domain of usage  
Relational database querying

If a column doesn't contain any data, it's considered `NULL`. To determine if a value is `NULL`, you don’t just check for equality. Instead, you use the `WHERE` clause with `IS NULL` in a `SELECT` statement to find rows where the column’s value is `NULL`.

The syntax for it looks like this: 

```sql
SELECT columns
FROM table 
WHERE column_name IS NULL;
```

Let's illustrate this with an example:




SELECT name
FROM country
WHERE region IS NULL;

import unittest
import pandas as pd
class TestTask(unittest.TestCase):
    @classmethod
    def setUpClass(cls):
        import psycopg2
        cls.connection = psycopg2.connect(
            host="localhost",
            user="agent",
            password="qwerty",
            dbname="testdb",
            sslmode="disable"
        )
        cls.cursor = cls.connection.cursor()

    def test_correct_columns_selected(self):
        """Check that only 'name' and 'capital' columns are selected."""
        with open("query.sql", "r") as f:
            user_query = f.read()
        self.cursor.execute(user_query)
        columns = [desc[0] for desc in self.cursor.description]
        expected_cols = ["name", "capital"]
        condition = columns == expected_cols
        _dynamic_test(
            self,
            condition,
            "Query selects only 'name' and 'capital' columns.",
            f"Query columns are {columns}, expected {expected_cols}."
        )

    def test_returns_only_null_population(self):
        """Check that only rows with population IS NULL are returned."""
        with open("query.sql", "r") as f:
            user_query = f.read()
        self.cursor.execute(user_query)
        rows = self.cursor.fetchall()
        if not rows:
            # No results, so nothing with NULL population
            condition = True
        else:
            # Check that every returned row corresponds to a country with population IS NULL
            for row in rows:
                name, capital = row
                self.cursor.execute(
                    "SELECT population FROM country WHERE name = %s AND capital = %s",
                    (name, capital)
                )
                pop = self.cursor.fetchone()
                if pop is None or pop[0] is not None:
                    condition = False
                    break
            else:
                condition = True
        _dynamic_test(
            self,
            condition,
            "All returned rows have population IS NULL.",
            "Some returned rows do not have population IS NULL."
        )

    def test_expected_rows_present(self):
        """Check that all expected countries with NULL population are present."""
        # Get expected rows
        self.cursor.execute(
            "SELECT name, capital FROM country WHERE population IS NULL ORDER BY name, capital"
        )
        expected = self.cursor.fetchall()
        # Get user rows
        with open("query.sql", "r") as f:
            user_query = f.read()
        self.cursor.execute(user_query)
        user_rows = self.cursor.fetchall()
        # Compare as sets of tuples
        expected_set = set(expected)
        user_set = set(user_rows)
        missing = expected_set - user_set
        extra = user_set - expected_set
        condition = (missing == set())
        fail_msg = ""
        if missing:
            fail_msg += f"Missing expected rows: {missing}. "
        if extra:
            fail_msg += f"Found unexpected rows: {extra}."
        _dynamic_test(
            self,
            condition,
            "All expected rows with NULL population are present.",
            fail_msg.strip()
        )

    def test_query_uses_is_null(self):
        """Check that the query uses 'IS NULL' in the WHERE clause for population."""
        with open("query.sql", "r") as f:
            user_query = f.read().lower().replace("\\n", " ")
        condition = ("where" in user_query and "population" in user_query and "is null" in user_query)
        _dynamic_test(
            self,
            condition,
            "Query uses IS NULL for population in WHERE clause.",
            "Query does not use 'IS NULL' for population in WHERE clause."
        )

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

Focuses on practical SQL skills for filtering data with WHERE, sorting results with ORDER BY, and summarizing information using GROUP BY and aggregate functions. Hands-on challenges reinforce each concept, preparing learners to analyze and summarize real-world datasets effectively.

Checking for NO Value

解答