I hope you reached this page while going through google search results or by accessing the direct link from any other community or social networking pages. And I assume the content you were searching for is somewhat related to databases.

Let me give you a good introduction about this article so that you can decide whether you want to stay on this page till it reaches the end or to continue with your voyage to find something better related to database programming 🙂

In this article, I am going to talk about the database and how to connect to a database and create and access a set of data from the database using some SQL commands.

First, we will see how to create a database using SQLite database engine.

Once the database is created we will see how to create a table and add some data to it.

And once we create a set of data, we will see how to access them using SQL commands.

I believe now you got what we are going to discuss here and it is somewhat a beginners level article. If you are good with the introduction given? You can move ahead – Good Luck!

Creating a Database using SQLite

  1. Download the binary file specific to your operating system from here (In my case I am using Windows).
  2. After extracting the ZIP file start program “sqlite3.exe”

Naming the Database

Say we have to record details of students present in a school and for this, I am going to name the database as “students.db” (You can keep any name for the database, even without the “.db” extension).

sqlite> .open student.db

Once you execute the above command a new database file will be created in the current working directory.

Creating a new Table

Here we are going to store some details specific to students. This includes the register number, name, and age. We don’t need too many details at this moment.

sqlite> create table student (
   ...>         register_number text primary key not null,
   ...>         name text not null,
   ...>         age integer not null);

Once we execute the above create table command, a new table named student will be added to the student database. I hope you already have some insight about the create table command and the data types and the concept of primary key.

Adding Data to Table

I am going to insert three student records into this table. This is pretty much simple and here I am going to show that.

sqlite> insert into student(register_number, name, age)
   ...> values ('1001', 'Tony', 10),
   ...>         ('1002', 'Wanda', 11),
   ...>         ('1003', 'Natasha', 9),
   ...>         ('1004', 'Peter', 12);

The above command once executed in the command line will create four records with unique registration number and inserts them into the student table.

Reading Data from Table

Now it is time to read from the student table. We will be issuing a select command for this.

sqlite> select
   ...>         register_number as student_id,
   ...>         name as display_name,
   ...>         age
   ...> from
   ...>         student nolock;

This will return the records which are present in the table and log it in the console and you can see the records and its value in the console itself.

Storing the Details in an External File

Say you have created 100+ student records in the table and now you have to send the copy of this details to school management. This is easy with SQLite console

Just type in the following command.

.excel

And then you issue the above select command. Once the command is finished it will open the details in a CSV file which you can save it somewhere or send it to someone for later usage.

Hope you had a good time reading this article and understood what we have discussed. This is just a beginning and in the next article, we will see how we can connect to this student database from a computer program and how to render the data as a standalone HTML page.

Stay Tuned!

Thank you for reading this article. If you have any suggestion – let it be anything related to my writing or the content or doubts, leave it as a comment here 🙂

Thanks

Seyed Sahil

Advertisement