Summary  
This chapter explains how to use the OR operator to filter query results by matching any of multiple conditions in a SQL SELECT statement.

General domain of usage  
Database querying

The `OR` operator is used to filter records based on more than one condition, like if you want to return all countries from Asia but also those from Europe.

Here is the syntax of using the OR operator:

```sql
SELECT columns
FROM table
WHERE column = 'first_option' OR column = 'second_option';
```

Let's look at the example:

SELECT name, population, continent, region
FROM country
WHERE continent = 'Asia' OR continent = 'Europe';

import unittest
import psycopg2
import pandas as pd
import re

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_select_name_from_country_or_operator(self):
        """Check query retrieves correct country names from Asia or North America using OR operator."""
        with open("query.sql", "r") as file:
            query = file.read()
        # Reference solution
        solution_query = """
        SELECT name
        FROM country
        WHERE continent = 'Asia' OR continent = 'North America';
        """
        self.cursor.execute(solution_query)
        expected_rows = self.cursor.fetchall()
        expected_names = set([row[0].strip() for row in expected_rows])
        self.cursor.execute(query)
        user_rows = self.cursor.fetchall()
        user_names = set([row[0].strip() for row in user_rows])
        condition = user_names == expected_names
        missing = expected_names - user_names
        extra = user_names - expected_names
        if not condition:
            failure_msg = f"Expected country names: {sorted(expected_names)}, but got: {sorted(user_names)}.\\nMissing: {sorted(missing)}\\nExtra: {sorted(extra)}"
        else:
            failure_msg = ""
        _dynamic_test(
            self,
            condition,
            "Query returns the correct country names from Asia or North America.",
            failure_msg
        )

    def test_query_uses_where_and_or(self):
        """Check that WHERE and OR are used in the query."""
        with open("query.sql", "r") as file:
            query = file.read().lower()
        has_where = "where" in query
        has_or = "or" in query
        condition = has_where and has_or
        _dynamic_test(
            self,
            condition,
            "Query uses WHERE clause and OR operator.",
            "Query must use both WHERE clause and OR operator."
        )

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

This course is perfect for beginners ready to explore the world of SQL. Whether you're just starting out in database management or aiming to use SQL for your application development projects, this course covers the essentials. You'll quickly learn how to leverage the full potential of SQL, from querying and managing data to seamlessly integrating it into real-world applications. By the end of the course, you'll have the confidence and skills to solve practical problems with SQL and enhance your development process.

Using the OR operator

解答