Here we will learn sqlite select query with example and how to use sqlite select statement to get data from single table or multiple tables with example.
In SQLite SELECT statement is used to get data from tables in database. Generally, sqlite select statement will return result set in the form of table and by using sqlite select statement we can perform simple calculations or multiple expressions based on our requirements.
SQLite SELECT is a read-only command and it will perform all the operations like standard SQL select statement.
Generally, in SQLite select statement the returned values are derived from the contents of database, but SELECT can also be used to return the value of simple expressions.
Following simple example of SQLite select statement with simple expressions.
SELECT 1+1, 'abc' || 'xyz'
The above SQLite select statement example gives the output as following
Following is the syntax of using sqlite select statement to query data from tables in database.
SELECT [ALL | DISTINCT] result [FROM table-list]
[GROUP BY expr-list]
[ORDER BY sort-expr-list]
[LIMIT integer [(OFFSET|,) integer]]
If you observe above sqlite select statement syntax we defined all possible ways of using, select statement in our databases to get data based on our requirements. We will learn each property in detailed in next chapters.
DISTINCT - If we use distinct keyword in our sqlite select statement it returns only distinct rows of data.
ALL - If we use ALL keyword in select statement it returns all the rows of data even if it is duplicated.
table-list - It is a list of tables from which you want to get data.
WHERE expr - The WHERE expr is used to define our custom conditions to get required data from tables.
GROUP BY expr-list - The GROUP BY expr-list in SQLit is used to combine one or more rows of result into a single row of output. This is especially useful when the result contains aggregate functions.
HAVING expr - The HAVING expr is similar to WHERE except that HAVING applies after grouping has occurred.
ORDER BY sort-expr-list - The ORDER BY sort-expr-list causes the output rows to be sorted.
LIMIT integer - The LIMIT integer is used to set limit on number of rows returned in the result. The optional OFFSET integer following LIMIT specifies how many rows to skip at the beginning of the result set.
The only required item in a SELECT statement is the result, which can be one of the following:
Following is the simple example of using sqlite select statement.
SELECT a, b FROM test;
If you give sqlite select statement as follows it gives error:
SELECT a+1, b+1 FROM test;
Here in SQLite select statement FROM clause is an optional if you are not fetching any data from database and you just giving expression or any SQLite functions like as shown below.
sqlite> SELECT (60 * 60 * 24);
sqlite> SELECT random();
For illustrating use of SELECT statement with tables, let’s create table called emp_master as follows:
CREATE TABLE emp_master
(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
Let’s insert some records in emp_master table like as shown below
INSERT INTO emp_master
Let’s look at simple example of SQLite SELECT statement for fetching name of employee and salary of employee from emp_master table.
sqlite> SELECT first_name, salary FROM emp_master;
If you want to fetch data of all column of employee table, then use * instead of writing each field like as shown below.
sqlite> SELECT * from emp_master;
emp_id first_name last_name salary
---------- ---------- ---------- ----------
1 Honey Patel 10100
2 Shweta Jariwala 19300
3 Vinay Jariwala 35100
We can also get data from multiple tables by using comma separated list of tables with FROM clause in SQLite as follows. To check this we will create another table called dept_master like as shown below.
CREATE TABLE dept_master(dept_id INTEGER PRIMARY KEY AUTOINCREMENT, dept_name TEXT);
Once dept_master table created insert some values like as shown below.
INSERT INTO dept_master(dept_name)
Now let’s look at the example of fetching emp_name and its department name from emp_master, dept_master table.
sqlite>SELECT e.first_name, d.dept_name FROM emp_master e, dept_master d;
Honey Quality Co
Shweta Quality Co
Vinay Quality Co
Here e and d are alias name given to emp_master and dept_master table respectively.