Executing Your First SQL Commands with SQLite

SQLite is a lightweight, disk-based database that doesn’t require a separate server process. It's ideal for small-scale projects and applications, prototyping, learning SQL, or even storing local data in mobile and desktop applications. In this article, we will guide you through the process of executing your first SQL commands using SQLite.

Installing SQLite

Before you start executing SQL commands, you need to have SQLite installed on your system. You can download it from the official SQLite website. Installation instructions are straightforward and vary slightly based on operating systems. After successful installation, make sure that the SQLite executable is added to your system's PATH so you can run it from the command line.

Running SQLite

Once SQLite is installed, you can start the SQLite command-line shell by entering the following command in your terminal:

sqlite3

This will launch the SQLite shell where you can execute SQL commands.

Creating a Database

In SQLite, databases are stored in files. To create a new database, run the following command within the SQLite shell:

.open my_database.db

This command will open a new or existing database named my_database.db.

Creating a Table

Once your database is ready, you need to create a table. SQLite's CREATE TABLE statement is used to create a new table.

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER
);

This SQL command creates a table named users with three columns: id, name, and age. The id column is an integer that auto-increments with each new record and acts as the table's primary key, making each record unique.

Inserting Data

To add data to the users table, use the INSERT INTO command. Here's how you can insert a new record:

INSERT INTO users (name, age) VALUES ('Alice', 30);
INSERT INTO users (name, age) VALUES ('Bob', 25);

These commands add two records to the users table with the names Alice and Bob, and ages 30 and 25, respectively.

Querying Data

To view the data stored in the table, use the SELECT statement. Here's a basic query to retrieve all records from the users table:

SELECT * FROM users;

The result will display all columns and rows stored in the users table. To retrieve specific columns, specify them as follows:

SELECT name FROM users;

This code will display only the names of all users in the users table.

Updating Data

If you need to update existing records, use the UPDATE statement. Here's how you can change Alice's age to 31:

UPDATE users SET age = 31 WHERE name = 'Alice';

This command selects the record with the name Alice and updates her age to 31.

Deleting Data

To remove records, the DELETE statement is used. For example, to delete Bob's record:

DELETE FROM users WHERE name = 'Bob';

This operation will remove Bob's entry from the users table.

Conclusion

With these simple SQL commands, you have learned how to perform basic database operations: creating databases and tables, inserting, querying, updating, and deleting data. SQLite is a powerful tool for learning SQL and managing data in lighter weight applications. As you gain more experience, you can explore advanced features and functions that SQLite offers. Happy coding!