Summary  
This chapter explains how to use the SQL SELECT statement to retrieve individual columns from a database table.

General domain of usage  
Relational database querying

**SQL** is a programming language for user interaction with databases, used to query, update, and manage databases.

Definition

A database is an organized structure designed to store, modify, and process large amounts of data.

### Why SQL Matters

SQL is essential for managing and querying databases efficiently. It is widely used due to its standardization, scalability, and ability to ensure data integrity. SQL's integration with other technologies makes it a versatile tool in various applications, supporting real-time data processing and decision-making.

Let's get started! In this course, we will work with a `country` database containing a `country` table. You can see it under the code editor on the right bottom corner.

### SQL Syntax Explanation

To retrieve data from a database, SQL uses the `SELECT` statement. Here's the basic syntax:

- **SELECT**: specifies the columns to retrieve;
- **FROM**: specifies the table from which to retrieve the data.

For example, to get the `sample` column from the `sample_table` table, the syntax is:

```sql
SELECT sample FROM sample_table;
```

The following query will return all entries in the `continent` column from the `country` table:

In our previous query, we utilized the `SELECT` to retrieve a **single continent column** from the **country table**.
It's important to note that you must specify the column name immediately after the `SELECT` keyword, and the `FROM` keyword specifies the name of the table from which we wish to retrieve data.

import unittest
import pandas as pd
import sqlite3

class TestTask(unittest.TestCase):
    @classmethod
    def setUpClass(cls):
        cls.connection = sqlite3.connect(":memory:")
        cls.cursor = cls.connection.cursor()
        # Create country table and insert test data
        cls.cursor.execute("""
        CREATE TABLE country (
            id INTEGER PRIMARY KEY,
            name TEXT,
            capital TEXT
        );
        """)
        capitals = [
            (1, "Japan", "Tokyo"),
            (2, "Latvia", "Riga"),
            (3, "Mexico", "Mexico City"),
            (4, "Netherlands", "Amsterdam"),
            (5, "Portugal", "Lisbon"),
            (6, "Sweden", "Stockholm"),
            (7, "Ukraine", "Kyiv"),
            (8, "USA", "Washington"),
            (9, "Thailand", "Bangkok"),
            (10, "Paraguay", "Asunción"),
            (11, "Philippines", "Manila"),
            (12, "New Zealand", "Wellington"),
            (13, "Norway", "Oslo"),
            (14, "Monaco", "Monaco"),
            (15, "Malta", "Valletta")
        ]
        cls.cursor.executemany("INSERT INTO country VALUES (?, ?, ?);", capitals)
        cls.connection.commit()

    def test_select_capital_column(self):
        """Check if the query selects only the 'capital' column from 'country' table."""
        with open("query.sql", "r") as file:
            query = file.read()
        try:
            self.cursor.execute(query)
            rows = self.cursor.fetchall()
            columns = [desc[0] for desc in self.cursor.description]
            condition = (columns == ["capital"])
            success_msg = "Query selects only the 'capital' column from 'country' table."
            failure_msg = f"Query should select only the 'capital' column, but got: {columns}"
        except Exception as e:
            condition = False
            success_msg = ""
            failure_msg = f"Query failed to execute: {e}"
        _dynamic_test(self, condition, success_msg, failure_msg)

    def test_capital_column_values(self):
        """Check if all expected capital values are present in the result."""
        with open("query.sql", "r") as file:
            query = file.read()
        expected_capitals = [
            "Tokyo", "Riga", "Mexico City", "Amsterdam", "Lisbon", "Stockholm", "Kyiv", "Washington", "Bangkok", "Asunción", "Manila", "Wellington", "Oslo", "Monaco", "Valletta"
        ]
        try:
            self.cursor.execute(query)
            rows = self.cursor.fetchall()
            result_capitals = [row[0] for row in rows]
            missing = [cap for cap in expected_capitals if cap not in result_capitals]
            condition = (len(missing) == 0)
            success_msg = "All expected capital values are present in the result."
            failure_msg = f"Expected capital(s) not found in result: {missing}"
        except Exception as e:
            condition = False
            success_msg = ""
            failure_msg = f"Query failed to execute: {e}"
        _dynamic_test(self, condition, success_msg, failure_msg)

    def test_row_count_matches(self):
        """Check if the number of rows matches the number of countries in the table."""
        with open("query.sql", "r") as file:
            query = file.read()
        try:
            self.cursor.execute("SELECT COUNT(*) FROM country;")
            expected_count = self.cursor.fetchone()[0]
            self.cursor.execute(query)
            rows = self.cursor.fetchall()
            condition = (len(rows) == expected_count)
            success_msg = "Number of rows matches the number of countries in the table."
            failure_msg = f"Expected {expected_count} rows, but got {len(rows)}."
        except Exception as e:
            condition = False
            success_msg = ""
            failure_msg = f"Query failed to execute: {e}"
        _dynamic_test(self, condition, success_msg, failure_msg)

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

Learn the core fundamentals of SQL and relational databases. Understand how databases store and organize data, write basic SQL queries, work with tables and perform essential operations. Build a strong foundation for working with databases in backend development.

Retrieving Individual Columns

What is SQL?

Database

Why SQL Matters

SQL Syntax Explanation

Oplossing