Advanced Table Modifications in SQLite

Advanced Table Modifications in SQLite

Introduction

Welcome back to our SQLite course! In this lesson, we'll learn how to modify existing tables in our database. We'll start by opening SQLite from the Linux command line, then move on to making changes to our tables.

Starting SQLite

First, let's open SQLite from the Linux command line. We'll assume you have a database file named `mydb.sqlite` in your current directory. If you don't have this file yet, don't worry—SQLite will create it for you.

Open your terminal and type:

$ sqlite3 mydb.sqlite

You should see output similar to this:>

```

SQLite version 3.34.1 2021-01-20 14:10:07

Enter ".help" for usage hints.

sqlite>

This `sqlite>` prompt means you're now in the SQLite interactive mode and ready to run SQL commands.

Tip: Enabling Headers and Column Mode

By default, SQLite doesn't display the headers (column names) when you run `SELECT` queries. To enable headers so that they appear with your query results, use the following command:

sqlite> .headers on

For nicely formatted, aligned output, you can also set the output mode to `column`:

sqlite> .mode column

Tip: Displaying `NULL` Values

By default, SQLite may not differentiate between `NULL` values and empty strings in query results. To make `NULL` values visible in your output, you can use the `.nullvalue` command:

sqlite> .nullvalue "NULL"

This setting ensures that `NULL` values are displayed explicitly in your query results, making it easier to differentiate them from empty strings.

Viewing Your Tables

To see what tables are in your database, you can use the `.tables` command:

sqlite> .tables

If you don't see a `books` table, let's create one.

Important Note on Multi-Line Commands

When entering commands that span multiple lines in SQLite, you don't need to press `Shift+Enter` or any special key combination. Simply press `Enter` at the end of each line, and SQLite will continue the command on the next line. The command won't be executed until you finish with a semicolon (`;`) and press `Enter` again. This allows you to write complex SQL statements in a more readable format, which is especially useful for long queries and table definitions.

Creating the `books` Table

Let’s create the `books` table with the `publication_year` column included:

sqlite> CREATE TABLE books (

   ...> id INTEGER PRIMARY KEY,

   ...> title TEXT NOT NULL,

   ...> author TEXT NOT NULL,

   ...> publication_year INTEGER

   ...> );

Inserting Data with Explicit `NULL` Values

To ensure consistency across different SQLite environments, let's explicitly insert `NULL` values into the `publication_year` column. This approach will give us consistent results regardless of the system configuration:

sqlite> INSERT INTO books (title, author, publication_year) VALUES 

   ...> ('To Kill a Mockingbird', 'Harper Lee', NULL),

   ...> ('1984', 'George Orwell', NULL),

   ...> ('Pride and Prejudice', 'Jane Austen', NULL);

Displaying the Table Contents

Now that we've created the table and inserted some data, let's display the contents of our `books` table to see what it looks like:

sqlite> SELECT * FROM books;

Output:

id  title                  author          publication_year

-----------------------------------------------------------

1   To Kill a Mockingbird  Harper Lee      NULL

2   1984                   George Orwell   NULL

3   Pride and Prejudice    Jane Austen     NULL

Adding a New Column

Let's say we want to add a "genre" column to our books table. Here's how we can do it:

sqlite> ALTER TABLE books ADD COLUMN genre TEXT;

Displaying the Updated Table

After adding the new column, let's see the updated table structure:

sqlite> SELECT * FROM books;

Output:

id  title                  author          publication_year   genre

-------------------------------------------------------------------

1   To Kill a Mockingbird  Harper Lee      NULL               NULL

2   1984                   George Orwell   NULL               NULL

3   Pride and Prejudice    Jane Austen     NULL               NULL

Renaming the Table

Now, let's rename our "books" table to "library_books":

sqlite> ALTER TABLE books RENAME TO library_books;

To verify the rename, let's check the tables in our database:

sqlite> .tables

Output:

library_books

Finally, let's rename the table back to its original name, "books":

sqlite> ALTER TABLE library_books RENAME TO books;

And check the tables again to confirm:

sqlite> .tables

Output:

books

Exiting SQLite

To exit SQLite, you can type:

sqlite> .quit

This will return you to your Linux command prompt.

Conclusion

In this lesson, we've covered how to create and modify tables in SQLite, including adding new columns and renaming tables. By practicing these techniques, you’ll become more comfortable managing your databases and preparing them for more complex queries in the future. Stay tuned for our next lesson, where we'll dive into advanced querying techniques!

Image:  Gerd Altmann from Pixabay