Summary  
This chapter explains how to modify a table’s schema using the ALTER operation to add or remove columns and how to insert new rows into a table using the INSERT operation, detailing their syntax and usage.

General domain of usage  
Relational database management

Let's imagine a situation where we need to **add a column to an existing table**. It wouldn't be right to delete the table (especially if it already contains some data) and then create a new table, filling it again with data.

Therefore, in this chapter, we will look at the `ALTER` operation.



`ALTER`: Used to change the structure of existing database objects, such as adding or removing columns from a table.

Definition

```sql
CREATE TABLE library (
  id INT PRIMARY KEY,
  title VARCHAR(50) NOT NULL,
  author VARCHAR(50),
  pages INT
);

ALTER TABLE library ADD price DECIMAL DEFAULT 300;

ALTER TABLE library DROP COLUMN price;
```

As you can see, this is the script for creating a table from the **previous chapter**.

Next, there are two `ALTER` operations. The first operation adds a `price` column to the table, setting the **default value** to `300` for this column. The second operation removes this column:



```sql
ALTER TABLE table_name ADD/DROP column_name DATA_TYPE;
```

Using the `ALTER` statement, you can perform various **schema-level operations** on a table, such as adding or removing constraints, renaming, changing data types, and adding or dropping indexes.

Note

Let's move on to another operation, namely the insertion operation.



`INSERT`: Used to add new rows to a table.

To use `INSERT`, we need to specify into which columns we want to add values. 

Here's what the syntax of this statement looks like:

```sql
INSERT INTO library (id, title, author, pages) VALUES 
  (1, 'CAMINO GHOSTS', 'John Grisham', '213'),
  (2, 'FUNNY STORY', 'Emily Henry', '341');
```

This snippet is from the previous chapter, showing how to insert data into the `library` table.

Here's a breakdown:

1. Start with `INSERT INTO`, followed by the table name;
2. Specify the column names in parentheses;
3. Use `VALUES` to list the data in the same order as the columns;
4. Ensure data types match the columns;
5. Close parentheses and separate rows with commas.

The general syntax is:

```sql
INSERT INTO table_name (column1_name, column2_name) VALUES
  (column1_value, column2_value),
  (column1_value, column2_value),
  ...;
```

**Don't forget about the semicolon in the end!**

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()

        with open("query.sql", "r") as file:
            full_query = file.read()

        # Execute only the user's part, before the read-only section
        user_query = full_query.split("-- Please do not modify the code below!")[0].strip()
        cls.cursor.execute(user_query)
        cls.connection.commit()

    def test_country_column_added(self):
        """Check if 'country' column was added to employees with correct data type."""
        self.cursor.execute("""
            SELECT column_name, data_type, character_maximum_length
            FROM information_schema.columns
            WHERE table_name = 'employees' AND column_name = 'country';
        """)
        result = self.cursor.fetchone()
        condition = (
            result is not None and
            normalize_text(result[0]) == 'country' and
            normalize_text(result[1]) in ['character varying', 'varchar'] and
            result[2] == 50
        )
        _dynamic_test(
            self,
            condition,
            "'country' column exists with VARCHAR(50) type.",
            "'country' column is missing or has incorrect type/length."
        )

    def test_inserted_rows(self):
        """Check if both specified rows were inserted correctly into employees."""
        self.cursor.execute("SELECT * FROM employees ORDER BY employee_id;")
        rows = self.cursor.fetchall()
        columns = [desc[0] for desc in self.cursor.description]
        result_df = pd.DataFrame(rows, columns=columns)

        expected_data = [
            {
                'employee_id': 1,
                'first_name': 'Emily',
                'last_name': 'Torres',
                'department': 'Operations',
                'salary': 80000,
                'country': 'United Kingdom'
            },
            {
                'employee_id': 2,
                'first_name': 'David',
                'last_name': 'Bobr',
                'department': 'Engineering',
                'salary': 95000,
                'country': 'Poland'
            }
        ]

        missing_rows = []
        for expected in expected_data:
            subset = {k: expected[k] for k in result_df.columns if k in expected}
            mask = (result_df[list(subset.keys())] == pd.Series(subset)).all(axis=1)
            if not mask.any():
                missing_rows.append(subset)

        condition = len(missing_rows) == 0 and len(result_df) == 2
        failure_msg = (
            "Missing or incorrect rows: " + str(missing_rows)
            if missing_rows
            else "Expected exactly 2 rows in employees table."
        )
        _dynamic_test(
            self,
            condition,
            "Both specified rows are inserted correctly.",
            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

This course is perfect for those who already have a basic understanding of SQL and want to delve into more advanced concepts to craft more powerful queries. Throughout the course, you will become familiar with data grouping and filtering grouped data. You will also learn how to work with multiple tables simultaneously, including how to combine them. Additionally, you will explore different types of table joins and how to apply them in practice.

In this section, you will learn how to group and manage data effectively. We will introduce the GROUP BY operator for grouping data. Additionally, you will learn how to filter grouped data using the HAVING operator.

You will learn about nested subqueries, how to create them, and how to use them effectively.

Here, you will learn how to combine multiple tables, allowing you to work with a unified table and simplify query creation.

Learn the fundamentals of Data Definition Language (DDL) and Data Manipulation Language (DML) in SQL, including how to create, modify, and delete database objects, as well as how to insert, update, and delete data within tables.

ALTER and INSERT Operations

Instructions

Solution

employee_id	first_name	last_name	department	salary	country
1	Emily	Torres	Operations	80000	United Kingdom
2	David	Bobr	Engineering	95000	Poland