Create a table using SQLite Database

“CREATE TABLE” statement is used to create a table in SQLite Database. SQLite Queries are same as SQL/MYSQL Database.

CREATE TABLE table_name(
column_name_1 data_type(size),
column_name_2 data_type(size),
column_name_3 data_type(size),
.
.
.
);
  • column_name specify the name of the column.
  • data_type specify that what type of data will a column hold i.e INTEGER, VARCHAR, DECIMAL etc.
CREATE TABLE Test(
"id" INTEGER primary key Autoincrement,
"name" VARCHAR,
"address" VARCHAR
);

Insert into table SQLite Database

“INSERT” statement is used to insert the values into the table.

Syntex of INSERT Statement: –

INSERT INTO table_name(
column_name1,
column_name2,
column_name3) VALUES(
value1,
value2,
value3 );
  • Here column_name specify the name of the column.
  • value specify the values to the associated columns.

INSERT INTO Example: –

INSERT INTO Test(
id,
name,
address) values(
1,
"JOHN",
"VA,USA"
);

SELECT Statement in SQLite Database: –

SELECT statement is used to select the data from the table in SQLite database.

Note: – “*” is used to select all the data in a table.

// Select statement in SQLite
SELECT * FROM Test;

 

SQLite UPDATE statement: –

SQLite UPDATE statement is used to update the existing record in the database. In UPDATE statement SET keyword is used to update the data.

Update: – 

SQLite UPDATE statement Example: –

// Update statement

UPDATE Test SET name ="SAMSHI VORRE" WHERE id=3;

 

SQLite WHERE Clause: –

SQLite WHERE Clause is used to filter the record from the table.

Example: –

// Where clause in SQLite database

SELECT * from Test WHERE name="JOHN";

DELETE statement in SQLite database: – 

DELETE statement is used to delete the record from the table in SQLite database.

Example: –

// Delete statement in SQLite

DELETE from Test WHERE name="JOHN";

 

AND & OR Operator in SQLite database: –

AND & OR statements are used to filter the records based on the condition check.

AND Operator: – AND Statement filters records if both the conditions are true i.e first condition and the second condition.

// AND Statement with SQLite

SELECT * from Test WHERE name="JOHN VAM" AND id="2";

 

OR Operator: – OR Statement is used to filter the records. it shows the result if one of the conditions either first or second condition is true.

// OR Statement in SQLite

SELECT * from Test WHERE name="JOHN VA" OR  id="2";

LIKE Operator in SQLite: –

Like operator is used to search the specific pattern. LIKE Operator is used with WHERE Clause.

// LIKE Operator In SQLite

SELECT * from Test WHERE name LIKE "S%";

Notes: – The “%” sign is used to define the wildcard both before and after the pattern. if Letter is used before “%” like “p%” then it will return column which starts with letter p here. and if the letter is used after “%” like “%P” then it will return column which ends with letter P here.

NOT Keyword: – NOT Keyword is used to select the column which does not start with letter %hi%

Example:-

// NOT LIKE Operator

SELECT * from Test WHERE name NOT LIKE "%SHI%";

 

JOIN in SQLite Database: –

SQLite joins are used to combine multiple rows from two or more tables.

INNER JOIN: – INNER JOIN is the most common join used in SQLite database. let’s take an example to understand inner join.

Table 1: – Test

Table 2 = Testing

Now apply Inner join query

Select Test.id,Testing.id,Test.name,Testing.name from Test inner join Testing on Test.id=Testing.id;

The result after applying Inner Join query: –

LEFT JOIN: – LEFT JOIN returns all the column from the left column in  SQLite database. let’s take an example to understand left join.

Table 1: – Test

Table 2 = Testing

Now apply LEFT join query

Select Test.id,Testing.id,Test.name,Testing.name from Test LEFT join Testing on Test.id=Testing.id;

The result after applying Inner Join query: –

Note: –  RIGHT JOIN and FULL JOIN are not supported in SQLite.

UNION operator in SQLite database: –

UNION operator is used to combining the result of two or more tables.

Table 1: – Test

Table 2 = Testing

Now Apply UNION Query

// UNION Query

Select Test.id, Test.name from Test UNION Select Testing.id ,Testing.name from Testing;

 

Result after executing the Query: –

ORDER BY keyword in SQLite database: –

ORDER BY keyword is used to sort the data either in Ascending or descending order.

ORDER BY Example: –

// ORDER BY Keyword in SQLite

SELECT  *  FROM Test ORDER BY name ASC;

 

 

 

If you really liked the article, please subscribe to our YouTube Channel for videos related to this article.Please find us on Twitter and Facebook.

Related Posts

Categories: