SQL: A Guide to Create a Relational Database with SQLite (and Export to Excel)

SQL (Structured Query Language), which could also be pronounced as sequal, is a programming language used to manage and manipulate data stored in a relational database management system. SQL syntax is similar to the English language, which makes it relatively easy to write, read, and interpret. This guide will walk you through creating an SQL table using SQLite and exporting the table data to an Excel spreadsheet.

What is SQLite?

SQLite is a popular open-source SQL database. Unlike other database systems such as MySQL or PostgreSQL, SQLite can store an entire database in a single file. One of the most significant advantages it provides is that all of the data can be stored locally without having to connect your database to a server. SQLite is commonly used in mobile apps, desktop applications, and websites.

Setting Up SQLite

Before we dive into creating a table, you need to have SQLite installed on your system. Here’s how you can get started:

1. Install SQLite

• Download the precompiled binaries of your operating system from the SQLite website: Download here
  

• Unzip into a folder
  

2. Access SQLite

• Open your terminal or command prompt.
  

• To start the SQLite command-line interface, type sqlite3 followed by the name of your database file. If the file does not exist, SQLite will create it for you.
  

sqlite3 Database_file.db

• This command opens the Database_file.db database (or creates it if it doesn't exist).
• Enter .help to browse commands usage hints.

Step 1: Creating a Table in SQLite

Once you're inside SQLite, the first step is to create a table. A table is the structure that stores your data, consisting of rows (records) and columns (fields). We will be creating a table for tourist database records.

Here’s the basic SQL syntax to create a table:

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  ...
);

In SQLite, the most common datatypes are:

• INTEGER for integer values
• TEXT for text strings
• REAL for floating-point values
• DATE for date values in the format of (yyyy-MM-dd)

Let's create the table to store information about the tourists.

CREATE TABLE Tourists (
  ID INTEGER,
  Name TEXT,
  Age INTEGER,
  Destination TEXT,
  Departure DATE
);

Step 2: Inserting Data into the Table

Once the table is created, you can insert data into it. The SQL command to insert data into a table is as follows:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Let’s insert a few records into our Tourists table:

INSERT INTO Tourists (ID, Name, Age, Destination, Departure)
VALUES (1, 'James Stone', 35, 'Califonia', '2024-12-15');
INSERT INTO Tourists (ID, Name, Age, Destination, Departure)
VALUES (2, 'Steve Jones', 29, 'Florida', '2024-12-20');

You can also insert multiple records in one go using multiple VALUES clauses:

INSERT INTO Tourists (ID, Name, Age, Destination, Departure)
VALUES (3, 'Jack Cage', 33, 'New York', '2024-12-20'),
(4, 'Mark Tyson', 56, 'New York', '2024-12-29'),
(5, 'John Dep', 50, 'Texas','2025-01-08'),
(6, 'James Brook', 45, 'Texas','2025-01-10');

Step 3: Viewing the Data

Once you've inserted data, you can retrieve it using a simple SELECT query:

SELECT * FROM Tourists;

This query retrieves all rows and columns from the Tourists table.

Step 4: Updating Data

To modify existing data, you can use the UPDATE statement:

UPDATE Tourists
SET Name = 'Johnny Dep'
WHERE ID = 5;

With this, we updated John Dep's name to 'Johnny Dep'.

Step 5: Deleting Data

To remove records, use the DELETE statement:

DELETE FROM Tourists
WHERE ID = 6;

This command deletes the tourist named James Brook from the Tourists table.

Step 6: Altering Data in the Table

To make changes to an existing table, we use the ALTER statement:

ALTER TABLE Tourists
ADD COLUMN Phone INTEGER;

This command adds a new column Phone to the table.

• NULL is a special value in SQL that represents missing or unknown data. Here, the rows that existed before the column was added have NULL (∅) values for Phone.

• Now let's update the Phone column created using the UPDATE statement.
  

UPDATE Tourists
SET Phone = 09011111111
WHERE ID = 1;
UPDATE Tourists
SET Phone = 08022222222
WHERE ID = 2;
UPDATE Tourists
SET Phone = 07033333333
WHERE ID = 3;
UPDATE Tourists
SET Phone = 08011111111
WHERE ID = 4;
UPDATE Tourists
SET Phone = 09022222222
WHERE ID = 5;

Step 7: Exporting the Table

SQLite allows this by exporting data to a CSV (Comma-Separated Values) file, which can be opened directly in Excel for further analysis or reporting.

• Add the headers i.e. column names

.headers on

• Set the Output Mode to CSV with the command:

.mode csv

• Set the Output File you want to export.

.output Tourists.csv

• Run a SELECT query to fetch the data you want to export.

SELECT * FROM Tourists;

• Reset the output back to the terminal and quit SQLite:

.output stdout
.quit

• Locate the Tourists.csv file and open it in Excel. Alternatively, open Excel, go to File > Open, and select the CSV file.

Conclusion

SQLite is an easy-to-use and lightweight database engine, perfect for applications where simplicity and portability are key. In this guide, we covered how to create a table, insert data, and export the table data to an Excel-compatible CSV file. This makes it easier to share and analyze data outside the SQLite environment.

Thank you for reading my blog.😊😊

If you need further assistance, feel free to reach out in the comment or hit me up on Twitter. You can also follow me on GitHub. My DM's are open and love discussions on cloud topics!😁

: Oluwatofunmi Emmanuel Oluwaloseyi