SQLite SELECT Query

SQLite is a serverless, popular, easy-to-use, relational database system that is written in c programming language. SQLite is a database engine that is built into all the popular devices that we use every day of our lives including TV, Mobile, and Computer.

As we know that we create the tables in the database and insert the data into them to store the data but, what is the use of storing the data without fetching the data? So let us learn how to retrieve the data from the database. Here comes the SELECT QUERY which is used to fetch the data from the database and in this article we are going to discuss SQLite SELECT Query. After reading this article we will gain knowledge about the Select Query.

SQLite SELECT Query

SELECT QUERY is used to retrieve the data from the database. The select query does not make any changes to the database it just fetches the data that we are asking for. Select query plays a major role in SQLite because you can not retrieve the data without it.

The basic syntax for the select query is the SELECT keyword followed by space, next FROM keyword and the TABLE NAME or the COLUMN NAMES end with semicolon.

Syntax:

SELECT column1, column2, columnN FROM table_name;

Here column1, column2 and columnN are the fields in our table.

Example: Fetch the Data by Using SELECT Query

We will create the table, insert the data and fetch the data. We will use SQLite select statement to fetch the data and the data is fetched in four ways as shown below:

1. Retrieving all the data
2. Retrieving the single column data
3. Retrieving the multiple columns data
4. Retrieving the data using alias

Creating the Students Table

We need the dabase to fetch the data. So, here we are going to create the students table with the columns stu_id, first_name, fees and email by using the below create syntax. In the syntax you can find that stu_id and fees are integer because they always hold the numbers, first_name and email are text because it contains alphabet.

CREATE TABLE students (
    "stu_id"    INTEGER,
    "first_name"    TEXT,
    "fees"    INTEGER,
    "email"    TEXT
);

Now the table structure is created as per our syntax and it is ready for inserting the data.

Output:

Inserting the Data

It is time to insert the data and we are inserting the 4 records. In the syntax you can observe that some values are written in the double quotes and single quotes right? then here a doubt arises that why are they written in inverted commas? and the answer is, as it is the text we write them in inverted commas.

insert into students(stu_id, first_name, fees, email)
VALUES
(1, 'Amulya', 50000, "ammuamulya@gmail"),
(2, 'Ram', 30000, "ramyerra@gmail"),
(3, 'Rani', 20000, "ranis@gmail"),
(4, 'Hari', 40000, "haribabu@gmail");

The data is inserted into the students table successfully and four rows are recorded.

Output:

Now, We will see some following examples of using SELECT Query:

1. Retrieving All the data

we can retrieve all the data using the “SELECT * ” . Syntax is select that is followed by asterisk ( * ), space, From keyword and the Table Name from which you are going to fetch the data.

syntax:

SELECT * FROM TABLE NAME;

Example:

SELECT * FROM students;

We can observe that we have retrieved whole data i.e the 4 records of the table by using select* and the fetched column include stu_id, first_name, fees and email.

Output:

2. Retrieving the Single Column Data

we can retrieve the single column’s data that want to fetch and it can be achieved by placing the column name in the place of ” * ” followed by from keyword and the table name.

Syntax:

SELECT  COLUMN NAME FROM TABLE NAME;

Example:

let us retrieve the data from the column i.e “first_name” from the students table to achieve that just place the first_name in the place of COLUMN NAME and students in the TABLE NAME that you find in the syntax.

SELECT first_name FROM students;

There in the output you can see the first_name column is fetched and it contains four records.

Output:

3. Retrieving the Multiple Columns Data

we can also fetch the data from multiple columns at a time by using the below syntax. In the syntax you can observe that there are multiple columns from 1 to N, from keyword followed by the TABLE NAME that you are going to fetch the data from.

Syntax:

SELECT COLUMN 1, COLUMN 2,...COLUMN N
FROM TABLE NAME;

Example:

let us retrieve the data from two columns thay are first_name and fees from students table. Youu need to just specify the column names in the place of COLUMN1, COLUMN2 that you find in the syntax and write the table name from which the data is to be retrieved.

SELECT first_name, fees
FROM students;

Here in the below output first_name and fees columns are retrieved.

Output:

4. Selecting Columns as Alias

we can also select the columns using the alias names i.e by using nick names. For example, we the humans have our original names and also the nick name in the same way we can use the alias names for our columns. In the syntax you can see that select keyword followed by column_name is the original name of the field, as keyword followed by the alias_name i.e the name by which you want to display the field, from followed by the table name from which the data is to be fetched.

Syntax:

SELECT column_name AS alias_name
FROM table_name

Example:

SELECT first_name AS stu_name FROM students;

And this is how the first_name column name is changed to stu_name by using the alias_ name keyword and such you can fetch the data of the columns even by aliasing.

Output:

Conclusion

This is how we fetch the data from the students table using the SELECT QUERY in SQLite in different ways, from the different columns, tables and this query plays the most important role because storing the data is not important if you can not fetch it. So, it is such important to know about SQLITE SELECT QUERY and this how it is used.